ORACLE 语句 新增Listagg函数（20201124）

2024-02-12 16:20
文章标签 oracle 函数 新增 语句 20201124 listagg

本文主要是介绍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)

这篇关于ORACLE 语句 新增Listagg函数（20201124）的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/703023。

相关文章

C++ Sort函数使用场景分析

C++ Sort函数使用场景分析

《C++Sort函数使用场景分析》sort函数是algorithm库下的一个函数,sort函数是不稳定的,即大小相同的元素在排序后相对顺序可能发生改变,如果某些场景需要保持相同元素间的相对顺序,可使... 目录C++ Sort函数详解一、sort函数调用的两种方式二、sort函数使用场景三、sort函数排序
阅读更多...
C语言函数递归实际应用举例详解

C语言函数递归实际应用举例详解

《C语言函数递归实际应用举例详解》程序调用自身的编程技巧称为递归,递归做为一种算法在程序设计语言中广泛应用,：本文主要介绍C语言函数递归实际应用举例的相关资料,文中通过代码介绍的非常详细,需要的朋... 目录前言一、递归的概念与思想二、递归的限制条件 三、递归的实际应用举例（一）求 n 的阶乘（二）顺序打印
阅读更多...
C/C++错误信息处理的常见方法及函数

C/C++错误信息处理的常见方法及函数

《C/C++错误信息处理的常见方法及函数》C/C++是两种广泛使用的编程语言,特别是在系统编程、嵌入式开发以及高性能计算领域,：本文主要介绍C/C++错误信息处理的常见方法及函数,文中通过代码介绍... 目录前言1. errno 和 perror()示例：2. strerror()示例：3. perror(
阅读更多...
Oracle数据库常见字段类型大全以及超详细解析

Oracle数据库常见字段类型大全以及超详细解析

《Oracle数据库常见字段类型大全以及超详细解析》在Oracle数据库中查询特定表的字段个数通常需要使用SQL语句来完成,：本文主要介绍Oracle数据库常见字段类型大全以及超详细解析,文中通过... 目录前言一、字符类型（Character）1、CHAR：定长字符数据类型2、VARCHAR2：变长字符数
阅读更多...
Kotlin 作用域函数apply、let、run、with、also使用指南

Kotlin 作用域函数apply、let、run、with、also使用指南

《Kotlin作用域函数apply、let、run、with、also使用指南》在Kotlin开发中,作用域函数（ScopeFunctions）是一组能让代码更简洁、更函数式的高阶函数,本文将... 目录一、引言：为什么需要作用域函数？二、作用域函China编程数详解1. apply：对象配置的 “流式构建器”最
阅读更多...
MySQL INSERT语句实现当记录不存在时插入的几种方法

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面：本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE
阅读更多...
MySQL新增字段后Java实体未更新的潜在问题与解决方案

MySQL新增字段后Java实体未更新的潜在问题与解决方案

《MySQL新增字段后Java实体未更新的潜在问题与解决方案》在Java+MySQL的开发中,我们通常使用ORM框架来映射数据库表与Java对象,但有时候,数据库表结构变更（如新增字段）后,开发人员可... 目录引言1. 问题背景：数据库与 Java 实体不同步1.1 常见场景1.2 示例代码2. 不同操作
阅读更多...
Android Kotlin 高阶函数详解及其在协程中的应用小结

Android Kotlin 高阶函数详解及其在协程中的应用小结

《AndroidKotlin高阶函数详解及其在协程中的应用小结》高阶函数是Kotlin中的一个重要特性,它能够将函数作为一等公民（First-ClassCitizen）,使得代码更加简洁、灵活和可... 目录1. 引言2. 什么是高阶函数？3. 高阶函数的基础用法3.1 传递函数作为参数3.2 Lambda
阅读更多...
C++中::SHCreateDirectoryEx函数使用方法

C++中::SHCreateDirectoryEx函数使用方法

《C++中::SHCreateDirectoryEx函数使用方法》::SHCreateDirectoryEx用于创建多级目录,类似于mkdir-p命令,本文主要介绍了C++中::SHCreateDir... 目录1. 函数原型与依赖项2. 基本使用示例示例 1：创建单层目录示例 2：创建多级目录3. 关键注
阅读更多...
C++中函数模板与类模板的简单使用及区别介绍

C++中函数模板与类模板的简单使用及区别介绍

《C++中函数模板与类模板的简单使用及区别介绍》这篇文章介绍了C++中的模板机制,包括函数模板和类模板的概念、语法和实际应用,函数模板通过类型参数实现泛型操作,而类模板允许创建可处理多种数据类型的类,... 目录一、函数模板定义语法真实示例二、类模板三、关键区别四、注意事项 ‌在C++中，模板是实现泛型编程
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2