MySQL Stored Procedure examples, including transactions, parameters, nested calls, cursors, loops, etc.

MySQL Stored Procedure examples, including transactions, parameters, nested calls, cursors, loops, etc.

MySQL Stored Procedure examples, including transactions, parameters, nested calls, cursors, loops, etc.

Drop procedure if exists pro_rep_shadow_rs;
Delimiter |
----------------------------------
-- Rep_shadow_rs
-- Used to process information addition, update, and deletion
-- Only data that has not been updated since the last time is updated each time.
-- Based on Different flag Spaces
-- An output parameter is required,
-- If the return value is 0, the call fails and the transaction is rolled back.
-- If the return value is 1, the call is successful and the transaction is committed.
--
-- 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 be prior to non-declared statements.
Declare iLast_rep_sync_id int default-1;
Declare iMax_rep_sync_id int default-1;
-- If an exception occurs, or automatic processing and rollback, but no longer notifies the caller
-- If you want the application to get an exception, you need to remove the following sentence and all the statements for starting and committing the transaction.
Declare exit handler for sqlexception rollback;
-- Find the last
Select eid into iLast_rep_sync_id from rep_de_proc_log where tbl = 'rep _ shadow_rs ';
-- If not, add a row.
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 Digit
Set iLast_rep_sync_id = iLast_rep_sync_id + 1;
-- Set the default return value to 0: Failed
Set rtn = 0;

-- Start the transaction
Start transaction;
-- Find the maximum number
Select max (rep_sync_id) into 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;

-- No exception occurred while running. Commit the transaction.
Commit;
-- Set the return value to 1.
Set rtn = 1;
End;
|
Delimiter;
Drop procedure if exists pro_rep_shadow_rs_do;
Delimiter |
---------------------------------
-- Process data within the specified number range
-- Two parameters are required.
-- Last_rep_sync_id is the minimum value of the number.
-- Max_rep_sync_id is the maximum 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 process the cursor reaching the last row.
Declare stop int default 0;
-- Declare a cursor
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;
-- Declare the exception handling of the cursor and set a termination flag
Declare continue handler for sqlstate '200' SET stop = 1;

-- Open the 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 end
While stop <> 1 do
-- Various 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 data of the next row
Fetch cur into iId, iRep_operationtype, iRep_status, iRep_Sync_id;
End while; -- end of the loop
Close cur; -- close the cursor
End;

Use testprocedure;

Delimiter //

Create procedure simpleproce1 (out par1 int)
Begin
Select count (*) into par1 from proce;
End
//

Delimiter;
Call simpleproce1 (@ );

Select @;

# <2> only one row can be retrieved at a time. 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 (20) returns char (50)
Return concat ('hello, ', s ,'! ');
//

Delimiter;
Select hello ('World ');

Show create function testprocedure. helloG

# It returns features of subroutines, such as databases, names, types, creators, and creation and modification dates.
Show function status like 'hello' G

# <4>
# Note that the name cannot be the same as the field 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 sets the SQLSTATE value, and SQLWARNING is a shorthand for all SQLSTATE codes starting with 01.
# Not found is a shorthand for all SQLSTATE codes starting with 02
# SQLEXCEPTION is a shorthand for all SQLSTATE Codes NOT captured by SQLWARNING or not found.
# Declare continue handler declares CONTINUE Exception Handling
# In fact, The 23000SQLSTATE is more commonly used. When a foreign key constraint error or a primary key constraint error occurs, it is called.
# If the 23000 exception is not found, the value of select @ x2 will be null, not 1,
# When the following 2nd statements are executed, the primary key constraint error will be reported. At this time, @ x2 = 1, @ x = 4. Although the first sentence has an exception, the subsequent statements continue to be executed.
# The data saved to the data is 3, test3, and 5, test5.

Use testprocedure;
Delimiter //
DROP procedure if exists handlerdemo
//

Create procedure handlerdemo ()
Begin
Declare continue handler for sqlstate '000000' 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 //

****************
# <7> the cursor must be declared before the handler is declared, and variables and conditions must be declared before the cursor or handler is declared
# Declare the variables a, B, and c first, and then declare the 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 '000000' set done = 1;
 
Open cur1;
Open cur2;

Repeat
Fetch cur1 into B,;
Fetch cur2 into c;
If not done then
If B <c then
Insert into proc4values (B, );
Else
Insert into proc4values (c, );
End if;
End if;
Until done end repeat;
 
Close cur1;
Close cur2;
End

# ***************** Case *******************
# <8> when... then; case... end 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 insert into casetest values (17 );
When 1 then insert 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... do... end while; To prevent null errors, set v = 0 is required.
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 the post-execution check (until v> = 5), while is the pre-execution 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> like loop and while, the initial condition is not required, and the end condition is not required as the repeat condition.
# 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 label. Note that until 0 = 0 is followed by a semicolon ";"
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 end character;
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 jumps out and labels; leave causes the program to jump out of complicated 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, which must use leave; iterate means to re-start the compound statement, which is equivalent to continue
#3 in the result will not be saved to the database table
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

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 do begin end;
End while;
Until v = v
End repeat;

Goto goto_label;

End;
//

Call p21-01 ()//

# **************** 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 );
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 );