ORACLE 语句 新增Listagg函数（20201124）

1.【NOT】 Between A and B

当A<=n<=B时，返回true。

select * from TABLE_A where n between A and B;

2.【NOT】 Exists

当TABLE_A.ID在TABLE_B.ID中存在时，返回true。

select * from TABLE_A where exists(select 1 from TABLE_B where TABLE_A.ID=TABLE_B.ID);

3.ABS函数

返回n的绝对值。

select abs（n） from dual;

4.CEIL函数，FLOOR函数

ceil：天花板，向上取整，即返回大于或等于n的数值。

floor：地板，向下取整，返回小于或等于n的数值。

select ceil(-5.25),ceil(5.25) from dual;

结果：-5，6。

select floor(-5.25),floor(5.25) from dual;

结果：-6，5。

5.WM_CONCAT函数

常用于行数据合并，返回大对象数据类型CLOB，也可以通过TO_CHAR()转换，中间为逗号隔开。

select wm_concat(name) from sys_student where name like '季%'; 

但是此函数无法排序，需配合Partition分区分组，再使用Order by排序，

SELECT LOT.LOT_NO,
       TO_CHAR(wm_concat(SERIAL_NUMBER)
               over(partition by LOT_NO order by SERIAL_NUMBER)) SN
  FROM G_PALLET_LOT LOT, G_SN_STATUS SN
 WHERE LOT.PALLET_NO = SN.PALLET_NO
   AND LOT.PALLET_NO = '2K31D5814115';

 再嵌套一层Select From，取Max（SN）即可。

SELECT COUNT(*) QTY, LOT_NO, MAX(TO_CHAR(SN)) SN
  FROM (SELECT LOT.LOT_NO,
               TO_CHAR(wm_concat(SERIAL_NUMBER) over(partition by LOT_NO order by SERIAL_NUMBER)) SN
          FROM G_PALLET_LOT LOT, G_SN_STATUS SN
         WHERE LOT.PALLET_NO = SN.PALLET_NO
           AND LOT.PALLET_NO = '2K31D5814115')
 GROUP BY LOT_NO;

 oracle11g引进了listagg函数，也可满足排序要求，见第10点。

 若已该值为条件进行SQL查询，则需使用regexp_substr()结合connetc by，正则表达式截取查询

SELECT *
      FROM G_SN_STATUS
     WHERE WORK_ORDER IN
           (SELECT REGEXP_SUBSTR('23024251,123', '[^,]+', 1, rownum) AS WORK_ORDER
              FROM DUAL
            CONNECT BY ROWNUM <=
                       LENGTH('23024251,123') - LENGTH(REPLACE('23024251,123', ',', '')) + 1);

6. UNION，UNION ALL

union 和 union all的区别仅在于union合并去重，union all不去重。

表TABLE_A

ID   name   sex

1     张三     男

2     李四     男

3     陆六     女

表TABLE_B

ID   name   sex

1     王五     男

2     李四     男

3     陆六     女

4     赵八     男

select id,name,sex from table_a

union

select id,name,sex from table_b;

结果：

1     张三     男

1     王五     男

2     李四     男

3     陆六     女

4     赵八     男

select id,name,sex from table_a

union all

select id,name,sex from table_b;

结果：

1     张三     男

2     李四     男

3     陆六     女

1     王五     男

2     李四     男

3     陆六     女

4     赵八     男

7. intersect，minus

intersect计算交集，

minus计算差集（左边的记录在右边存在则删除，不存在则显示；右边的记录在左边不管存不存在都删除）。

select id,name,sex from table_a

intersect

select id,name,sex from table_b;

结果：

2     李四     男

3     陆六     女

select id,name,sex from table_a

minus

select id,name,sex from table_b;

结果：

1     张三     男

 8.新增、修改、删除列

新增tell，create_time列，默认系统日期。

alter table table_a add （tell number,create_time date default sysdate）;

修改tell列为tell_phone，并将字符类型修改为varchar2(13)。

