Db2存储过程的屏幕输出，输出参数，返回结果集

最新推荐文章于 2024-06-09 20:30:54 发布
最新推荐文章于 2024-06-09 20:30:54 发布
阅读量3.8k 收藏 10
点赞数 2
分类专栏： DB 文章标签： sql db2 存储过程 SQL PL 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/duke_ding2/article/details/125109658
版权

存储过程除了能够处理和运行业务逻辑，也能输出信息和返回结果。

  • 屏幕输出：通过屏幕输出，显式的向用户展示数据，此外，在调试存储过程时也非常有用；
  • 参数输出：通过 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 （中文版）
蓝黑2020
关注
  • 2
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
存储过程-输出参数
cxz08050924的专栏
08-12 485
 CREATE PROCEDURE [AddOppt]@name varchar(20),@chengwei varchar(10), @company varchar(50),  @tel varchar(20), @addr varchar(100),  @country varchar(50),@outputOpptid int outputAS...set @outputOppti
thinkphp调用sqlserver储存过程返回多个结果集
10-15
【ThinkPHP调用SQLServer储存过程返回多个结果集】 在ThinkPHP框架中，调用SQLServer的储存过程并处理返回的多个结果集是一项常见的任务，特别是在处理复杂的业务逻辑时。以下将详细介绍如何进行操作。 首先，确保...
存储过程小实践
Arielle-L
10-14 412
关于存储过程听大家说了N次，始终不太了解，不明白那就去实践一把，话说教是最好的学，那么实践也是如此；了解新事物的起初总会有相同的疑问，它是什么，有什么作用，如何使用，对于存储过程的认识也不例外；
存储过程的两种返回方法
最新发布
06-09 403
@IDENTITY 返回当前会话的标识值，无论是否在同一个作用域，在测试1、2中，可以看到它返回的是触发器中插入记录的标识值，而在测试3中，因为当前会话无插入记录，所以返回NULL。SCOPE_IDENTITY() 返回当前会话同一作用域的标识值，所以在测试1、2中，它返回的值不受触发器的影响，而在测试3中，因为当前会话无插入记录，所以返回NULL。"语文","问答","知识点","第一期","dd","2006-9-11 8:32:54","重文","2006","艰难", "高中",
DB2 存储过程返回结果集
小熊来了
05-14 5555
存储过程返回结果集，但是并未明确定义out参数的情况下，要正确获取到结果集，则必须明确设置返回结果集个数：result set 结果集个数如果没有声明返回结果集个数，那么存储过程中定义的游标是获取不到其值的1：创建存储过程create procedure proName(in tabName varchar(20)) result set 1 language sql p1:begin de...
db2存储过程返回结果集
tzc_xyh的博客
09-14 2054
使用的是DbVisualizer来编写db2的存储过程 1.连接好db2数据库后，选中希望创建存储过程的schemas，右击Stored Procedures，选中create procedures, 会创建出一个基础格式的db2存储过程，如下图： 2.修改BEGIN 和END之间的sql语句 DECLARE clientcur CURSOR WITH RETURN TO CALLER FOR SELECT ORGANNO, SUPORGANNO FROM BS_ORG; OPEN cli.
DB2 存储过程应用（返回值和输出参数应用）
weixin_30241919的博客
11-29 865
我弄了小半天才弄出来，希望对有需要的朋友有所帮助和提示。欢迎光临如转载请说明出处。DROP SPECIFIC PROCEDURE DB2INST1.SQL121129100123456;#SYNC 10;SET SCHEMA TYCOON_SP;SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","NORTHTYCOON";CREATE PROC...
DB2存储过程开发最佳实践
03-02
对输入参数进行必要的的检查和预处理最佳实践3：正确设定游标的返回类型最佳实践4：异常（condition）处理最佳实践5：合理使用临时表最佳实践6：寻找并rebind非法的存储过程结束语参考资料本文以DB2开发人员的角度...
DB2数据库存储过程技术文档
12-25
2. `OUT`参数不能在调用时直接传递值，而是在过程执行后返回结果。 3. `OUT`参数未赋值时，默认返回`NULL`。 4. `IN/OUT`参数无默认值，必须在`CALL`语句中指定。 5. `CALL`语句调用过程时，所有参数必须是变量，不...
db2存储过程开发
12-06
### DB2存储过程开发知识点详解 #### 一、DB2存储过程概述 DB2是IBM公司推出的一款关系型数据库管理系统，广泛应用于金融、电信等行业。存储过程是在数据库中存储的一组预编译的SQL语句和流程控制指令，用于执行...
DB2中创建一个返回结果集的函数
马欣的记事本
10-17 3675
--创建函数 CREATE FUNCTION F_TEST() RETURNS TABLE(DIN_CODE    VARCHAR(150),                 DIN_NAME   VARCHAR(150),                 DIN_PCO
存储过程的输出参数，返回值与结果集
01-19
每个存储过程都有默认的返回值，默认值为0。下面我们分别看看在management studio中如何查看输出参数，返回值以及结果集，然后我们再在ASP.NET调用存储过程中如何获得输出参数，返回值以及结果集。 首先：在sql server management studio中查看输出参数，返回值以及结果集。本示例以Northwind数据库为例。 代码如下: create proc Employee @Rowcount int=0 output as begin SELECT * FROM [Northwind].[dbo].[Employees] set @Rowcount=@@ROWCOU
存储过程输出参数
yzd
06-23 125
代码调用方法：SqlDatabase db = new SqlDatabase(); db.DbConn.Open(); db.DbCmd.CommandType = CommandType.StoredProcedure; db.DbCmd.CommandText = "eip_ReportMt1"; db.DbCmd.Parameters.AddWithValue("@userId","1")...
oracle存储过程执行中输出日志文件
jimmy9495的专栏
12-17 1213
[code="sql"] create or replace procedure p_outputdebug(a varchar2,b varchar2,c varchar2) is vFileName varchar2(100); OutputFile UTL_FILE.FILE_TYPE; begin select '...
DB2 存储过程 - 利用 dbms_output.put_line 输出至屏幕
热门推荐
crybaby8866的博客
06-22 1万+
优点: 可以调用 oracle 的 put_line 方法来执行,并将结果打印在屏幕上, 无需再调用 shell 脚本. 缺点: 在存储过程正在执行的过程中, 无法实时监控到追踪的结果. ## 设置注册变量 打开兼容性开关 DB2_COMPATIBILITY_VECTOR 设置其值为 ORA db2set  DB2_COMPATIBILITY_VECTOR=ORA ## 打
MySQL存储过程详细演示示例（in、out、inout）
weixin_48190875的博客
10-21 1519
存储过程简述存储过程存储过程的优缺点优点缺点存储过程的创建和调用MySQL 存储过程中的关键语法案例存储过程的参数1、 in 输入参数2、out输出参数3、inout输入参数报错 简述存储过程 存储过程就是数据库中的函数，增强数据库的安全性 MySQL 5.0 版本开始支持存储过程。 存储过程的优缺点 优点 存储过程执行一次后，生成的二进制代码就驻留在缓冲区 存储过程是SQL语句加上控制语句的集合 存储过程存储在服务器端，客户端调用时，直接在服务器端执行，客户端只是传输的调用语句，从而可以降低网络负载 存
带有输出过程的存储过程
weixin_30653097的博客
09-26 509
delimiter $ cerate procedure pro_out(out str varchar(20)) begin set str='hello word '; end $ DILIMITER ; 这里存在一个回话 变量的知识 下一次说 @name 假设这就是一个会画变量 call pro_out(@name) select @name 转载于:...
在DB2存储过程中返回一个数据集
weixin_30618985的博客
05-31 153
在存储过程体内写上DECLARE clientcur CURSOR WITH RETURN TO CALLER;FOR SELECT name, dept, job FROM staff WHERE salary > 15000;OPEN clientcur; 转载于:https://www.cnblogs.com/ryb/archive/2006/05/31/413983.html...
db2存储过程循环select结果集
05-12
可以在 DB2 存储过程中使用游标来循环遍历 SELECT 语句的结果集。 以下是一个示例存储过程，它使用游标循环遍历 SELECT 语句的结果集，并对每一行进行处理： ``` CREATE PROCEDURE my_proc() LANGUAGE SQL BEGIN DECLARE cur CURSOR WITH RETURN TO CALLER FOR SELECT col1, col2, col3 FROM my_table; DECLARE v_col1 INTEGER; DECLARE v_col2 VARCHAR(50); DECLARE v_col3 DATE; OPEN cur; FETCH cur INTO v_col1, v_col2, v_col3; WHILE (SQLCODE = 0) DO -- 处理每一行数据 -- 可以在此处编写自己的逻辑 -- ... FETCH cur INTO v_col1, v_col2, v_col3; END WHILE; CLOSE cur; END; ``` 在这个存储过程中，首先通过 `DECLARE cur CURSOR` 声明一个游标，并通过 `WITH RETURN TO CALLER` 指定游标的结果集应该返回给调用者。然后通过 `OPEN cur` 打开游标，开始遍历结果集。 在循环体中，通过 `FETCH cur INTO` 语句将当前行的数据读取到变量中，并在 `WHILE` 循环中处理每一行数据。最后，在循环结束后通过 `CLOSE cur` 关闭游标。 注意，在游标遍历结束后，需要手动关闭游标，以释放资源。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值