oracle - Oracle 修改 sys refcursor 并在 PL/SQL 中返回修改后的游标
问题描述
我试图创建一个将 sys refcursor 作为 in 参数的过程,并根据以下代码中注释中解释的逻辑对其进行修改
TYPE t_params IS
TABLE OF VARCHAR2(32767 CHAR);
/
CREATE OR REPLACE PROCEDURE modify_cursor (
p_cursor IN OUT SYS_REFCURSOR,
p_array_binary IN t_params,
p_values IN t_params
)
/*
p_cursor IN OUT SYS_REFCURSOR
-- contains a single row {empId:123, ename:"king", mgr:"Porter",deptNo:200}
p_array_binary IN t_params
-- contains one binary value corresponding to each column in above cursor ["1","0","1","1"]
p_values IN t_params
-- contains one binary value corresponding to each column in above cursor ["123","king2","new manager","200"]
*/
IS
BEGIN
/*
Based on p_array_binary
if binary value 0 then take cursor should retain value as it is fro corresponding column
if binary value 1 then cusrsor should have the correspondoing column value from p_values
In short, the out cursor should be {empId:123, ename:"king", mgr:"new manager", deptNo:200}
*/
END;
/
在这方面的任何帮助将不胜感激。
解决方案
如果您知道 ref 游标结构——它总是显示数据类型的四列——那么这将相对简单:
CREATE OR REPLACE PROCEDURE modify_cursor (
p_cursor IN OUT SYS_REFCURSOR,
p_array_binary IN t_params,
p_values IN t_params
)
IS
l_empid number;
l_ename varchar2(30);
l_mgr varchar2(30);
l_deptNo number;
BEGIN
-- get original values into local variables
fetch p_cursor into l_empId, l_ename, l_mgr, l_deptNo;
-- re-open cursor using either local variables of p_values depending on p_binary flag
open p_cursor for
select
case when p_array_binary(1) = '1' then to_number(p_values(1)) else l_empId end as empId,
case when p_array_binary(2) = '1' then p_values(2) else l_ename end as ename,
case when p_array_binary(3) = '1' then p_values(3) else l_mgr end as mgr,
case when p_array_binary(4) = '1' then to_number(p_values(4)) else l_deptNo end as deptNo
from dual;
END;
/
通过 SQL*Plus/SQL Developer/SQLcl 绑定变量使用您的示例数据进行演示:
var rc refcursor;
begin
open :rc for
select 123 as empId, 'king' as ename, 'Porter' as mgr, 200 as deptNo
from dual;
modify_cursor(:rc, t_params('1', '0', '1', '1'), t_params('123', 'king2', 'new manager', '200'));
end;
/
print rc
EMPID ENAME MGR DEPTNO
---------- -------------------------------- -------------------------------- ----------
123 king new manager 200
由于您事先不知道结构,因此您将不得不使用动态 SQL,这有点复杂。这是一个大纲:
CREATE OR REPLACE PROCEDURE modify_cursor (
p_cursor IN OUT SYS_REFCURSOR,
p_array_binary IN t_params,
p_values IN t_params
)
IS
l_c integer;
l_col_cnt integer;
l_desc_t dbms_sql.desc_tab3;
l_varchar2 varchar2(32767 char);
l_values t_params := new t_params();
l_result integer;
BEGIN
-- convert ref cursor to dbms_sql cursor
l_c := dbms_sql.to_cursor_number(rc => p_cursor);
-- analyse the cursor (columns, data types)
dbms_sql.describe_columns3(c => l_c, col_cnt => l_col_cnt, desc_t => l_desc_t);
-- optionally check l_col_cnt matches sise of t_params arguments?
l_values.extend(l_col_cnt);
-- define each column for fetch; here you're treating everything as strings,
-- which will cause issues with some other data types
for i in 1..l_col_cnt loop
dbms_sql.define_column(c => l_c, position => i, column => l_varchar2, column_size => 32767);
end loop;
-- fetch original values - only one row to worry about so no loop
l_result := dbms_sql.fetch_rows(c => l_c);
for i in 1..l_col_cnt loop
-- depending on p_array_binary, set l_values from either fetched data or p_values
if p_array_binary(i) = '1' then
l_values(i) := p_values(i);
else
-- this forces everything to varchar2, which is OK (ish) for your sample data;
-- if you have other data types e.g. dates then you will probably want type-specific
-- handling so you can control the conversions - which affects this, define_column
-- and the final cursor to retrieve the values. But you have the same issue with p_values.
dbms_sql.column_value(c => l_c, position => i, value => l_values(i));
end if;
end loop;
-- finished with original cursor, so close it
dbms_sql.close_cursor(c => l_c);
-- re-open ref cursor using l_values data, with another dynamic SQL statement
l_varchar2 := 'select ';
for i in 1..l_col_cnt loop
if i > 1 then
l_varchar2 := l_varchar2 || ', ';
end if;
if l_desc_t(i).col_type = 2 then
l_varchar2 := l_varchar2 || l_values(i);
else
l_varchar2 := l_varchar2 || '''' || l_values(i) || '''';
end if;
l_varchar2 := l_varchar2 || ' as "' || l_desc_t(i).col_name || '"';
end loop;
l_varchar2 := l_varchar2 || ' from dual';
open p_cursor for l_varchar2;
END;
/
运行完全相同的演示块给出:
EMPID ENAM MGR DEPTNO
---------- ---- ----------- ----------
123 king new manager 200
如果需要,您可以添加对其他数据类型的处理、错误处理等。
阅读有关dbms_sql的更多信息。
推荐阅读
- spring - Spring Boot Kafka:使用特定主题的所有实例使用相同的消息
- xml - xslt 基于特殊字符分割字符串
- c# - Range.AutoFilter - 包含字符串的值
- python - CSV 数据到 MySQL 表
- c# - WindowHandles 在 IE 上不起作用:未切换到新选项卡
- python - 从python中的dict列表中提取dict值
- django - AWS S3 存储桶返回 403 禁止
- azure-active-directory - 是否可以使用 msal.js 为 SPA 网站实现切换帐户功能?
- python - 什么更好,一次阅读所有页面或在 python-camelot 中逐页阅读?
- python - Python网页抓取数据操作