alter table table_a rename column tell to tell_phone;

alter table table_a modify tell_phone varchar2(13);

删除tell_phone列。

alter table table_a drop column tell_phone;

 9.索引的添加，删除

为table_a.id添加索引。

create index id_idx on table_a(ID);

删除索引id_idx。

drop index id_idx;

 10.Listagg聚合函数

Listagg传两个参数，第一个为字段，第二个为分割的字符（不写直接拼接字符串）。

within group(order by xx)里面的就是聚合列拼接顺序的排序，用法跟order by 一样。

因为是聚合函数，所以需配合Group by使用。

SELECT LOT.LOT_NO,
       LISTAGG(SERIAL_NUMBER, ',') WITHIN GROUP(ORDER BY SERIAL_NUMBER) SN
  FROM G_PALLET_LOT LOT, G_SN_STATUS SN
 WHERE LOT.PALLET_NO = SN.PALLET_NO
   AND LOT.PALLET_NO = '2K31D5814115'
 GROUP BY LOT_NO;

 11.进制转换函数

1.10进制转16进制

select to_char(123,'fmXXX') from dual;

若位数长，多加X，过多会在左边多出空格，X前加fm即可。

2.16进制转10进制

select to_number('fff','XXX') from dual;

3.10进制转32进制
  --0~9，A-Z，不可用I、O、Q、U，序号不可重码
  FUNCTION TRANS10TO36_NOT_IOQU(IN_NUM NUMBER, OUT_LENGTH NUMBER)  
   RETURN VARCHAR AS
    RET VARCHAR(100);
    X   NUMBER;
    N   NUMBER;
    L   VARCHAR(32);
  BEGIN
    IF (INTEGER_NUM IS NULL) THEN
      IF (OUT_LENGTH IS NULL) THEN
        RETURN NULL;
      ELSE
        RETURN LPAD('0', OUT_LENGTH, '0');
      END IF;
    END IF;
    L := '0123456789ABCDEFGHJKLMNPRSTVWXYZ'; --不可用I、O、Q、U  完整就是36进制
    X := INTEGER_NUM;
    LOOP
      N   := MOD(X, 32); --mod(x,y) (1)MOD返回x除以y的余数,如果y是0,返回x。
      RET := CONCAT(SUBSTR(L, ABS(N) + 1, 1), RET);
      X   := TRUNC(X / 32);
      EXIT WHEN X = 0;
    END LOOP;
    IF (OUT_LENGTH IS NOT NULL AND LENGTH(RET) < OUT_LENGTH) THEN
      RET := LPAD(RET, OUT_LENGTH, '0');
    END IF;
    IF (INTEGER_NUM < 0) THEN
      RET := '-' || RET;
    END IF;
    RETURN RET;
  END;

