Shulou(Shulou.com)05/31 Report--

This article mainly introduces the use of MySQL stored procedures, the article is very detailed, has a certain reference value, interested friends must read it!

1. Brief introduction of stored procedure

Our commonly used database language SQL statements need to be compiled and then executed, and a stored procedure (StoredProcedure) 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 reachable function that is created and saved in. 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, MySQL5.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 [,...]]) Procedure

[features.] Process body

Here's an example:

1. > 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)。 Declare delimiter

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 pairing; / * query input parameters * /

5.-> SET pendant 2; / * modify * /

6.-> SELECT pinning; / * View the modified value * /

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.There is a lot of music.

12. | 2 |

13.There is a lot of music

14.

15.mysql > SELECT @ p_in

16.There is a lot of music.

17. | @ p_in |

18.After music +

19. | 1 |

20.After music +

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 pumped outbound * View output parameters * /

5.-> SET pumped outbound 2 * modify parameter value * /

6.-> SELECT pairing outbound * see if there are any changes * /

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. / * is not defined. Return NULL*/

9. +-

10. | p_out |

11.There is a lot of music.

12. | 2 |

13.There is a lot of music

14.

15.mysql > SELECT @ p_out

16.There is a lot of music.

17. | p_out |

18.After music +

19. | 2 |

20.After music +

Ⅲ. 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.There is a lot of music.

12. | 2 |

13.There is a lot of music

14.

15.mysql > SELECT @ p_inout

16.There is a lot of music.

17. | @ p_inout |

18.After music +

19. | 2 |

20.After music +

(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.There is a lot of music.

11. | @ y |

12.There is a lot of music

13. | Goodbye Cruel World |

14.After music +

15.

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

17.mysql > SELECT @ z

18.After music +

19. | @ z |

20.After music +

21. | 6 |

22.There is a lot of music

ⅱ. 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_procedure)

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: / * comment content * / is generally used for multi-line comments

For example:

1. Mysql > DELIMITER / /

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

3.-> (IN parameter1 INTEGER) / * parameters parameter * /

4.-> BEGIN / * start of block statement block * /

5.-> DECLARE variable1 CHAR (10); / * variables variable declaration * /

6.-> IF parameter1 = 17 THEN / * start of IF IF condition starts * /

7.-> SET variable1 = 'birds'; / * assignment assignment * /

8.-> ELSE

9.-> SET variable1 = 'beasts'; / * assignment assignment * /

10.-> END IF; / * end of IF IF ends * /

11.-> INSERT INTO table1 VALUES (variable1); / * statement SQL statement * /

12.-> END / * end of end of block statement block * /

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 showtables; 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

Selectname from mysql.proc where db=' database name'

Or

Selectroutine_name from information_schema.routines where routine_schema=' database name'

Or

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

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

DROPPROCEDURE

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

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

2. Mysql > DELIMITER / /

3. Mysql > CREATE PROCEDURE proc10 ()

4.-> begin

5.-> declare v int

6.-> set vault 0

7.-> LOOP_LABLE:loop

8.-> if vault 3 then

9.-> set v=v+1

10.-> ITERATE LOOP_LABLE

11.-> end if

12.-> insert into t values (v)

13.-> set v=v+1

14.-> if v > = 5 then

15.-> leave LOOP_LABLE

16.-> end if

17.-> end loop

18.-> end

19.-> / /

20.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.There is a lot of talk about the color of music.

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

12.PUBG / MUTHUTHUE +

13. | ab |

14. Talk about music, talk about it, talk about it.

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.There is a lot of music.

11. | round (1.56) |

12.There is a lot of music

13. | 2 |

14.After music +

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 above is all the content of the article "what is the use of MySQL stored procedures?" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.