几个常用函数-Oracle

朱雀 2023-06-12 08:53 67阅读 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;