DB2 存储过程应用（返回值和输出参数应用）

最新推荐文章于 2024-10-08 14:09:43 发布

weixin_30241919

最新推荐文章于 2024-10-08 14:09:43 发布

阅读量915

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/northeastTycoon/archive/2012/11/29/2794662.html

本文提供了一个DB2存储过程的实例，展示了如何通过SQL语言创建一个名为SP_UP_P_INV_WASTE的过程，该过程用于更新TABLE_CHOOL表中的记录，并返回操作结果。此示例包含了输入参数和输出参数的使用，以及条件判断等基本流程控制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

我弄了小半天才弄出来，希望对有需要的朋友有所帮助和提示。欢迎光临如转载请说明出处。
DROP SPECIFIC PROCEDURE DB2INST1.SQL121129100123456;

#SYNC 10;

SET SCHEMA TYCOON_SP;

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","NORTHTYCOON";

CREATE PROCEDURE DB2INST1.SP_UP_P_INV_WASTE
(IN "IN_CREATE_WHEN" DATE,
IN "IN_NAME" CHARACTER(20),
IN "IN_SCHOOL" SMALLINT,
IN "IN_TIME" TIMESTAMP,
IN "IN_ID" INTEGER,
OUT "OUT_NUM" INTEGER
)
SPECIFIC DB2INST1.SQL121129100123456
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL ACTION
OLD SAVEPOINT LEVEL
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
P1: BEGIN
--DECLARE RESULTCOUNT CURSOR WITH RETURN FOR
DECLARE NUM INTEGER;
DECLARE OUT_COUNT INTEGER;

   SET OUT_COUNT = 0;
   SET NUM=(SELECT COUNT(1) from(
                SELECT * FROM TABLE_CHOOL
) as TB);
--OPEN RESULTCOUNT;

IF (0 = NUM) THEN
   P2: BEGIN
      SET OUT_COUNT = 1;
      UPDATE TABLE_CHOOL
      SET CREATE_WHEN = IN_CREATE_WHEN,NAME =IN_NAME,SCHOOL=IN_SCHOOL,TIME=IN_TIME,
      WHERE ID=IN_ID;
   END P2;
ELSEIF (1<= NUM) THEN
    SET OUT_COUNT = 999;
END IF;
   SET OUT_NUM = OUT_COUNT;
RETURN OUT_NUM;

END P1;

#SYNC 20;