oracle常用函数问答

1. 如何得到字符串的第一个字符的ASCII值?
ASCII(CHAR)
SELECT ASCII('ABCDE') FROM DUAL;
结果: 65

2. 如何得到数值N指定的字符?
CHR(N)
SELECT CHR(68) FROM DUAL;
结果: D

3. 如何连接两个字符串?
CONCAT(CHAR1,CHAR2)
SELECT CONCAT('ABC','DEFGH') FROM DUAL;
结果: 'ABCDEFGH'

4. 如何将列中的数值代替为字符串?
DECODE(CHAR,N1,CHAR1,N2,CHAR2...)
SELECT DECODE(DAY,1,'SUN',2,'MON') FROM DUAL;
5. INITCAP(CHAR)

将字符串CHAR的第一个字符为大写,其余为小写.
SELECT INITCAP('ABCDE') FROM DUAL;

6. LENGTH(CHAR)
取一字符串CHAR的长度.
SELECT LENGTH('ABCDE') FROM DUAL;

7. LOWER(CHAR)
将字符串CHAR全部变为小写.
SELECT LOWER('ABCDE') FROM DUAL;

8. LPAD(CHAR1,N,CHAR2)
用字符串CHAR2包括的字符左填CHAR1,使其长度为N.
SELECT LPAD('ABCDEFG',10'123') FROM DUAL;
结果: '123ABCDEFG'

9. LTRIM(CHAR,SET)
从字符串CHAR的左边移去字符串SET中的字符,直到第一个不是
SET中的字符为止.
SELECT ('CDEFG','CD') FROM DUAL;
结果: 'EFG'

10. NLS_INITCAP(CHAR)
取字符CHAR的第一个字符大写,其余字符为小写.
SELECT NLS_INITCAP('ABCDE') FROM DUAL;

11. NLS_LOWER(CHAR)
将字符串CHAR包括的字符全部小写.
SELECT NLS_LOWER('AAAA') FROM DUAL;

12. NLS_UPPER(CHAR)
将字符串CHAR包括的字符全部大写.
SELECT NLS_UPPER('AAAA') FROM DUAL;

13. REPLACE(CHAR1,CHAR2,CHAR3)
用字符串CHAR3代替每一个列值为CHAR2的列,其结果放在CHAR1
中.
SELECT REPLACE(EMP_NO,'123','456') FROM DUAL;

14. RPAD(CHAR1,N,CHAR2)
用字符串CHAR2右填字符串CHAR1,使其长度为N.
SELECT RPAD('234',8,'0') FROM DUAL;

15. RTRIM(CHAR,SET)
移去字符串CHAR右边的字符串SET中的字符,直到最后一个不是
SET中的字符为止.
SELECT RTRIM('ABCDE','DE') FROM DUAL;

16. SUBSTR(CHAR,M,N)
得到字符串CHAR从M处开始的N个字符. 双字节字符,一个汉字为一
个字符的.
SELECT SUBSTR('ABCDE',2,3) FROM DUAL;

17. SUBSTRB(CHAR,M,N)
得到字符串CHAR从M处开始的N个字符. 双字节字符,一个汉字为二
个字符的.
SELECT SUBSTRB('ABCDE',2,3) FROM DUAL;

18. TRANSLATE(CHAR1,CHAR2,CHAR3)
将CHAR1中的CHAR2的部分用CHAR3代替.
SELECT TRANSLATE('ABCDEFGH','DE','MN') FROM DUAL;

19. UPPER(CHAR)
将字符串CHAR全部为大写.

20. ADD_MONTHS(D,N)
将N个月增加到D日期.
SELECT ADD_MONTHS(SYSDATE,5) FROM DUAL;

21. LAST_DAY(D)
得到包含D日期的月份的最后的一天的日期.
SELECT LAST_DAY(SYSDATE) FROM DUAL;

22. MONTH_BETWEEN(D1,D2)
得到两个日期之间的月数.
SELECT MONTH_BETWEEN(D1,D2) FROM DUAL;

23. NEXT_DAY(D,CHAR)
得到比日期D晚的由CHAR命名的第一个周日的日期.
SELECT NEXT_DAY(TO_DATE('2003/09/20'),'SATDAY')
FROM DUAL;

24. ROUNT(D,FMT)
得到按指定的模式FMT舍入到的最进的日期.
SELECT ROUNT('2003/09/20',MONTH) FROM DUAL;

25. SYSDATE
得到当前系统的日期和时间.
SELECT SYSDATE FROM DUAL;

26. TO_CHAR(D,FMT)
将日期D转换为FMT的字符串.
SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD') FROM DUAL;

27. TO_DATE(CHAR,FMT)
将字符串CHAR按FMT的格式转换为日期.
SELECT TO_DATE('2003/09/20','YYYY/MM/DD') FROM
DUAL;

28. ABS(N)
得到N的绝对值.
SELECT ABS(6)
FROM DUAL;

29. CEIL(N)
得到大于或等于N的最大整数.
SELECT CEIL(5.6) FROM DUAL;

