米鼠商城

多快好省,买软件就上米鼠网

最新项目

人才服务

靠谱的IT人才垂直招聘平台

Oracle 数据库常用函数

  • xym01
  • 1
  • 2021-04-14 17:59

    主要记录一下使用Oracle数据库时常用的一些函数,便于平时看一下,加深一下印象,避免时间长忘了!

1、条件取值:decode()函数

    语法: DECODE(条件,值1,翻译值1,...值n,翻译值n,缺省值),根据条件字段不同值,取对应的翻译值作为结果,如果没有一个值符合条件,就取最后的缺省值。

    使用场景:1、字典值翻译;2、类型字段,根据不同值,返回表中不同字段的值。还可以和其他函数结合使用。

2、null校验: NVL()函数

    语法:NVL(EXPR1,EXPR2),若EXPR1是NULL,则返回EXPR2,否则返回EXPR1。

    使用场景:空值显示处理。

3、递归查询:START WITH  CONNECT BY PRIOR

    语法:START WITH [condition1] CONNECT BY PRIOR [condition2],condition1 是遍历起始条件,condition2是连接条件,PRIOR 决定遍历方向。

    使用场景:表中数据有层级关系,查询层级关系,如:查询指定组织的所有上级组织或下级组织。

    使用示例:

        1、查询指定组织及其子级组织: select org_id,org_name from orgnization START with org_id = 10001002  CONNECT BY PRIOR org_id = parent_id。

        2、查询指定组织及其父级组织: select org_id,org_name from orgnization  START with org_id = 10001002  CONNECT BY PRIOR parent_id = org_id。

4、临时表: WITH ... AS 

    语法:with temp1  as (), temp2 as ()    select  from temp1 ,temp2 ,根据需要设置一个或多个临时表。

    使用场景:sql较为复杂,简化结构,增加可读性;可复用一些查询结果。

5、拼接一列多行结果集: wm_concat()函数

    语法:wm_concat(col),会把多行结果,转换成一行,多个值逗号隔开。

    使用场景:一对多关系,只需要一个合并的结果,如: select  to_char(wm_concat(groupedict_id)) from  GROUPMEMBER where  Client_Id =1900。

6、多行合并: listagg() WITHIN GROUP () 

    语法:listagg(col1,'分隔符') WITHIN GROUP (order by col2),col1 是需要查询的列,col2 是多个结果顺序的排序列。

    使用场景:指定条件,查询结果多行转一行;分组查询,多行转一行。

    使用示例:

1、指定条件,查询结果多行转一行

WITH TEMP AS(   SELECT 500 POPULATION, 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL   SELECT 1500 POPULATION, 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL   SELECT 1000 POPULATION, 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL    )   SELECT  LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY desc)  RANK   FROM TEMP  WHERE NATION = 'CHINA'

2、分组查询,多行转一行

WITH TEMP AS(   SELECT 500 POPULATION, 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL   SELECT 1500 POPULATION, 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL   SELECT 1000 POPULATION, 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL    )   SELECT  NATION, LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY desc)  RANK   FROM TEMP  GROUP BY NATION

还有一种语法形式,可以使分组的多行结果合并,但是查询行数不变少

WITH TEMP AS(   SELECT 500 POPULATION, 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL   SELECT 1500 POPULATION, 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL   SELECT 1000 POPULATION, 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL    )   SELECT  NATION, LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY desc)   OVER(PARTITION BY NATION ) RANK   FROM TEMP  

7、 EXISTS函数

    这个函数可以有两个作用:1、代替 DISTINCT,更加高效 ;2、代替 IN , 使用EXISTS,Oracle会首先检查主查询,然后运行子查询直到它找到第一个匹配项,这就节省了时间。Oracle在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。 这也就是使用EXISTS比使用IN通常查询速度快的原因。IN对应的有 NOT IN ,这个也有 NOT EXISTS 。

select DISTINCT t1.id from T_CONF t1 LEFT JOIN T_TASK_DETAIL t2   ON t1.id = t2.id; select id from T_CONF where EXISTS (                 select 1/'a'(任意写) from T_CONF t1 LEFT JOIN T_TASK_DETAIL t2  ON t1.id = t2.id  )

select id from T_CONF t1 where EXISTS (  select 1 from  T_TASK_DETAIL t2 WHERE   t1.id = t2.id ); select t1.id from T_CONF t1 where t1.id in   (select t2.id from T_TASK_DETAIL t2);

数据量T1 << T2 时,EXISTS 的查询效率高。数据量T1 >> T2 时,in 的查询效率高。

8、字符截取函数: substr()

    语法:1、substr(string string, int a, int b),a 截取字符串的开始位置(注:当a等于0或1时,都是从第一位开始截取), b 要截取的字符串的长度。2、 substr(string string, int a) , a 可以理解为从第a个字符开始截取后面所有的字符串。(注:同上)。

9、数字截取函数:trunc(),ROUND()

    语法: TRUNC(number,num_digits),Number 需要截尾取整的数字, Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。如果Num_digits为正数,则截取小数点后Num_digits位;如果为负数,则先保留整数部分,然后从个位开始向前数,并将遇到的数字都变为0。 TRUNC()函数在截取时不进行四舍五入,直接截取。

    ROUND() 函数类似,但是 会将计算结果进行四舍五入

    使用示例:

10、格式化函数:TO_CHAR()

    感觉最常用,主要关注一下数字精度问题: TO_CHAR(A/B,‘FM99990.99’)/TO_CHAR(A/B,‘FM99990.00’) 。 0和9应该都是类似于占位符,你实际除得的结果中的数字一般会替换掉9,但是当你整数部分只有一位且是0的时候,那么你需要在格式化的时候用0来指定格式化的模型,如那个.67的样子,小数点后面的值,你可以用0也可以用9来指定模式,用9指定的结果就是你的小数点本来后面几位它就显示几位,如果用0来指代,它在位数不够的情况下会用0去进行占位填充。

