例一:
以下是引用片段:
CASEv_workdept
WHEN'A00'
THENUPDATEdepartment
SETdeptname='DATAACCESS1';
WHEN'B01'
THENUPDATEdepartment
SETdeptname='DATAACCESS2';
ELSEUPDATEdepartment
SETdeptname='DATAACCESS3';
ENDCASE;
例二:
以下是引用片段:
CASE
WHENv_workdept='A00'
THENUPDATEdepartment
SETdeptname='DATAACCESS1';
WHENv_workdept='B01'
THENUPDATEdepartment
SETdeptname='DATAACCESS2';
ELSEUPDATEdepartment
SETdeptname='DATAACCESS3';
ENDCASE;
for 表达式
for 循环名 as
游标名或select 表达式
do
sql表达式;
end for;
例:
1)
以下是引用片段:
DECLAREfullnameCHAR(40);
FORvlAS
SELECTfirstnme,midinit,lastnameFROMemployee
DO
SETfullname=lastname||','||firstnme||''||midinit;
INSERTINTOtnamesVALUE(fullname);
ENDFOR
2)
以下是引用片段:
forloopcs1ascousor1cursoras
selectmarket_codeasmarket_code
fromtb_market_code
forupdate
do
endfor;
goto表达式
goto 标示名;
标示名:
逻辑体;
例:
GOTO FAIL;
...
SUCCESS: RETURN 0
FAIL: RETURN -200
while表达式
while 条件表达式 do
逻辑体;
end while;
LOOP表达式
LOOP... END LOOP;
例:
以下是引用片段:
OPENc1;
ins_loop:
LOOP
FETCHc1INTOv_dept,v_deptname,v_admdept;
IFat_end=1THEN
LEAVEins_loop;--中断循环
ELSEIFv_dept='D11'THEN
ITERATEins_loop;--下一个循环
ENDIF;
INSERTINTOdepartment(deptno,deptname,admrdept)
VALUES('NEW',v_deptname,v_admdept);
ENDLOOP;
CLOSEc1;
关于游标
定义游标:
DECLARE 游标名 CURSOR FOR
Select 语句;
打开游标:
OPEN 游标名;
取值:
FETCH 游标名 INTO 变量列表
文章来源于领测软件测试网 https://www.ltesting.net/