存储过程/存储函数

📄字数 6.4K

👁️阅读量加载中...

在 PL/SQL 中，存储过程（Procedure）和存储函数（Function）是两种封装业务逻辑的子程序，它们的主要区别在于：函数必须返回一个值，而存储过程不返回值（但可通过输出参数返回数据）。

存储过程/函数的创建、删除重编译

存储过程的创建、删除、重编译在数据库对象中作了详细描述，参考存储过程。
存储函数的创建、删除、重编译在数据库对象中作了详细描述，参考存储函数。

查看存储过程/函数

存储过程和函数保存在数据库的字典中，几个常用的字典视图如下：

SYS_PROCEDURES：sys_procedures 系统表用于存储、管理系统库和用户库中所有创建的存储函数和过程信息，查看需在系统库下有系统权限。
ALL_PROCEDURES：all_procedures 系统视图用于存储和管理创建的所有存储函数和过程信息。
DBA__PROCEDURES：dba_procedures 系统视图用于存储和管理当前库中所有创建的存储函数和过程信息，查看需要在当前库下且有查看权限。
USER_PROCEDURES：user_procedures 系统视图用于查询当前用户创建的所有存储函数和过程信息。

示例

sql

-- 查看sys_procedures
SQL> select * from sys_procedures;

+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | PROC_ID |       PROC_NAME       | LANGUAGE | RET_TYPE | DEFINE |       CREATE_TIME        | VALID | COMMENTS | IS_SYS | PIPELINED | RESERVED1 | RESERVED2|
+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+
| 1     | 1         | 1       | 1048628 | RAND_VALUE            | PLSQL    | BIGINT   | <CLOB> | 2025-07-09 18:54:03.912  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
| 1     | 1         | 1       | 1048637 | PROC_TEST_DISASSEMBLE | PLSQL    | <NULL>   | <CLOB> | 2025-07-11 11:42:12.161  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
| 1     | 1         | 1       | 1048639 | FUNC_TEST_DISASSEMBLE | PLSQL    | INTEGER  | <CLOB> | 2025-07-11 11:42:12.171  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+

-- 查看all_procedures
SQL> select * from all_procedures;

+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | PROC_ID |       PROC_NAME       | LANGUAGE | RET_TYPE | DEFINE |       CREATE_TIME        | VALID | COMMENTS | IS_SYS | PIPELINED | RESERVED1 | RESERVED2|
+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+
| 1     | 1         | 1       | 1048628 | RAND_VALUE            | PLSQL    | BIGINT   | <CLOB> | 2025-07-09 18:54:03.912  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
| 1     | 1         | 1       | 1048637 | PROC_TEST_DISASSEMBLE | PLSQL    | <NULL>   | <CLOB> | 2025-07-11 11:42:12.161  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
| 1     | 1         | 1       | 1048639 | FUNC_TEST_DISASSEMBLE | PLSQL    | INTEGER  | <CLOB> | 2025-07-11 11:42:12.171  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+


-- 查看dba_procedures
SQL> select * from dba_procedures;

+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | PROC_ID |       PROC_NAME       | LANGUAGE | RET_TYPE | DEFINE |       CREATE_TIME        | VALID | COMMENTS | IS_SYS | PIPELINED | RESERVED1 | RESERVED2|
+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+
| 1     | 1         | 1       | 1048628 | RAND_VALUE            | PLSQL    | BIGINT   | <CLOB> | 2025-07-09 18:54:03.912  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
| 1     | 1         | 1       | 1048637 | PROC_TEST_DISASSEMBLE | PLSQL    | <NULL>   | <CLOB> | 2025-07-11 11:42:12.161  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
| 1     | 1         | 1       | 1048639 | FUNC_TEST_DISASSEMBLE | PLSQL    | INTEGER  | <CLOB> | 2025-07-11 11:42:12.171  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+

-- 查看user_procedures
SQL> select * from user_procedures;

+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+
| DB_ID | SCHEMA_ID | USER_ID | PROC_ID |       PROC_NAME       | LANGUAGE | RET_TYPE | DEFINE |       CREATE_TIME        | VALID | COMMENTS | IS_SYS | PIPELINED | RESERVED1 | RESERVED2|
+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+
| 1     | 1         | 1       | 1048628 | RAND_VALUE            | PLSQL    | BIGINT   | <CLOB> | 2025-07-09 18:54:03.912  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
| 1     | 1         | 1       | 1048637 | PROC_TEST_DISASSEMBLE | PLSQL    | <NULL>   | <CLOB> | 2025-07-11 11:42:12.161  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
| 1     | 1         | 1       | 1048639 | FUNC_TEST_DISASSEMBLE | PLSQL    | INTEGER  | <CLOB> | 2025-07-11 11:42:12.171  | T     | <NULL>   | F      | F         | <NULL>    | <NULL>|
+-------+-----------+---------+---------+-----------------------+----------+----------+--------+--------------------------+-------+----------+--------+-----------+-----------+-----------+

