时间戳

首页 > 解决方案 > 如何从 oracle PL/SQL 集合中选择所有行到 SYS_REFCURSOR

sql - 如何从 oracle PL/SQL 集合中选择所有行到 SYS_REFCURSOR

问题描述

注意：我见过很多解决方案，都说我不能将 SQL 与 PL/SQL 类型一起使用。我必须使用 CREATE 或 REPLACE，但我的限制是我不能使用系统对象来完成这项任务。

我在下面的示例中尝试过的仅返回最后一行。

create or replace PROCEDURE SP_TEST (TEST_cursor OUT SYS_REFCURSOR)IS
 TYPE TEMP_RECORD IS RECORD(
                    entries   NUMBER,
                    name      VARCHAR2(50),
                    update    VARCHAR2(200)
                );
TYPE TEMP_TABLE IS TABLE OF TEMP_RECORD INDEX BY PLS_INTEGER;
VAR_TEMP       TEMP_TABLE;
IDX            PLS_INTEGER := 0;
BEGIN 
VAR_TEMP(IDX).cur_entries := 1;
VAR_TEMP(IDX).cur_entries := 2;
 OPEN TEST_cursor FOR
 SELECT VAR_TEMP(idx).cur_entries from dual;
END SP_TEST;

尝试了另一种方法。

OPEN TEST_cursor FOR
  SELECT * FROM TABLE(VAR_TEMP)
--- It gives compilation error ora-

标签： sqloracleplsql

解决方案

鉴于您无法在数据库中创建对象，我能想到的唯一解决方案是使用动态 SQL：

CREATE TYPE temp_record AS OBJECT
(
    entries NUMBER,
    entry_name VARCHAR2 (50),
    update_value VARCHAR2 (200)
);

CREATE TYPE temp_table IS TABLE OF temp_record;

CREATE OR REPLACE PROCEDURE sp_test (test_cursor OUT SYS_REFCURSOR) IS
    var_temp temp_table := temp_table ();
    strSql  VARCHAR2(32767);
BEGIN
    -- Populate the temp table, or pass it in from elsewhere

    var_temp.EXTEND();
    var_temp (var_temp.LAST).entries := 1;
    var_temp (var_temp.LAST).entry_name := 'test';

    FOR i IN 1..var_temp.COUNT LOOP
      strSql := strSql ||
                  CASE
                    WHEN LENGTH(strSql) > 0 THEN ' UNION ALL '
                    ELSE NULL
                  END ||
                  'SELECT ' || var_temp.ENTRIES || ' ENTRIES,' ||
                            '''' || var_temp.ENTRY_NAME || ''' ENTRY_NAME FROM DUAL';
    END LOOP;

    OPEN test_cursor FOR strSql;
END sp_test;

现在，我可能有点搞砸了字符串连接逻辑，但目标是最终得到一个看起来像这样的 SQL 字符串

SELECT 1 ENTRIES,'test' ENTRY_NAME FROM DUAL
UNION ALL
SELECT 2 ENTRIES,'test 2' ENTRY_NAME FROM DUAL
UNION ALL
SELECT 3 ENTRIES,'test_3' ENTRY_NAME FROM DUAL

但是，当然，没有漂亮的空白等等。

动态 SQL 的 32K 限制最终可能会咬你一口，但如果迫在眉睫，你可以使用 DBMS_SQL 包来处理任意大的 SQL 文本，尽管这也带来了挑战。

祝你好运。

推荐阅读