MySQL Stored Procedure

MySQL 5.0 Stored Procedure learning Summary

1. Create a stored procedure
1. Basic Syntax:
Create procedure sp_name ()
Begin
End
2. parameter transfer

Ii. Call the Stored Procedure
1. Basic Syntax: Call sp_name ()
Note: The stored procedure name must be enclosed in parentheses, even if the stored procedure has no parameters

Iii. delete stored procedures
1. Basic Syntax:
Drop procedure sp_name //
2. Notes
(1) You cannot delete another stored procedure in one stored procedure. You can only call another stored procedure.

4. blocks, conditions, and loops
1. Block definition, commonly used
Begin
End;
You can also create an alias for the block, such:
Lable: Begin
End lable;
You can use leave lable.Code
2. Condition Statement 3. Loop statement: while loop repeat until Loop

5. Other Common commands
1. Show procedure status
Displays basic information about all stored procedures in the database, including the database, stored procedure name, and creation time.
2. Show create procedure sp_name
Displays detailed information about a stored procedure.

MySQL stored procedure basic functions
I. string type
Charset (STR) // returns the string Character Set
Concat (string2 [,...]) // connection string
Instr (string, substring) // returns the position of the first occurrence of the substring in the string. If no position exists, 0 is returned.
Lcase (string2) // converts it to lowercase
Left (string2, length) // take the length from the left of string2
Length (string) // String Length
Load_file (file_name) // read content from the file
Locate (substring, string [, start_position]) is the same as instr, but the start position can be specified.
Lpad (string2, length, pad) // repeat pad to start with string until the string length is length
Ltrim (string2) // remove leading Spaces
Repeat (string2, count) // repeat count times
Replace (STR, search_str, replace_str) // replace search_str with replace_str in Str
Rpad (string2, length, pad) // use pad after STR until the length is length.
Rtrim (string2) // remove backend Spaces
Strcmp (string1, string2) // compare the size of two strings by character,
Substring (STR, position [, length]) // starts from the position of STR and takes length characters,
That is, the position parameter must be greater than or equal to 1.
Trim ([[Both | leading | trailing] [Padding] From] string2) // remove the specified character from the specified position
Ucase (string2) // converts to uppercase
Right (string2, length) // gets the last length character of string2
Space (count) // generate count Spaces

Ii. Mathematics
ABS (number2) // absolute value
Bin (decimal_number) // convert decimal to binary
Ceiling (number2) // rounded up
Conv (number2, from_base, to_base) // hexadecimal conversion
Floor (number2) // round down
Format (number, decimal_places) // number of reserved decimal places
Hex (decimalnumber) // convert to hexadecimal
Note: Hex () can input a string, returns its ASC-11 code, such as hex ('def ') returns 4142143
You can also input a decimal integer to return its hexadecimal encoding. For example, Hex (25) returns 19.
Least (number, number2 [,...]) // calculates the minimum value.
MoD (numerator, denominator) // evaluate the remainder
Power (number, power) // Exponent
Rand ([seed]) // Random Number
Round (number [, decimals]) // rounding, decimals is the number of decimal places]
Iii. Date and Time
Addtime (date2, time_interval) // Add time_interval to date2
Convert_tz (datetime2, fromtz, totz) // convert the time zone
Current_date () // current date
Current_time () // current time
Current_timestamp () // current Timestamp
Date (datetime) // return the date part of datetime
Date_add (date2, interval d_value d_type) // Add a date or time in date2
Date_format (datetime, formatcodes) // display datetime in formatcodes format
Date_sub (date2, interval d_value d_type) // subtract a time from date2
Datediff (date1, date2) // two date differences
Day (date) // returns the day of the date
Dayname (date) // english week
Dayofweek (date) // Week (1-7), 1 is Sunday
Dayofyear (date) // The day of the year
Extract (interval_name from date) // extract the specified part of the date from date
Makedate (year, day) // specifies the day of the year and year to generate a date string.
Maketime (hour, minute, second) // generate a time string
Monthname (date) // name of the English month
Now () // current time
Sec_to_time (seconds) // converts seconds to time
Str_to_date (string, format) // convert string to time, which is displayed in format
Timediff (datetime1, datetime2) // two time difference
Time_to_sec (time) // time to seconds]
Week (date_time [, start_of_week]) // week
Year (datetime) // year
Dayofmonth (datetime) // The day of the month
Hour (datetime) // hour
Last_day (date) // the last date of the Month of date
Microsecond (datetime) // microsecond
Month (datetime) // month
Minute (datetime) // minute

# ********** 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 the childProgramFeatures 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 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;

Http://blog.zol.com.cn/893/article_892123.html