MySQL stored procedures

1. Introduction to Stored Procedures

The SQL statements that we commonly use to manipulate database languages need to be compiled and executed at the time of execution, while stored procedures (Stored Procedure) are sets of SQL statements that are compiled for specific functions and stored in the database after compilation. The user invokes execution by specifying the name of the stored procedure and the given parameter (if the stored procedure has parameters).

A stored procedure is a programmable function that is created and saved in the database. It can consist 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 encapsulate specific functionality. Stored procedures in a database can be seen as simulations of object-oriented methods in programming. It allows control over how data is accessed.

Stored procedures often 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 a strong flexibility to complete complex judgments and more complex operations.

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

(3). The stored procedure can achieve a faster execution speed. If an operation contains a large number of Transaction-sql code or is executed more than once, the stored procedure is much faster than the batch execution. Because the stored procedure is precompiled. When you run a stored procedure for the first time, the optimizer optimizes it for analysis and gives the execution plan that is ultimately stored in the system table. The batch TRANSACTION-SQL statements are compiled and optimized each time they are run, relatively slowly.

(4). Stored procedures can reduce network traffic. For operations on the same database object, such as queries, modifications, if the TRANSACTION-SQL statement involved in this operation is an organized stored procedure, when the stored procedure is called on the client computer, only the calling statement is transmitted on the network, which greatly increases network traffic and reduces network load.

(5). Stored procedures can be used as a security mechanism to make full use of them. The system administrator restricts the access to the corresponding data by executing the permission of a stored procedure, avoids the unauthorized user's access to the data, and ensures 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 great compromise for applications. Fortunately, MySQL 5.0 has finally started to support stored procedures, which can greatly improve the processing speed of the database, but also improve the flexibility of database programming.

3. Creation of MySQL stored procedures

(1). Format

Format created by the MySQL stored procedure: Create PROCEDURE procedure name ([process parameters [,...]])
[Features ...] Process Body

Here is a first example:

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

Note:

(1) Note here is delimiter//and delimiter; two sentences, delimiter is the meaning of the separator, because MySQL defaults to ";" As a delimiter, if we do not declare the separator, then the compiler will treat the stored procedure as an SQL statement, the process of compiling the stored procedure will be error-free, so you have to use the delimiter keyword in advance to declare the current segment delimiter, so that MySQL will ";" 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 as required, here is an output parameter s, type int, if there are multiple parameters with "," split open.

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

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

(2). Declaring a separator

In fact, about the declaration of the separator, the above note has been written very clearly, do not need to say, just a little bit of attention is: If you are using MySQL Administrator management tools, you can create directly, no longer need to declare.

(3). Parameters

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 called, and the value of the parameter modified in the stored procedure cannot be returned as 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, and can be changed and returned

Ⅰ. In Parameter examples

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 Result:


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 Examples

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 Result:


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 Examples

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 Result:

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). variables

Ⅰ. Variable definitions

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

Where datatype is the data type of 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 being padded ';

Ⅱ. Assigning values to variables

SET variable name = expression Value [, Variable_name = ' expression ...]

Ⅲ. User Variables

Ⅰ. Using user variables in MySQL client

MySQL > SELECT ' Hello world ' into @x;
mysql > SELECT @x;
+-------------+
| @x |
+-------------+
| Hello World |
+-------------+
MySQL > SET @y= ' Goodbye cruel World ';
mysql > SELECT @y;
+---------------------+
| @y |
+---------------------+
| Goodbye Cruel World |
+---------------------+

mysql > SET @z=1+2+3;
mysql > SELECT @z;
+------+
| @z |
+------+
| 6 |
+------+
Ⅱ. Using 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 scope user variables between stored procedures

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

Attention:

① user variable names usually begin with @
② misuse of user variables can cause the program 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: Generally used for multi-line annotations
For example:

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

4. Calls to the MySQL stored procedure

With call and your procedure name and a parenthesis, enclose the parameters in parentheses as necessary, parameters include input parameters, output parameters, input and output parameters. Refer to the example above for specific invocation methods.

5. Query for MySQL stored procedures

We like to know a database below those tables, we generally use show tables; So are we going to look at the stored procedures under a database, or can we use them? The answer is that we can look at the stored procedure under a database, but it's a one-way 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 should we do? Can you also use the describe table name as an action table to view it?

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

SHOW CREATE PROCEDURE database. The name of the stored procedure;

You can view the details of the current stored procedure.

Modification of 6.MySQL stored procedures

ALTER PROCEDURE

Change the pre-specified stored procedure established with create PROCEDURE, which does not affect the associated stored procedure or storage functionality.

7. Removal of MySQL stored procedures

Deleting a stored procedure is as simple as 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 enjoy higher precedence within their scope when executed to end. variable, the internal variable disappears, and the variable is no longer visible at this time outside its scope, and should be stored
The declared variable cannot be found outside the procedure, but you can either pass out the parameter or assign its value
To the session variable to save 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 statements

Ⅰ. If-then-else statements

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). Loop statements

