一个在AS/400 DB2下的存储过程示例
因程序需要,要在AS/400系统下建立DB2存储过程,参考了很多资料终于写完了一个可以运行的例子.1CREATE PROCEDURE CIT003/PSCANO
2(IN LOTNO INT,
3 IN MODELNAME VARCHAR(50),
4 IN STARTNO INT,
5 IN QTY INT,
6 IN INTDATE INT,
7 IN INTTIME INT)
8 LANGUAGE SQL MODIFIES SQL DATA
9 P1:BEGIN
10 DECLARE I INT;
11 DECLARE ANAMNO CHAR(15);
12 DECLARE BARCODE CHAR(15);
13 DECLARE TMPSTR CHAR(15);
14 DECLARE FRISTNO CHAR(15);
15 DECLARE LASTNO CHAR(15);
16 DECLARE LINE INT;
17
18 DECLARE S11 CURSOR FOR SELECT SWRKC FROM BPCSFCT/FSO WHERE SORD=LOTNO;
19 OPEN S11;
20 FETCH S11 INTO LINE;
21 CLOSE S11;
22
23 SET TMPSTR='00000'||TRIM(CHAR(STARTNO));
24 SET FRISTNO=TRIM(CHAR(LOTNO))||SUBSTR(TMPSTR,LENGTH(TRIM(TMPSTR))-4,5);
25 SET TMPSTR='00000'||TRIM(CHAR(STARTNO+QTY));
26 SET LASTNO=TRIM(CHAR(LOTNO))||SUBSTR(TMPSTR,LENGTH(TRIM(TMPSTR))-4,5);
27
28 IF NOT EXISTS (SELECT SCBC FROM BPCSUSRFC/SCA WHERE SCBC>=FRISTNO AND SCBC<=LASTNO) THEN
29 SET I=1;
30 WHILE (I<=QTY) DO
31 SET TMPSTR='00000'||TRIM(CHAR(STARTNO));
32 SET BARCODE=TRIM(CHAR(LOTNO))||SUBSTR(TMPSTR,LENGTH(TRIM(TMPSTR))-4,5);
33 INSERT INTO BPCSUSRFC/SCA (SCID,SCFAC,SCDTE,SCWC,SCBC,
34 SCBCTM,SCSO,SCCDT,SCPRD)
35 VALUES ('SC','CF1',INTDATE,LINE,TRIM(BARCODE),INTTIME,LOTNO,
36 INTDATE,MODELNAME);
37 SET I=I+1;
38 SET STARTNO=STARTNO+1;
39 END WHILE;
40 END IF ;
41 END P1;
42
页:
[1]