Oracle - 从引用游标中选择特定的列 [英] Oracle - select a specific column from a ref cursor
问题描述
我有一个名为Table1的表。它有很多列,其中一个是Column1。我不知道其他列,他们甚至可能改变有时。有一个强类型的ref游标类型,返回Table1%rowtype,命名为cur_Table1。我有一个名为SP1的存储过程,它的out参数类型为cur_Table1。我从另一个只看到此存储过程的数据库调用此SP1存储过程,但不调用表或类型本身。如何从返回的游标中仅选择Column1?我知道我可以抓取一个记录或尽可能多的变量,因为游标有列,但我只知道一列的存在,所以我不能声明完整的记录或正确数量的变量。
<你可以用
DBMS_SQL
来做到这一点,但它不漂亮。 表格和示例数据(COLUMN1的数字为1 - 10):
create table table1(column1 number,column2 date,column3 varchar2(1000),column4 clob);
insert into table1
select level,sysdate,level,level from dual connect by level< = 10;
commit;
使用打开引用游标并选择所有内容的过程的包: / p>
创建或替换包test_pkg是
类型cur_Table1是ref游标返回table1%rowtype;
procedure sp1(p_cursor in out cur_table1);
end;
/
创建或替换包主体test_pkg是
过程sp1(p_cursor在外部cur_table1中)是
begin
为选择column1,column2打开p_cursor, column3,column4 from table1;
end;
end;
/
从引用游标读取COLUMN1数据的PL / SQL块:
- 基本步骤是:调用过程,转换光标,描述和查找列,
- - 然后提取行并检索列值。
-
- 需要在此处添加COLUMN1的每种可能的数据类型。
- 目前只支持NUMBER。
declare
v_cursor sys_refcursor;
v_cursor_number number;
v_columns number;
v_desc_tab dbms_sql.desc_tab;
v_position number;
v_typecode number;
v_number_value number;
begin
- 打开游标的调用过程
test_pkg.sp1(v_cursor);
- 将游标转换为DBMS_SQL游标
v_cursor_number:= dbms_sql.to_cursor_number(rc => v_cursor);
- 获取列上的信息
dbms_sql.describe_columns(v_cursor_number,v_columns,v_desc_tab);
- 遍历所有列,找到COLUMN1位置并键入
for i in 1 .. v_desc_tab.count循环
如果v_desc_tab(i).col_name ='COLUMN1'那么
v_position:= i;
v_typecode:= v_desc_tab(i).col_type;
- 选择要选择的COLUMN1。
if v_typecode = dbms_types.typecode_number then
dbms_sql.define_column(v_cursor_number,i,v_number_value);
--...对每种可能的类型重复。
end if;
end if;
end loop;
- 获取所有行,然后获取相关的列值并打印
,而dbms_sql.fetch_rows(v_cursor_number)> 0 loop
if v_typecode = dbms_types.typecode_number then
dbms_sql.column_value(v_cursor_number,v_position,v_number_value);
dbms_output.put_line('Value:'|| v_number_value);
--...对每个可能的类型重复
end if;
end loop;
end;
/
I have a table named Table1. It has lots of columns, one of them is Column1. I don't know the other columns, they may even change sometimes. There is a strongly typed ref cursor type which returns Table1%rowtype, named cur_Table1. I have a stored procedure named SP1 which has an out parameter of type cur_Table1. I'm calling this SP1 stored procedure from another database that only sees this stored procedure, but not the table or the type itself. How do I select only Column1 from the returned cursor? I know I can fetch into a record or as many variables as the cursor has columns, but I only know of one column's existence so I can't declare the complete record or correct number of variables.
You can do this with DBMS_SQL
, but it ain't pretty.
Table and sample data (COLUMN1 has the numbers 1 - 10):
create table table1(column1 number, column2 date, column3 varchar2(1000), column4 clob);
insert into table1
select level, sysdate, level, level from dual connect by level <= 10;
commit;
Package with a procedure that opens a ref cursor and selects everything:
create or replace package test_pkg is
type cur_Table1 is ref cursor return table1%rowtype;
procedure sp1(p_cursor in out cur_table1);
end;
/
create or replace package body test_pkg is
procedure sp1(p_cursor in out cur_table1) is
begin
open p_cursor for select column1, column2, column3, column4 from table1;
end;
end;
/
PL/SQL block that reads COLUMN1 data from the ref cursor:
--Basic steps are: call procedure, convert cursor, describe and find columns,
--then fetch rows and retrieve column values.
--
--Each possible data type for COLUMN1 needs to be added here.
--Currently only NUMBER is supported.
declare
v_cursor sys_refcursor;
v_cursor_number number;
v_columns number;
v_desc_tab dbms_sql.desc_tab;
v_position number;
v_typecode number;
v_number_value number;
begin
--Call procedure to open cursor
test_pkg.sp1(v_cursor);
--Convert cursor to DBMS_SQL cursor
v_cursor_number := dbms_sql.to_cursor_number(rc => v_cursor);
--Get information on the columns
dbms_sql.describe_columns(v_cursor_number, v_columns, v_desc_tab);
--Loop through all the columns, find COLUMN1 position and type
for i in 1 .. v_desc_tab.count loop
if v_desc_tab(i).col_name = 'COLUMN1' then
v_position := i;
v_typecode := v_desc_tab(i).col_type;
--Pick COLUMN1 to be selected.
if v_typecode = dbms_types.typecode_number then
dbms_sql.define_column(v_cursor_number, i, v_number_value);
--...repeat for every possible type.
end if;
end if;
end loop;
--Fetch all the rows, then get the relevant column value and print it
while dbms_sql.fetch_rows(v_cursor_number) > 0 loop
if v_typecode = dbms_types.typecode_number then
dbms_sql.column_value(v_cursor_number, v_position, v_number_value);
dbms_output.put_line('Value: '||v_number_value);
--...repeat for every possible type
end if;
end loop;
end;
/
这篇关于Oracle - 从引用游标中选择特定的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!