oracle如何合并多个sys_refcursor详解

2024-08-09 13:42:23

一、背景

在数据开发中，有时你需要合并两个动态游标sys_refcursor。

开发一个存储过程PROC_A,这个过程业务逻辑相当复杂，代码篇幅较长。一段时间后要开发一个PROC_B,要用PROC_A同样的逻辑,而且在这个过程中，还要循环调用PROC_A这个过程。摆在你面前的有两个选择。

打开PL/SQL,仔细的读PROC_A这个过程，一直到明白了所有的逻辑，然后在自己的过程中重写这个逻辑。
直接复制PROC_A这个过的代码过来，多写极端。还是业界标准大法好
针对循环调用的，建立一个临时表，循环插入数据到临时表（但这里还有一个问题，每次返回的游标可能列都不相同，建立临时表就显得复杂了）

好吧，这个新的过程是完成了，可是看上去，它更复杂了，代码量更大了。完全不能接受，必须改改！
这时，已经默默打开了ORACLE官方帮助文档 https://docs.oracle.com/cd/B19306_01/index.htm，寻找一个可行的办法，最终目标标是要解析，整合，合并游标 sys_refcursor

二、思路

经过搜索查询，找到以下可行的方案

序列化sys_refcursor为xml文档,ORACLE对xml支持还不错，12C已经有JSON格式了
使用ORACLE xml解析的方法，对序列化的xml文档，添加、删除、修改
转换为内存表，通过游标返回查询的结果

为此你需要掌握的知识有

使用 Dbms_Lob 个package操作clob类型数据，因为解析后的游标可能用varchar2是装不下的，帮助地址 https://docs.oracle.com/cd/E11882_01/timesten.112/e21645/d_lob.htm#TTPLP600。
重点掌握Oracle类型xmltype如何使用 https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/t_xml.htm#BABHCHHJ

三、实现

从上边的帮助文档中，知道xmltype的构造函数中可以直接传入游标xmltype(refcursor)从而得到一个xmltype，调用xmltype的getClobVal方法，可得到序列化的结果，所以它的结构是这样的

<?xml version="1.0"?>
<ROWSET>
<ROW>
<COLUMNNAME1></COLUMNNAME1>
<COLUMNNAME2></COLUMNNAME2>
<...>...</...>
</ROW>
....
</ROWSET>

所以，如果需要合并两个数据列相同游标，只需要提取DOM中的ROW节点数据保存到定义的clob字段中去。

提取dom中片段，采用标准的xpath语法，/ROWSET/ROW这里提取ROW信息

Declare
x xmltype;
rowxml clob;
mergeXml clob;
ref_cur Sys_Refcursor;
ref_cur2 Sys_Refcursor;
ref_cur3 Sys_Refcursor;
begin
 open ref_cur for
 select F_USERNAME, F_USERCODE, F_USERID
 from Tb_System_User
 where F_userid = 1;
 Dbms_Lob.createtemporary(mergeXml, true);
 Dbms_Lob.writeappend(mergeXml, 8, '<ROWSET>');
 x := xmltype(ref_cur);
 Dbms_Output.put_line('=====完整的REFCURSOR结构=====');
 Dbms_Output.put_line(x.getClobVal());
 Dbms_Output.put_line('=====只提取行信息=====');
 rowxml := x.extract('/ROWSET/ROW').getClobVal(0, 0);
 Dbms_Output.put_line(rowxml);
 Dbms_Lob.append(mergeXml, rowxml);ROWSET
 open ref_cur2 for
 select F_USERNAME, F_USERCODE, F_USERID
 from Tb_System_User
 where F_userid = 1000;
 x := xmltype(ref_cur2);
 rowxml := x.extract('/ROWSET/ROW').getClobVal(0, 0);
 Dbms_Lob.append(mergeXml, rowxml);
 Dbms_Lob.writeappend(mergeXml, 9, '</ROWSET>');
 Dbms_Output.put_line('=====合并后的信息=====');
 Dbms_Output.put_line(mergeXml);
end;

执行这段代码输出的结果是这样的

=====完整的REFCURSOR结构=====
<?xml version="1.0"?>
<ROWSET>
<ROW>
<F_USERNAME>系统管理员</F_USERNAME>
<F_USERCODE>admin</F_USERCODE>
<F_USERID>1</F_USERID>
</ROW>
</ROWSET>

=====只提取行信息=====
<ROW>
<F_USERNAME>系统管理员</F_USERNAME>
<F_USERCODE>admin</F_USERCODE>
<F_USERID>1</F_USERID>
</ROW>

=====合并后的信息=====
<ROWSET><ROW>
<F_USERNAME>系统管理员</F_USERNAME>
<F_USERCODE>admin</F_USERCODE>
<F_USERID>1</F_USERID>
</ROW>
<ROW>
<F_USERNAME>黄燕</F_USERNAME>
<F_USERCODE>HUANGYAN</F_USERCODE>
<F_USERID>1000</F_USERID>
</ROW>
</ROWSET>

