MySQL Stored procedure Summary detailed

1. Introduction to Stored Procedures

Our common Operations database language SQL statements need to be compiled and executed at execution time, and the stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in a database in order to complete a specific function. The user invokes execution by specifying the name of the stored procedure and given the parameter, if the stored procedure has parameters.

A stored procedure is a programmable function that is created and saved in the database. It can be composed of SQL statements and some special control structures. Stored procedures are useful when you want to perform the same functions on different applications or platforms, or when you encapsulate specific functionality. Stored procedures in a database can be seen as a simulation of object-oriented methods in programming. It allows you to control how data is accessed.

Stored procedures usually have the following advantages:

(1). Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with strong flexibility to complete complex judgments and complex operations.

(2). Stored procedures allow standard components to be programmed. After a stored procedure is created, it can be called multiple times in a program without having to rewrite the SQL statement of the stored procedure. and database professionals can modify stored procedures at any time, without affecting the application's source code.

(3). The stored procedure can achieve faster execution speed. If an action contains a large amount of transaction-sql code or is executed several times, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. The query, which is parsed and optimized by the optimizer when a stored procedure is first run, and gives an execution plan that is ultimately stored in the system table. The Transaction-sql statements for batches are compiled and optimized every time they run, and the speed is relatively slow.

(4). Stored procedures can reduce network traffic. Actions for the same database object, such as queries, modification), if the Transaction-sql statement involved in this operation is organized into a process stored procedure, then when the stored procedure is invoked on the client computer, only the calling statement is transmitted in the network, which greatly increases network traffic and reduces network load.

(5). Stored procedures can be fully exploited as a security mechanism. The system administrator restricts the access to the corresponding data by executing the permission of a certain stored procedure, avoids the access of the unauthorized users to the data, and guarantees the security of the data.

2. About MySQL stored procedures

Stored procedures are an important feature of database storage, but MySQL does not support stored procedures until 5.0, which makes MySQL a big discount for apps. Fortunately, MySQL 5.0 finally began to support the stored procedures, which can greatly improve the processing speed of the database, but also can improve the flexibility of database programming.

3. mysql Stored procedure creation

(1). Format

MySQL stored procedure created format: Create PROCEDURE procedure name ([procedure parameters [,...]])
[Attributes ...] Process Body

Here's an example:

Mysql> DELIMITER//
Mysql> CREATE PROCEDURE Proc1 (out s int)
-> BEGIN
-> SELECT COUNT (*) into s from user;
-> End
->//
Mysql> DELIMITER;

Note:

(1) Here is the delimiter//and delimiter; two sentences, delimiter is the meaning of the delimiter, because MySQL defaults to ";" As a separator, if we do not declare a delimiter, then the compiler will treat the stored procedure as an SQL statement, the stored procedure will be compiled to the error, so the delimiter keyword to declare the current segment separator, so that MySQL will be ";" As the code in the stored procedure, the code is not executed, and the delimiter is restored after it is exhausted.

(2) The stored procedure may have input, output, input and output parameters according to the need, here is an output parameter s, the type is int, if there are more than one parameter with "," split open.

(3) The beginning and end of the process body is identified using begin and ends.

In this way, one of our MySQL stored procedures completed, is not very easy? It doesn't matter if you don't understand, then we'll explain it in detail.

(2). Declaration Separator

In fact, on the declaration of the separator, the above annotation has been written very clearly, do not need to say, but a little note is: If the administrator of the MySQL administrator tool, you can create, no longer need to declare.

(3). parameter

The parameters of the MySQL stored procedure are used in the definition of stored procedure, there are three kinds of parameter types, in,out,inout, form such as:

