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。

相关文章

oracle DBMS_SQL.PARSE的使用方法和示例

oracle DBMS_SQL.PARSE的使用方法和示例

《oracleDBMS_SQL.PARSE的使用方法和示例》DBMS_SQL是Oracle数据库中的一个强大包,用于动态构建和执行SQL语句,DBMS_SQL.PARSE过程解析SQL语句或PL/S... 目录语法示例注意事项DBMS_SQL 是 oracle 数据库中的一个强大包，它允许动态地构建和执行
阅读更多...
在MySQL执行UPDATE语句时遇到的错误1175的解决方案

在MySQL执行UPDATE语句时遇到的错误1175的解决方案

《在MySQL执行UPDATE语句时遇到的错误1175的解决方案》MySQL安全更新模式（SafeUpdateMode）限制了UPDATE和DELETE操作,要求使用WHERE子句时必须基于主键或索引... mysql 中遇到的 Error Code: 1175 是由于启用了 安全更新模式（Safe Upd
阅读更多...
Python itertools中accumulate函数用法及使用运用详细讲解

Python itertools中accumulate函数用法及使用运用详细讲解

《Pythonitertools中accumulate函数用法及使用运用详细讲解》：本文主要介绍Python的itertools库中的accumulate函数,该函数可以计算累积和或通过指定函数... 目录1.1前言：1.2定义：1.3衍生用法：1.3Leetcode的实际运用:总结 1.1前言：本文将详
阅读更多...
PLsql Oracle 下载安装图文过程详解

PLsql Oracle 下载安装图文过程详解

《PLsqlOracle下载安装图文过程详解》PL/SQLDeveloper是一款用于开发Oracle数据库的集成开发环境,可以通过官网下载安装配置,并通过配置tnsnames.ora文件及环境变... 目录一、PL/SQL Developer 简介二、PL/SQL Developer 安装及配置详解1.下
阅读更多...
轻松上手MYSQL之JSON函数实现高效数据查询与操作

轻松上手MYSQL之JSON函数实现高效数据查询与操作

《轻松上手MYSQL之JSON函数实现高效数据查询与操作》：本文主要介绍轻松上手MYSQL之JSON函数实现高效数据查询与操作的相关资料,MySQL提供了多个JSON函数,用于处理和查询JSON数... 目录一、jsON_EXTRACT 提取指定数据二、JSON_UNQUOTE 取消双引号三、JSON_KE
阅读更多...
MySQL数据库函数之JSON_EXTRACT示例代码

MySQL数据库函数之JSON_EXTRACT示例代码

《MySQL数据库函数之JSON_EXTRACT示例代码》：本文主要介绍MySQL数据库函数之JSON_EXTRACT的相关资料,JSON_EXTRACT()函数用于从JSON文档中提取值,支持对... 目录前言基本语法路径表达式示例示例 1: 提取简单值示例 2: 提取嵌套值示例 3: 提取数组中的值注意
阅读更多...
oracle如何连接登陆SYS账号

oracle如何连接登陆SYS账号

《oracle如何连接登陆SYS账号》在Navicat12中连接Oracle11g的SYS用户时,如果设置了新密码但连接失败,可能是因为需要以SYSDBA或SYSOPER角色连接,解决方法是确保在连接... 目录oracle连接登陆NmOtMSYS账号工具问题解决SYS用户总结oracle连接登陆SYS账号
阅读更多...
Oracle数据库如何切换登录用户(system和sys)

Oracle数据库如何切换登录用户(system和sys)

《Oracle数据库如何切换登录用户(system和sys)》文章介绍了如何使用SQL*Plus工具登录Oracle数据库的system用户,包括打开登录入口、输入用户名和口令、以及切换到sys用户的... 目录打开登录入口登录system用户总结打开登录入口win+R打开运行对话框，输php入：sqlp
阅读更多...
查询Oracle数据库表是否被锁的实现方式

查询Oracle数据库表是否被锁的实现方式

《查询Oracle数据库表是否被锁的实现方式》本文介绍了查询Oracle数据库表是否被锁的方法,包括查询锁表的会话、人员信息,根据object_id查询表名,以及根据会话ID查询和停止本地进程,同时,... 目录查询oracle数据库表是否被锁1、查询锁表的会话、人员等信息2、根据 object_id查询被
阅读更多...
Java function函数式接口的使用方法与实例

Java function函数式接口的使用方法与实例

《Javafunction函数式接口的使用方法与实例》：本文主要介绍Javafunction函数式接口的使用方法与实例,函数式接口如一支未完成的诗篇,用Lambda表达式作韵脚,将代码的机械美感... 目录引言-当代码遇见诗性一、函数式接口的生物学解构1.1 函数式接口的基因密码1.2 六大核心接口的形态学
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2