Oracle – 从引用游标中选择特定列

前端之家收集整理的这篇文章主要介绍了Oracle – 从引用游标中选择特定列前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我有一个名为Table1的表.它有很多列,其中一列是Column1.我不知道其他专栏,有时甚至可能会改变.有一个强类型的引用游标类型,它返回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;

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

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

猜你在找的Oracle相关文章