存储过程除了能够处理和运行业务逻辑,也能输出信息和返回结果。
- 屏幕输出:通过屏幕输出,显式的向用户展示数据,此外,在调试存储过程时也非常有用;
- 参数输出:通过
out
参数,输出信息,可以通过屏幕展示,也可以被其它存储过程所访问; - 返回结果:隐式返回结果集,可以通过屏幕展示,也可以被其它存储过程所访问;
环境
- 操作系统:Ubuntu 20.04
- Db2:11.5.0.0
屏幕输出
可以通过调用 dbms_output
模块向“消息缓冲区”输出文本:
dbms_output.newline
:类似于Java的System.out.println()
;dbms_output.put(xxx)
:类似于Java的System.out.print(xxx)
;dbms_output.putline(xxx)
:类似于Java的System.out.println(xxx)
;
注意:消息缓冲区默认并不是“标准输出”(也就是屏幕输出),所以,需要显式的指定一下:
set server output on
:打开标准输出;set server output off
:关闭标准输出;
注意:运行 dbms_output.put(xxx)
并不会立即输出文本到屏幕,只有后面的 dbms_output.newline
或者 dbms_output.putline(xxx)
才会导致其输出。
创建文件 test1.sql
如下:
set serveroutput on@
begin
call dbms_output.put_line('hello');
call dbms_output.put('world');
call dbms_output.put('OK');
call dbms_output.put_line('good');
call dbms_output.new_line();
call dbms_output.put_line('better');
end@
set serveroutput off@
运行脚本 test1.sql
,如下:
➜ temp0602 db2 -td@ -f test1.sql
DB20000I The SET SERVEROUTPUT command completed successfully.
DB20000I The SQL command completed successfully.
hello
worldOKgood
better
DB20000I The SET SERVEROUTPUT command completed successfully.
屏幕输出在调试脚本时非常方便。
参数输出
直接调用存储过程,打印其输出参数
创建文件 test2.sql
如下:
create or replace procedure myproc1(in p1 int, in p2 int, out p3 int)
begin
set p3 = p1 + p2;
end@
运行脚本 test2.sql
。
现在就可以调用 myproc1
了,out参数用 ?
占位:
➜ temp0602 db2 "call myproc1(100, 50, ?)"
Value of output parameters
--------------------------
Parameter Name : P3
Parameter Value : 150
Return Status = 0
在存储过程中访问另一个存储过程返回的输出参数
也可以在其它存储过程中调用 myproc1
。创建文件 test3.sql
如下:
set serveroutput on@
create or replace procedure myproc2()
begin
declare p1, p2, p3 int;
set p1 = 111;
set p2 = 6;
call myproc1(p1, p2, p3);
call dbms_output.put_line('p3 = ' || p3);
end@
call myproc2@
set serveroutput off@
运行脚本 test3.sql
,如下:
➜ temp0602 db2 -td@ -f test3.sql
DB20000I The SET SERVEROUTPUT command completed successfully.
DB20000I The SQL command completed successfully.
Return Status = 0
p3 = 117
DB20000I The SET SERVEROUTPUT command completed successfully.
注:如果是在其它存储过程中调用 myproc1
,out参数就不能用 ?
占位了。
返回结果
存储过程不像函数,无法显式返回结果,不过存储过程可以隐式返回结果集。
直接调用存储过程,打印其返回的结果集
创建文件 test4.sql
如下:
create or replace procedure myproc3
begin
declare cur1 cursor with return for
select c1, c2 from t1;
open cur1;
end@
call myproc3@
运行脚本 test4.sql
,如下:
➜ temp0602 db2 -td@ -f test4.sql
DB20000I The SQL command completed successfully.
Result set 1
--------------
C1 C2
----------- -----------
1 111
2 222
3 333
3 record(s) selected.
Return Status = 0
一个存储过程可以返回多个结果集。创建文件 test5.sql
如下:
create or replace procedure myproc4
begin
declare cur1 cursor with return for
select c1, c2 from t1;
declare cur2 cursor with return for
select c1, c2 from t2;
open cur1;
open cur2;
end@
call myproc4@
运行脚本 test5.sql
,如下:
➜ temp0602 db2 -td@ -f test5.sql
DB20000I The SQL command completed successfully.
Result set 1
--------------
C1 C2
----------- -----------
1 111
2 222
3 333
3 record(s) selected.
Result set 2
--------------
C1 C2
----------- ----------
11 aaa
22 bbb
2 record(s) selected.
Return Status = 0
在定义存储过程时,可以指定返回结果集的最大个数。比如,创建文件 test6.sql
如下:
create or replace procedure myproc5
dynamic result sets 1
begin
declare cur1 cursor with return for
select c1, c2 from t1;
declare cur2 cursor with return for
select c1, c2 from t2;
open cur1;
open cur2;
end@
call myproc5@
运行脚本 test6.sql
,如下:
➜ temp0602 db2 -td@ -f test6.sql
DB20000I The SQL command completed successfully.
Result set 1
--------------
C1 C2
----------- -----------
1 111
2 222
3 333
3 record(s) selected.
Result set 2
--------------
C1 C2
----------- ----------
11 aaa
22 bbb
2 record(s) selected.
Return Status = 0
SQL0464W Procedure "DB2INST1.MYPROC5" returned "2" query result sets, which
exceeds the defined limit "1". SQLSTATE=0100E
➜ temp0602 echo $?
2
本例中,在存储过程里设置了 dynamic result sets 1
,但是实际返回了2个结果集,所以运行时Db2给出了警告信息。
在存储过程中访问另一个存储过程返回的结果集
创建文件 test7.sql
如下:
create or replace procedure myproc6
dynamic result sets 2
begin
declare cur1 cursor with return for
select c1, c2 from t1;
declare cur2 cursor with return for
select c1, c2 from t2;
open cur1;
open cur2;
end@
create or replace procedure myproc7
begin
declare rs1, rs2 result_set_locator varying;
declare c1, c2 int default 0;
declare sqlcode, mysqlcode int default 0;
call myproc6;
associate result set locators(rs1, rs2) with procedure myproc6;
allocate ccur1 cursor for result set rs1;
repeat
fetch ccur1 into c1, c2;
set mysqlcode = sqlcode;
call dbms_output.put_line('c1 = ' || c1 || ', c2 = ' || c2);
until (mysqlcode <> 0)
end repeat;
close ccur1;
end@
set serveroutput on@
call myproc7@
set serveroutput off@
从本例可以看出,要想在存储过程中访问另一个存储过程返回的结果集,步骤如下:
declare rs1, rs2 result_set_locator varying
:定义rs1
和rs2
这两个“结果集定位器”;call myproc6
:调用存储过程;associate result set locators(rs1, rs2) with procedure myproc6
:把rs1
和rs2
关联到存储过程;allocate ccur1 cursor for result set rs1
:给结果集分配游标,注意这里的游标变量不能提前定义,也不能显式open游标;fetch ccur1 into c1, c2
:获取结果集的内容;
运行脚本 test7.sql
,如下:
➜ temp0602 db2 -td@ -f test7.sql
DB20000I The SQL command completed successfully.
DB20000I The SQL command completed successfully.
DB20000I The SET SERVEROUTPUT command completed successfully.
Return Status = 0
c1 = 1, c2 = 111
c1 = 2, c2 = 222
c1 = 3, c2 = 333
c1 = 3, c2 = 333
DB20000I The SET SERVEROUTPUT command completed successfully.
注意: ccur1
虽然是游标,但是不能通过 ccur1 is found
来判断是否fetch到数据,会报错-206,不明白是为什么。只好换成一个土办法,就是判断fetch的sqlcode是不是0,如果不是0,就说明fetch没成功。
参考
- https://www.ibm.com/docs/en/db2/11.5?topic=data-developing-routines
- https://www.ibm.com/docs/zh/db2/11.5?topic=data-developing-routines (中文版)