oracle ʱ¼ä²î
//¼ÆËãºÁÃë²î(Á½¸ödateÀàÐ͵ÄÏà¼õΪÌìÊý²î±ð£¬È»ºóת»»ÎªºÁÃë)
select ceil(to_date('209-11-17 13:00:12','yyyy-mm-dd hh24:mi-ss')-to_date(2009-11-18 14:00:12','yyyy-mm-dd hh24:mi-ss') )from dual;
//¼ÆËãÏà²îÔ·Ý
select (EXTRACT(year from to_date('209-11-17','yyyy-mm-dd'))-EXTRACT(year from to_date('2009-11-18','yyyy-mm-dd') ))*12+(EXTRACT(month from to_date('209-11-17','yyyy-mm-dd'))-EXTRACT(month from to_date('2009-11-18','yyyy-mm-dd') )) from dual;
//ͨ¹ýʱ¼ä´ÁÔËËã
select to_timestamp('2009-11-17 19:20:12 234','yyyy-mm-dd hh24:mi:ss ff')-to_timestamp('2009-11-16 11:12:34 167','yyyy-mm-dd hh24:mi:ss ff') from dual;
·µ»ØÖµÎª+000000010 00:02:24.00000000 ×Ö·û´®Òª×ª»»ÎªºÁÃëÊý×Ö£¬×Ô¶¨Ò庯ÊýʵÏÖ
//×Ô¶¨ÒåÓÃʱ¼ä´ÁÔËË㺯Êý
create or replace function TIME_INTERVAL(endTime varchar2,startTime varchar2)
return number
IS
p_1 varchar2(40);
begin
p_1 := to_timestamp(endTime,'yyyy-mm-dd hh24:mi:ss ff')-to_timestamp(startTime,'yyyy-mm-dd hh24:mi:ss ff');
return trunc(to_number(substr((p_1),1,instr(p_1,' '))))*24*60*60+to_number(substr((p_1),instr((p_1),' ')+1,2))*60*60+to_number(substr((p_1),instr((p_1),' ')+4,2))*60+to_number(substr((p_1),instr((p_1),' ')+7,2));
end;
Ïà¹ØÎĵµ£º
1. Ñ¡ÓÃÊʺϵÄOracleÓÅ»¯Æ÷
¡¡¡¡OracleµÄÓÅ»¯Æ÷¹²ÓÐ3ÖÖ£º
¡¡¡¡a. RULE (»ùÓÚ¹æÔò)
¡¡¡¡b. COST (»ùÓڳɱ¾)
¡¡¡¡c. CHOOSE (Ñ¡ÔñÐÔ)
¡¡¡¡ÉèÖÃȱʡµÄÓÅ»¯Æ÷£¬¿ÉÒÔͨ¹ý¶Ôinit.oraÎļþÖÐOPTIMIZER_MODE²ÎÊýµÄ¸÷ÖÖÉùÃ÷£¬ÈçRULE£¬COST£¬CHOOSE£¬ALL_ROWS£¬FIRST_ROWS . Ä㵱ȻҲÔÚSQL¾ä¼¶»òÊǻỰ(session)¼¶¶ÔÆä½øÐи²¸Ç¡£ ......
¡¡¡¡decode()º¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLʵÏÖ»¹Ã»Óд˹¦ÄÜ¡£
DECODEº¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLʵÏÖ»¹Ã»Óд˹¦ÄÜ¡£DECODEÓÐÊ²Ã´Ó ......
ÊÂÎñ¾ÍÊDZ»°ó¶¨ÔÚÒ»Æð×÷Ϊһ¸öÂß¼¹¤×÷µ¥ÔªµÄSQLÓï¾ä·Ö×飬Èç¹ûÈκÎÒ»¸öÓï¾ä²Ù×÷ʧ°ÜÄÇôÕû¸ö²Ù×÷¾Í±»Ê§°Ü£¬ÒÔºó²Ù×÷¾Í»á»Ø¹öµ½²Ù×÷ǰ״̬£¬»òÕßÊÇÉÏÓиö½Úµã¡£ÎªÁËÈ·±£ÒªÃ´Ö´ÐУ¬ÒªÃ´²»Ö´ÐУ¬¾Í¿ÉÒÔʹÓÃÊÂÎñ¡£ÊÂÎñÓÐËĸöÌØÐÔ£¬·Ö±ðÊÇ£ºÔ×ÓÐÔ£¬Ò»ÖÂÐÔ£¬¸ôÀëÐԺͳ־ÃÐÔ¡£
Ë÷Òý¾ÍÒ»ÖÖÌØÊâµÄ²éѯ±í£¬Êý¾Ý¿âµÄËÑË÷ÒýÇæ¿ ......
ËäÈ»±¾ÈËûÓÐÑéÖ¤¹ý,²»¹ý¾Ý˵ͨ¹ýADODB·ÃÎÊOracle¶ÔÐÔÄÜÓ°ÏìºÜС.¶øÇÒADODB±ÈOCI8Ò²Òª·½±ãºÜ¶à,±Ï¾¹Ðí¶à¶«Î÷¶¼Ôٴηâ×°¹ý.²»¹ý²»¹ÜÔõô˵Õâ¶¼ÔÚÖмäÓÖ¼ÓÉÏÁËÒ»²ã,ÀíÂÛÉÏÀ´Ëµ³öÏÖÎÊÌâµÄ¼¸Âʺͽâ¾öÎÊÌâµÄÄѶȶ¼±ä´óÁË.¶øOracleÓÖ¾³£ÓÃÔÚ²»ÄÜËæ±ã³öÎÊÌâµÄ»·¾³,ËùÒÔÏë±ØÏëÒªÖ±½ÓʹÓÃOCIµÄÇé¿öÒ²²»ÉÙ.ÒÔϾÍÊÇÎÒµÄÀý×Ó:
<?p ......
±í1£ºtemp1
AA BB CC
1 1 1
1 1 2
1 1 3
±í2£ºtemp2
AA BB ......