从上边打印的结果看，我们已经成功的将两个游标 ref_cur和ref_cur2中我们需要的列信息合并到了一个xml文档中。那么接下了，我们就需要通过解析这个xml并返回一个新的sys_refcursor,这里你有必要了解以下oracle xmltable的用法(https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions228.htm)接上边代码

Dbms_Output.put_line(mergeXml);
open ref_cur3 for
 select *
 from xmltable('/ROWSET/ROW' Passing xmltype(mergeXml) Columns
  F_USERNAME varchar2(100) path 'F_USERNAME',
  F_USERCODE varchar2(100) path 'F_USERCODE');

简单说明下xmltable构造函数

声明xpath，指明你需要解析的dom在哪里，比如从根找到ROW /ROWSET/ROW
指明你要查询的xmltype
定义转换列，比如把ROW下边的F_USERNAME这个节点值，映射到游标列F_USERNAME 这个列中

附：sys_refcursor 和 cursor 优缺点比较

优点比较

优点一：sys_refcursor，可以在存储过程中作为参数返回一个table格式的结构集（我把他认为是table类型，容易理解，其实是一个游标集）， cursor 只能用在存储过程，函数，包等的实现体中，不能做参数使用。

优点二：sys_refcursor 这东西可以使用在包中做参数，进行数据库面向对象开放。哈哈。我喜欢。cursor就不能。

缺点比较：

缺点：sys_refcursor 不能用open，close ，fetch 进行操作。不好学，难理解。

cursor可以用 open，close ，fetch操作，容易学，易懂

四、总结

xml作为早期数据传输，序列化和反序列化的文件格式，在oracle中也有良好的支持。所以，对于基于语言之上的知识，各个语言实现方式基本相识。基础终究是重要的。

好了，以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对我们的支持。

oracle合并列的函数wm_concat的使用详解

oracle wm_concat(column)函数使我们经常会使用到的,下面就教您如何使用oracle wm_concat(column)函数实现字段合并,如果您对oracle wm_concat(column)函数使用方面感兴趣的话,不妨一看.shopping:-----------------------------------------u_id goods num------------------------------------------1
Oracle 多行记录合并/连接/聚合字符串的几种方法

什么是合并多行字符串(连接字符串)呢,例如: SQL> desc test; Name Type Nullable Default Comments ------- ------------ -------- ------- -------- COUNTRY VARCHAR2(20) Y CITY VARCHAR2(20) Y SQL> select * from test; COUNTRY CITY -------------------- -------------------- 中国台
oracle列合并的实现方法

很多场合我们都会用到oracle的列合并,oracle提供了如下一些方法用来实现列合并: 一.Oracle 10G以前使用WMSYS.WM_CONCAT: wmsys.wm_concat将字段的值用","来隔开. select id,wm_concat(name) from tab_name group by id; 二.使用sys_connect_by_path sys_connect_by_path(字段名, 2个字段之间的连接符号),这里的连接符号不要使用逗号,oracle会报错
oracle 合并查询事务 sql函数小知识学习

表查询: 合并查询:使用union关键字,可将满足条件的重复行去掉. 复制代码代码如下: select ename,sal,job from emp where sal > 2500 union select ename,sal,job from emp where job = 'MANAGER'; 而union all用法和union相似,但是不会取消重复行. intersect 用来取两个结果的交集. minus用来取两个结果的差集. 使员工scott的岗位,工资,补助与SMITH员工一样
oracle实现多行合并的方法

本文实例讲述了oracle实现多行合并的方法.分享给大家供大家参考.具体分析如下: 在写sql时,经常会有将某列的字段合并起来,比如将某人名下每个月的工资列示,但是每个人只能占一行. 像这种场景,可能用行列转换也能实现,但如果这个月份的信息不固定,就无法使用行列转换了. oracle10g以后,提供了一个函数WMSYS.WM_CONCAT,能很轻松实现该功能. 复制代码代码如下: select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_i
oracle如何合并多个sys_refcursor详解

一.背景在数据开发中,有时你需要合并两个动态游标sys_refcursor. 开发一个存储过程PROC_A,这个过程业务逻辑相当复杂,代码篇幅较长.一段时间后要开发一个PROC_B,要用PROC_A同样的逻辑,而且在这个过程中,还要循环调用PROC_A这个过程.摆在你面前的有两个选择. 打开PL/SQL,仔细的读PROC_A这个过程,一直到明白了所有的逻辑,然后在自己的过程中重写这个逻辑 . 直接复制PROC_A这个过的代码过来,多写极端.还是业界标准大法好针对循环调用的,建立一个临时表,循
Oracle中游标Cursor基本用法详解