Ⅰ. While End While:

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE proc4 ()
Begin
-Declare var int;
Set var=0;
-And while-var<6 do
INSERT into T values (VAR);
Set var=var+1;
and end while;
-End;
//
MySQL > DELIMITER;

Ⅱ. Repeat End repeat:

It checks the results after performing the operation, 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:

Loop loops do not require initial conditions, which are similar to while loops and do not require an end condition like repeat loops, and the meaning of the leave 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 Marking:

The label can be used before the begin repeat while or loop statement, and the statement designator can only be used before a valid statement. You can jump out of the loop so that the run instruction reaches the final step of the compound statement.

(4). Iterate Iteration

Ⅰ. Iterate:

To start a compound statement from scratch by referencing the label of a compound statement

MySQL > DELIMITER//
MySQL > CREATE PROCEDURE proc10 ()
Begin
-Declare v int;
Set v=0;
Loop_lable:loop
If V=3 Then
Set v=v+1;
Iterate loop_lable;
-End If;
INSERT into T values (v);
Set v=v+1;
If V>=5 Then
Leave loop_lable;
-End If;
-End loop;
-End;
//
MySQL > DELIMITER;

Basic functions for 9.MySQL stored procedures

(1). String class

CHARSET (str)//return string character set
CONCAT (string2 [,...])//connection string
INSTR (string, substring)//returns the position where substring first appeared in string, no return 0
LCASE (string2)//Convert to lowercase
Left (string2, length)//The length of the string from string2
Length (String)//string
Load_file (file_name)//read content from File
LOCATE (substring, string [, Start_position]) same as InStr, but can specify start position
Lpad (string2, length, pad)//repeat pad to start with string until string length
LTRIM (string2)//Remove front-end spaces
REPEAT (string2, count)//Repeat Count times
Replace (str, SEARCH_STR, REPLACE_STR)//replaces SEARCH_STR with REPLACE_STR in str
Rpad (string2, length, pad)//after STR with pad supplement until length
RTRIM (string2)//Remove back-end spaces
STRCMP (string1, string2)//character comparison two string size,
SUBSTRING (str, position [, length])//starting with the position of STR, taking a length character,
Note: When working with 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 ',);
+-----------------------+
| SUBSTRING (' ABCD ', up) |
+-----------------------+
| AB |
+-----------------------+
1 row in Set (0.02 sec)
TRIM ([[[Both| Leading| TRAILING] [padding] from]string2)//remove specified characters from the specified position
UCASE (string2)//Convert to uppercase
Right (String2,length)//Take string2 last length character
Space (count)//Generate Count of spaces

(2). Math class

ABS (NUMBER2)//Absolute value
BIN (Decimal_number)//decimal into binary
CEILING (NUMBER2)//Up rounding
CONV (number2,from_base,to_base)//Binary conversion
Floor (NUMBER2)//Down rounding
FORMAT (number,decimal_places)//number of decimal digits reserved
Hex (Decimalnumber)//Turn 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) to return 19
LEAST (number, number2 [,..])//Find minimum
MOD (numerator, denominator)//redundancy
Power (number, Power)//Index
RAND ([seed])//random number
ROUND (number [, decimals])//rounded, decimals to decimal place]

Note: Return types are not all integers, such as:
(1) The default becomes an integer 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 digits, return the 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 timestamp
Date (datetime)//Return datetime part
Date_add (Date2, INTERVAL d_value d_type)//Add date or time to Date2
Date_format (datetime, Formatcodes)//Use formatcodes format to display datetime
Date_sub (Date2, INTERVAL d_value d_type)//Subtract one time from 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)//Extract the specified part of the date
Makedate (year, day)//gives the first days of the years and years, generating a date string
Maketime (hour, minute, second)//Generate time string
MONTHNAME (date)//English month name
Now ()//Current time
Sec_to_time (seconds)//seconds turn into time
Str_to_date (string, format)//string turns into time, displayed in format
Timediff (datetime1, datetime2)//Two time difference
Time_to_sec (time)//times to seconds]
WEEK (Date_time [, Start_of_week])//weeks
Year (DateTime)//Years
DayOfMonth (DateTime)/day of the month
HOUR (DateTime)//hour

MySQL stored procedures