db2数据库存储过程---简单的增删改查

新的工作中要用到db2数据库，并且大部分业务逻辑都是用存储过程实现，所以最近开始在研究 db2 数据库存储过程，下面是一个简单的 demo 实现了增删改查：

1、数据准备：创建员工表EMPINFO

drop table DLX.EMPINFO; CREATE TABLE DLX.EMPINFO( EM_ID CHARACTER(8) not null, -- ID   EM_NAME VARCHAR(50), --姓名  EM_SEX CHARACTER(1),  --性别 1、男  0、女  EM_AGE SMALLINT,  --年龄  EM_TELNUM varchar(11),--手机号码  EM_BIRTHDAY DATE,--生日   EM_ADD VARCHAR(255),--地址  EM_SALARY DECIMAL(15,2),--薪资  EM_CHANGEDAT DATE,  --修改时间  PRIMARY KEY(EM_ID));

2、实现添加数据存储过程：

CREATE OR REPLACE PROCEDURE DLX.SP_E_EMPINFO_ADD_V1(    IN ID CHARACTER(8),    IN NAME VARCHAR(50),    IN SEX CHARACTER(1),     IN AGE CHARACTER(4),    IN TELNUM varchar(11),    IN BIRTHDAY CHARACTER(12),    IN EMADD VARCHAR(255),    IN SALARY VARCHAR(20),    OUT pRtCode INTEGER)SPECIFIC DLX.SP_E_EMPINFO_ADD_V1LANGUAGE SQLBEGIN  --异常处理  DECLARE SQLCODE INTEGER;   DECLARE EXIT HANDLER FOR SQLEXCEPTION SET pRtCode = SQLCODE;   SET pRtCode = -1;  IF ID = '' THEN SET ID = NULL; END IF;  IF NAME = '' THEN SET NAME = NULL; END IF;  IF SEX = '' THEN SET SEX = NULL; END IF;  IF AGE = '' THEN SET AGE = NULL; END IF;  IF TELNUM = '' THEN SET TELNUM = NULL; END IF;  IF BIRTHDAY = '' THEN SET BIRTHDAY = NULL; END IF;  IF EMADD = '' THEN SET EMADD = NULL; END IF;  IF SALARY = '' THEN SET SALARY = NULL; END IF;  INSERT INTO DLX.EMPINFO(EM_ID,EM_NAME,EM_SEX,EM_AGE,EM_TELNUM,EM_BIRTHDAY,EM_ADD,EM_SALARY,EM_CHANGEDAT)    VALUES(ID,NAME,SEX,CAST(AGE AS SMALLINT),TELNUM,TO_DATE(BIRTHDAY,'YYYY-MM-DD'),EMADD,CAST(SALARY AS DECIMAL(15,2)),current timestamp);  SET pRtCode = 0;END;

3、实现删除数据存储过程：

--删除员工信息 存储过程CREATE OR REPLACE PROCEDURE DLX.SP_E_EMPINFO_DEL_V1(  IN ID CHARACTER(8),  OUT PRTCODE  INTEGER)  SPECIFIC "SP_E_EMPINFO_DEL_V1"  LANGUAGE SQLBEGIN  DECLARE numrows INTEGER;   DECLARE SQLCODE INTEGER;  DECLARE EXIT HANDLER FOR SQLEXCEPTION SET pRtCode = SQLCODE;  SET pRtCode = -1;  DELETE FROM DLX.EMPINFO  WHERE "EM_ID" = ID;  GET DIAGNOSTICS numrows = ROW_COUNT;  IF numrows = 0 THEN    SET pRtCode = 401;    RETURN;  END IF;  SET pRtCode = 0;END;

4、实现修改数据存储过程：

