oracle常用函数总结

1 在Oracle中，不等号有三种：<>，!=，^=

例如：select * from test where name<>'xn';

返回的结果是name不为xn，且name不空的记录。我们的目的是得到name为xn的全部记录，也包括name为空的记录。为了解决这个问题，我们可以采用以下两种方案：

select * from test where instr(concat(name,'xx'),'xn') = 0 ;

select * from test where nvl(name,'xx')<>'xn' ;

备注：null只能通过is null或者is not null来判断，其它操作符与null操作都是false。

各数据库中的字符串连接方法

1）MySQL：CONCAT()

2）Oracle：CONCAT()，||

3）SQL Server： +

例如：

SELECT 'this is '+'a test'; 返回值this a test

SELECT CONCAT('this is ','a test') from dual; 返回值this a test

SELECT 'this is '||'a test' from dual; 返回值this a test

2 instr函数:判断某个字符串是否含有指定的字符,在一个字符串中查找指定的字符,返回被查找到的指定的字符的位置。

语法：instr(sourceString,destString,start,appearPosition)

instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）

其中sourceString代表源字符串；

destString代表要从源字符串中查找的子串；

start代表查找的开始位置，这个参数可选的，默认为1；

appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的，默认为1

如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算。

返回值为：查找到的字符串的位置。 例如：select instr('abcdefghbc','bc',3) position from dual; --(从第3个字符开始算起第3个字符是c，所以从3开始以后的字符串找查找bc,返回9)

POSITION ---------- 9 select instr('qinyinglianqin','qin', 1, 2) position from dual;--(从第1个字符开始，查找第2次出现子串的位置 )

POSITION ---------- 12

3 通用函数：NVL,NVL2,NULLIF,COALESCE

NVL函数的格式如下：NVL(expr1,expr2) 如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

NVL2函数的格式如下：NVL2(expr1,expr2,expr3) 如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

NULLIF(exp1,expr2) 函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

Coalesce(expr1, expr2, expr3?.. exprn) 所有表达式必须是相同类型，或者可以隐性转换为相同的类型。返回表达式中第一个非空表达式， 如有以下语句： SELECT COALESCE(NULL,NULL,3,4,5) FROM dual 其返回结果为：3

如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值。

4 Round 函数 (四舍五入)

描述 : 传回一个数值，该数值是按照指定的小数位元数进行四舍五入运算的结果。

SELECT ROUND( number, [ decimal_places ] ) FROM DUAL 参数:

number : 欲处理之数值

decimal_places : 四舍五入 , 小数取几位 ( 预设为 0 ),如果decimal_places小于0则四舍五入到小数点向左第decimal_places位。 Sample :

select round(123.456, 0) from dual; 回传 123 select round(123.456, 1) from dual; 回传 123.5 select round(123.456, 3) from dual; 回传 123.456 select round(-123.456, 2) from dual; 回传 -123.46

select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual; 返回：5555.67 , 5600 , 5556

【相近】trunc(x[,y]) 返回截取后的值，用法同round(x[,y]),只是不四舍五入

5 trunc(x[,y])

【功能】返回x按精度y截取后的值

【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。

【返回】数字

【示例】 select trunc(5555.66666,2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;

返回：5555.66 5500 5555

【相近】round(x[,y]) 返回截取后的值，用法同trunc(x[,y]),只是要做四舍五入

6 substr与substrb区别

说明:substr,substrb均为字符串截取函数，都带有三个参数，第一个参数为所要截取的字符串，第二个参数为strart（索引均从1开始），第三个参数为length。

SQL> select substr('今天是个好日子',3,5) from dual; ---------- 是个好日子

SQL> select substrb('今天是个好日子',3,5) from dual; ----- 天是

结论:substr是按照字来算的，而substrb()是按照字节来算的。看下面的例子：

SQL> select substr('abcdef',3,4) from dual; ----

cdef

SQL> select substrb('abcdef',3,4) from dual; ---- cdef

分析：对于字母来说，substr与substrb作用时一样的，但对于汉字来说，substr是按字来取值，而substrb是按字节来取值，当所取长度为奇数时，则自动舍弃最后一位字节。 类似的还有，

length与lengthb长度计算函数

select length('你好') from dual ----output:2 select lengthb('你好') from dual ----output :4

Instr与Instrb字符串查找函数instr(原字符串,查的字符串,起始位置,第几个匹配) 返回字符串位置,找不到返回0 .

select instr('日日花前长病酒','花前',1,1) from dual ----output:3 select instrb('日日花前长病酒','花前',1,1) from dual ----output:5

7 DECODE函数

DECODE有什么用途呢？先构造一个例子，假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，通常的做法是，先选出记录中的工资字段值? select salary into var-salary from employee，然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。如果用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2),salary from employee 是不是很简洁？ DECODE的语法：DECODE(value,if1,then1,if2,then2,if3,then3,...,else)，表示如果value 等于if1时，DECODE函数的结果返回then1,...,如果不等于任何一个if值，则返回else。初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。 oracle decode函数使用方法 该函数的含义如下： IF 条件=值1 THEN

RETURN(翻译值1) ELSIF 条件=值2 THEN

RETURN(翻译值2) ......

ELSIF 条件=值n THEN

RETURN(翻译值n) ELSE

RETURN(缺省值) END IF

该函数的含义如下：

IF 条件=值1 THEN

RETURN(翻译值1) ELSIF 条件=值2 THEN

RETURN(翻译值2) ......

ELSIF 条件=值n THEN

RETURN(翻译值n) ELSE

RETURN(缺省值) END IF

1、比较大小

select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值 sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1 例如:

变量1=10，变量2=20

则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

2、表、视图结构转化

现有一个商品销售表sale，表结构为: month char(6) --月份