执行存储过程/函数

语法格式

ExecuteFuncStmt 为执行存储过程或函数语法；
ExecuteImmediateStmt 则是执行动态SQL语法。

func_name：存储过程名、存储函数名、包中定义的存储过程名或存储函数名，其中如果调用包中存储过程或存储函数则需加包名；
func_params：过程/函数参数列表；
expr：要执行的动态SQL表达式，可为字符串类型变量，也可为字符串常量；
USING expr_list：在执行动态SQL语句时对参数列表进行绑定，expr_list 参数列表与动态SQL语句中的占位符进行绑定；
INTO ident_list：标识符链，将动态SQL的执行返回结果存入标识符中；
BULK COLLECT：单个集合变量或多个集合变量的多记录可使用 BULK COLLECT；
INCONST：可限制动态SQL查询结果条数。

说明

以上语法已经包含了块语句执行的语法，所以示例中也会包含块语句的执行范例

示例

sql

-- 创建无参存储过程/函数
SQL> CREATE TABLE test_proc_tab(id INT,dtime TIME);

SQL> CREATE OR REPLACE PROCEDURE proc_test() IS
        loop_num INT;
      BEGIN
        loop_num := 0;
        FOR i IN 1 .. 10 LOOP
          INSERT INTO test_proc_tab (id, dtime) VALUES (i, current_time);
          loop_num := loop_num + 1;
        END LOOP;
        SEND_MSG('过程执行完成' || loop_num || '次');
        COMMIT;
      END;
      /

--执行存储过程/函数
SQL> EXEC proc_test();

SQL> CALL proc_test();


-- 创建有参存储过程/函数
SQL> CREATE OR REPLACE PROCEDURE proc_test2(parameter INTEGER) AS
        x int;
      BEGIN
        x := 0;
        FOR i IN 1 .. parameter LOOP
          INSERT INTO test_proc_tab VALUES (i, sysdate);
          x := x + SQL%ROWCOUNT;
        END LOOP;
        SEND_MSG('共插入：' || x || '次');
        COMMIT;
      END;
      /

--执行存储过程/函数
SQL> EXEC proc_test2(3);

SQL> CALL proc_test2(3);

SQL> CREATE TABLE ttt(id int);

-- EXECUTE IMMEDIATE
SQL> DECLARE
     NUM INT:=100;
     BEGIN
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=''TTT''';
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /
100

+-------+
| EXPR1 |
+-------+
| 1     |
+-------+

-- RETURNING INTO USING
SQL> DECLARE
     NUM INT;
     BEGIN
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=:TABLE_NAME ' RETURNING INTO NUM USING 'TTT';
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /
1

-- USING RETURNING INTO
SQL> DECLARE
     NUM INT;
     BEGIN
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=:TABLE_NAME ' USING 'TTT' RETURNING INTO NUM ;
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /
1


-- RETURNING INTO
SQL> DECLARE
     NUM INT;
     BEGIN
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=''TTT''' RETURNING INTO NUM ;
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /
1


-- USING INTO
SQL> DECLARE
     NUM INT;
     BEGIN
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=:TABLE_NAME ' USING 'TTT' INTO NUM ;
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /
1


-- INTO USING
SQL> DECLARE
     NUM INT;
     BEGIN
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=:TABLE_NAME ' INTO NUM USING 'TTT' ;
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /
1

-- INTO
SQL> DECLARE
     NUM INT;
     BEGIN
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=''TTT'' ' INTO NUM ;
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /
1

-- USING
SQL> DECLARE
     NUM INT:=100;
     BEGIN
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=:TABLE_NAME' USING 'TTT';
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /
100

+-------+
| EXPR1 |
+-------+
| 1     |
+-------+

-- RETURNING
SQL> DECLARE
     NUM INT:=100;
     BEGIN
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=:TABLE_NAME ' RETURNING ;
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /

Error: [E19132] 语法错误

-- USING RETURNING
SQL> DECLARE
     NUM INT:=100;
     BEGIN
     EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=:TABLE_NAME ' USING 'TTT' RETURNING ;
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /

Error: [E19132] 语法错误

-- RETURNING USING
SQL> DECLARE
     NUM INT;
     BEGIN
     CALL IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=:TABLE_NAME' RETURNING USING 'TTT';
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /

Error: [E19132] 语法错误

-- CALL IMMEDIATE
SQL> DECLARE
     NUM INT;
     BEGIN
     CALL IMMEDIATE 'SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME=:TABLE_NAME ' RETURNING INTO NUM USING 'TTT';
     DBMS_OUTPUT.PUT_LINE(NUM);
     END;
     /
