《DB2开发培训》课件.pptVIP

* * * * 流程控制： if 表达式 if 条件1 then 逻辑体； elseif 条件2 then 逻辑体； else 逻辑体； end if; 例： IF rating = 1 THEN UPDATE employee SET salary = salary * 1.10, bonus = 1000 WHERE empno = employee_number; ELSEIF rating = 2 THEN UPDATE employee SET salary = salary * 1.05, bonus = 500 WHERE empno = employee_number; ELSE UPDATE employee SET salary = salary * 1.03, bonus = 0 WHERE empno = employee_number; END IF; case表达式 case 变量名 when 变量值1 then . . . when 变量值2 then - - - else . . . end case; 或 case when 变量名=变量值1 then . . . when 变量名=变量值2 then - - - else . . . end case; 例一： CASE v_workdept WHEN A00 THEN UPDATE department SET deptname = DATA ACCESS 1; WHEN B01 THEN UPDATE department SET deptname = DATA ACCESS 2; ELSE UPDATE department SET deptname = DATA ACCESS 3; END CASE； 例二： CASE WHEN v_workdept = A00 THEN UPDATE department SET deptname = DATA ACCESS 1; WHEN v_workdept = B01 THEN UPDATE department SET deptname = DATA ACCESS 2; ELSE UPDATE department SET deptname = DATA ACCESS 3; END CASE； for 表达式 for 循环名 as 游标名或select 表达式 do sql表达式； end for; 例： 1） DECLARE fullname CHAR(40); FOR vl AS SELECT firstnme, midinit, lastname FROM employee DO SET fullname = lastname || , || firstnme || || midinit; INSERT INTO tnames VALUE (fullname); END FOR 2） for loopcs1 as cousor1 cursor as select market_code as market_code from tb_market_code for update do end for; goto表达式 goto 标示名； 标示名： 逻辑体； 例： GOTO FAIL； ... SUCCESS: RETURN 0 FAIL: RETURN -200 while表达式 while 条件表达式 do 逻辑体； end while； LOOP表达式 LOOP... END LOOP； 例： OPEN c1; ins_loop: LOOP FETCH c1 INTO v_dept, v_deptname, v_admdept; IF at_end = 1 THEN LEAVEins_loop; --中断循环 ELSEIF v_dept = D11 THEN ITERATEins_loop; --下一个循环 END IF; INSERT INTO department (deptno, deptname, admrdept) VALUES (NEW, v_deptname, v_admdept); END LOOP; CLOSE c1; * * * * * * * * * * * * * 1. db2pd 范围选项 实例 -inst 或 -ins 用于实例 数据库 -database x 或 –db x 用于特定的数据库x 数据库 -alldatabase 或 –