4.特殊10进制转32进制
  --0~9999十进制 10000开始为999A，999B...ZZZZ
  FUNCTION NUM_TO_32(IN_SN VARCHAR2)  
   RETURN VARCHAR2 AS  
    STR     VARCHAR2(32) := '0123456789ABCDEFGHJKLMNPRSTVWXYZ'; --0~9 A~Z字符串去除I、O、Q、U 
    OUT_NUM VARCHAR2(25);
  BEGIN
  
    IF IN_SN = 'ZZZZ' THEN
      RETURN '0001';
    END IF;
  
    --32进制+1计算，根据获取SN的最后一码往后+1，例如：最后一码为A，则截取STR字符串A的后一位B
    IF SUBSTR(IN_SN, 4, 1) <> 'Z' THEN
      OUT_NUM := SUBSTR(IN_SN, 1, 3) ||
                 SUBSTR(STR, INSTR(STR, SUBSTR(IN_SN, 4, 1)) + 1, 1);
    ELSIF SUBSTR(IN_SN, 4, 1) = 'Z' AND SUBSTR(IN_SN, 3, 1) <> 'Z' THEN
    
      OUT_NUM := SUBSTR(IN_SN, 1, 2) ||
                 SUBSTR(STR, INSTR(STR, SUBSTR(IN_SN, 3, 1)) + 1, 1) || 0;
    
    ELSIF SUBSTR(IN_SN, 4, 1) = 'Z' AND SUBSTR(IN_SN, 3, 1) = 'Z' AND
          SUBSTR(IN_SN, 2, 1) <> 'Z' THEN
      OUT_NUM := SUBSTR(IN_SN, 1, 1) ||
                 SUBSTR(STR, INSTR(STR, SUBSTR(IN_SN, 2, 1)) + 1, 1) || '00';
    
    ELSIF SUBSTR(IN_SN, 4, 1) = 'Z' AND SUBSTR(IN_SN, 3, 1) = 'Z' AND
          SUBSTR(IN_SN, 2, 1) = 'Z' AND SUBSTR(IN_SN, 1, 1) <> 'Z' THEN
      OUT_NUM := SUBSTR(STR, INSTR(STR, SUBSTR(IN_SN, 1, 1)) + 1, 1) ||
                 '000';
    END IF;
  
    RETURN OUT_NUM;
  EXCEPTION
    WHEN OTHERS THEN
      OUT_NUM := 'MES_ERROR:' || SQLERRM;
      RETURN OUT_NUM;
  END;

5.数字转AAA-ZZZ算法

FUNCTION FUN_TO_AZ(IN_NUM NUMBER) RETURN VARCHAR2 AS
    STR VARCHAR2(25) := 'ABCDEFGHJKLMNPQRSTUVWXYZ'; --A~Z字符串去除I、O
  
    V_NUM    NUMBER;
    V_NUMBER VARCHAR(10);
    OUT_NUM  VARCHAR2(25);
  BEGIN
    V_NUM := IN_NUM;
  
    IF V_NUM < 0 OR V_NUM > (24 * 24 * 24 + 24 * 24 + 24) * 10000 THEN
      RETURN OUT_NUM;
    END IF;
    IF MOD(IN_NUM, 10000) = 0 THEN
      V_NUM := IN_NUM + 1;
    END IF;
    IF LENGTH(IN_NUM) < 4 THEN
      V_NUMBER := LPAD(V_NUM, 4, 0);
    ELSE
      V_NUMBER := LPAD(SUBSTR(V_NUM, -4, 4), 4, 0);
    END IF;
  
    V_NUM := TRUNC(V_NUM / 10000);
  
    --默认A开头,共24种组合
    IF V_NUM < 24 THEN
    
      OUT_NUM := SUBSTR(STR, V_NUM + 1, 1) || V_NUMBER;
    
      --AA-ZZ共24的2次方组合
    ELSIF V_NUM >= 24 AND V_NUM < (24 * 24 + 24) THEN
    
      OUT_NUM := SUBSTR(STR, TRUNC(V_NUM / 24), 1) ||
                 SUBSTR(STR, MOD(V_NUM, 24) + 1, 1) || V_NUMBER;
    
      --AAA-ZZZ共24的3次方组合
    ELSIF V_NUM >= (24 * 24 + 24) AND V_NUM < 24 * 24 * 24 THEN
    
      OUT_NUM := SUBSTR(STR, TRUNC((V_NUM / 24) / 24), 1) ||
                 SUBSTR(STR, MOD(TRUNC(V_NUM / 24), 24), 1) ||
                 SUBSTR(STR, MOD(V_NUM, 24) + 1, 1) || V_NUMBER;
    ELSE
      OUT_NUM := SUBSTR(STR, TRUNC((V_NUM / 24) / 24), 1) ||
                 SUBSTR(STR, MOD(TRUNC(V_NUM / 24), 24) + 1, 1) ||
                 SUBSTR(STR, MOD(V_NUM, 24) + 1, 1) || V_NUMBER;
    END IF;
  
    RETURN OUT_NUM;
  END;

12.误删记录以后，可查询1200s以内的数据，时间可设置

SELECT * FROM table as of timestamp(systimestamp - interval'1200'second)