30. COS(N)
得到N的余弦值.
SELECT COS(1) FROM DUAL;

31. SIN(N)
得到N的正弦值.
SELECT SIN(1) FROM DUAL;

32. COSH(N)
得到N的双曲余弦值.
SELECT COSH(1) FROM DUAL;

33. EXP(N)
得到N的E的N次幂.
SELECT EXP(1) FROM DUAL;

34. FLOOR(N)
得到小于或等于N的最小整数.
SELECT FLOOR(5.6) FROM DUAL;

35. LN(N)
得到N的自然对数.
SELECT LN(1) FROM DUAL;

36. LOG(M,N)
得到以M为底N的对数.
SELECT LOG(2,8) FROM DUAL;

37. MOD(M,N)
得到M除以N的余数.
SELECT MOD(100,7) FROM DUAL;

38. POWER(M,N)
得到M的N幂.
SELECT POWER(4,3) FROM DUAL;

39. ROUND(N,M)
将N舍入到小数点后M位.
SELECT (78.87653,2) FROM DUAL;

40. SIGN(N)
当N<0时,得到1;
当N>0时,得到1;
当N=0时,得到0;
SELECT SIGN(99) FROM DUAL;

41. SINH(N)
得到N的双曲正弦值.
SELECT SINH(1) FROM DUAL;

42. SORT(N)
得到N的平方根,N>=0
SELECT SORT(9) FROM DUAL;

43. TAN(N)
得到N的正切值.
SELECT TAN(0) FROM DUAL;

44. TANH(N)
得到N的双曲正切值.
SELECT TANH(0) FROM DUAL;

45. TRUNC(N,M)
得到在M位截断的N的值.
SELECT TRUNC(7.7788,2) FROM DUAL;

46. COUNT()
计算满足条件的记录数.
SELECT COUNT(*) FROM TABLE1 WHERE COL1='AAA';

47. MAX()
对指定的列求最大值.
SELECT MAX(COL1) FROM TABLE1;

48. MIN()
对指定的列求最小值.
SELECT MIN(COL1) FROM TABLE1;

49. AVG()
对指定的列求平均值.
SELECT AVG(COL1) FROM TABLE1;

50. SUM()
计算列的和.
SELECT SUM(COL1) FROM DUAL;

51. TO_NUMBER(CHAR)
将字符转换为数值.
SELECT TO_NUMBER('999') FROM DUAL;

52. CHARTOROWID(CHAR)
将包含外部语法ROWID的CHAR或VARCHAR2数值转换为内部的二进
制语法,参数CHAR必须是包含外部语法的ROWID的18字符的字符
串.
SELECT NAME FROM BSEMPMS WHERE ROWID=CHARTOROWID
('AAAAfZAABAAACp8AAO');
NAME : LEIXUE

53. CONVERT(CHAR,DEST_CHAR_SET,SOURCE_CHAR_SET)
CONVERT将字符串CHAR中的字符从SOURCE_CHAR_SET标识的字
符集转换为由DEST_CHAR_SET标识的字符集
SELECT CONVERT('GroB','US7ASCII','WE8HP')
'CONVERSION' FROM PUBS;
CONVERSION: Gross

54. HEXTORAW(CHAR)
将包含十六进制的CHAR转换为一个RAW数值.
INSERT INTO BSEMPMS(RAW_COLUMN) SELECT HEXTORAW
('7D') FROM TEST;

55. RAWTOHEX(RAW)
将RAW数值转换为一个包含十六进制的CHAR值.
SELECT RAWTOHEX(RAW_COLUMN) 'CONVERSION' FROM
BSEMPMS;
CONVERSION: 7D

56. ROWIDTOCHAR(ROWID)
将一个ROWID数值转换为VARCHAR2数据类型.
SELECT ROWID FROM BSEMPMS WHERE ROWIDTOCHAR
(ROWID) LIKE '%BR1AAB%';

57. TO_MULTI_BYTE(CHAR)
将CHAR中的单字节转换为等价的多字节字符.
SELECT TO_MULTI_BYTE('ASFDFD') FROM TEST;

58. TO_SINGLE_BYTE(CHAR)
将CHAR中的多字节转换为等价的单字节字符.
SELECT TO_SINGLE_BYTE('ASFDFD') FROM TEST;

