Posted on 2007-04-12 10:13
火星 阅读(538)
评论(0) 编辑 收藏 所属分类:
DB2
1、insert into "test"
(
select char(f_index) from T_QA_RPT_FACE_MONTH where F_RPT_ID=0
);
如果“select char(f_index) from T_QA_RPT_FACE_MONTH where F_RPT_ID=0“
返回结果数为0，那么test表中不会插入记录。
2、存储过程里用for语句比while loop好用些：（类似VB语法）
SET SCHEMA MES ;
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","MES";
CREATE PROCEDURE "MES"."P_QA_TEST" ( )
DECLARE iCount INT DEFAULT 0;
SET iCount = 0;
FOR dayrpt AS SELECT * FROM T_QA_RPT_FACE_DAY WHERE F_RPT_ID > 0 DO
ADD_BONUS:
for monrpt as SELECT * FROM T_QA_RPT_FACE_MONTH WHERE F_RPT_ID > 0 DO
if (monrpt.F_INDEX = dayrpt.F_INDEX) AND (monrpt.F_TYPE = dayrpt.F_TYPE) THEN
SET iCount=iCount+1;
insert into "test" values(char(iCount));
LEAVE ADD_BONUS;
END IF;
end for ADD_BONUS;
END FOR;
return iCount;
END;
3、上例中：
FOR dayrpt AS SELECT * FROM T_QA_RPT_FACE_DAY WHERE F_RPT_ID > 0 DO
dayrpt 就是后面那个select语句的结果集，即for语句只需读一次表，而不会像游标那样锁定表然后进行逐记录select