如何通过未知类型的sys_refcursor进行迭代? [英] How to iterate through a sys_refcursor with unknown type?

问题描述

让我们定义一个函数

create or replace function GET_SOME_CURSOR(X number) return sys_refcursor is
  R sys_refcursor;
begin
  open R for
    select * from MY_TABLE T where T.RATING = X;
  return R;
end;

假设我们不知道MY_TABLE列的名称(除了RATING). 请告诉我如何解决以下任务.也许看起来没有意义，但我希望您的解决方案 将向我展示我需要了解的PL/SQL的某些方面.

Suppose we don't know what names MY_TABLE columns have (in addition to RATING). Please, tell me how to solve the following task. Maybe it looks pointless, but I hope your solution will show me some aspects of PL/SQL I need to know.

编写一个函数，该函数会循环(对于x = 1到10)从GET_SOME_CURSOR(x)获取另一个refcursor 如果引用结果集中不存在名为TITLE的列，则返回NULL， 否则，在结果集中找到所有行，这些行在TITLE列中包含子字符串"ABC"，并且 放入一个数组(或您想要的任何东西)中并返回该数组.

Write a function which in cycle (for x = 1 to 10) gets another refcursor from GET_SOME_CURSOR(x) and if a column named TITLE is NOT present in the refcursor result set then return NULL, otherwise find all rows in the result set, which contain substring 'ABC' in the TITLE column and put into an array (or anyting you want) and return this array.

推荐答案

这应该提供所需的信息

DECLARE
    r SYS_REFCURSOR;

    cur INTEGER;
    col_tab DBMS_SQL.DESC_TAB;
    col_cnt INTEGER;

BEGIN
    OPEN r FOR 
    select * from MY_TABLE T where T.RATING = X;

    cur := DBMS_SQL.TO_CURSOR_NUMBER(rc);
    DBMS_SQL.DESCRIBE_COLUMNS(cur, col_cnt, col_tab);
    FOR i IN 1..col_cnt LOOP
        DBMS_OUTPUT.PUT_LINE('Column '||i||': '||col_tab(i).col_name 
            ||' Data type is: '||col_tab(i).col_type );
    END LOOP;
    CLOSE r;
END;

从col_type号到您通过此查询获得的可读数据类型字符串的转换:

Translation from col_type number to readable data type string you get by this query:

SELECT text
FROM all_source
WHERE owner = 'SYS' 
    AND NAME = 'DBMS_TYPES' 
    AND TYPE = 'PACKAGE' 
    AND REGEXP_LIKE(text, 'TYPECODE');

这篇关于如何通过未知类型的sys_refcursor进行迭代?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！