db2存储过程简单写法

主要实现：循环插入、更新、删除指定数据
表原型：

CREATE TABLE
    CALCLAIMNOTIFY
    (
        CLAIMSEQUENCENO VARCHAR(50) NOT NULL,
        USERCODE VARCHAR(30) NOT NULL,
        AREACODE VARCHAR(10) NOT NULL,
        COMPANYCODE VARCHAR(8) NOT NULL,
        CONFIRMSEQUENCENO VARCHAR(50) NOT NULL,
        POLICYNO VARCHAR(50),
        CLAIMNOTIFYNO VARCHAR(50) NOT NULL,
        NOTIFICATIONTIME TIMESTAMP NOT NULL,
        REPORTER VARCHAR(256),
        DRIVERNAME VARCHAR(256),
        CERTITYPE CHARACTER(2),
        CERTICODE VARCHAR(20),
        LOSSTIME TIMESTAMP NOT NULL,
        LOSSAREA VARCHAR(255) NOT NULL,
        LOSSDESCRIPTION VARCHAR(1000),
        CREATETIME TIMESTAMP,
        DRIVERLICENSENO VARCHAR(20),
        LOSSCAUSECODE CHARACTER(6),
        LICENSENO VARCHAR(15),
        LICENSETYPE CHARACTER(2),
        ACCIDENTLIAB CHARACTER(1),
        OPTIONTYPE CHARACTER(1),
        SUBROGATIONFLAG CHARACTER(1) DEFAULT '0',
        VALIDSTATUS CHARACTER(1) DEFAULT '1',
        UPDATETIME TIMESTAMP,
        RISKQUERYSEQUENCENO VARCHAR(50),
        RISKTYPE CHARACTER(2),
        AREAFLAG VARCHAR(10),
        VERSION VARCHAR(100),
        COORDINATE VARCHAR(60),
        COORDINATESYSTEM VARCHAR(2),
        LOSSAREAMESSAGE VARCHAR(50),
        TEST VARCHAR(100),
        CONSTRAINT P_KEY_1 PRIMARY KEY (CLAIMSEQUENCENO)
    );

CREATE TABLE
    CALCLAIMSTATUS
    (
        CLAIMSEQUENCENO VARCHAR(50) NOT NULL,
        USERCODE VARCHAR(30) NOT NULL,
        AREACODE VARCHAR(10) NOT NULL,
        COMPANYCODE VARCHAR(8) NOT NULL,
        CONFIRMSEQUENCENO VARCHAR(50) NOT NULL,
        POLICYNO VARCHAR(50),
        CLAIMNOTIFYNO VARCHAR(50),
        NOTIFICATIONTIME TIMESTAMP,
        CLAIMREGISTNO VARCHAR(50),
        CLAIMREGISTTIME TIMESTAMP,
        CASEID VARCHAR(50),
        CLAIMCLOSETIME TIMESTAMP,
        CLAIMSTATUSCODE CHARACTER(1),
        CLAIMSTATUSCAUSE CHARACTER(2),
        CLAIMSTATUSDESC VARCHAR(400),
        VALIDSTATUS CHARACTER(1) NOT NULL,
        REMARK VARCHAR(200),
        LOSSTIME TIMESTAMP,
        LOSSAREA VARCHAR(300),
        LOSSDESCRIPTION VARCHAR(1000),
        REOPENTIMES DECIMAL(5,0) DEFAULT 0,
        CLAIMPROGRESS VARCHAR(30),
        CANCLETIME TIMESTAMP,
        SUBROGATIONFLAG CHARACTER(1) DEFAULT '0',
        SUBCERTITYPE CHARACTER(1),
        SUBCLAIMFLAG CHARACTER(1),
        ISREFUSECASE CHARACTER(1) DEFAULT '0',
        FIRSTENDCASETIME TIMESTAMP,
        LASTENDCASETIME TIMESTAMP,
        CHECKSTARTTIME TIMESTAMP,
        CHECKENDTIME TIMESTAMP,
        ESTIMATESTARTTIME TIMESTAMP,
        UNDERENDTIME TIMESTAMP,
        UNDERWRITEENDTIME TIMESTAMP,
        CREATETIME TIMESTAMP,
        COMCLAIMPROGRESS CHARACTER(2),
        REGCANCLETIME TIMESTAMP,
        AREAFLAG VARCHAR(10),
        ISFRAUD VARCHAR(2),
        FRAUDLOGO VARCHAR(2),
        FRAUDRECOVERAMOUNT DECIMAL(14,2),
        UPDATETIME TIMESTAMP,
        CITYCODE VARCHAR(10),
        COUNTYCODE VARCHAR(10),
        CONSTRAINT P_KEY_1 PRIMARY KEY (CLAIMSEQUENCENO)
    );

创建存储过程：

db2 "
CREATE OR REPLACE PROCEDURE p_company(
)
begin
DECLARE v_company varchar(8);
    FOR V AS MYCURSOR CURSOR FOR SELECT ClaimSequenceNo, userCode FROM CALClaimStatus where COMPANYCODE = '' AND CREATETIME >= '2018-12-08 00:00:00' AND CREATETIME <= '2018-12-16 23:59:59'
    DO
        BEGIN
set v_company = substr(V.userCode,1,4);
UPDATE CALClaimNotify SET COMPANYCODE=v_company where ClaimSequenceNo = V.ClaimSequenceNo;

        END;
		
    END FOR;	
end
"

执行存储过程

db2 "call p_company()"