存储过程out输出参数

发表于2024-11-21 09:26:22128次浏览8个评论

下面这段代码在调用时会报错，调用的代码该如何写？

DROP PROCEDURE IF EXISTS test3;
create procedure test3(
begindate NVARCHAR(100),
endDate NVARCHAR(100),
out departID NVARCHAR(100) default '',
hospitalID NVARCHAR(100)
)
returning nvarchar(4096),nvarchar(4096),nvarchar(4096);
define StorageTypeName nvarchar(4096);
define ItemID nvarchar(4096);
define ItemName nvarchar(4096);
let StorageTypeName = 'a';
let ItemID = 'a';
let ItemName = 'a';
return StorageTypeName,ItemID,ItemName;
end procedure;

--调用
call test3(begindate='b',endDate='c',departID='',hospitalID='d')

登录后才可以发表评论

liaosnet发表于 1年前

out参数需要别的存储过程或者程序来调用，无法直接call调用。
简单的方式参考（存储过程调用方式）：https://gbasedbt.com/index.php/archives/8/

GBase用户24232发表于 1年前

@liaosnet：但实际情况是这个存储过程返回的是多行数据的话，在另一个存储过程里怎么接收并正确回显？

GBase用户24232发表于 1年前

@liaosnet：DROP PROCEDURE IF EXISTS P_GetDailyInventoryCheck;
create procedure P_GetDailyInventoryCheck(
begindate NVARCHAR(100),
endDate NVARCHAR(100),
out departID NVARCHAR(100) default '',
hospitalID NVARCHAR(100)
)
returning nvarchar(4096),nvarchar(4096),nvarchar(4096),int,int,int;
define v_sqlstr varchar(4096);

define StorageTypeName nvarchar(4096);
define ItemID nvarchar(4096);
define ItemName nvarchar(4096);
define storenum int;
define socNumber int;
define InStoreNumber int;
let v_sqlstr = "SELECT tabname,owner,tabtype,rowsize,version,fextsize from systables";

prepare stmt from v_sqlstr;

declare v_cursor cursor for stmt;
open v_cursor;
while(1 = 1)
fetch v_cursor into StorageTypeName,ItemID,ItemName,storenum,socNumber,InStoreNumber;
if( SQLCODE != 100) then return StorageTypeName,ItemID,ItemName,storenum,socNumber,InStoreNumber with resume;
else exit;
end if
end while close v_cursor;

free v_cursor;
free stmt;

end procedure;

--调用
DROP PROCEDURE IF EXISTS P_GetDailyInventoryCheck2;
create procedure P_GetDailyInventoryCheck2()
returning nvarchar(4096),nvarchar(4096),nvarchar(4096),int,int,int;
define icode NVARCHAR(100);
define StorageTypeName nvarchar(4096);
define ItemID nvarchar(4096);
define ItemName nvarchar(4096);
define storenum int;
define socNumber int;
define InStoreNumber int;
let icode = '';
call P_GetDailyInventoryCheck(begindate='2024-05-20',endDate='2024-05-20',departID=icode,hospitalID='HS0001') returning StorageTypeName,ItemID,ItemName,storenum,socNumber,InStoreNumber;
RETURN StorageTypeName,ItemID,ItemName,storenum,socNumber,InStoreNumber;
END PROCEDURE;

call P_GetDailyInventoryCheck2();

liaosnet发表于 1年前

@GBase用户24232：循环赋值给值列表
return 值列表 with resume;
循环

GBase用户24232发表于 1年前

@liaosnet：不太明白，可以直接在我这个代码上改一下吗，我用的是systables这个表

liaosnet发表于 1年前

@GBase用户24232：-- 返回游标
DROP PROCEDURE IF EXISTS P_GetDailyInventoryCheck;
create procedure P_GetDailyInventoryCheck(
begindate NVARCHAR(100),
endDate NVARCHAR(100),
out departID NVARCHAR(100) default '',
hospitalID NVARCHAR(100)
) returning sys_refcursor;
define v_sqlstr varchar(4096);
define v_cursor sys_refcursor;
let v_sqlstr = 'SELECT tabname,owner,tabtype,rowsize,version,fextsize from systables';
open v_cursor for v_sqlstr;
return v_cursor;
end procedure;

-- 接收游标，接受处理out参数
DROP PROCEDURE IF EXISTS P_GetDailyInventoryCheck2;
create procedure P_GetDailyInventoryCheck2()
returning nvarchar(4096),nvarchar(4096),nvarchar(4096),int,int,int;
define icode NVARCHAR(100);
define StorageTypeName nvarchar(4096);
define ItemID nvarchar(4096);
define ItemName nvarchar(4096);
define storenum int;
define socNumber int;
define InStoreNumber int;
define v_cursor sys_refcursor;
let icode = '';
call P_GetDailyInventoryCheck(begindate='2024-05-20',endDate='2024-05-20',departID=icode,hospitalID='HS0001') returning v_cursor;
loop
fetch v_cursor into StorageTypeName,ItemID,ItemName,storenum,socNumber,InStoreNumber;
exit when sqlcode = 100;
return StorageTypeName,ItemID,ItemName,storenum,socNumber,InStoreNumber with resume;
end loop;
close v_cursor;
END PROCEDURE;

GBase用户24232发表于 1年前

@liaosnet：

路路路发表于 9个月前

如果还有问题可以再发问答贴~

存储过程out输出参数

评论

热门帖子