CREATE PROCEDURE ([[In | Out | INOUT] Parameter name data class ...]

In input parameter: The value that represents the parameter must be specified when the stored procedure is invoked, and the value that modifies the parameter in the stored procedure cannot be returned, the default value

Out output parameter: This value can be changed inside the stored procedure and can be returned

INOUT input and OUTPUT parameters: Specified at call time, and can be changed and returned

Ⅰ. In parameter example

Create:

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE demo_in_parameter (in p_in int)
-> BEGIN
-> SELECT p_in;
-> SET p_in=2;
-> SELECT p_in;
-> end;
->//
MySQL > DELIMITER;

Execution results:

MySQL > SET @p_in = 1;
MySQL > Call demo_in_parameter (@p_in);
+------+
| p_in |
+------+
| 1 |
+------+

+------+
| p_in |
+------+
| 2 |
+------+

Mysql> SELECT @p_in;
+-------+
| @p_in |
+-------+
| 1 |
+-------+

As you can see, p_in is modified in the stored procedure, but does not affect the value of @p_id

Ⅱ.out Parameter Example

Create:

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE demo_out_parameter (out p_out int)
-> BEGIN
-> SELECT p_out;
-> SET p_out=2;
-> SELECT p_out;
-> end;
->//
MySQL > DELIMITER;

Execution results:

MySQL > SET @p_out = 1;
MySQL > Call sp_demo_out_parameter (@p_out);
+-------+
| P_out |
+-------+
| NULL |
+-------+

+-------+
| P_out |
+-------+
| 2 |
+-------+

Mysql> SELECT @p_out;
+-------+
| P_out |
+-------+
| 2 |
+-------+

Ⅲ. InOut parameter Example

Create:

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE demo_inout_parameter (inout p_inout int)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> end;
->//
MySQL > DELIMITER;

Execution results:

MySQL > SET @p_inout = 1;
MySQL > Call demo_inout_parameter (@p_inout);
+---------+
| P_inout |
+---------+
| 1 |
+---------+

+---------+
| P_inout |
+---------+
| 2 |
+---------+

mysql > SELECT @p_inout;
+----------+
| @p_inout |
+----------+
| 2 |
+----------+

(4). Variable

Ⅰ. Variable definition

DECLARE variable_name [, variable_name ...] datatype [DEFAULT value];

Where datatype is the data type for MySQL, such as: int, float, date, varchar (length)

For example:

DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric Number (8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT ' 1999-12-31 ';
DECLARE l_datetime datetime DEFAULT ' 1999-12-31 23:59:59 ';
DECLARE L_varchar varchar (255) DEFAULT ' This is not to be padded ';

Ⅱ. Variable Assignment

SET variable name = expression Value [, variable_name = expression ...]

Ⅲ. User Variables

?. Using user variables on MySQL clients

mysql > SELECT ' Hello World ' to @x; 
mysql > SELECT @x; 
+-------------+ 
|& nbsp;  @x        | 
+-------------+ 
| Hello World | 
+-------------+ 
mysql > SET @y= ' Goodbye cruel World '; 
MySQL > SELECT @y; 
+---------------------+ 
|     @y               | 
+---------------------+ 
| Goodbye Cruel World | 
+---------------------+ 
 
mysql > SET @z=1+2+3; 
MySQL & Gt SELECT @z; 
+------+ 
| @z   | 
+------+ 
|  6   | 
+------+ 
? Use User variables in stored procedures

MySQL > CREATE PROCEDURE greetworld () SELECT CONCAT (@greeting, ' world ');
MySQL > SET @greeting = ' Hello ';
MySQL > Call Greetworld ();
+----------------------------+
| CONCAT (@greeting, ' World ') |
+----------------------------+
| Hello World |
+----------------------------+

?. Passing global-scoped user variables between stored procedures

mysql> CREATE PROCEDURE p1 () SET @last_procedure = ' P1 ';
Mysql> CREATE PROCEDURE p2 () SELECT CONCAT (' Last PROCEDURE is ', @last_proc);
Mysql> call P1 ();
Mysql> call P2 ();
+-----------------------------------------------+
| CONCAT (' Last procedure is ', @last_proc |
+-----------------------------------------------+
| Last Procedure is P1 |
+-----------------------------------------------+

Attention:

① user variable name usually begins with @

② misuse of user variables can cause programs to be difficult to understand and manage

(5). Notes

MySQL stored procedures can use two styles of annotations

Double-mode bar:--

This style is typically used for single-line annotations

C Style: General use for multiline annotations

For example:

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE proc1--name Stored procedure name
-> (in Parameter1 INTEGER)
-> BEGIN
-> DECLARE variable1 CHAR (10);
-> IF parameter1 = THEN
-> SET variable1 = ' birds ';
-> ELSE
-> SET variable1 = ' beasts ';
-> End IF;
-> INSERT into table1 VALUES (variable1);
-> End
->//
MySQL > DELIMITER;

4. mysql Stored procedure call

With call and your procedure name and a bracket, enclose the parameters according to your needs, including input parameters, output parameters, input and output parameters. The specific invocation method can be described in the above example.

5. mysql Stored procedure query

We like to know that there are tables under a database, we generally use show tables; So we're going to look at the stored procedures under a database, and can we take them? The answer is, we can look at the stored procedure under a database, but it's a way to make it a clock.

We can use

Select name from Mysql.proc where db= ' database name ';

Or

Select Routine_name from information_schema.routines where routine_schema= ' database name ';

Or

Show procedure status where db= ' database name ';

To query.

If we want to know the details of a stored procedure, what do we do? Is it possible to use the describe table name to view the same as the action table?

The answer is: we can look at the details of the stored procedure, but we need another way:

Show CREATE PROCEDURE database. stored procedure name;

You can view the details of the current stored procedure.

6. Modification of MySQL stored procedure

ALTER PROCEDURE

Change a predefined stored procedure established with the Create PROCEDURE, which does not affect the associated stored procedure or storage functionality.

7. mysql Stored procedure deletion

Deleting a stored procedure is simpler, as is deleting a table:

DROP PROCEDURE

Deletes one or more stored procedures from the MySQL table.

8. Control statements for MySQL stored procedures

(1). Variable scope

Internal variables have a higher priority within their scope, when executed to end. variable, the internal variable disappears, and the variable is no longer visible in its scope, and should be stored in the
The declared variable cannot be found outside the process, but you can either pass the out parameter or assign its value to the
The session variable to hold its value.

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE proc3 ()
-> begin
-> DECLARE x1 varchar (5) Default ' outer ';
-> begin
-> DECLARE x1 varchar (5) Default ' inner ';
-> Select X1;
-> end;
-> Select X1;
-> end;
->//
MySQL > DELIMITER;

(2). Conditional statement

Ⅰ. If-then-else statement

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE proc2 (in Parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> if Var=0 Then
-> INSERT INTO T values (17);
-> End If;
-> if Parameter=0 Then
-> update T set s1=s1+1;
-> Else
-> update T set s1=s1+2;
-> End If;
-> end;
->//
MySQL > DELIMITER;

Ⅱ. Case statement:

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE proc3 (in Parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> case Var
-> when 0 Then
-> INSERT INTO T values (17);
-> when 1 Then
-> INSERT INTO t values (18);
-> Else
-> INSERT INTO T values (19);
-> End case;
-> end;
->//
MySQL > DELIMITER;

(3). Circular statements

Ⅰ. While End While:

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE proc4 ()
-> begin
-> declare var int;
-> set var=0;
-> while var<6 todo
-> INSERT into T values (VAR);
-> set var=var+1;
-> End While;
-> end;
->//
MySQL > DELIMITER;

Ⅱ. Repeat End repeat:

It checks the results after the action is performed, while the while is checked before execution.

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE proc5 ()
-> begin
-> declare v int;
-> set v=0;
-> Repeat
-> INSERT into T values (v);
-> set v=v+1;
-> until v>=5
-> end Repeat;
-> end;
->//
MySQL > DELIMITER;

Ⅲ. Loop End Loop:

The loop loop does not require an initial condition, which is similar to a while loop, and does not require an end conditions as the repeat loop does, leave the meaning of the statement is to leave the loop.

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE proc6 ()
-> begin
-> declare v int;
-> set v=0;
-> Loop_lable:loop
-> INSERT into T values (v);
-> set v=v+1;
-> if v >=5 then
-> leave loop_lable;
-> End If;
-> end Loop;
-> end;
->//
MySQL > DELIMITER;

Ⅳ. Lables Label:

The label can be used before the begin repeat while or the loop statement, and the statement label can only be used before the legitimate statement. You can jump out of the loop so that the running instruction reaches the last step of the compound statement.

(4). Iterate Iteration

Ⅰ. Iterate:

To start a compound statement anew by referencing the label of a compound statement

MySQL > DELIMITER// 
mysql > CREATE PROCEDURE proc10 ()  
    -> Begin
    -> declare v int; 
    -> set v=0; 
&N bsp;   -> loop_lable:loop 
    -> if v=3 then  
& nbsp;   -> set v=v+1; 
    -> iterate loop_lable; 
     -> End if; 
    -> inserts into T values (v); 
   ;  -> set v=v+1; 
    -> if v>=5 then
    -&G T Leave loop_lable; 
    -> end if; 
    -> End LOOP;&N Bsp
    -> end; 
    ->// 
mysql > DELIMITER;  

9. The basic functions of the MySQL stored procedure

(1). String class

CHARSET (str)//return string character set
CONCAT (string2 [,...])//connection string
INSTR (string, substring)//Returns the position that substring first appeared in string and does not exist return 0
LCASE (string2)//Convert to lowercase
Left (string2, length)//The length characters from the left-hand side of the string2
Length (string)//string lengths
Load_file (file_name)//read content from File
LOCATE (substring, string [, Start_position]) with InStr, but you can specify the start position
Lpad (string2, length, pad)//repeat pad at beginning of string until length of string
LTRIM (string2)//Remove front space
REPEAT (string2, count)//Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)//replaced with REPLACE_STR in STR search_str
Rpad (string2, length, pad)//after STR supplemented with pad until length
RTRIM (string2)//Remove back-end spaces
STRCMP (string1, string2)//character comparison two string size,
SUBSTRING (str, position [, length])//begins with the position of STR, takes the length character,
Note: When handling strings in MySQL, the default first character subscript is 1, that is, the parameter position must be greater than or equal to 1

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

(2). 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

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

Mysql> Select round (1.567,2);
+----------------+
| Round (1.567,2) |
+----------------+
| 1.57 |
+----------------+
1 row in Set (0.00 sec)
SIGN (number2)//

(3). 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