sell number(10,2) --月销售金额 现有数据为: 200001 1000 200002 1100 200003 1200 200004 1300 200005 1400 200006 1500 200007 1600 200101 1100 200202 1200 200301 1300

想要转化为以下结构的数据:

year char(4) --年份

month1 number(10,2) --1月销售金额 month2 number(10,2) --2月销售金额 month3 number(10,2) --3月销售金额 month4 number(10,2) --4月销售金额 month5 number(10,2) --5月销售金额 month6 number(10,2) --6月销售金额 month7 number(10,2) --7月销售金额 month8 number(10,2) --8月销售金额 month9 number(10,2) --9月销售金额

month10 number(10,2) --10月销售金额 month11 number(10,2) --11月销售金额 month12 number(10,2) --12月销售金额

结构转化的SQL语句为: create or replace view

v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12) as select

substrb(month,1,4),

sum(decode(substrb(month,5,2),'01',sell,0)), sum(decode(substrb(month,5,2),'02',sell,0)), sum(decode(substrb(month,5,2),'03',sell,0)), sum(decode(substrb(month,5,2),'04',sell,0)),

补充1：

有学生成绩表student,现在要用decode函数实现以下几个功能：成绩>85,显示优秀；>70显示良好；>60及格；否则是不及格。 假设student的编号为id,成绩为score,那么：

select id, decode(sign(score-85),1,'优秀',0,'优秀',-1, decode(sign(score-70),1,'良好',0,'良好',-1,

decode(sign(score-60),1,'及格',0,'及格',-1,'不及格'))) from student；

补充2：

Decode函数的语法结构如下:

decode (expression, search_1, result_1)

decode (expression, search_1, result_1, search_2, result_2)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n) decode (expression, search_1, result_1, default)

decode (expression, search_1, result_1, search_2, result_2, default)

decode (expression, search_1, result_1, search_2, result_2, ...., search_n, result_n, default)

decode函数比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

以下是一个简单测试，用于说明Decode函数的用法:

SQL> create table t as select username,default_tablespace,lock_date from dba_users; Table created.

SQL> select * from t;

USERNAME DEFAULT_TABLESPACE

LOCK_DATE

------------------------------ ------------------------------ --------- SYS SYSTEM SYSTEM SYSTEM OUTLN SYSTEM CSMIG SYSTEM SCOTT SYSTEM EYGLE USERS DBSNMP SYSTEM

WMSYS SYSTEM 20-OCT-04

8 rows selected.

SQL> select username,decode(lock_date,null,'unlocked','locked') status from t; USERNAME STATUS ------------------------------ --------

SYS unlocked SYSTEM unlocked OUTLN unlocked CSMIG unlocked SCOTT unlocked EYGLE unlocked DBSNMP unlocked WMSYS locked 8 rows selected.

SQL> select username,decode(lock_date,null,'unlocked') status from t; USERNAME STATUS ------------------------------ --------

SYS unlocked SYSTEM unlocked OUTLN unlocked CSMIG unlocked SCOTT unlocked EYGLE unlocked DBSNMP unlocked WMSYS

8 rows selected.

8 SYS_CONNECT_BY_PATH

在Oracle中，SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。它一定要和connect by子句合用！第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符！

实例如下：

create table test (a varchar2(10),b varchar2(10));

INSERT INTO TEST (A, B) VALUES ('1', '我'); INSERT INTO TEST (A, B) VALUES ('1', '们'); INSERT INTO TEST (A, B) VALUES ('2', '一'); INSERT INTO TEST (A, B) VALUES ('2', '起'); COMMIT;

SELECT A, B FROM TEST

A B ---------- ---------- 1 我 1 们 2 一 2 起

现在需要达到如下的效果， A B ---------- ---------- 1 我,们 2 一,起

只想用一句sql来返回结果。

SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ',')), ',') B

FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN FROM TEST) START WITH RN = 1

CONNECT BY RN - 1 = PRIOR RN AND A = PRIOR A GROUP BY A;

其中，SYS_CONNECT_BY_PATH函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分，然后连接在一个列中显示。

row_number函数的用途是非常广泛，这个函数的功能是为查询出来的每一行记录生成一个序号。生产序号的方法通过over()函数里面的语句来控制。

START WITH 代表你要开始遍历的的节点！

CONNECT BY PRIOR 是标示父子关系的对应！

9 oracle中 connect by prior 递归算法

Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的，其基本语法是：

select ... from tablename start with 条件1 connect by 条件2 where 条件3; 例：

select * from table

start with org_id = 'HBHqfWGWPy' connect by prior org_id = parent_id;

简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段: org_id,parent_id那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。

用上述语法的查询可以取得这棵树的所有记录。 其中：

条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。

条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id就是说上一条记录的org_id是本条记录的parent_id，即本记录的父亲是上一条记录。

条件3 是过滤条件，用于对返回的所有记录进行过滤。

10 group by

group by 里必须包含所有select的直接字段

11 Having与Where的区别

?where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，

where条件中不能包含聚组函数，使用where条件过滤出特定的行。

?having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。

示例1

select 类别, sum(数量) as 数量之和 from A group by 类别

having sum(数量) > 18

示例2：Having和Where的联合使用方法 select 类别, SUM(数量)from A where 数量 gt;8 group by 类别

having SUM(数量) gt; 10

11 查询表字段数 select count(column_name) from user_tab_columns where table_name=upper('WY_FDDEF'); 12

添加字段的语法：alter table tablename add (columnname datatype [default value][null/not null],?.);

修改字段的语法：alter table tablename modify (columnname datatype [default value][null/not null],?.);

删除字段的语法：alter table tablename drop (columnname);

添加、修改、删除多列的话，用逗号隔开。

本文来源：https://www.bwwdw.com/article/yfb8.html