Examples of MySQL stored procedures, including transactions, parameters, nesting calls, cursors, loops, etc.

drop procedure if exists pro_rep_shadow_rs;
Delimiter |
----------------------------------
--Rep_shadow_rs
--Used to process information additions, updates, and deletions
--Update only data that hasn't been done since last time
--According to the different logo bit
--Requires an output parameter,
--if returned to 0, the call fails and the transaction rolls back
--if returned as 1, the call succeeds and the transaction commits
--
--Test method
--Call Pro_rep_shadow_rs (@rtn);
--Select @rtn;
----------------------------------
CREATE PROCEDURE pro_rep_shadow_rs (out RTN int)
Begin
--Declare variables, all declarations must precede statements that are not declared
declare ilast_rep_sync_id int default-1;
declare imax_rep_sync_id int default-1;
--If an exception occurs, or is automatically processed and rollback, the caller is no longer notified
--If you want the application to get an exception, you need to remove the following sentence, as well as the statements that start the transaction and commit the transaction
Declare exit handler for SqlException rollback;
--Find the last time
Select Eid into ilast_rep_sync_id from Rep_de_proc_log where tbl= ' rep_shadow_rs ';
--Add a row if it does not exist
If Ilast_rep_sync_id=-1 Then
Insert into Rep_de_proc_log (RID,EID,TBL) VALUES (0,0, ' rep_shadow_rs ');
Set ilast_rep_sync_id = 0;
End If;

--Next Number
Set ilast_rep_sync_id=ilast_rep_sync_id+1;
--Set the default return value of 0: failed
Set rtn=0;

--Start a transaction
Start transaction;
--Find the maximum number
Select Max (rep_sync_id) into the imax_rep_sync_id from Rep_shadow_rs;
--New data available
If imax_rep_sync_id>=ilast_rep_sync_id Then
--Call
Call Pro_rep_shadow_rs_do (ilast_rep_sync_id,imax_rep_sync_id);
--Update log
Update Rep_de_proc_log set rid=ilast_rep_sync_id,eid=imax_rep_sync_id where tbl= ' rep_shadow_rs ';
End If;

--run without exception, commit transaction
Commit
--Set return value of 1
Set rtn=1;
End
|
delimiter;
drop procedure if exists pro_rep_shadow_rs_do;
Delimiter |
---------------------------------
--process data within a specified number range
--You need to enter 2 parameters
--LAST_REP_SYNC_ID is the minimum value of the number
--MAX_REP_SYNC_ID is the maximum value of the number
--No return value
---------------------------------
CREATE PROCEDURE Pro_rep_shadow_rs_do (last_rep_sync_id int, max_rep_sync_id int)
Begin
DECLARE irep_operationtype varchar (1);
DECLARE irep_status varchar (1);
declare irep_sync_id int;
declare iId int;
--this is used to handle the last line of cursor arrival
declare stop int default 0;
--Declaring a cursor
Declare cur cursor FOR select id,rep_operationtype,irep_status,rep_sync_id from Rep_shadow_rs where rep_sync_id between L ast_rep_sync_id and max_rep_sync_id;
--declares exception handling for cursors, setting a stop tag
Declare CONTINUE HANDLER for SQLSTATE ' 02000 ' SET stop=1;

--Open cursor
Open cur;

--read a row of data to a variable
Fetch cur into iid,irep_operationtype,irep_status,irep_sync_id;
--This is to determine whether the cursor has reached the final
While stop <> 1 do
--All kinds of judgments
If irep_operationtype= ' I ' then
Insert into rs0811 (ID,FNBM) Select ID,FNBM from Rep_shadow_rs where rep_sync_id=irep_sync_id;
ElseIf irep_operationtype= ' U ' then
Begin
If irep_status= ' A ' then
Insert into rs0811 (ID,FNBM) Select ID,FNBM from Rep_shadow_rs where rep_sync_id=irep_sync_id;
ElseIf irep_status= ' B ' then
Delete from rs0811 where id=iid;
End If;
End
ElseIf irep_operationtype= ' D ' then
Delete from rs0811 where id=iid;
End If;

--Read the next line of data
Fetch cur into iid,irep_operationtype,irep_status,irep_sync_id;
End while; --End of Loop
Close cur; --Close cursor
End

Use TestProcedure;

Delimiter//

CREATE PROCEDURE Simpleproce1 (out par1 int)
Begin
Select COUNT (*) into the par1 from Proce;
End
//

delimiter;
Call Simpleproce1 (@a);

Select @a;

