登 录
MySQL存储过程例子，包含事务，参数，嵌套调用，游标，循环等
drop procedure if exists pro_rep_shadow_rs;<br /> delimiter |<br /> ----------------------------------<br /> -- rep_shadow_rs<br /> -- 用来处理信息的增加，更新和删除<br /> -- 每次只更新上次以来没有做过的数据<br /> -- 根据不同的标志位<br /> -- 需要一个输出的参数，<br /> -- 如果返回为0，则调用失败，事务回滚<br /> -- 如果返回为1，调用成功，事务提交<br /> --<br /> -- 测试方法<br /> -- call pro_rep_shadow_rs(@rtn);<br /> -- select @rtn;<br /> ----------------------------------<br /> create procedure pro_rep_shadow_rs(out rtn int)<br /> begin<br /> -- 声明变量，所有的声明必须在非声明的语句前面<br /> declare iLast_rep_sync_id int default -1;<br /> declare iMax_rep_sync_id int default -1;<br /> -- 如果出现异常，或自动处理并rollback,但不再通知调用方了<br /> -- 如果希望应用获得异常，需要将下面这一句，以及启动事务和提交事务的语句全部去掉<br /> declare exit handler for sqlexception rollback;<br /> -- 查找上一次的<br /> select eid into iLast_rep_sync_id from rep_de_proc_log where tbl='rep_shadow_rs';<br /> -- 如果不存在，则增加一行<br /> if iLast_rep_sync_id=-1 then<br /> insert into rep_de_proc_log(rid,eid,tbl) values(0,0,'rep_shadow_rs');<br /> set iLast_rep_sync_id = 0;<br /> end if; </p> <p> -- 下一个数字<br /> set iLast_rep_sync_id=iLast_rep_sync_id+1;<br /> -- 设置默认的返回值为0：失败<br /> set rtn=0; </p> <p> -- 启动事务<br /> start transaction;<br /> -- 查找最大编号<br /> select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;<br /> -- 有新数据<br /> if iMax_rep_sync_id>=iLast_rep_sync_id then<br /> -- 调用<br /> call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);<br /> -- 更新日志<br /> update rep_de_proc_log set rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';<br /> end if; </p> <p> -- 运行没有异常，提交事务<br /> commit;<br /> -- 设置返回值为1<br /> set rtn=1;<br /> end;<br /> |<br /> delimiter ;<br /> drop procedure if exists pro_rep_shadow_rs_do;<br /> delimiter |<br /> ---------------------------------<br /> -- 处理指定编号范围内的数据<br /> -- 需要输入2个参数<br /> -- last_rep_sync_id 是编号的最小值<br /> -- max_rep_sync_id 是编号的最大值<br /> -- 无返回值<br /> ---------------------------------<br /> create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id int)<br /> begin<br /> declare iRep_operationtype varchar(1);<br /> declare iRep_status varchar(1);<br /> declare iRep_Sync_id int;<br /> declare iId int;<br /> -- 这个用于处理游标到达最后一行的情况<br /> declare stop int default 0;<br /> -- 声明游标<br /> declare cur cursor for select id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where rep_sync_id between last_rep_sync_id and max_rep_sync_id;<br /> -- 声明游标的异常处理，设置一个终止标记<br /> declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1; </p> <p> -- 打开游标<br /> open cur; </p> <p> -- 读取一行数据到变量<br /> fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;<br /> -- 这个就是判断是否游标已经到达了最后<br /> while stop <> 1 do<br /> -- 各种判断<br /> if iRep_operationtype='I' then<br /> insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;<br /> elseif iRep_operationtype='U' then<br /> begin<br /> if iRep_status='A' then<br /> insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;<br /> elseif iRep_status='B' then<br /> delete from rs0811 where id=iId;<br /> end if;<br /> end;<br /> elseif iRep_operationtype='D' then<br /> delete from rs0811 where id=iId;<br /> end if; </p> <p> -- 读取下一行的数据<br /> fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;<br /> end while; -- 循环结束<br /> close cur; -- 关闭游标<br /> end;<br /> use testprocedure;<br /> delimiter //</p> <p>create procedure simpleproce1 (out par1 int)<br /> begin<br /> select count(*) into par1 from proce;<br /> end<br /> //<br /> delimiter ;<br /> call simpleproce1(@a);<br /> select @a;<br /> #<2>,每次只有单一的行可以被取回select id,name into par1,par2 from proce LIMIT 1;中的LIMIT 1;<br /> use testprocedure;<br /> delimiter //<br /> DROP procedure IF EXISTS simpleproce2<br /> create procedure simpleproce2 (out par1 int,out par2 char(30))<br /> begin<br /> select id,name into par1,par2 from proce LIMIT 1;<br /> end<br /> //<br /> delimiter ;<br /> call simpleproce2(@a,@b);<br /> select @a,@b;</p> <p>## *********second test,function************<br /> #<3><br /> delimiter //<br /> DROP FUNCTION IF EXISTS hello<br /> //<br /> create function hello(s char(20)) returns char(50)<br /> return concat('Hello, ',s,'!');<br /> //<br /> delimiter ;<br /> select hello('world');<br /> show create function testprocedure.helloG<br /> #它返回子程序的特征，如数据库，名字，类型，创建者及创建和修改日期<br /> show function status like 'hello'G<br /> #<4><br /> #注意name不能和字段名相同<br /> delimiter //<br /> DROP procedure IF EXISTS test //<br /> CREATE PROCEDURE test ()<br /> BEGIN<br /> DECLARE name VARCHAR(5) DEFAULT 'bob';<br /> DECLARE newname VARCHAR(5);<br /> DECLARE xid INT;</p> <p> SELECT name,id INTO newname,xid<br /> FROM proce WHERE name = name;<br /> SELECT newname;<br /> END;<br /> //<br /> call test1() //<br /> #***<br /> delimiter //<br /> DROP procedure IF EXISTS test2 //<br /> CREATE PROCEDURE test2 ()<br /> BEGIN</p> <p> DECLARE newname VARCHAR(5);<br /> DECLARE xid INT;</p> <p> SELECT name,id INTO newname,xid<br /> FROM proce limit 1;<br /> SELECT newname,xid;<br /> END;<br /> //<br /> call test2() //<br /> #<5><br /> use testprocedure;<br /> CREATE PROCEDURE p1 () SELECT * FROM proce;<br /> call p1();<br /> #<6>注意此处的handler是设置SQLSTATE值，SQLWARNING是对所有以01开头的SQLSTATE代码的速记<br /> #NOT FOUND是对所有以02开头的SQLSTATE代码的速记<br /> #SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记<br /> #DECLARE CONTINUE HANDLER声明CONTINUE异常处理<br /> #事实上这里的23000SQLSTATE是更常用的，当外键约束出错或主键约束出错就被调用了。<br /> #当没有发生该23000异常时， select @x2的值将是null,而不是1,<br /> #并且后面的第2个语句执行时将会报主键约束错误，此时@x2=1,@x=4,虽然第2句有了异常，但是后面的语句继续执行<br /> #保存到数据的数据是3,test3和5,test5<br /> use testprocedure;<br /> delimiter //<br /> DROP procedure IF EXISTS handlerdemo<br /> //<br /> create procedure handlerdemo()<br /> begin<br /> declare continue handler for sqlstate '23000' set @x2=1;<br /> set @x=1;<br /> insert into proce values(3,'test3');<br /> set @x=2;<br /> insert into proce values(3,'test4');<br /> set @x=3;<br /> insert into proce values(5,'test5');<br /> set @x=4;<br /> end;<br /> //<br /> call handlerdemo()//<br /> select @x //<br /> select @x2 //<br /> ## ************光标****************<br /> #<7>光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明<br /> #在这里先声明变量a,b,c,后声明cursor<br /> create procedure curdemo()<br /> begin<br /> declare done int default 0;<br /> declare a char(16);<br /> declare b,c int;<br /> declare cur1 cursor for select id,name from proce;<br /> declare cur2 cursor for select id from proce2;<br /> declare continue handler for sqlstate '02000' set done=1;<br /> open cur1;<br /> open cur2;<br /> repeat<br /> fetch cur1 into b,a;<br /> fetch cur2 into c;<br /> if not done then<br /> if b<c then<br /> insert into proce3 values(b,a);<br /> else<br /> insert into proce3 values(c,a);<br /> end if;<br /> end if;<br /> until done end repeat;<br /> close cur1;<br /> close cur2;<br /> end<br /> ## **************** Case *******************<br /> #<8>when ... then ;case ... end case;<br /> delimiter //<br /> DROP procedure IF EXISTS p13<br /> //<br /> create procedure p13(in par1 int)<br /> begin<br /> declare var1 int;<br /> set var1=par1+1;<br /> case var1<br /> when 0 then insert into casetest values(17);<br /> when 1 then insert into casetest values(18);<br /> else insert into casetest values(19);<br /> end case;<br /> end;<br /> //<br /> call p13(-1)//<br /> call p13(0)//<br /> call p13(1)//<br /> call p13(null)//</p> <p>## **************** while ****************<br /> #<9>while ... do ... end while;为了防止null的错误，set v=0是必须的<br /> delimiter //<br /> DROP procedure IF EXISTS p14<br /> //<br /> create procedure p14()<br /> begin<br /> declare v int;<br /> set v=0;<br /> while v < 5 do<br /> insert into casetest values (v);<br /> set v=v+1;<br /> end while;<br /> end;//<br /> call p14()//<br /> ## ***************** repeat *****************<br /> #<10>repeat ...until ... end repeat; 是执行后检查（until v>=5），而while是执行前检查(while v<5)<br /> delimiter //<br /> DROP procedure IF EXISTS p15<br /> //<br /> create procedure p15()<br /> begin<br /> declare v int;<br /> set v=0;<br /> repeat<br /> insert into casetest values(v);<br /> set v=v+1;<br /> until v >=5<br /> end repeat;<br /> end;<br /> //<br /> call p15()//<br /> ## ***************** loops *****************<br /> #<11> loop 和while一样不需要初始条件，同时和repeat一样不需要结束条件<br /> # loop_label: loop<br /> # ...<br /> # if .. then<br /> # leave loop_label<br /> # end if<br /> # end loop<br /> delimiter //<br /> DROP procedure IF EXISTS p16<br /> //<br /> create procedure p16()<br /> begin<br /> declare v int;<br /> set v=0;<br /> loop_label: loop<br /> insert into casetest values(v);<br /> set v=v+1;<br /> if v >=5 then<br /> leave loop_label;<br /> end if;<br /> end loop;<br /> end;//<br /> call p16()//<br /> ## ***************** Labels *****************<br /> # <12>labels标号； 注意此处的until 0=0后面没有分号“;”<br /> delimiter //<br /> DROP procedure IF EXISTS p17//<br /> create procedure p17()<br /> label_1:begin<br /> label_2:while 0=1 do leave label_2; end while;<br /> label_3:repeat leave label_3;until 0=0 end repeat;<br /> label_4:loop leave label_4; end loop;<br /> end;//<br /> call p17()//<br /> #<13>labels 标号结束符；<br /> delimiter //<br /> DROP procedure IF EXISTS p18//<br /> create procedure p18()<br /> label_1:begin<br /> label_2:while 0=1 do leave label_2; end while label_2;<br /> label_3:repeat leave label_3;until 0=0 end repeat label_3;<br /> label_4:loop leave label_4; end loop label_4;<br /> end label_1;//<br /> call p18()//<br /> #<14>leave和labels 跳出和标号;leave 使程序跳出复杂的语句<br /> delimiter //<br /> DROP procedure IF EXISTS p19//<br /> create procedure p19(par char)<br /> label_1:begin<br /> label_2:begin<br /> label_3:begin<br /> if par is not null then<br /> if par='a' then leave label_1;<br /> else<br /> begin<br /> if par='b' then<br /> leave label_2;<br /> else<br /> leave label_3;<br /> end if;<br /> end;<br /> end if;<br /> end if;<br /> end label_3;<br /> end label_2;<br /> end label_1;<br /> //<br /> call p19('a')//</p> <p>#<15>iterate迭代，必须用leave;iterate意思是重新开始复合语句，相当于 continue<br /> #该结果中3将不被保存到数据库表中<br /> delimiter //<br /> DROP procedure IF EXISTS p20//<br /> create procedure p20()<br /> begin<br /> declare v int;<br /> set v=0;<br /> loop_label:loop<br /> if v=3 then<br /> set v=v+1;<br /> iterate loop_label;<br /> end if;<br /> insert into casetest values(v);<br /> set v=v+1;<br /> if v>=5 then<br /> leave loop_label;<br /> end if;<br /> end loop loop_label;<br /> end;//<br /> call p20()//</p> <p>#<16>Grand combination大组合<br /> delimiter //<br /> DROP procedure IF EXISTS p21//<br /> create procedure p21(in par1 int,out par2 int)<br /> language sql deterministic sql security invoker<br /> begin<br /> declare v int;<br /> label goto_label;<br /> start_label:loop<br /> if v=v then<br /> leave start_label;<br /> else<br /> iterate start_label;<br /> end if;<br /> end loop start_label;<br /> repeat<br /> while 1=0 do begin end;<br /> end while;<br /> until v=v<br /> end repeat;<br /> goto goto_label;</p> <p>end;<br /> //<br /> call p21()//<br /> ## **************** trigger ***************************<br /> #<17><br /> use testprocedure;<br /> CREATE TABLE trig1(a1 int);<br /> CREATE TABLE trig2(a2 int);<br /> CREATE TABLE trig3(a3 int not null AUTO_INCREMENT PRIMARY KEY);<br /> CREATE TABLE trig4(<br /> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,<br /> b4 INT DEFAULT 0<br /> );<br /> insert into trig3(a3) values(null),(null),(null),(null),(null),(null),(null),(null),(null),(null);<br /> insert into trig4(a4) values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0);<br /> delimiter //<br /> DROP trigger trigtest//<br /> create trigger trigtest before insert on trig1<br /> for each row begin<br /> insert into trig2 set a2=NEW.a1;<br /> delete from trig3 where a3=NEW.a1;<br /> update trig4 set b4=b4+1 where a4=NEW.a1;<br /> end;<br /> //</p> <p>delimiter ;<br /> INSERT INTO trig1 VALUES(1), (3), (1), (7), (1), (8), (4), (4);<br /> ------ -------------------------------------------------------------------------<br /> Drop Procedure If Exists p_report;<br /> Delimiter forend<br /> Create Procedure p_report<br /> (In year Int,<br /> In month Int,<br /> In Id Char(10),<br /> Out status Int)<br /> Begin<br /> SelectYear,<br /> Month,<br /> OfficeId,<br /> OnTimeRate<br /> FromReportByMonth<br /> WhereOfficeId = Id And<br /> Year = year;<br /> End<br /> forend<br /> Delimiter ;
抱歉!评论已关闭.