1

-- returning bulk collect
SQL> create table t1(c1 int, c2 varchar, c3 date);

SQL> create table t2(c1 varchar, c2 int, c3 varchar, c4 date);

SQL> create table t3(c1 varchar, c2 varchar)
SQL> create table t_msg(c1 varchar, c2 varchar);

SQL> insert into t1 values(1, 'a', '2001-01-01');

SQL> insert into t1 values(2, 'b', '2012-02-02');

SQL> insert into t1 values(3, 'c', '2023-03-03');

SQL> insert into t1 values(4, 'd', '2034-04-04');

SQL> declare
     v_sql varchar;
     v_msg varchar;
     type type_c1 is table of t1.c1%type;
     type type_c2 is table of t1.c2%type;
     type type_c3 is table of t1.c3%type;
     v1 type_c1;
     v2 type_c2;
     v3 type_c3;
     begin
     v_sql := 'select * from t1';
     v_msg := 'execute immediate sql returning bulk collect into';
     execute immediate v_sql returning bulk collect into v1, v2, v3;
     for i in 1..4 loop
     insert into t2 values(v_msg, v1(i), v2(i), v3(i));
     end loop;
     end;
     /

SQL> select * from t2;

+---------------------------------------------------+----+----+-------------+
|                        C1                         | C2 | C3 |     C4      |
+---------------------------------------------------+----+----+-------------+
| execute immediate sql returning bulk collect into | 1  | a  | 2001-01-01  |
| execute immediate sql returning bulk collect into | 2  | b  | 2012-02-02  |
| execute immediate sql returning bulk collect into | 3  | c  | 2023-03-03  |
| execute immediate sql returning bulk collect into | 4  | d  | 2034-04-04  |
+---------------------------------------------------+----+----+-------------+

-- bulk collect
SQL> declare
     v_sql varchar;
     v_msg varchar;
     v_i int;
     type type_c3 is table of date;
     v3 type_c3;
     begin
     v_sql := 'select c3 from t1 where c1 < ?';
     v_msg := 'execute immediate sql using bulk collect into';
     v_i := 3;
     execute immediate v_sql using v_i bulk collect into v3;
     for i in 1..2 loop
     insert into t2(c1, c4) values(v_msg, v3(i));
     end loop;
     end;
     /

SQL> select * from t2;

+---------------------------------------------------+--------+--------+-------------+
|                        C1                         |   C2   |   C3   |     C4      |
+---------------------------------------------------+--------+--------+-------------+
| execute immediate sql returning bulk collect into | 1      | a      | 2001-01-01  |
| execute immediate sql returning bulk collect into | 2      | b      | 2012-02-02  |
| execute immediate sql returning bulk collect into | 3      | c      | 2023-03-03  |
| execute immediate sql returning bulk collect into | 4      | d      | 2034-04-04  |
| execute immediate sql using bulk collect into     | <NULL> | <NULL> | 2001-01-01  |
| execute immediate sql using bulk collect into     | <NULL> | <NULL> | 2012-02-02  |
+---------------------------------------------------+--------+--------+-------------+

-- limit
SQL> declare
     v_sql varchar;
     v_msg varchar;
     v1 int;
     v2 varchar;
     v3 date;
     begin
     v_sql := 'select * from t1';
     v_msg := 'call immediate sql returning into limit';
     call immediate v_sql returning into v1, v2, v3 limit 1;
     insert into t2 values(v_msg, v1, v2, v3);
     end;
     /

SQL> select * from t2;

+---------------------------------------------------+--------+--------+-------------+
|                        C1                         |   C2   |   C3   |     C4      |
+---------------------------------------------------+--------+--------+-------------+
| execute immediate sql returning bulk collect into | 1      | a      | 2001-01-01  |
| execute immediate sql returning bulk collect into | 2      | b      | 2012-02-02  |
| execute immediate sql returning bulk collect into | 3      | c      | 2023-03-03  |
| execute immediate sql returning bulk collect into | 4      | d      | 2034-04-04  |
| execute immediate sql using bulk collect into     | <NULL> | <NULL> | 2001-01-01  |
| execute immediate sql using bulk collect into     | <NULL> | <NULL> | 2012-02-02  |
| call immediate sql returning into limit           | 4      | d      | 2034-04-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
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286

存储过程/存储函数 ​

存储过程/函数的创建、删除重编译 ​

查看存储过程/函数 ​

执行存储过程/函数 ​

语法格式 ​

存储过程/存储函数

存储过程/函数的创建、删除重编译

查看存储过程/函数

执行存储过程/函数

语法格式