Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

ORACLEÖÐSQLÈ¡×îºóÒ»Ìõ¼Ç¼µÄ¼¸ÖÖ·½·¨

  ÔÚETL¹ý³ÌÖУ¬¾­³£»áÅöµ½È¡½á¹û¼¯µÄ×îºó»ò×îǰһÌõ¼Ç¼¡£ÈçÈ¡»îÆÚ´æ¿îµÄµ±Ç°ÀûÂÊ£¬¿ª»§½ð¶î£¬Ð­¶¨ÀûÂʵȡ£Èç¹û²»ÓÃLOOKUPµÄ·½Ê½£¬Èçͨ¹ýÓαêÈ¡»òÕßETL¹¤¾ßLOOKUP×é¼þʲôµÄ£¬ÔÚÒ»ÌõSQLÀïʵÏÖ£¬Ä¿Ç°ÊµÏÖÓм¸ÖÖ·½·¨¡£
1.ÒÔʱ¼ä»òÆäËû×ֶηÖ×éºóÔÚ×ÔÁ¬×Ô¼º£¬ÕâÑù²»½ö¿ÉÒÔ´ø³öÐèÒªLOOKUPµÄ×ֶΣ¬»¹¿ÉÒÔ´ø³öÆäËûÐèÒªµÄ×ֶΡ£
SELECT A.CDDPTY CDDPTY,A.CDCURR CDCURR,A.CDVLDT CDVLDT,  
             A.CDYRAT CDYRAT  
from DCPPDATA.TBBFMCDRT A INNER JOIN   
           (SELECT B.CDDPTY,B.CDCURR,MAX(B.CDVLDT) CDVLDT  
             from DCPPDATA.TBBFMCDRT B  
             GROUP BY B.CDDPTY, B.CDCURR) C   
             ON A.CDDPTY =C.CDDPTY  
             AND A.CDCURR =C.CDCURR  
             AND A.CDVLDT =C.CDVLDT 
2.ÓÃROW_NUMBER() OVER(ORDER BY filedName)
SELECT B.CDDPTY,B.CDCURR,  
             ROW_NUMBER() OVER(ORDER BY B.CDVLDT DESC)  
             from DCPPDATA.TBBFMCDRT B  
             WHERE ROWNUM = 1 


Ïà¹ØÎĵµ£º

oracle¸´Ï°£¨Ò»£© Ö®OracleÌåϵ½á¹¹

      ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬Ô­À´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·­Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
      µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......

ORACLE JOBS

³£ÒªoracleÊý¾Ý¿â¶¨Ê±µÄ×Ô¶¯Ö´ÐÐһЩ½Å±¾£¬»ò×öÊý¾Ý¿â±¸·Ý£¬»ò×öÊý¾ÝµÄÌáÁ¶£¬»ò×öÊý¾Ý¿âµÄÐÔÄÜÓÅ»¯£¬°üÀ¨Öؽ¨Ë÷ÒýµÈµÈµÄ¹¤×÷,ÕâʱÐèÒªÓõ½Ò»¸öº¯Êýdbms_job.submit£¬À´Íê³ÉOracle¶¨Ê±Æ÷Jobʱ¼äµÄ´¦ÀíÉÏ¡£Ê¹ÓÃdbms_job.submitÕâ¸öº¯Êý£¬ÎÒÃÇÖ»ÐèÒª¿¼ÂÇÁ½¸öÊÂÇ飺°²ÅÅijһÈÎÎñ£¬ºÍ¶¨ÖÆÒ»¸öÖ´ÐÐÈÎÎñµÄʱ¼äµã¡£µ«×îÖØÒªÒ²ÊÇ× ......

oracleÖеÄÉÁ±í¹¦ÄÜ

1.Èç¹ûÄãÎóɾ³ýÁËÊý¾Ý,²»Óú¦ÅÂ,¶Ìʱ¼äÄÚ»¹ÊÇ¿ÉÒÔ»Ö¸´µÄ.
select * from (select * from claim_monitor_dispatch as of timestamp to_date('20091203 08:30:00','yyyymmdd hh24:mi:ss')
)
where to_char(created_date,'yyyymmdd')='20090707'
2.Îóɾ³ý±íÖ®ºóµÄ»Ö¸´,»Ö¸´ÕûÕűí
 flashback table name before dr ......

ÀûÓÃOracleµÄutl_smtp ·¢ËÍÓʼþ£¨¹«ÍøÓʼþºÍ·þÎñÆ÷£©

CREATE OR REPLACE PROCEDURE send_mail(  
     p_recipient VARCHAR2, -- Óʼþ½ÓÊÕÈË  
     p_subject   VARCHAR2, -- Óʼþ±êÌâ  
     p_message   VARCHAR2  -- ÓʼþÕýÎÄ  
) ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