59. TRANSLATE USING(TEXT USING
{CHAR_CS|NCHAR_CS})
将文本TEXT按照指定的转换方式转换成数据库字符集和民族字符
集.
其中TEXT是待转换的.
USING CHAR_CS参数转换TEXT为数据库字符集,输出数据类型是
VARCHAR2.
USING NCHAR_CS参数转换TEXT为数据库字符集,输出数据类型是
NVARCHAR2.
CREATE TABLE TEST(CHAR_COL CHAR(20),NCHAR_COL
NCHAR(20));
INSERT INTO TEST VALUES('HI,N'BYE');
SELECT * FROM TEST;

60. DUMP
(EXPR,RETURN_FORMAT,START_POSITION,LENGTH)
返回一个包含数据类型代码,字节长度等内部表示信息的VARCHAR2
值.返回结果是当前数据库字符集,数据类型按照下面规定的内部数
据类型的编码作为一个数字进行返回:
代码 数据类型
0 VARCHAR2
1 NUMBER
8 LONG
12 DATE
23 RAW
24 LONG RAW
69 ROWID
96 CHAR
106 MSSLABEL
参数RETUEN_FORMAT指定按照下面的基数表示返回的数值.
RETURN_FORMAT RESULT
8 8进制
10 10进制
16 16进制
17 单字符表示
如果参数RETURN_FORMAT没有指定,则按十进制表示返回.
如果参数START_POSITION和LENGTH被指定,则从
START_POSITION开始的长为LENGTH的字节将被返回,缺省是返
回整数表示.
SELECT DUMP('ABC',1016) FROM TEST;
select dump(ename,8,3,2) 'example' from emp where
name='ccbzzp';

61. empty_b|clob()
返回一个空的LOB定位符,用在初始化LOB变量,或用在INSERT及
UPDATE声明去初始化LOB列或将其属性置为空.
INSERT INTO TABLE1 VALUES(EMPTY_BLOB());
UPDATE TABLE1 SET CLOB_COL=EMPTY_BLOB();

62. BFILENAME('DIRECTORY','FILENAME')
返回一个BFILE定位符,相关的二进制LOB物理文件在服务器的文件
系统上.目录DIRECTORY是指在服务器的文件系统上实际搜索路径
全名的别名. FILENAME是指服务器的文件系统的文件名.
INSERT INTO FILE_TAB VALUES(BFILENAME
('LOB_DIR','IMAGE1.GIF'));

63. GREATEST(EXPR,EXPR,...)
GREATEST返回参数的最大值.
SELECT GREATEST('HARRY','HARRIOT','HAROLD')
'SAMPLE' FROM TABLE1;

64. LEAST(EXPR,EXPR,...)
LEAST返回参数的最小值.
SELECT LEAST('HARRY','HARRIOT','HAROLD') 'SAMPLE'
FROM TABLE1;

65. NLS_CHARSET_DECL_LEN(BYTECNT,CSID)
返回一个NCHAR列的宽度.
SELECT NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID
('JA16EEFDFDF')) FROM TABLE1;

66. NLS_CHARSET_ID(TEXT)
返回相应于NLS字符集名字的NLS字符集ID数.
SELECT NLS_CHARSET_D('JADFDFFDF') FROM TABLE1;

67. NLS_CHARSET_NAME(N)
返回相应于ID数N的NLS字符集名称.
SELECT NLS_CHARSET_NAME(2) FROM TABLE1;

68. NVL(EXPR1,EXPR2)
若EXPR1是NULL,则返回EXPR2,否则返回EXPR1.
SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION')
FROM TABLE1;

69. UID
返回唯一标识当前数据库用户的整数.
SELECT UID FROM TABLE1;

70. USER
用VARCHAR2数据类型返回当前ORACLE用户的名称.
SELECT USER,UID FROM TABLE1;

71. USERENV(OPTION)
返回当前的会话信息.
OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.
OPTION='LANGUAGE'返回数据库的字符集.
OPTION='SESSIONID'为当前会话标识符.
OPTION='ENTRYID'返回可审计的会话标识符.
OPTION='LANG'返回会话语言名称的ISO简记.
OPTION='INSTANCE'返回当前的实例.
SELECT USERENV('LANGUAGE') FROM DUAL;

72. VSIZE(EXPR)
返回EXPR内部表示的字节数.
SELECT NAME,VSIZE(NAME) FROM TABLE1;|

73. DEREF(E)
返回参数E的对象引用.
SELECT DEREF(C2) FROM TABLE1;

74. REFTOHEX(R)
将参数R转换为16进制.
SELECT REFTOHEX(C2) FROM TABLE1;

75. MAKE_REF(TABLE,KEY,KEY...)
通过把给定的键作为主键来创建给定视图对象中一行的引用.
CREATE TYPE T1 AS OBJECT(A NUMBER,B NUMBER);
CREATE TABLE TB1(C1 NUMBER,C2 NUMBER,PRIMARY KEY
(C1,C2));
CREATE VIEW V1 OF T1 WITH OBJECT OID(A,B) AS
SELECT * FROM TB1;
SELECT MAKE_REF(V1,1,3) FROM PUBS;

76. STDDEV(DISTINCT|ALL X)
STDDEV给出一组行值的标准差.
SELECT STDDEV(SALARY) AS EXAMPLE FROM EMPLOYEE;

77. VARIANCE(DISTINCT|ALL X)
VARIANCE返回一组行中所有VALUE的方差.
SELECT VARIANCE(SALARY) AS EXAMPLE FROM EMPLOYEE;

转自 http://tb.blog.csdn.net/TrackBack.aspx?
PostId=193926 作者xinqing6888