Shulou(Shulou.com)06/01 Report--

Detailed explanation of mysql stored procedure

1. Brief introduction of stored procedure

Our commonly used database language SQL statements need to be compiled and then executed, and a stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in the database in order to complete a specific function. The user calls and executes the stored procedure by specifying the name of the stored procedure and giving parameters (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 execute the same function on different applications or platforms, or to encapsulate specific functions. The stored procedure in the database can be regarded as a simulation of the object-oriented method in programming. It allows you to control how data is accessed.

Stored procedures typically have the following advantages:

(1)。 Stored procedures enhance the functionality and flexibility of the SQL language. The stored procedure can be written with flow control statements, which has strong flexibility and can complete complex judgments and more complex operations.

(2)。 Stored procedures allow standard components to be programming. After a stored procedure is created, it can be called multiple times in the program without having to rewrite the SQL statement of the stored procedure. And database professionals can modify the stored procedure at any time, which has no effect on the application source code.

(3)。 Stored procedures can achieve faster execution speed. If an operation contains a large amount of Transaction-SQL code or is executed multiple times separately, the stored procedure executes much faster than batch processing. Because the stored procedure is precompiled. When a stored procedure is run for the first time, the optimizer analyzes and optimizes it, and gives the execution plan that is eventually stored in the system table. Batch Transaction-SQL statements are compiled and optimized each time they are run, which is relatively slow.

(4)。 Stored procedures can reduce network traffic. For the operation of the same database object (such as query, modification), if the Transaction-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is called on the client computer, only the calling statement is transmitted in the network, thus greatly increasing the network traffic and reducing the network load.

(5)。 Stored procedures can be fully utilized as a security mechanism. By restricting the permissions of a stored procedure, the system administrator can restrict the access to the corresponding data, avoid the access of unauthorized users to the data, and ensure the security of the data.

two。 About the stored procedure of MySQL

Stored procedure is an important function of database storage, but MySQL did not support stored procedure before 5. 0, which greatly reduced the application of MySQL. Fortunately, MySQL 5.0 has finally begun to support stored procedures, which can not only greatly improve the processing speed of the database, but also improve the flexibility of database programming.

3. Creation of MySQL stored procedure

(1)。 Format

Format of MySQL stored procedure creation: CREATE PROCEDURE procedure name ([procedure parameters [,...]])

[features.] Process body

Here's an example:

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE proc1 (OUT s int)

3.-> BEGIN

4.-> SELECT COUNT (*) INTO s FROM user

5.-> END

6.-> / /

7. Mysql > DELIMITER

Note:

(1) what we need to pay attention to here are DELIMITER / / and DELIMITER; in two sentences, DELIMITER means a delimiter, because MySQL defaults to ";". If we do not declare a delimiter, the compiler will treat the stored procedure as a SQL statement, and the compilation process of the stored procedure will report an error, so the current segment delimiter must be declared with the DELIMITER keyword in advance, so that MySQL will set the " "as code in a stored procedure, the code will not be executed and the delimiter will be restored when it is used up.

(2) the stored procedure may have input, output, input and output parameters as needed, here is an output parameter s, the type is int, if there are multiple parameters separated by ",".

(3) the beginning and end of the process body are marked by BEGIN and END.

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

(2)。 Declarative partition

In fact, with regard to the declaration separator, the above comments have been written very clearly, there is no need to say more, just a little note is: if you are using MySQL's Administrator management tool, you can directly create, no longer need to declare.

(3)。 Parameters.

The parameters of MySQL stored procedures are used in the definition of stored procedures. There are three parameter types, IN,OUT,INOUT, in the form of:

CREATE PROCEDURE ([[IN | OUT | INOUT] parameter name data class.])

IN input parameter: indicates that the value of this parameter must be specified when calling the stored procedure. The value that modifies this parameter in the stored procedure cannot be returned and is 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 when called, and can be changed and returned

Ⅰ. Example of IN parameters

Create:

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE demo_in_parameter (IN p_in int)

3.-> BEGIN

4.-> SELECT p_in

5.-> SET p_in=2

6.-> SELECT p_in

7.-> END

8.-> / /

9. Mysql > DELIMITER

Execution result:

1. Mysql > SET @ p_in=1

2. Mysql > CALL demo_in_parameter (@ p_in)

3. +-

4. | p_in |

5. +-

6. | 1 |

7. +-

8.

9. +-

10. | p_in |

11. +-

12. | 2 |

13. +-

14.

15. Mysql > SELECT @ p_in

16. +-

17. | @ p_in |

18. +-

19. | 1 |

20. +

As can be seen above, although p_in is modified in the stored procedure, it does not affect the value of @ p_id

Example of Ⅱ .out parameter

Create:

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE demo_out_parameter (OUT p_out int)

3.-> BEGIN

4.-> SELECT p_out

5.-> SET p_out=2

6.-> SELECT p_out

7.-> END

8.-> / /

9. Mysql > DELIMITER

Execution result:

1. Mysql > SET @ p_out=1

2. Mysql > CALL sp_demo_out_parameter (@ p_out)

3. +-

4. | p_out |

5. +-

6. | NULL |

7. +-

8.

9. +-

10. | p_out |

11. +-

12. | 2 |

13. +-

14.

15. Mysql > SELECT @ p_out

16. +-

17. | p_out |

18. +-

19. | 2 |

20. +

Ⅲ. Example of INOUT parameters

Create:

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE demo_inout_parameter (INOUT p_inout int)

3.-> BEGIN

4.-> SELECT p_inout

5.-> SET p_inout=2

6.-> SELECT p_inout

7.-> END

8.-> / /

9. Mysql > DELIMITER

Execution result:

1. Mysql > SET @ p_inout=1

2. Mysql > CALL demo_inout_parameter (@ p_inout)

3. +-

4. | p_inout |

5. +-

6. | 1 |

7. +-

8.

9. +-

10. | p_inout |

11. +-

12. | 2 |

13. +-

14.

15. Mysql > SELECT @ p_inout

16. +-

17. | @ p_inout |

18. +-

19. | 2 |

20. +

(4)。 Variable

Ⅰ. Variable definition

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:

1. DECLARE l_int int unsigned default 4000000

2. DECLARE l_numeric number (8pm 2) DEFAULT 9.95

3. DECLARE l_date date DEFAULT '1999-12-31'

4. DECLARE l_datetime datetime DEFAULT '1999-12-31 23V 59purl 59'

5. DECLARE l_varchar varchar (255) DEFAULT 'This will not be padded'

Ⅱ. Variable assignment

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

Ⅲ. User variable

ⅰ. Using user variables on the MySQL client

1. Mysql > SELECT 'Hello World' into @ x

2. Mysql > SELECT @ x

3. +-

4. | @ x |

5. +-

6. | Hello World |

7. +-

8. Mysql > SET @ y='Goodbye Cruel World'

9. Mysql > SELECT @ y

10. +-

11. | @ y |

12. +-

13. | Goodbye Cruel World |

14. +-

15.

16. Mysql > SET @ zonal 1, 2, 3

17. Mysql > SELECT @ z

18. +-

19. | @ z |

20. +

21. | | 6 |

twenty-two。 +-+

ⅱ. Using user variables in stored procedures

1. Mysql > CREATE PROCEDURE GreetWorld () SELECT CONCAT (@ greeting,' World')

2. Mysql > SET @ greeting='Hello'

3. Mysql > CALL GreetWorld ()

4. +-

5. | CONCAT (@ greeting,' World') |

6. +-+

7. | Hello World |

8. +-+

ⅲ. Pass global-scoped user variables between stored procedures

1. Mysql > CREATE PROCEDURE p1 () SET @ last_procedure='p1'

2. Mysql > CREATE PROCEDURE p2 () SELECT CONCAT ('Last procedure was', @ last_proc)

3. Mysql > CALL p1 ()

4. Mysql > CALL p2 ()

5. +-+

6. | CONCAT ('Last procedure was', @ last_proc |

7. +-+

8. | Last procedure was p1 |

9. +-+

Note:

① user variable names usually start with @

The abuse of user variables by ② will make the program difficult to understand and manage.

(5)。 Annotation

MySQL stored procedures can use two styles of annotations

Double-mode bars:--

This style is generally used for single-line comments

C style: generally used for multiline comments

For example:

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE proc1-- name stored procedure name

3.-> (IN parameter1 INTEGER)

4.-> BEGIN

5.-> DECLARE variable1 CHAR (10)

6.-> IF parameter1 = 17 THEN

7.-> SET variable1 = 'birds'

8.-> ELSE

9.-> SET variable1 = 'beasts'

10.-> END IF

11.-> INSERT INTO table1 VALUES (variable1)

12.-> END

13.-> / /

14. Mysql > DELIMITER

4. Call of MySQL stored procedure

Use call and your procedure name and a parenthesis to add parameters as needed, including input parameters, output parameters, input and output parameters. For specific invocation methods, see the example above.

5. Query of MySQL stored procedure

We want to know which tables are under a database, and we usually use show tables; to look at them. So if we want to look at the stored procedures under a certain database, can we also use them? The answer is that we can look at the stored procedures under a database, but in an hour-long way.

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'

Make an inquiry.

If we want to know the details of a stored procedure, what should we do? Can you also use the describe table name to view it like an action table?

The answer is: we can view the details of the stored procedure, but we need to use another method:

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 to a pre-specified stored procedure established by CREATE PROCEDURE, which does not affect the relevant stored procedure or storage function.

7. Deletion of MySQL stored procedures

Deleting a stored procedure is as simple as deleting a table:

DROP PROCEDURE

Removes one or more stored procedures from the table in MySQL.

8. Control statements of MySQL stored procedures

(1)。 Variable scope

Internal variables have higher priority within their scope when executed to end. Variable, the internal variable disappears, it is already outside its scope, the variable is no longer visible, and it should be stored in the

This declared variable can no longer be found outside the procedure, but you can use the out parameter or assign its value

Give the session variable to hold its value.

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE proc3 ()

3.-> begin

4.-> declare x1 varchar (5) default 'outer'

5.-> begin

6.-> declare x1 varchar (5) default 'inner'

7.-> select x1

8.-> end

9.-> select x1

10.-> end

11.-> / /

12. Mysql > DELIMITER

(2)。 Conditional statement

Ⅰ. If-then-else statement

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE proc2 (IN parameter int)

3.-> begin

4.-> declare var int

5.-> set var=parameter+1

6.-> if var=0 then

7.-> insert into t values (17)

8.-> end if

9.-> if parameter=0 then

10.-> update t set s1=s1+1

11.-> else

12.-> update t set s1=s1+2

13.-> end if

14.-> end

15.-> / /

16. Mysql > DELIMITER

Ⅱ. Case statement:

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE proc3 (in parameter int)

3.-> begin

4.-> declare var int

5.-> set var=parameter+1

6.-> case var

7.-> when 0 then

8.-> insert into t values (17)

9.-> when 1 then

10.-> insert into t values (18)

11.-> else

12.-> insert into t values (19)

13.-> end case

14.-> end

15.-> / /

16. Mysql > DELIMITER

(3)。 Loop statement

Ⅰ. While end while:

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE proc4 ()

3.-> begin

4.-> declare var int

5.-> set var=0

6.-> while var insert into t values (var)

8.-> set var=var+1

9.-> end while

10.-> end

11.-> / /

12. Mysql > DELIMITER

Ⅱ. Repeat end repeat:

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

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE proc5 ()

3.-> begin

4.-> declare v int

5.-> set vault 0

6.-> repeat

7.-> insert into t values (v)

8.-> set v=v+1

9.-> until v > = 5

10.-> end repeat

11.-> end

12.-> / /

13. Mysql > DELIMITER

Ⅲ. Loop end loop:

The loop loop does not need an initial condition, which is similar to the while loop, and like the repeat loop, there is no end condition, and the meaning of the leave statement is to leave the loop.

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE proc6 ()

3.-> begin

4.-> declare v int

5.-> set vault 0

6.-> LOOP_LABLE:loop

7.-> insert into t values (v)

8.-> set v=v+1

9.-> if v > = 5 then

10.-> leave LOOP_LABLE

11.-> end if

12.-> end loop

13.-> end

14.-> / /

15. Mysql > DELIMITER

Ⅳ. LABLES label:

Labels can be used before begin repeat while or loop statements, and statement labels can only be used before legitimate statements. You can jump out of the loop and make the run instruction reach the last step of the compound statement.

(4)。 ITERATE iteration

Ⅰ. ITERATE:

Start the compound statement from scratch by referencing the label of the compound statement

1. Mysql > DELIMITER / /

2. Mysql > CREATE PROCEDURE proc10 ()

3.-> begin

4.-> declare v int

5.-> set vault 0

6.-> LOOP_LABLE:loop

7.-> if vault 3 then

8.-> set v=v+1

9.-> ITERATE LOOP_LABLE

10.-> end if

11.-> insert into t values (v)

12.-> set v=v+1

13.-> if v > = 5 then

14.-> leave LOOP_LABLE

15.-> end if

16.-> end loop

17.-> end

18.-> / /

19. Mysql > DELIMITER

9. Basic functions of MySQL stored procedures

(1)。 String class

CHARSET (str) / / returns the string character set

CONCAT (string2 [,...]) / / connection string

INSTR (string, substring) / / returns the location where substring first appeared in string, and 0 is not returned.

LCASE (string2) / / convert to lowercase

LEFT (string2, length) / / fetches length characters from the left in string2

LENGTH (string) / / string length

LOAD_FILE (file_name) / / read from a file

LOCATE (substring, string [, start_position]) is the same as INSTR, but the start position can be specified

LPAD (string2, length, pad) / / add pad to the beginning of string repeatedly until the string length is length

LTRIM (string2) / / remove front-end 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) / / add pad after str until the length is length

RTRIM (string2) / / remove backend whitespace

STRCMP (string1, string2) / / compare the size of two strings character by character

SUBSTRING (str, position [, length]) / / start with the position of str and take length characters

Note: when dealing with strings in mysql, the default first character subscript is 1, that is, the parameter position must be greater than or equal to 1.

1. Mysql > select substring ('abcd',0,2)

2. +-+

3. | substring ('abcd',0,2) |

4. +-+

5. |

6. +-+

7. 1 row in set (0.00 sec)

8.

9. Mysql > select substring ('abcd',1,2)

10. +-+

11. | substring ('abcd',1,2) |

12. +-+

13. | ab |

14. +-+

15. 1 row in set (0. 02 sec)

TRIM ([[BOTH | LEADING | TRAILING] [padding] FROM] string2) / / removes the specified characters at the specified location

UCASE (string2) / / convert to uppercase

RIGHT (string2,length) / / fetch the last length character of string2

SPACE (count) / / generate count spaces

(2)。 Mathematics class

ABS (number2) / / absolute value

BIN (decimal_number) / / Decimal to binary

CEILING (number2) / / rounding up

CONV (number2,from_base,to_base) / / binary conversion

FLOOR (number2) / / rounding down

FORMAT (number,decimal_places) / / keep decimal places

HEX (DecimalNumber) / / convert to hexadecimal

Note: if a string can be passed in HEX (), its ASC- 11 code is returned, for example, HEX ('DEF') returns 4142143

You can also pass in a decimal integer and return its hexadecimal code, for example, HEX (25) returns 19

LEAST (number, number2 [,..]) / / find the minimum

MOD (numerator, denominator) / / residual

POWER (number, power) / / find the index

RAND ([seed]) / / Random number

ROUND (number [, decimals]) / / rounded, decimals is decimal]