#<2>, each time only a single row can be retrieved select Id,name into Par1,par2 from Proce LIMIT 1; LIMIT 1;

Use TestProcedure;

Delimiter//

DROP procedure IF EXISTS Simpleproce2

CREATE PROCEDURE Simpleproce2 (out par1 int,out par2 char (30))
Begin
Select Id,name into Par1,par2 from Proce LIMIT 1;
End
//

delimiter;
Call Simpleproce2 (@a,@b);

Select @a,@b;

# # *********second test,function************
#<3>
Delimiter//

DROP FUNCTION IF EXISTS Hello
//

Create function Hello (s char () returns char (50)
Return concat (' Hello, ', S, '! ');
//

delimiter;
Select Hello (' World ');

Show Create function Testprocedure.hellog

#它返回子程序的特征, such as database, name, type, creator and date of creation and modification
Show function status like ' Hello ' G

#<4>
#注意name不能和字段名相同
Delimiter//
DROP procedure IF EXISTS Test//

CREATE PROCEDURE Test ()
BEGIN
DECLARE name VARCHAR (5) DEFAULT ' Bob ';
DECLARE newname VARCHAR (5);
DECLARE XID INT;

SELECT Name,id into Newname,xid
From proce WHERE name = name;
SELECT newname;
End;
//

Call Test1 ()//

#***
Delimiter//
DROP procedure IF EXISTS test2//

CREATE PROCEDURE test2 ()
BEGIN

DECLARE newname VARCHAR (5);
DECLARE XID INT;

SELECT Name,id into Newname,xid
from Proce limit 1;
SELECT Newname,xid;
End;
//

Call Test2 ()//

#<5>
Use TestProcedure;
CREATE PROCEDURE p1 () SELECT * from Proce;

Call P1 ();

#<6> Note that the handler here is set SQLState value, SQLWarning is shorthand for all SQLSTATE code that starts with 01
#NOT found is shorthand for all SQLSTATE codes that start with 02
Shorthand for the SQLSTATE code captured by the #SQLEXCEPTION是对所有没有被SQLWARNING或NOT found
#DECLARE CONTINUE Handler declaration CONTINUE exception handling
#事实上这里的23000SQLSTATE是更常用的, a FOREIGN KEY constraint error or a PRIMARY KEY constraint error is invoked.
#当没有发生该23000异常时, the value of the select @x2 will be null, not 1,
#并且后面的第2个语句执行时将会报主键约束错误, at this time @x2=1,@x=4, although the 2nd sentence has an exception, but the following statement continues to execute
#保存到数据的数据是3, Test3 and 5,TEST5.

Use TestProcedure;
Delimiter//
DROP procedure IF EXISTS Handlerdemo
//

CREATE PROCEDURE Handlerdemo ()
Begin
Declare continue handler for SQLState ' 23000 ' Set @x2 = 1;
Set @x=1;
INSERT into PROCE values (3, ' test3 ');
Set @x=2;
INSERT into PROCE values (3, ' test4 ');
Set @x=3;
INSERT into PROCE values (5, ' test5 ');
Set @x=4;
End
//

Call Handlerdemo ()//

Select @x//
Select @x2//

# # ************ Cursor ****************
The #<7> cursor must be declared before declaring the handler, and the variables and conditions must be declared before the cursor or handler is declared
#在这里先声明变量a, B,C, after declaring cursor
CREATE PROCEDURE Curdemo ()
Begin
declare done int default 0;
Declare a char (16);
declare b,c int;
Declare CUR1 cursor FOR select id,name from Proce;
Declare CUR2 cursor for select ID from Proce2;
Declare continue handler for SQLState ' 02000 ' set done=1;

Open cur1;
Open CUR2;

Repeat
Fetch CUR1 into b,a;
Fetch CUR2 into C;
If not done then
If B<c Then
INSERT into PROCE3 values (b,a);
Else
INSERT into PROCE3 values (c,a);
End If;
End If;
Until do end repeat;

Close Cur1;
Close CUR2;
End

# # **************** Case *******************
#<8>when ... then; case;
Delimiter//
DROP procedure IF EXISTS P13
//
CREATE PROCEDURE P13 (in par1 int)
Begin
declare var1 int;
Set var1=par1+1;

Case VAR1
When 0 then inserts into casetest values (17);
When 1 then inserts into casetest values (18);
Else insert into casetest values (19);
End case;
End
//

Call P13 (-1)//
Call P13 (0)//
Call P13 (1)//
Call P13 (NULL)//

# # **************** while ****************
#<9>while.. end While; Set v=0 is required to prevent null errors
Delimiter//
DROP procedure IF EXISTS P14
//

CREATE PROCEDURE P14 ()
Begin
declare v int;
Set v=0;
While v < 5 do
INSERT into casetest values (v);
Set v=v+1;
End while;
end;//

Call P14 ()//

# # ***************** Repeat *****************
#<10>repeat until ... end repeat; is a post check (until v>=5), while the while is a pre-perform check (while v<5)
Delimiter//
DROP procedure IF EXISTS P15
//

CREATE PROCEDURE P15 ()
Begin
declare v int;
Set v=0;
Repeat
INSERT into casetest values (v);
Set v=v+1;
Until v >=5

End repeat;

End
//

Call P15 ()//

# # ***************** Loops *****************
#<11> loops and while do not require initial conditions, and do not require an end condition like repeat
# Loop_label:loop
#      ...
# If.. Then
# Leave Loop_label
# End If
# End Loop

Delimiter//
DROP procedure IF EXISTS p16
//

CREATE PROCEDURE p16 ()
Begin
declare v int;
Set v=0;
Loop_label:loop
INSERT into casetest values (v);
Set v=v+1;
If v >=5 Then
Leave Loop_label;
End If;
End Loop;
end;//

Call P16 ()//

# # ***************** Labels *****************
# <12>labels Marking; Note that there is no semicolon behind the until 0=0 here ";"
Delimiter//
DROP procedure IF EXISTS p17//

CREATE PROCEDURE P17 ()
Label_1:begin

Label_2:while 0=1 do leave label_2; End while;

Label_3:repeat leave label_3;until 0=0 end repeat;

Label_4:loop leave Label_4; End Loop;

end;//

Call P17 ()//

#<13>labels label Terminator;
Delimiter//
DROP procedure IF EXISTS p18//

CREATE PROCEDURE P18 ()
Label_1:begin

Label_2:while 0=1 do leave label_2; End While label_2;

Label_3:repeat leave label_3;until 0=0 end repeat label_3;

Label_4:loop leave Label_4; End Loop Label_4;

End label_1;//

Call P18 ()//

#<14>leave and labels jump and label; leave to make a program jump out of complex statements
Delimiter//
DROP procedure IF EXISTS p19//

Create PROCEDURE p19 (par char)

Label_1:begin
Label_2:begin
Label_3:begin

If par is not NULL then
If par= ' A ' then leave label_1;
Else
Begin
If par= ' B ' then
Leave label_2;
Else
Leave Label_3;
End If;
End
End If;
End If;

End Label_3;
End label_2;
End Label_1;

//

Call P19 (' a ')//

#<15>iterate iteration, must be leave;iterate meaning is to restart the compound statement, equivalent to continue
#该结果中3将不被保存到数据库表中
Delimiter//
DROP procedure IF EXISTS p20//

CREATE PROCEDURE P20 ()
Begin
declare v int;
Set v=0;
Loop_label:loop

If V=3 Then
Set v=v+1;
Iterate Loop_label;
End If;
INSERT into casetest values (v);
Set v=v+1;

If V>=5 Then
Leave Loop_label;
End If;

End Loop Loop_label;
end;//

Call P20 ()//

#<16>grand Combination Large Combination

Delimiter//
DROP procedure IF EXISTS p21//

CREATE PROCEDURE P21 (in par1 int,out par2 int)
Language SQL deterministic SQL security Invoker
Begin
declare v int;

Label Goto_label;

Start_label:loop
If V=v Then
Leave Start_label;
Else
Iterate Start_label;
End If;
End Loop Start_label;

Repeat
While 1=0 doing begin end;
End while;
Until V=v
End repeat;

Goto Goto_label;

End
//

Call P21 ()//

# # **************** Trigger ***************************
#<17>
Use TestProcedure;

CREATE TABLE trig1 (a1 int);
CREATE TABLE trig2 (a2 int);
CREATE TABLE TRIG3 (A3 int not null auto_increment PRIMARY KEY);

CREATE TABLE TRIG4 (
A4 INT not NULL auto_increment PRIMARY KEY,
B4 INT DEFAULT 0
);

Insert into TRIG3 (A3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
Insert into TRIG4 (A4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Delimiter//
DROP Trigger trigtest//

Create trigger trigtest before insert on TRIG1
For each row begin
INSERT into TRIG2 set a2=new.a1;
Delete from trig3 where a3=new.a1;
Update trig4 set b4=b4+1 where a4=new.a1;
End
//

delimiter;

INSERT into Trig1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);