Functions and basic usage of MySQL stored procedures

Basic usage

The mysql stored procedure is gradually adding new functionality from the MySQL 5.0 start. Stored procedures are also more advantageous than disadvantages in practical applications. But the main thing is execution efficiency and SQL code encapsulation. In particular, the SQL Code encapsulation function, if there is no stored procedure.

When an external program accesses a database (for example, PHP), you organize a lot of SQL statements.

In particular, the complexity of business logic, a lot of SQL and conditions mixed in the PHP code, makes people shudder. Now that you have a MySQL stored procedure, business logic can encapsulate stored procedures so that it is not only easy to maintain, but also efficient to perform.

First, MySQL Create stored procedures

"Pr_add" is a simple MySQL stored procedure, this MySQL stored procedure has two int type input parameter "a", "B", returns these two parameter's and.

The code is as follows	Copy Code
drop procedure if exists pr_add;

Calculate the sum of two numbers

code is as follows	copy code
CREATE PROCEDURE Pr_add ( a int, b int ) Begin declare c int; If A is null then Set a = 0; End If; If B is null then Set B = 0; End If; Set C = a + B; Select C as Sum; /* return C; cannot be used in a MySQL stored procedure. Return can only appear in the function. */ End

Second, call the MySQL stored procedure

Call Pr_add (10, 20);

Execute the MySQL stored procedure, the stored procedure parameter is the MySQL user variable.

The code is as follows	Copy Code
Set @a = 10; Set @b = 20; Call Pr_add (@a, @b);

The characteristics of MySQL stored procedure

The simple syntax for creating a MySQL stored procedure is:

The code is as follows	Copy Code
Create procedure stored procedure name () ( [In|out|inout] Parameter datatype ) Begin MySQL statement; End

MySQL stored procedure Parameters If you do not explicitly specify ' in ', ' Out ', ' inout ', the default is ' in '. In practice, we do not explicitly specify the parameters that are "in".

1. The "()" after the MySQL stored procedure name is required, even if there is no parameter, "()"

2. The MySQL stored procedure parameter, cannot add "@" before the parameter name, for example: "@a int". The following create stored procedure syntax is incorrect in MySQL (correct in SQL Server). MySQL stored procedure variables, do not need to add "@" before the variable name, although the MySQL client user variable to add a "@".

The code is as follows	Copy Code
CREATE PROCEDURE Pr_add ( @a int,--Error b INT--Correct )

3. The parameters of the MySQL stored procedure cannot specify a default value.

4. The MySQL stored procedure does not need to add "as" in front of the procedure body. SQL Server stored procedures must be added with the "as" keyword.

The code is as follows	Copy Code
CREATE PROCEDURE Pr_add ( a int, b int ) As--error, MySQL does not need "as" Begin MySQL statement ...; End

5. If the MySQL stored procedure contains more than one MySQL statement, you need the begin end keyword.

The code is as follows	Copy Code
CREATE PROCEDURE Pr_add ( a int, b int ) Begin MySQL statement 1 ...; MySQL statement 2 ...; End

6. The end of each statement in the MySQL stored procedure is preceded by a semicolon ";"

...

The code is as follows	Copy Code
declare c int; If A is null then Set a = 0; End If; ... End

7. The annotations in the MySQL stored procedure.

The code is as follows	Copy Code
/* This is a Multi-line MySQL annotation. */ declare c int; --This is a single MySQL note (note--at least one space is needed) If A is null then # This is also a single line MySQL note Set a = 0; End If; ... End

8. You cannot use the "return" keyword in a MySQL stored procedure.

The code is as follows	Copy Code
Set C = a + B; Select C as Sum; /* return C; --cannot be used in a MySQL stored procedure. Return can only appear in the function. */ End

9. When invoking a MySQL stored procedure, you need to add "()" after the process name, even if there is no parameter, "()"

The code is as follows	Copy Code
Call Pr_no_param ();

10. Because the MySQL stored procedure parameter has no default value, you cannot omit the parameter when invoking the MySQL stored procedure. can be substituted with null.

MySQL stored procedure basic function

A. String class

CHARSET (str)//return string character set
CONCAT (string2  [,...])//connection string
INSTR (string, substring)//return substring for the first time in string , no presence returned 0
LCASE (string2)//Convert lowercase
Left (string2, length)//to a length character
Length (string)//st Ring length
Load_file (file_name)/read content from File
LOCATE (substring, string  [, Start_position]) is the same as InStr, but you can specify the start position
Lpad (string2, length, pad)///repeat pad at beginning of string until length
LTRIM (string2)//Remove front space
REPEAT (string2, Count)/ /Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)////replacement with REPLACE_STR in str search_str
Rpad (string2, length, pad)//In After STR is supplemented with pad until length is
RTRIM (string2)//Remove back-end space
STRCMP (string1, string2)//character comparison two string sizes,
SUBSTRING (str, POS ition  [, Length])//starts with the position of STR, takes the length character,
Note: When processing a string in MySQL, the default first character is subscript 1, that is, the parameter position must be greater than or equal to 1

code is as follows

copy code

