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

Oracle - select a specific column from a ref cursor

提问by fejesjoco

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.

我有一个名为 Table1 的表。它有很多列，其中之一是 Column1。我不知道其他列，它们有时甚至可能会发生变化。有一个强类型引用游标类型，它返回 Table1%rowtype，名为 cur_Table1。我有一个名为 SP1 的存储过程，它有一个类型为 cur_Table1 的输出参数。我正在从另一个数据库调用这个 SP1 存储过程，该数据库只看到这个存储过程，而不是表或类型本身。如何从返回的游标中仅选择 Column1？我知道我可以获取一条记录或与游标具有的列一样多的变量，但我只知道一列的存在，因此我无法声明完整的记录或正确数量的变量。

采纳答案by Jon Heller

You can do this with DBMS_SQL, but it ain't pretty.

你可以用 来做到这一点DBMS_SQL，但它并不漂亮。

Table and sample data (COLUMN1 has the numbers 1 - 10):

表和示例数据（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;

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:

从引用游标读取 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;
/

回答by fejesjoco

Given the original question, jonearles's answer is still correct, so I'll leave it marked as such, but I ended up doing something completely different and much better.

鉴于最初的问题，jonearles 的答案仍然是正确的，所以我将其标记为这样，但我最终做了一些完全不同且更好的事情。

The problem was/is that I have no control over SP1's database, I just have to call it from somewhere else as a 3rd party client. Now I managed to get permission to see not only SP, but also the type of the cursor. I still don't see the table but now there is a much cleaner solution:

问题是/是我无法控制 SP1 的数据库，我只需要从其他地方作为 3rd 方客户端调用它。现在我设法获得了不仅可以查看 SP 还可以查看光标类型的权限。我仍然没有看到表格，但现在有一个更干净的解决方案：

In the other database I have been granted access to see this type now:

在另一个数据库中，我现在已被授予查看此类型的权限：

type cur_Table1 is ref cursor return Table1%rowtype;

So in my database I can do this now:

所以在我的数据库中，我现在可以这样做：

mycursor OtherDB.cur_Table1;
myrecord mycursor%rowtype;
...
OtherDB.SP1(mycursor);
fetch mycursor into myrecord;
dbms_output.put_line(myrecord.Column1);

See, I still don't need any access to the table, I see the cursor only. The key is that the magical %rowtype works for cursors as well, not just tables. It doesn't work on a sys_refcursor, but it does on a strongly typed one. Given this code, I don't have to care if anything changes on the other side, I don't have to define all the columns or records at all, I just specify the one column I'm interested in.

看，我仍然不需要对表的任何访问，我只看到光标。关键是神奇的 %rowtype 也适用于游标，而不仅仅是表。它不适用于 sys_refcursor，但适用于强类型的。鉴于此代码，我不必关心另一侧是否有任何更改，我根本不必定义所有列或记录，我只需指定我感兴趣的一列。

I really love this OOP attitude about Oracle.

我真的很喜欢这种关于 Oracle 的 OOP 态度。

回答by DCookie

Don't know if it's an option or not, but wouldn't a better solution be to create a function that returns the specific value you're looking for? That avoids the overhead of sending the extra data. Alternatively, you could define a cursor with a set of known fields in it that both parties know about.

不知道这是否是一个选项，但是创建一个返回您正在寻找的特定值的函数不是更好的解决方案吗？这避免了发送额外数据的开销。或者，您可以定义一个游标，其中包含一组双方都知道的已知字段。