DM8存储过程练习笔记

内外LOOP

create or replace procedure proc_block as declare x int:=0; counter int:=0; begin for i in 1 .. 4 loop x:=x+1000; counter:=counter+1; print cast(x as char(10)) || cast(counter as char(10)) || 'outer loop'; declare x int:=0; begin for i in 1 .. 4 loop x:=x+1; counter:=counter+1; print cast(x as char(10)) || cast(counter as char(10)) || 'inner loop'; end loop; end; end loop; end; /

IF子句

delcare procedure proc_if( sales dec, quota dec, emp_id dec ) is bonus dec:=0; begin if sales > (quota + 400) then bonus:=(sales - quota)/4; else bonus:=100; end if; dbms_output.put_line('bonus = ' || bonus); update ...... end proc_if; begin proc_if(30100,20000,1); proc_if(15000,10000,2); end; /

CASE子句

create or replace procedure proc_case(grade char(10)) as declare appraisal varchar2(20); begin appraisal:= case grade when null then 'is null' when 'A' then 'excellent' when 'B' then 'good' when 'C' then 'fair' else 'no such grade' end; dbms_output.put_line('grade ' || grade || ' is ' ||appraisal); end; /

switch子句

{ varchar appraisal:='B'; switch (appraisal) { case null:print 'is null';breadk; case 'A':print 'excellent';break; case 'B':print 'good';break; case 'C':print 'fair';break; default:print 'no such grade'; } } /

LOOP语句

create or replace procedure proc_loop(a in out int) as begin loop if a

WHILE语句

create or replace procedure proc_while(a in int) as begin while a>0 loop print a; a:=a-1; end loop; end; /

create or replace procedure proc_1(a in int) as b int; begin select a into b ; a = b+4; print 'a='||a; end; / declare id int; begin id = 10; call proc_1(id); print 'id=' ||id; end; / create or replace procedure proc_2(a in out int) as b int; begin select a into b ; a = b+4; print 'a='||a; end; / declare id int; begin id = 10; call proc_2(id); print 'id=' ||id; end; /

FOR语句

create or replace procedure proc_for1 (a in out int) as begin for i in reverse 1 .. a loop print i; a:=i-1; end loop; end; /

declare v1 date:=date '2021-12-14'; begin for v1 in 3 .. 5 loop print v1; end loop; print v1; end; /

REPRAT语句

declare a int; begin a:=0; repeat a:=a+1; print a; until a>10; end; /

FOR ALL语句

create table t1_forall(c1 int,c2 varchar(50)); create or replace procedure p1_forall as begin forall i in 1 .. 10 insert into t1_forall select top 1 tea_id,tea_name from teacher; end; / call p1_forall(); select * from t1_forall;

EXIT语句 declare a int; b int; begin a:=0; loop for b in 1 .. 2 loop print '内层循环' || b; exit when a > 3; end loop; a:=a+2; print '外层循环' || a; exit when a > 5; end loop; end; /

CONTINUE语句

declare x int:=0; begin > --continue跳出之后，回到这里 for i in 1 .. 4 loop dbms_output.put_line('循环内部,CONTINUE之前:x='|| TO_CHAR(x)); x:=x+1; CONTINUE flag1; dbms_output.put_line('循环内部,CONTINUE之后:x='|| TO_CHAR(x)); end loop; dbms_output.put_line('循环外部:x='|| TO_CHAR(x)); end; /

RETURN语句

create or replace function func_return(a int) return varchar(10) as begin if (a

FETCH declare tea_name varchar(20); tea_id varchar(20); c1 cursor; begin open c1 for select tea_name,tea_id from teacher; loop fetch c1 into tea_name,tea_id; exit when c1%notfound; print tea_name || tea_id; end loop; close c1; end; /

显示游标

declare cursor c1 for select tea_name,tea_id,tea_sex from teacher; name char(10); id int; sex varchar(10); begin open c1; loop fetch c1 into name,id,sex; exit when c1%notfound; print id || ' is ' || name || ' ' || sex; exit when c1%rowcount=5; end loop; close c1; end; /

动态游标

declare name varchar(20); id int; sex varchar(10); c1 cursor; begin open c1 for select tea_name,tea_id,tea_sex from teacher; --open .. for .. loop fetch c1 into name,id,sex; exit when c1%notfound; print '工号' || id || '名为' || name || '性别是' || sex; end loop; close c1; end; /

declare cursor csr is select tea_sex from teacher where tea_id=111 for update; begin open csr; if csr%ISOPEN then fetch csr; update teacher set tea_sex='女' where current of csr; --where current of

更新游标所在行数据

else print 'cursor is not opened'; end if; close csr; end; /

create or replace proc(cate in int,time in date)

as

declare

str_sql varchar:='select tea_name,tea_prof from teacher where tea_id= ? and tea_birthday= ?';

begin

excute immediate str_sql using cate,time;

exception

when others then print 'error';

end;

/

达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台达梦数据库产品体验站，DM8在线试玩，达梦数据库全系列产品免费下载，官方权威的快速上手文档和产品手册，最活跃的达梦技术社区，面向全行业ISV厂商免费的云适配服务。https://eco.dameng.com