mysql> Select substring (' ABCD ', 0,2); +-----------------------+ | SUBSTRING (' ABCD ', 0,2) | +-----------------------+ | | +-----------------------+ 1 row in Set (0.00 sec) mysql> Select substring (' ABCD ', 1,2); +-----------------------+ | SUBSTRING (' ABCD ', 1,2) | +-----------------------+ | AB | +-----------------------+ 1 row in Set (0.02 sec) TRIM ([[both| Leading| Trailing] [padding] from]string2)//Remove the specified character from the specified position UCASE (string2)//Convert to uppercase Right (String2,length)//Fetch string2 last length character Space (count)//Generate Count spaces

Two. Mathematics class

ABS (NUMBER2)//Absolute value
BIN (Decimal_number)//decimal Turn binary
CEILING (number2)//Upward rounding
CONV (Number2,from_base,to_base)//in-process conversion
FLOOR (NUMBER2)//Down rounding
FORMAT (number,decimal_places)//Reserved Decimal places
HEX (Decimalnumber)//Hex
Note: HEX () can pass in a string, then return its ASC-11 code, such as HEX (' DEF ') return 4142143
You can also pass in a decimal integer, returning its hexadecimal encoding, such as Hex (25) Returning 19
Least (number, number2 [,..])//Minimum value
MOD (numerator, denominator)//remainder
Power (number, Power)//Index
RAND ([seed])//random number
ROUND (number [, decimals])//rounded, decimals to decimal places]

Note: The return type is not all integers, such as:
(1) Change the default to a cosmetic value

The code is as follows	Copy Code
Mysql> Select round (1.23); +-------------+ | Round (1.23) | +-------------+ | 1 | +-------------+ 1 row in Set (0.00 sec) Mysql> Select round (1.56); +-------------+ | Round (1.56) | +-------------+ | 2 | +-------------+ 1 row in Set (0.00 sec)

(2) You can set the number of decimal places, return floating-point data

The code is as follows	Copy Code
Mysql> Select round (1.567,2); +----------------+ | Round (1.567,2) | +----------------+ | 1.57 | +----------------+ 1 row in Set (0.00 sec) SIGN (NUMBER2)//return symbol, plus or minus 0 SQRT (NUMBER2)//Open Square

Three. Date Time class

Addtime (Date2, Time_interval)//Add Time_interval to Date2
Convert_tz (DateTime2, Fromtz, Totz)//Convert time zone
Current_date ()//Current date
Current_time ()//Current time
Current_timestamp ()//Current time stamp
Date (datetime)//Return datetime part
Date_add (Date2, INTERVAL d_value d_type)//Add date or time in Date2
Date_format (datetime, Formatcodes)//display datetime using Formatcodes format
Date_sub (Date2, INTERVAL d_value d_type)//Minus one time on date2
DATEDIFF (Date1, Date2)//Two date difference
Day (date)//days of return date
Dayname (date)/English week
DayOfWeek (date)//week (1-7), 1 for Sunday
DayOfYear (date)//day of the year
EXTRACT (interval_name from date)//To extract the specified portion of a date from
Makedate (year, day)//gives the first days of years and years, generating a date string
Maketime (hour, minute, second)//Generate time series
MonthName (date)//English month name
Now ()//Current time
Sec_to_time (seconds)//sec turns into time
Str_to_date (string, format)//String turn to time, displayed in format format
Timediff (datetime1, datetime2)//two time lag
Time_to_sec (time)//times in seconds]
WEEK (Date_time [, Start_of_week])//first few weeks
Year (datetime)/Years
DayOfMonth (DateTime)//day of the month
HOUR (DateTime)//hour
Last date of the month of Last_day (date)//date
Microsecond (datetime)/microsecond
MONTH (DateTime)//month
MINUTE (datetime)/min

MySQL Stored procedure Summary

Core tip: I. Create a stored procedure 1. Basic syntax:

The code is as follows	Copy Code
CREATE PROCEDURE Sp_name () Begin

.........
End2. Parameter pass two. Invoke stored procedure 1. Basic syntax: Call Sp_name ()
Note: The stored procedure name must be followed by parentheses, even if the

I. Creating a stored Procedure

1. Basic grammar:

The code is as follows	Copy Code
CREATE PROCEDURE Sp_name () Begin ......... End

2. Parameter transfer
Two. Call the stored procedure

1. Basic syntax: Call Sp_name ()
Note: The stored procedure name must be followed by parentheses, even if the stored procedure has no parameter passing
Three. Delete stored procedures

1. Basic grammar:

The code is as follows	Copy Code
drop procedure sp_name//

2. Matters needing attention
(1) cannot delete another stored procedure in one stored procedure, can only call another stored procedure
Four. blocks, conditions, loops

1. Block definition, commonly used
Begin
......
End
You can also alias a block, such as:
Lable:begin
...........
End lable;
You can use leave lable to jump out of blocks and execute code after blocks
2. Conditional statement

The code is as follows	Copy Code
If condition Then Statement Else Statement End If;

3. Circular statements
(1). While loop

The code is as follows	Copy Code
[Label:] While expression do Statements End while [Label];

(2). Loop loop

[Label:]

The code is as follows	Copy Code
LOOP Statements End loop [Label];(3). Repeat until cycle [Label:] REPEAT Statements UNTIL expression End REPEAT [Label];

Five. Other common commands

1.show Procedure Status
Displays basic information about stored procedures stored in the database, including the owning database, stored procedure name, creation time, and so on
2.show CREATE PROCEDURE Sp_name
Show details of a stored procedure