oracle º¯ÊýÕûÀí(Ò»)
1. ASCII: ·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii('A') A,ascii('a') a,ascii(0) zero,ascii('') space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48
2. CHR:¸ø³öÕûÊý,·µ»Ø¶ÔÓ¦µÄ×Ö·û;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH C
-- -
ÕÔ A
3. CONCAT:Á¬½ÓÁ½¸ö×Ö·û´®;
SQL> select concat('010-','88888888')||'ת23' ¸ßǬ¾ºµç»° from dual;
¸ßǬ¾ºµç»°
----------------
010-88888888ת23
4. INITCAP:·µ»Ø×Ö·û´®²¢½«×Ö·û´®µÄµÚÒ»¸ö×Öĸ±äΪ´óд;
SQL> select initcap('smith') upp from dual;
UPP
-----
Smith
5.INSTR(C1,C2,I,J):ÔÚÒ»¸ö×Ö·û´®ÖÐËÑË÷Ö¸¶¨µÄ×Ö·û,·µ»Ø·¢ÏÖÖ¸¶¨µÄ×Ö·ûµÄλÖÃ;
C1 ±»ËÑË÷µÄ×Ö·û´®
C2 Ï£ÍûËÑË÷µÄ×Ö·û´®
I ËÑË÷µÄ¿ªÊ¼Î»ÖÃ,ĬÈÏΪ1
J ³öÏÖµÄλÖÃ,ĬÈÏΪ1
SQL> select instr('oracle traning','ra',1,2) instring from dual;
INSTRING
---------
9
6.LENGTH:·µ»Ø×Ö·û´®µÄ³¤¶È;
SQL> select length('smith') length from dual;
length
-----
5
7.LOWER:·µ»Ø×Ö·û´®,²¢½«ËùÓеÄ×Ö·ûСд
SQL> select lower('AaBbCcDd')AaBbCcDd from dual;
AABBCCDD
--------
aabbccdd
8.UPPER:·µ»Ø×Ö·û´®,²¢½«ËùÓеÄ×Ö·û´óд
SQL> select upper(AaBbCcDd) upper from dual;
UPPER
--------
AABBCCDD
9.RPADºÍLPAD(Õ³Ìù×Ö·û):RPAD ÔÚÁеÄÓÒ±ßÕ³Ìù×Ö·û LPAD ÔÚÁеÄ×ó±ßÕ³Ìù×Ö·û
SQL> select lpad(rpad('gao',10,'*'
Ïà¹ØÎĵµ£º
1. Ñ¡ÓÃÊʺϵÄOracleÓÅ»¯Æ÷
¡¡¡¡OracleµÄÓÅ»¯Æ÷¹²ÓÐ3ÖÖ£º
¡¡¡¡a. RULE (»ùÓÚ¹æÔò)
¡¡¡¡b. COST (»ùÓڳɱ¾)
¡¡¡¡c. CHOOSE (Ñ¡ÔñÐÔ)
¡¡¡¡ÉèÖÃȱʡµÄÓÅ»¯Æ÷£¬¿ÉÒÔͨ¹ý¶Ôinit.oraÎļþÖÐOPTIMIZER_MODE²ÎÊýµÄ¸÷ÖÖÉùÃ÷£¬ÈçRULE£¬COST£¬CHOOSE£¬ALL_ROWS£¬FIRST_ROWS . Ä㵱ȻҲÔÚSQL¾ä¼¶»òÊǻỰ(session)¼¶¶ÔÆä½øÐи²¸Ç¡£ ......
1. ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2. CHR
¸ø³öÕûÊý,·µ»Ø¶ÔÓ¦µÄ×Ö·û;
SQL> select chr(54740) zhao,chr(65) chr65 from ......
¡¡¡¡decode()º¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLʵÏÖ»¹Ã»Óд˹¦ÄÜ¡£
DECODEº¯ÊýÊÇORACLE PL/SQLÊǹ¦ÄÜÇ¿´óµÄº¯ÊýÖ®Ò»£¬Ä¿Ç°»¹Ö»ÓÐORACLE¹«Ë¾µÄSQLÌṩÁ˴˺¯Êý£¬ÆäËûÊý¾Ý¿â³§É̵ÄSQLʵÏÖ»¹Ã»Óд˹¦ÄÜ¡£DECODEÓÐÊ²Ã´Ó ......
ÊÂÎñ¾ÍÊDZ»°ó¶¨ÔÚÒ»Æð×÷Ϊһ¸öÂß¼¹¤×÷µ¥ÔªµÄSQLÓï¾ä·Ö×飬Èç¹ûÈκÎÒ»¸öÓï¾ä²Ù×÷ʧ°ÜÄÇôÕû¸ö²Ù×÷¾Í±»Ê§°Ü£¬ÒÔºó²Ù×÷¾Í»á»Ø¹öµ½²Ù×÷ǰ״̬£¬»òÕßÊÇÉÏÓиö½Úµã¡£ÎªÁËÈ·±£ÒªÃ´Ö´ÐУ¬ÒªÃ´²»Ö´ÐУ¬¾Í¿ÉÒÔʹÓÃÊÂÎñ¡£ÊÂÎñÓÐËĸöÌØÐÔ£¬·Ö±ðÊÇ£ºÔ×ÓÐÔ£¬Ò»ÖÂÐÔ£¬¸ôÀëÐԺͳ־ÃÐÔ¡£
Ë÷Òý¾ÍÒ»ÖÖÌØÊâµÄ²éѯ±í£¬Êý¾Ý¿âµÄËÑË÷ÒýÇæ¿ ......
1. ASCII
·µ»ØÓëÖ¸¶¨µÄ×Ö·û¶ÔÓ¦µÄÊ®½øÖÆÊý;
SQL> select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual;
A A ZERO SPACE
--------- --------- --------- ---------
65 97 48 32
2. CHR
¸ø³öÕûÊý,·µ»Ø¶ÔÓ¦µÄ×Ö·û;
SQL> select chr(54740) zhao,chr(65) chr65 from dual;
ZH ......