Oracle SQL Developer PL/SQL返回一个数组

Question

开发者们，

我已经搜索了各个地方，但是我找不到解决这个简单问题的方法。

情况：

我需要编写一个过程，它以列名作为输入并返回该列中所有不同值作为输出。然后我需要在一些C#代码中使用这个过程。

在MS服务器中，这非常简单，因为它会直接给出结果集，而在PL/SQL中则不同。

我写的脚本（没有给我需要的结果）：

CREATE OR REPLACE
PROCEDURE GetCol(PARAM IN STRING, recordset OUT sys_refcursor)
AS
BEGIN
OPEN recordset FOR
SELECT DISTINCT(PARAM)
FROM my_table;
END

当我尝试使用以下代码检查记录集中的数据时：

DECLARE
  l_cursor  SYS_REFCURSOR;
  l_sname VARCHAR2(50);
BEGIN
  GetCol('col_name',l_cursor);
    LOOP 
    FETCH l_cursor INTO  l_sname;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_sname);
  END LOOP;
  CLOSE 

请问有人能帮我解决这段代码吗？

- Gagan Bodduluru

---

逻辑看起来没问题，但你的选择语句很奇怪。如果你运行“select distinct('col_name') from my_table”，你会得到什么？你需要使用execute immediate代替。 - OldProgrammer

我只需要获取该特定列中的所有唯一值。 - Gagan Bodduluru

Answer 1

您还可以为字符串值打开一个游标(ref_cursor)。请看以下示例：

CREATE OR REPLACE PROCEDURE GetCol(PARAM IN VARCHAR2, recordset OUT sys_refcursor)
AS
QRY varchar2(100);
BEGIN
QRY := 'SELECT DISTINCT '||PARAM||' FROM my_table';
OPEN recordset FOR QRY;
END;

然后：

DECLARE
  l_cursor  SYS_REFCURSOR;
  l_sname VARCHAR2(50);
BEGIN
  GetCol('col_name',l_cursor);
    LOOP 
    FETCH l_cursor INTO  l_sname;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_sname);
  END LOOP;
END;

- Aramillo

---

太有帮助了！非常感谢。 - Gagan Bodduluru

Answer 2

你遇到的问题是由于对过程中SELECT语句中的PARAM含义不明确造成的：

CREATE OR REPLACE
PROCEDURE GetCol(PARAM IN STRING, recordset OUT sys_refcursor)
AS
BEGIN
OPEN recordset FOR
SELECT DISTINCT(PARAM) -- Ambiguity here
FROM my_table;
END;

< p >“PARAM”指的是表列还是存储过程的第一个参数？Oracle已经假定了该参数。您可以明确地表示如下：

SELECT DISTINCT(my_table.PARAM) 
FROM my_table;

如果适用的话（这里可能不适用），您可以指定过程参数：

SELECT DISTINCT(GetCol.PARAM) 
FROM my_table;

通常最好避免以下情况：

• 在select语句中始终使用表别名作为列引用，并且
• 制定参数名称的标准，使它们不太可能发生冲突，例如P_PARAM。

- Tony Andrews

---

PARAM在这里是列名，也是该过程的唯一IN参数。每当用户提供一个列名时，该过程应返回该特定列中所有不同的值。 - Gagan Bodduluru