--修改员工信息存储过程CREATE OR REPLACE PROCEDURE DLX.SP_E_EMPINFO_UPD_V1(    IN ID CHARACTER(8),    IN NAME VARCHAR(50),    IN SEX CHARACTER(1),     IN AGE CHARACTER(4),    IN TELNUM varchar(11),    IN BIRTHDAY CHARACTER(12),    IN EMADD VARCHAR(255),    IN SALARY VARCHAR(20),    OUT pRtCode INTEGER)SPECIFIC DLX.SP_E_EMPINFO_UPD_V1LANGUAGE SQLBEGIN  DECLARE numrows INT DEFAULT 0;   --异常处理  DECLARE SQLCODE INTEGER;   DECLARE EXIT HANDLER FOR SQLEXCEPTION SET pRtCode = SQLCODE;   SET pRtCode = -1;  UPDATE DLX.EMPINFO  SET EM_NAME = NAME,EM_SEX = SEX,EM_AGE = CAST(AGE AS SMALLINT),      EM_TELNUM = TELNUM,EM_BIRTHDAY = TO_DATE(BIRTHDAY,'YYYY-MM-DD'), EM_ADD = EMADD,      EM_SALARY = CAST(SALARY AS DECIMAL(15,2)),EM_CHANGEDAT = DATE(CURRENT TIMESTAMP) WHERE EM_ID = ID;    GET DIAGNOSTICS numrows = ROW_COUNT;   IF numrows = 0 THEN     SET pRtCode = 401;     RETURN;   END IF;   SET pRtCode = 0;END;

5、实现分页查询存储过程：

DROP PROCEDURE DLX.SP_E_EMPINFO_QRY_ALL_V1;  --先删除已经存在的--分页查询存储过程CREATE OR REPLACE PROCEDURE DLX.SP_E_EMPINFO_QRY_ALL_V1(  IN NAME VARCHAR(50), --输入参数定义  IN SEX CHARACTER(1),  IN START INTEGER,  IN LIMIT INTEGER,  OUT O_TOTAL INTEGER, --输出参数定义  OUT O_RTCODE INTEGER)SPECIFIC "SP_E_EMPINFO_QRY_ALL_V1"  --定义别名，唯一的DYNAMIC RESULT SETS 1   --定义游标BEGIN  --异常处理  DECLARE SQLCODE INTEGER;  DECLARE EXIT HANDLER FOR SQLEXCEPTION SET O_RTCODE = SQLCODE;  SET O_RTCODE = -1;  --查询总数  BEGIN    DECLARE v_cur2 CURSOR FOR    SELECT COUNT(1) FROM DLX.EMPINFO    WHERE (NAME IS NULL OR NAME = '' OR EM_NAME like '%' || NAME || '%')         AND (SEX IS NULL OR SEX = '' OR EM_SEX = SEX);    OPEN v_cur2;    FETCH v_cur2 INTO O_TOTAL;    CLOSE v_cur2;  END;  --查询分页结果  BEGIN    DECLARE v_cur1 CURSOR WITH RETURN TO CLIENT FOR     SELECT EM_ID,EM_NAME,EM_SEX,EM_AGE,EM_TELNUM,EM_BIRTHDAY,EM_ADD,EM_SALARY,EM_CHANGEDAT    FROM(SELECT             B.EM_ID,            B.EM_NAME,            B.EM_SEX,            B.EM_AGE,            B.EM_TELNUM,            B.EM_BIRTHDAY,            B.EM_ADD,            B.EM_SALARY,            B.EM_CHANGEDAT,            ROW_NUMBER() OVER(ORDER BY B.EM_ID ASC) RN          FROM            DLX.EMPINFO B          WHERE            (NAME IS NULL OR NAME = '' OR EM_NAME like '%' || NAME || '%')             AND (SEX IS NULL OR SEX = '' OR EM_SEX = SEX)    ) RS    WHERE      RS.RN >= START AND RS.RN <= (START+LIMIT);    OPEN v_cur1;  END;  SET O_RTCODE = 0;END;

注意：调存储过程的方法：
调用的时候输入的参数传值，输出的参数用 ? 代替
例如调添加数据存储过程：
CALL DLX.SP_E_EMPINFO_ADD_V1(‘000013’,’拉拉’,’1’,22,’16066548712’,’2015-5-20’,’广州’,’666.99’,?);

这篇文章中只是写了最基本的增删改查的存储过程，详细的介绍及学习，以后慢慢总结。