Oracleº¯Êý´óÈ«
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 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 name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;
NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))
------ ------------ ---------------- ------------ --------- --------------------
¸ßǬ¾º 3 ±±¾©Êк£¶§Çø 6 9999.99
Ïà¹ØÎĵµ£º
1.ÔÚOracle ÖУ¬Äã»áÁ˽⵽£º
ÊÂÎñÊÇÿ¸öÊý¾Ý¿âµÄºËÐÄ£¬ËüÃÇÊÇ“ºÃ¶«Î÷”¡£
Ó¦¸ÃÑÓ³Ùµ½Êʵ±µÄʱ¿Ì²ÅÌá½»¡£²»ÒªÌ«¿ìÌá½»£¬ÒÔ±ÜÃâ¶Ôϵͳ´øÀ´Ñ¹Á¦¡£ÕâÊÇÒòΪ£¬Èç¹û
ÊÂÎñºÜ³¤»òºÜ´ó£¬Ò»°ã²»»á¶ÔϵͳÓÐѹÁ¦¡£ÏàÓ¦µÄÔÔòÊÇ£ºÔÚ±ØҪʱ²ÅÌá½»£¬µ«ÊÇ´ËÇ°²»ÒªÌá
½»¡£ÊÂÎñµÄ´óСֻӦ¸Ã¸ù¾ÝÒµÎñÂß¼À´¶¨¡£
Ö»ÒªÐèÒ ......
001¡¢×Ö·û
length/lengthb ×Ö·ûÊý(1¸öºº×Ö1¸ö×Ö·û) / ×Ö½ÚÊý(1¸öºº×Ö2¸ö×Ö½Ú)
ltrim/rtrim/trim ɾ³ý¿Õ¸ñ
lower/upper ´óСдת»»
select length('abc') from dual;
select substr(ename, 1, 3) from emp; ´ÓµÚÒ»¸ö×Ö·û¿ªÊ¼½Ø£¬Ò»¹²½Ø3¸ö×Ö·û
substr('abcdefg',2,3) => ......
The following are number examples for the to_char function.
to_char(1210.73, '9999.9')
would return '1210.7'
to_char(1210.73, '9,999.99')
would return '1,210.73'
to_char(1210.73, '$9,999.00')
would return '$1,210.73'
to_char(21, '000099')
would return '000021'
The following is a list ......
--´´½¨Ò»¸ö°ü
create or replace package types
as
type cursorType is ref cursor;
end types;
--´´½¨´æ´¢¹ý³Ì,ÓαêÀàÐÍ·µ»Ø²ÎÊý
create or replace procedure SP_Fee_Instance(v_company in varchar, v_sdate in nva ......