几个常用函数-Oracle-蒲公英云

几个常用函数-Oracle

朱雀 2023-06-12 08:53 88阅读 0赞

1、decode 函数

含义解释：decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
该函数的含义如下： 
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

2） IF-THEN-ELSE语句相似

Decode函数与一系列嵌套的 IF-THEN-ELSE语句相似。base_exp与compare1,compare2等等依次进行比较。如果base_exp和第i 个compare项匹配，就返回第i 个对应的value 。如果base_exp与任何的compare值都不匹配，则返回default。每个compare值顺次求值，如果发现一个匹配，则剩下的compare值（如果还有的话）就都不再求值。一个为NULL的base_exp被认为和NULL compare值等价。如果需要的话，每一个compare值都被转换成和第一个compare 值相同的数据类型，这个数据类型也是返回值的类型。

select decode(dir,1,0,1) from a1_interval
dir 的值是1变为0，是0则变为1
比如我要查询某班男生和女生的数量分别是多少?
通常我们这么写:
select count(*) from 表 where 性别 ＝ 男；
select count(*) from 表 where 性别 ＝ 女；
要想显示到一起还要union一下，太麻烦了
用decode呢，只需要一句话
select decode(性别，男，1，0），decode(性别，女，1，0） from 表

3）order by对字符列进行特定的排序

例：表table_subject，有subject_name列。要求按照：语、数、外的顺序进行排序。
这时，就可以非常轻松的使用Decode完成要求了。
select * from table_subject order by decode(subject_name, '语文', 1, '数学', 2, , '外语',3)

4）LISTAGG() within group()

对查询的子数据去重合并
LISTAGG(合并字段，连接符) within group(排序字段)

需求：查询编号绑定的部门，显示编号的详细信息，部门名称合并显示
select rule.*,
     (select name
        from (select nd.no_id as id,
                     LISTAGG(dp.name, ',') within group(order by dp.name) as name
                from sys_number_dept nd, sys_dept dp
               where nd.dept_id = dp.id
               group by nd.no_id)
       where id = rule.id)
from sys_number_rule rule
where rule.DELETE_STATUS = 1;

5）start with connect by prior 递归查询用法

start with ：遍历起始条件，有个小技巧，如果要查父结点，这里可以用子结点的列，反之亦然。

connect by ：连接条件。
关键词prior：prior跟父节点列放在一起，就是往父结点方向遍历；prior跟子结点列放在一起，则往叶子结点方向遍历。

//例如：
    SELECT D.ID
        FROM SYS_DEPT D
      START WITH D.ID = '7125805245709b55c1919dd623308259'
     CONNECT BY nocycle D.PARENT_ID = PRIOR D . ID
       AND D.DELETE_STATE = 1
    //nocycle ：避免死循环，
    //本例查询所有的子部门，包括2级和3级部门

6）将NVARCHAR2转换为VARCHAR2:

declare     
      v_username   varchar2(12)   ;     
      v_nm_login   nvarchar2(12);     
  begin     
      select   utl_raw.cast_to_varchar2(utl_raw.cast_to_raw(v_nm_login))     
          into   v_username     
          from   dual;     
  end;

7 将VARCHAR2转换为NVARCHAR2:

declare     
      v_username   varchar2(12)   :=   'pavan408';     
      v_nm_login   nvarchar2(12);     
  begin     
      select   utl_raw.cast_to_nvarchar2(utl_raw.cast_to_raw(v_username))     
          into   v_nm_login     
          from   dual;     
  end;