查询 SELECT语句用于从数据库中查询数据,当在PL/SQL中使用SELECT语句时,要与INTO子句一起使用,查询的返回值被赋予INTO子句中的变量,变量的声明是在DELCARE中.SELECT INTO语法如下: SELECT [DISTICT|ALL]{*|column[,column,...]} INTO (variable[,variable,...] |record) FROM {table|(sub-query)}[alias] WHERE............ PL/SQL
Oracle基本PLSQL的使用实例详解

Oracle基本PLSQL的使用实例详解 PL/SQL 块是在 SQL 语言之上发展起来的一种应用,可以集中的处理各种复杂的 SQL 操作. 组成: DECLARE: 声明部分 BEGIN 编写主题 EXCEPTION 捕获异常 END; 1.一个简单的PL/SQL块 DECLARE i number; BEGIN I:=30; DBMS_OUTPUT.put_line('I的内容为:'||i); END; 此时,直接执行程序即可. 执行之后发现没有任何的输出.因为 Oracle 在系统设置中
Linux下重启oracle服务及监听器和实例详解

一.在Linux下重启Oracle数据库及监听器: 方法1: 用root以ssh登录到linux,打开终端输入以下命令: cd $ORACLE_HOME #进入到oracle的安装目录 dbstart #重启服务器 lsnrctl start #重启监听器 cd $ORACLE_HOME #进入到oracle的安装目录 dbstart #重启服务器 lsnrctl start #重启监听器 ----------------------------------- 方法2: Sql代码 cd $OR
Oracle分页查询性能优化代码详解

对于数据库中表的数据的 Web 显示,如果没有展示顺序的需要,而且因为满足条件的记录如此之多,就不得不对数据进行分页处理.常常用户并不是对所有数据都感兴趣的,或者大部分情况下,他们只看前几页. 通常有以下两种分页技术可供选择. Select * from ( Select rownum rn,t.* from table t) Where rn>&minnum and rn<=&maxnum 或者 Select * from ( Select rownum rn,t.* fro
Oracle中instr和substr存储过程详解

instr和substr存储过程,分析内部大对象的内容 instr函数 instr函数用于从指定的位置开始,从大型对象中查找第N个与模式匹配的字符串. 用于查找内部大对象中的字符串的instr函数语法如下: dbms_lob.instr( lob_loc in blob, pattern in raw, offset in integer := 1; nth in integer := 1) return integer; dbms_lob.instr( lob_loc in clob char
Oracle中的游标和函数详解

Oracle中的游标和函数详解 1.游标游标是一种 PL/SQL 控制结构:可以对 SQL 语句的处理进行显示控制,便于对表的行数据逐条进行处理. 游标并不是一个数据库对象,只是存留在内存中. 操作步骤: 声明游标打开游标取出结果,此时的结果取出的是一行数据关闭游标到底那种类型可以把一行的数据都装进来此时使用 ROWTYPE 类型,此类型表示可以把一行的数据都装进来. 例如:查询雇员编号为 7369 的信息(肯定是一行信息). 例:查询雇员编号为 7369 的信息(肯定是一
centos 6.5 oracle开机自启动的环境配置详解

centos 6.5 oracle开机自启动的环境配置详解环境:centos 6.5 + Oracle 11g 自启动之前问题虚拟机里的oracle环境,每次重启完系统,用plsql developer连接,先是报错: 无TNS监听程序解决方法是切换到系统的oracle用户,执行lsnrctl start,但是执行之前,因为ORACLE_HOME环境变量没有生效,还要是环境变量文件生效,步骤如下: [oracle@localhost ~]$ source .bash_profile [or
Oracle自定义脱敏函数的代码详解

对于信息安全有要求的,在数据下发和同步过程中需要对含有用户身份信息的敏感字段脱敏,包括用户姓名.证件号.地址等等,下面是自定义函数的代码 CREATE OR REPLACE FUNCTION F_GET_SENSITIVE(IN_STR VARCHAR, IN_TYPE NUMBER) RETURN VARCHAR2 IS V_STR_LENGTH NUMBER; V_NAME VARCHAR2(1000); V_N NUMBER; V_HID VARCHAR2(200); V_SQL VARC
Oracle 11g服务器安装详细步骤图文详解

Oracle 11g是在推出的最新数据库软件,Oracle 11g有400多项功能,经过了1500多个小时的测试,开发工作量达到了3.6万人/月,相当于1000名员工连续研发3年.Oracle 11g提供了高性能.伸展性.可用性和安全性,并能更方便地在低成本服务器和存储设备组成的网格上运行 ,相对过往版本而言,Oracle 11g具有了与众不同的特性. 下文重点给大家介绍Oracle 11g服务器安装详细步骤图文详解,具体内容如下所示: 1.大家可以根据自己的操作系统是多少位(32位或64位)的

oracle如何合并多个sys_refcursor详解

相关推荐

随机推荐