ORACLE´¦ÀíÈÕÆÚ´óÈ«
ÔÚoracleÖд¦ÀíÈÕÆÚ´óÈ«
TO_DATE¸ñʽ
Day:
dd number 12
dy abbreviated fri
day spelled out friday
ddspth spelled out, ordinal twelfth
Month:
mm number 03
mon abbreviated mar
month spelled out march
Year:
yy two digits 98
yyyy four digits 1998
24Сʱ¸ñʽÏÂʱ¼ä·¶Î§Îª£º 0:00:00 - 23:59:59....
12Сʱ¸ñʽÏÂʱ¼ä·¶Î§Îª£º 1:00:00 - 12:59:59 ....
1.
ÈÕÆÚºÍ×Ö·ûת»»º¯ÊýÓ÷¨£¨to_date,to_char£©
2.
select to_char( to_date(222,'J'),'Jsp') from dual
ÏÔʾTwo Hundred Twenty-Two
3.
ÇóijÌìÊÇÐÇÆÚ¼¸
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
ÐÇÆÚÒ»
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
monday
ÉèÖÃÈÕÆÚÓïÑÔ
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
Ò²¿ÉÒÔÕâÑù
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
4.
Á½¸öÈÕÆÚ¼äµÄÌìÊý
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
5. ʱ¼äΪnullµÄÓ÷¨
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
×¢ÒâÒªÓÃTO_DATE(null)
6.
a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')
ÄÇô12ÔÂ31ºÅÖÐÎç12µãÖ®ºóºÍ12ÔÂ1ºÅµÄ12µã֮ǰÊDz»°üº¬ÔÚÕâ¸ö·¶Î§Ö®Äڵġ£
ËùÒÔ£¬µ±Ê±¼äÐèÒª¾«È·µÄʱºò£¬¾õµÃto_char»¹ÊDZØÒªµÄ
7. ÈÕÆÚ¸ñʽ³åÍ»ÎÊÌâ
ÊäÈëµÄ¸ñʽҪ¿´Äã°²×°µÄORACLE×Ö·û¼¯µÄÀàÐÍ, ±ÈÈç: US7ASCII, date¸ñʽµÄÀàÐ;ÍÊÇ: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American
alter session set NLS_DATE_LANGUAGE
Ïà¹ØÎĵµ£º
ËäÈ»ÕâÊÇÎÒÕÒµ½×îÏêϸµÄÅäÖÃÃèÊö£¬µ«Êdz¢ÊÔ»¹ÊÇûÓгɹ¦¡£
1.ÏÂÔØOracle Client Package
.
´Ó
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html
ÏÂÔØ
Instant
Client Package – Basic
°ü
(
±ê×¢
:All files
required to run OCI, OCCI, and JDBC-OC ......
80¡£LAST
¹¦ÄÜÃèÊö£º´ÓDENSE_RANK·µ»ØµÄ¼¯ºÏÖÐÈ¡³öÅÅÔÚ×îºóÃæµÄÒ»¸öÖµµÄÐУ¨¿ÉÄܶàÐУ¬ÒòΪֵ¿ÉÄÜÏàµÈ£©£¬Òò´ËÍêÕûµÄÓï·¨ÐèÒªÔÚ¿ªÊ¼´¦¼ÓÉÏÒ»¸ö¼¯ºÏº¯ÊýÒÔ´ÓÖÐÈ¡³ö¼Ç¼
SAMPLE£ºÏÂÃæÀý×ÓÖÐDENSE_RANK°´²¿ÃÅ·ÖÇø£¬ÔÙ°´Ó¶½ðcommission_pctÅÅÐò£¬FIRSTÈ¡³öÓ¶½ð×îµÍµÄ¶ÔÓ¦µÄËùÓÐÐУ¬È»ºóÇ°ÃæµÄMAXº¯Êý´ÓÕâ¸ö¼¯ºÏÖÐÈ¡³öнˮ×îµ ......
CREATE OR REPLACE TYPE ty_str_array IS TABLE OF VARCHAR2 (4000);
CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN ty_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
&nbs ......
OracleÖÐDecode()º¯ÊýʹÓü¼ÇÉ
¡¡¡¡decode()º¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLʵÏÖ»¹Ã»Óд˹¦ÄÜ¡£
DECODEº¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLÊµÏ ......
ÔÚOracleÖУ¬Èç¹ûÔÚsqlÖгöÏÖ'&'·ûºÅ£¬»á±»×Ô¶¯×ªÒ壻
¶ø±»ÒªÇóÊäÈëÔÚ&·ûºÅºó¸úËæµÄ×Ö·û´®µÄÖµ£¬ÀýÈ磺
update tablename set columnName='http://www.g.cn/cv2.jsp?spid=222&cid=333';
Ö´ÐÐÕâ¸ö²Ù×÷ʱ£¬Oracle»áÌáʾ
Enter value for cid:
ÔÒòÊÇÔÚOracleÖÐ & ·ûºÅÊÇ×÷ΪתÒå×Ö·ûʹÓõġ£
½â¾ö·½·¨ ......