Note: not all return types are integers, such as:

(1) the default is changed to the shaping value.

1. Mysql > select round (1.23)

2. +-+

3. | round (1.23) |

4. +-+

5. | 1 |

6. +-

7. 1 row in set (0.00 sec)

8.

9. Mysql > select round

10. +-

11. | round (1.56) |

12. +-

13. | 2 |

14. +-

15. 1 row in set (0.00 sec)

(2) you can set the number of decimal places to return floating-point data

1. Mysql > select round (1.567)

2. +-+

3. | round (1.567 Personium 2) |

4. +-+

5. | 1.57 |

6. +-

7. 1 row in set (0.00 sec)

SIGN (number2) / /

(3)。 Date and 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) / / returns the date part of the datetime

DATE_ADD (date2, INTERVAL d_value d_type) / / add a date or time to the 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) / / the day of the return date

DAYNAME (date) / / English week

DAYOFWEEK (date) / / week (1-7), 1 is Sunday

DAYOFYEAR (date) / / Day of the year

EXTRACT (interval_name FROM date) / / extract the specified part of the date from date

MAKEDATE (year, day) / / give the year and the day of the year, and generate a date string

MAKETIME (hour, minute, second) / / generate time string

MONTHNAME (date) / / English month name

NOW () / / current time

SEC_TO_TIME (seconds) / / number of seconds converted to time

STR_TO_DATE (string, format) / / string conversion time, displayed in format format

TIMEDIFF (datetime1, datetime2) / / two time differences

TIME_TO_SEC (time) / / number of seconds in time]

WEEK (date_time [, start_of_week]) / / weeks old

YEAR (datetime) / / year

Day ordinal of DAYOFMONTH (datetime) / / month

HOUR (datetime) / / hour

The last date of the month of LAST_DAY (date) / / date

MICROSECOND (datetime) / / microsecond

MONTH (datetime) / month

MINUTE (datetime) / / returns a symbol, plus or minus or 0

SQRT (number2) / / squared

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.