如何通过pl/sql存储函数返回多行? [英] How to return multiple row by pl/sql stored function?

问题描述

我搜索了此问题，最后在此网站上找到了解决方案，但出现一个奇怪的错误.我搜索了错误，但是因为我非常了解pl/sql，所以无法解决它.这是我的函数，它试图返回表的名称:

I search for this issue and finally I found a solution on this website but I get a strange error. I searched for the error but because I know very basic of pl/sql I was not able to solve it. here is my function which is trying to return name of the table:

CREATE OR REPLACE Function FF(BSB_NUMBER IN BANK.BSB#%TYPE) RETURN SYS_REFCURSOR 
IS
    C_RESULT SYS_REFCURSOR;
BEGIN
    OPEN C_RESULT for 
    select * from bank where bank.bsb# = BSB_NUMBER;
    return C_RESULT;    
EXCEPTION
  WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE('Error ! There is no such account');
END FF;
/

运行后

Function created.

执行后

SELECT * FROM TABLE(FF(012878));


ERROR:

    ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

推荐答案

通常，引用游标会传递给客户端程序，例如JDBC结果集.但是，在SQL中调用ref游标很容易:

Normally ref cursors are passed to client programs, such as JDBC Result Sets. However, it's easy enough to call a ref cursor in SQL:

select  FF(012878) from dual;

这篇关于如何通过pl/sql存储函数返回多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！