oracle游标添加列,Oracle – 从引用游标中选择特定列

您可以使用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;

打包一个打开引用游标并选择所有内容的过程的包：

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;

/

从ref游标中读取COLUMN1数据的PL / SQL块：

--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;

/