11、排序空值处理:Nulls first和nulls last

    如果Order by 中指定了表达式Nulls first则表示null值的记录将排在最前(不管是asc 还是 desc)。如果Order by 中指定了表达式Nulls last则表示null值的记录将排在最后 (不管是asc 还是 desc)。

12、替换字符函数:repalce()

    语法: repalce(col,'a','b'),col 要被替换字段值,a是字段值中的字符,b是要替换a的字符。 会替换掉字符串中所有的a。

13、行数:ROW_NUMBER() OVER()

    语法:ROW_NUMBER() OVER(ORDER BY col1),ROW_NUMBER() OVER(PARTITION BY col2  ORDER BY col1),给结果集返回一个行数。

    使用场景:目前主要用来去重,外层查询设置条件行数等于1,可去重。

WITH TEMP AS(   SELECT 500 POPULATION, 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL   SELECT 1500 POPULATION, 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL   SELECT 1000 POPULATION, 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL    )   SELECT POPULATION,NATION, ROW_NUMBER() OVER(partition by NATION order by POPULATION) FROM TEMP  

WITH TEMP AS(   SELECT 500 POPULATION, 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL   SELECT 1500 POPULATION, 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL   SELECT 1000 POPULATION, 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL   SELECT 500 POPULATION, 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL    )   SELECT POPULATION,NATION, ROW_NUMBER() OVER( order by POPULATION) FROM TEMP  WHERE NATION = 'CHINA'

14、行转列:Pivot() 与列转行unpivot()

    语法:pivot(任一聚合函数 for 要转行的列名 in (需转为列名的值  对应列名)); unpivot(列对应值的新列名 for 列转行后的新列名 in (需转为行的列名));

    直接看示例:

WITH TEMP AS(   SELECT 1 GP , '01' STATUS, 10 CNT FROM DUAL UNION ALL   SELECT 1 GP , '02' STATUS, 30 CNT FROM DUAL UNION ALL   SELECT 2 GP , '01' STATUS, 40 CNT FROM DUAL UNION ALL   SELECT 2 GP , '02' STATUS, 10 CNT FROM DUAL UNION ALL   SELECT 3 GP , '01' STATUS, 60 CNT FROM DUAL UNION ALL   SELECT 3 GP , '02' STATUS, 20 CNT FROM DUAL  )  

SELECT *  FROM TEMP  pivot(             max(CNT) --max(CNT) 此处必须为聚合函数             for STATUS in ( --STATUS 即要转成列的字段             '01' as 在线,              '02' as 下线)) --in () 对要转成列的每一个值指定一个列名

可以直接加上  unpivot (数量 for 状态 in (在线, 下线)) 看看列转行

15、判断大小:sign()

    语法:sign(num), 根据某个值是0、正数还是负数,分别返回0、1、-1,如:sign(sale-6000) 判断大小。

16、字符查找函数: instr()

    语法: 1、instr( string1, string2 ) /instr(源字符串, 目标字符串); 2、instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, 目标字符串, 起始位置, 匹配序号)

    使用场景:模糊查询。

    使用示例:

         select instr('helloworld','l') from dual; --返回结果:3 默认第一次出现“l”的位置          select instr('helloworld','lo') from dual; --返回结果:4 即:在“lo”中,“l”开始出现的位置          select instr('helloworld','wo') from dual; --返回结果:6 即“w”开始出现的位置          select instr('helloworld','l',2,2) from dual; --返回结果:4 也就是说:在"helloworld"的第2(e)号位置开始,查找第二次出现的“l”的位置          select instr('helloworld','l',3,2) from dual; --返回结果:4 也就是说:在"helloworld"的第3(l)号位置开始,查找第二次出现的“l”的位置          select instr('helloworld','l',4,2) from dual; --返回结果:9 也就是说:在"helloworld"的第4(l)号位置开始,查找第二次出现的“l”的位置          select instr('helloworld','l',-1,1) from dual; --返回结果:9 也就是说:在"helloworld"的倒数第1(d)号位置开始,往回查找第一次出现的“l”的位置          select instr('helloworld','l',-2,2) from dual; --返回结果:4 也就是说:在"helloworld"的倒数第2(l)号位置开始,往回查找第二次出现的“l”的位置         select instr('helloworld','l',2,3) from dual; --返回结果:9 也就是说:在"helloworld"的第2(e)号位置开始,查找第三次出现的“l”的位置         select instr('helloworld','l',-2,3) from dual; --返回结果:3 也就是说:在"helloworld"的倒数第2(l)号位置开始,往回查找第三次出现的“l”的位置

 

 

 



城市合伙人全球招募中:400-150-9800

参与线下宏伟蓝图,用业绩说话!
软件线索、软件需求,米鼠网帮你变现!
更灵活的合作模式(不限地域、不限金额、不限项目)
更高额的提成比例(提成是软件项目利润的80%)
利润的核算方式:
以平台公开招标的最低价中标价格为基准,剩下的为利润部分,如对平台的最低中标价格有异议,可以推荐供应商进行竞标。
义务:
1、作为城市合伙人,在该城市利用自身优势推广“米鼠网平台”,拓展甲 方所拥有的“米鼠网平台”实名认证用户和 VIP 用户
2、作为城市合伙人,在该城市利用自身优势推广“米鼠网商城”,并寻求该地域软件产品销售商,促成软件产品销售商委托甲方在“米鼠网商城”上代理销售软件产品销售商的软件产品的交易,并拓展软件产品采购用户促成与甲方的采购交易。



如有侵权请联系邮箱(service@misuland.com)

猜你喜欢

评论留言