MySQL Stored Procedure instance

# ********** First test, procedure **********
# <1>
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. Hello/g

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