oracle内置函数

骑猪看日落 2022-01-12 16:13 396阅读 0赞

oracle数据库 SQL语句、内置函数大全

1、数值函数

函数　　　　　　返回值　　　　　　　　　　　　　样例　　　　　　　　　　　　显示

CEIL(n) 大于或等于数值n的最小整数　　SELECT CEIL(10.6) FROM TABLE\_NAME; 11

FLOOR(n) 小于等于数值n的最大整数　 SELECT FLOOR (10.6) FROM TABLE\_NAME; 10

MOD(m,n) m除以n的余数,若n=0,则返回m SELECT MOD (7,5) FROM TABLE\_NAME l; 2

POWER(m,n) m的n次方　　　　　　　　 SELECT POWER (3,2) FROM TABLE\_NAME; 9

ROUND(n,m) 将n四舍五入,保留小数点后m位　　SELECT ROUND (1234.5678,2) FROM TABLE\_NAME; 1234.57

SIGN(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1 SELECT SIGN (12) FROM TABLE\_NAME; 1

SQRT(n) n的平方根　　　　　　　　　SELECT SQRT (25) FROM TABLE\_NAME; 5

2、常用字符函数

INITCAP(char) 把每个字符串的第一个字符换成大写　　SELECT INITICAP ('mr.ecop') FROM TABLE\_NAME; Mr.Ecop

LOWER(char) 整个字符串换成小写　　　　　　　　　SELECT LOWER ('MR.ecop') FROM TABLE\_NAME; mr.ecop

REPLACE(char,str1,str2) 字符串中所有str1换成str2 SELECT REPLACE ('Scott','s','Boy') FROM TABLE\_NAME; Boycott

SUBSTR(char,m,n) 取出从m字符开始的n个字符的子串　　SELECT SUBSTR ('ABCDEF',2,2) FROM TABLE\_NAME; CD

LENGTH(char) 求字符串的长度　　　　SELECT LENGTH ('ACD') FROM TABLE\_NAME; 3

|| 并置运算符　　　 SELECT 'ABCD'||'EFGH' FROM TABLE\_NAME; ABCDEFGH

3、日期型函数

SYSDATE 当前日期和时间 SELECT SYSDATE FROM TABLE\_NAME;

LAST\_DAY 　本月最后一天 SELECT LAST\_DAY(SYSDATE) FROM TABLE\_NAME;

ADD\_MONTHS(d,n)　当前日期d后推n个月 SELECT ADD\_MONTHS (SYSDATE,2) f FROM TABLE\_NAME;

MONTHS\_BETWEEN(d,n)

日期d和n相差月数 SELECT MONTHS\_BETWEEN(SYSDATE,TO\_DATE('20020812','YYYYMMDD'))FROMTABLE\_NAME;

NEXT\_DAY(d,day) d后第一周指定day的日期 SELECT NEXT\_DAY (SYSDATE,'Monday') FROM TABLE\_NAME;

day 格式　　有　　'Monday' 星期一　　'Tuesday' 星期二

'wednesday' 　星期三 'Thursday' 星期四 'Friday' 星期五

'Saturday' 星期六 'Sunday' 星期日

4、特殊格式的日期型函数

Y或YY或YYY 年的最后一位，两位，三位 SELECTTO\_CHAR(SYSDATE,'YYY') FROM TABLE\_NAME;

Q 季度,1-3月为第一季度　　　　SELECT TO\_CHAR(SYSDATE,'Q') FROM TABLE\_NAME;

MM 　月份数　　　　　　　　　　　SELECT TO\_CHAR(SYSDATE,'MM') FROM TABLE\_NAME;

RM 月份的罗马表示 SELECT TO\_CHAR(SYSDATE,'RM') FROM TABLE\_NAME; IV

month 用9个字符表示的月份名 SELECT TO\_CHAR(SYSDATE,'month') FROM TABLE\_NAME;

ww 当年第几周　　　　　　　　　SELECT TO\_CHAR(SYSDATE,'ww') FROM TABLE\_NAME l;

w 本月第几周　　　　　　　　　SELECT TO\_CHAR(SYSDATE,'w') FROM TABLE\_NAME;

DDD 当年第几天,一月一日为001 ,二月一日032 SELECT TO\_CHAR(SYSDATE,'DDD') FROM TABLE\_NAME;

DD 当月第几天 SELECT TO\_CHAR(SYSDATE,'DD') FROM TABLE\_NAME;

D 周内第几天 SELECT TO\_CHAR(SYSDATE,'D') FROM TABLE\_NAME; 如　sunday

DY 周内第几天缩写　　　　　　　SELECT TO\_CHAR(SYSDATE,'DY') FROM TABLE\_NAME; 如　sun

hh12 12小时制小时数　　　　　　　SELECT TO\_CHAR(SYSDATE,'hh12') FROM TABLE\_NAME;

hh24 24小时制小时数　　　　　　　SELECT TO\_CHAR(SYSDATE,'hh24') FROM TABLE\_NAME;

Mi 分钟数　　　　　　　　　　　 SELECT TO\_CHAR(SYSDATE,'Mi') FROM TABLE\_NAME;

ss 秒数　　　　　　　　　　　　 SELECT TO\_CHAR(SYSDATE,'ss') FROM TABLE\_NAME;

SELECT TO\_CHAR(SYSDATE,'YYYY-MM-DD HH:24:mi:ss') FROM TABLE\_NAME;

TO\_NUMBER() 将合法的字符串转换为数字 SELECT TO\_NUMBER ('88877') FROM TABLE\_NAME; 88877

TO\_CHAR() 将数字转换为字符串　 SELECT TO\_CHAR(88877) FROM TABLE\_NAME; '88877'

5、字符函数

字符函数主要用于修改字符列。这些函数接受字符输入，返回字符或数字值。Oracle 提供的一些字符函数如下。

1. CONCAT (char1, char2)

返回连接“char2”的“char1”。

示例 SELECT CONCAT( CONCAT( COLUMN\_NAME, ' is a '), job) FROM TABLE\_NAME;

2. INITCAP(string)

将“string”的字符转成大写。

SELECT INITCAP( COLUMN\_NAME) from TABLE\_NAME;

3. LOWER (string)

将“string”转成小写。

SELECT LOWER( COLUMN\_NAME) from TABLE\_NAME;

4. LPAD(char1,n \[,char2\])

返回“char1”，左起由“char2”中的字符补充到“n”个字符长。如果“char1”比“n”长，则函数返回“char1”的前“n”个字符。

SELECT LPAD( COLUMN\_NAME,15,'\*') FROM TABLE\_NAME;

5. LTRIM(string,trim\_set)

从左边删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim\_set” 是我们要去掉的字符的集合。

SELECT LTRIM('abcdab','a') FROM TABLE\_NAME;

6. REPLACE(string, if, then)

用 0 或其他字符代替字符串中的字符。“if”是字符或字符串，对于每个出现在“string”中的“if”，都用“then”的内容代替。

SELECT REPLACE('JACK and JUE','J','BL') FROM TABLE\_NAME;

7. RPAD(char1, n \[,char2\])

返回“char1”，右侧用“char2”中的字符补充到“n”个字符长。如果 “char1”比“n” 长，则函数返回“char1”的前“n”个字符。

SELECT RPAD( COLUMN\_NAME,15,'\*') FROM TABLE\_NAME;

8. RTRIM(string,trim\_set)

从右侧删除字符，此处“string”是数据库的列，或者是字面字符串，而“trim\_set” 是我们要去掉的字符的集合。

SELECT RTRIM('abcdef', 'f') FROM TABLE\_NAME;

9. SOUNDEX(char)

返回包含“char”的表意字符的字符串。它允许比较英语中拼写不同而发音类似的字。

SELECT COLUMN\_NAME FROM TABLE\_NAME WHERE SOUNDEX( COLUMN\_NAME) = SoUNDEX('SMYTHE');

10. SUBSTR(string, start \[,count\])

返回“string”中截取的一部分。该命令截取“string”的一个子集，从“start”位置开始，持续“count”个字符。如果我们不指定“count”，

则从“start”开始截取到“string”的尾部。

SELECT SUBSTR('ABCDEFGIJKLM',3,4) FROM TABLE\_NAME;

11. TRANSLATE(string, if, then)

“if”中字符的位置，并检查“then”的相同位置，然后用该位置的字符替换 “string”中的字符。

SELECT TRANSLATE( COLUMN\_NAME,'AEIOU', 'XXXXX') FROM TABLE\_NAME;

12. UPPER(string)

返回大写的“string”。

SELECT UPPER('aptech computer education') FROM TABLE\_NAME;

13. ASCII(string)

该命令是“American Standard Code for Information Interchange”的缩写。它是使用数字表示可打印字符的基本规则。

该函数返回 “string”中第一个（最左边）字符的 ASCII 值。

SELECT ASCII('APTECH') FROM TABLE\_NAME;

14. INSTR (string, set\[, start\[, occurrence\] \] )

该命令“string”中从“start”位置开始查找字符集合的位置，再查找“set”出现的第一次、第二次等等的“occurrence”（次数）。

“start”的值也可以是负数，代表从字符串结尾开始向反方向搜索。该函数也用于数字和日期数据类型。

SELECT INSTR('aptech is aptech','ap',1,2) FROM TABLE\_NAME;

15. LENGTH(string)

返回“string”的长度值。

SELECT COLUMN\_NAME, LENGTH( COLUMN\_NAME) FROM TABLE\_NAME;

6、NULL函数

1.COALESCE(expr)

返回表达式列表中第一个不为NULL的函数，如果都为NULL，则返回一个null

SELECT COALESCE(NULL,9-9,NULL) FROM TABLE\_NAME;

2.LNNVL(condition)

返回除了condition要求的条件之外的数据，包括NULL的条件，通常用于WHERE条件中

SELECT COLUMN\_NAME1,...COLUMN\_NAME2 FROM TABLE\_NAME WHERE LNNVL(condition);

3.NVL(expr1,expr2\[,expr3\])

NVL(expr1,expr2)，替换NULL值，如果expr1为NULL值，则返回expr2的值，否则返回expr1的值。该函数要求两个参数类型一致至少相互

之间可以进行隐式的转换

SELECT NVL(COLUMN\_NAME1,COLUMN\_NAME2) FROM TABLE\_NAME;

NVL(expr1,expr2,expr3),，替换NULL值，如果expr1为NULL值，则返回expr2的值，否则返回expr3的值。该函数要求expr2、expr3参数

类型一致至少相互之间可以进行隐式的转换

SELECT NVL(COLUMN\_NAME1,COLUMN\_NAME2,COLUMN\_NAME3) FROM TABLE\_NAME;

7、集合函数

1.AVG(\[distinct l all\] expr)

求取列的平均值，表示某组的平均值，返回数值类型

distinctlall：去除重复的值

all：表示所有的值，包括重复的，也是默认值

expr：表达式，只能是数值类型

SELECT AVG(ALL COLUMN\_NAME)　FROM TABLE\_NAME \[WHERE CONDITION\];

2.MAX(\[distinct l all\] expr) MIN(\[distinct l all\] expr)

指定列的最大值和最小值

SELECT \* FROM TABLE\_NAME WHERE COLUMN\_NAME = (SELECT MAX(COLUMN\_NAME) FROM TABLE\_NAME);

SELECT \* FROM TABLE\_NAME WHERE COLUMN\_NAME = (SELECT MIN(COLUMN\_NAME) FROM TABLE\_NAME);

3.SUM(\[distinct l all\] expr) MIN(\[distinct l all\] expr)

求和函数，返回制定列的数值和

SELECT SUM(ALL COLUMN\_NAME)　FROM TABLE\_NAME \[WHERE CONDITION\];

4.COUNT(\[distinct l all\] expr) MIN(\[distinct l all\] expr)

返回查询的记录数

SELECT COUNT(ALL COLUMN\_NAME)　FROM TABLE\_NAME \[WHERE CONDITION\];

8、其他函数

1.USER

返回登录名函数

SELECT USER FROM DUAL;

2.USERENV(parameter)

返回当前会话信息

parameter:Language 返回当前会话对应语言和字符集

parameter:SESSIONID 返回当前会话ID

parameter:ISDBA 返回当前用户是否DBA

SELECT USERENV('ISDBA')　FROM TABLE\_NAME \[WHERE CONDITION\];

3.SYS\_CONTEXT(namespace,parameter)

返回oracle已经创建的context，名为USERENV的属性对应值

4.DECODE(expr,search,result\[,search1,result1\]\[,default\])

表达式匹配函数，当expr符合条件search时返回result的值，该过程可以重复多个，如果没有匹配结果放回默认值default，这是一对一匹配过程，

类似switch

转载于:https://blog.51cto.com/12926044/2352440

oracle内置函数

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 (Oracle基础篇) Oracle 内置函数

相关 Oracle sql 内置函数大全

相关 oracle的内置函数难吗,Oracle惯用的内置函数

相关 ORACLE 内置函数

相关 oracle 11g(十)：SQL 函数（oracle的内置函数）单行函数

相关十七、Oracle内置函数

相关 oracle内置函数

相关 Oracle常用内置函数

相关 oracle内置函数和时间函数整理

相关内置函数