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

来源：互联网发布：淘宝卖家注册子账号编辑：程序博客网时间：2024/06/11 18:47

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

Answer:

2014-10-12 20:04 
提问者采纳 
热心网友
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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 命令未正确结束

0 0