Question

oracle的函数返回一个表怎么写

我有一个很长的sql做查询，我想直接写一个函数或者存储过程，但是网上的例子都不行，大家有没有好的方法，函数要带参数的

Answer 1

Oracle 需要通过 返回一个游标来处理。

-- 测试表数据。
select * from test_main;
        ID VALUE
---------- --------------------
         1 ONE
         3 THREE
         2 TWO
 
-- 返回结果集的函数.
CREATE OR REPLACE FUNCTION Get_Test_Main_All
RETURN SYS_REFCURSOR
IS
  return_cursor SYS_REFCURSOR;
BEGIN
  OPEN return_cursor FOR 'SELECT * FROM test_main';
  RETURN return_cursor;
END;
/
 
-- 普通的查询，来查看结果.
SELECT Get_Test_Main_All() FROM dual;
GET_TEST_MAIN_ALL()
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
        ID VALUE
---------- --------------------
         1 ONE
         3 THREE
         2 TWO

-- 存储过程调用， 来获取结果.
DECLARE 
  -- 调用函数的返回值.
  testCursor SYS_REFCURSOR;
  -- 存储单行数据.
  testRec  test_main%ROWTYPE;
BEGIN
  -- 调用返回结果集的函数.
  testCursor := Get_Test_Main_All();
  -- 循环，遍历结果.
  LOOP
    -- 游标向前.
    FETCH testCursor INTO testRec;
    -- 无数据的情况下，退出循环.
 EXIT WHEN testCursor%NOTFOUND;
    -- 输出调试信息.
    dbms_output.put_line( TO_CHAR(testRec.id) || ' ' || testRec.value);
  END LOOP;
END;
/
1 ONE
3 THREE
2 TWO
PL/SQL 过程已成功完成。

--------------------------------------------------------------------------------
上面的是使用动态SQL处理的。
下面是正常SQL处理的。

CREATE OR REPLACE FUNCTION Get_Test_Main 
RETURN SYS_REFCURSOR
IS
  return_cursor SYS_REFCURSOR;
BEGIN
  OPEN return_cursor FOR SELECT * FROM test_main;
  RETURN return_cursor;
END;
/
SQL> SELECT Get_Test_Main() FROM dual;
GET_TEST_MAIN()
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
        ID VALUE
---------- --------------------
         2 TWO
 
--------------------------------------------------------------------------------
上面的是没有参数的
下面是有参数的

CREATE OR REPLACE FUNCTION Get_Test_Main (
  p_id  INT
)
RETURN SYS_REFCURSOR
IS
  return_cursor SYS_REFCURSOR;
BEGIN
  OPEN return_cursor FOR SELECT * FROM test_main WHERE id = p_id;
  RETURN return_cursor;
END;
/
SQL> SELECT Get_Test_Main(2) FROM dual;
GET_TEST_MAIN(2)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
        ID VALUE
---------- --------------------
         2 TWO
 
返回游标的函数，不是 “表值函数”
SQL> SELECT * FROM Get_Test_Main(2);
SELECT * FROM Get_Test_Main(2)
                           *
ERROR 位于第 1 行:
ORA-00933: SQL 命令未正确结束

Answer 2

你那个很长的sql为啥不弄成一个视图？