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

This article focuses on "the advantages of using MySQL stored procedures". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Now let the editor take you to learn the advantages of using MySQL stored procedures.

1. The advantages of using stored procedures are:

(1) the stored procedure runs on the server side and executes quickly.

(2) after the stored procedure is executed once, its execution plan resides in the cache, and in the future operation, only the compiled binary code is called from the cache to improve the performance of the system.

(3) to ensure the security of the database. All database operations can be completed by using stored procedures, and the access to information of these operations can be controlled in a way. Www.2cto.com

two。 You can use create procedure statements to create stored procedures.

To create a stored procedure in MySQL 5.1, you must have CREATE routine permission. To see which stored procedures are in the database, you can use the SHOW PROCEDURE STATUS command. To view specific information about a stored procedure, use the SHOWCREATE PROCEDURE sp_name command, where sp_name is the name of the stored procedure.

The syntax format of CREATE PROCEDURE:

CREATE PROCEDURE sp_name ([proc_parameter [,...]])

[characteristic...] Routine_body

The parameters of proc_parameter are as follows:

[IN | OUT | INOUT] param_name type

The characteristic features are as follows:

Language SQL

| | [NOT] DETERMINISTIC |

| | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} |

| | SQL SECURITY {DEFINER | INVOKER} |

| | COMMENT 'string' |

Description:

● sp_name: the name of the stored procedure, created in the current database by default. When you need to create a stored procedure in a specific database, precede the name with the name of the database in the format: db_name.sp_name. It is worth noting that this name should be avoided as much as possible with the same name as the built-in function of MySQL, otherwise an error will occur.

● proc_parameter: the parameter of the stored procedure. Param_name is the parameter name, type is the parameter type, and is separated by a comma when there are multiple parameters. A stored procedure can have 0, 1, or more parameters. MySQL stored procedures support three types of parameters: input parameters, output parameters, and input / output parameters, with the keywords IN, OUT, and INOUT, respectively. Input parameters enable data to be passed to a stored procedure. When you need to return an answer or result, the stored procedure uses output parameters. Input / output parameters can act as both input and output parameters. Stored procedures can also take no parameters, but the parentheses after the name cannot be omitted.

Note: the name of the parameter should not be equal to the name of the column, otherwise an error message will not be returned, but the SQL statement in the stored procedure will treat the parameter name as the column name, resulting in unpredictable results.

Characteristic: some characteristic settings of stored procedures, which are described below:

Language sql: indicates that the language for writing this stored procedure is SQL. Currently, MySQL stored procedures cannot be written in an external programming language, that is, this option can be unspecified. It will be extended in the future, and the first language most likely to be supported is PHP. Www.2cto.com

Deterministic: set to DETERMINISTIC means that the stored procedure produces the same results for the same input parameters, and set to NOT DETERMINISTIC means that it produces uncertain results. The default is NOTDETERMINISTIC.

Contains SQL: indicates that the stored procedure does not contain statements that read or write data. NO SQL indicates that the stored procedure does not contain SQL statements. Reads SQL DATA means that the stored procedure contains statements that read data, but not statements that write data. Modifies SQL DATA indicates that the stored procedure contains statements to write data. If these characteristics are not explicitly given, the default is CONTAINS SQL.

The SQL SECURITY:SQL SECURITY feature can be used to specify whether the stored procedure is executed with the permission of the user (DEFINER) who created the stored procedure or with the permission of the caller (INVOKER). The default value is DEFINER.

COMMENT 'string': description of stored procedures, string is the description content. This information can be displayed with a SHOWCREATE PROCEDURE statement.

● routine_body: this is the body of a stored procedure, also known as the stored procedure body. It contains statements that must be executed during a procedure call, which always starts with begin and ends with end. Of course, the BEGIN-END flag can be omitted when there is only one SQL statement in the body of the stored procedure.

3. Before you start creating stored procedures, let's introduce a very useful command, the delimiter command. In MySQL, the server processes statements with a semicolon as the closing flag. However, when creating a stored procedure, the body of the stored procedure may contain multiple SQL statements, and each SQL statement ends with a semicolon. When the server processor encounters the first semicolon, it will think that the program is finished, which is definitely not possible. So here you use the DELIMITER command to change the closing flag of the MySQL statement to another symbol.

DELIMITER syntax format is: DELIMITER $$

Note: $$is a user-defined Terminator, usually this symbol can be some special symbols, such as two "#", a "¥", numbers, letters and so on. When using the DELIMITER command, you should avoid using the backslash ("\") character because that is the escape character of MySQL.

Example: create a stored procedure to delete a specific student's information.

DELIMITER $$

CREATE PROCEDURE DELETE_STUDENT (IN XH CHAR (6))

BEGIN

DELETE FROM XS WHERE student number = XH

END $$

DELIMITER

Note: when this stored procedure is called, MySQL deletes the corresponding data in the XS table according to the value of the parameter XH provided.

The stored procedure body is specified between the keywords BEGIN and END, and of course, BEGIN-END compound statements can also be used in nesting.

4. Local variable

Local variables can be declared in stored procedures, and they can be used to store temporary results. To declare local variables, you must use the declare statement. You can also assign an initial value to a local variable while declaring it.

DECLARE syntax format: DECLARE var_name [,...] Type [DEFAULT value]

Note: var_name is the name of the variable; type is the type of variable; the default clause assigns a default value to the variable, if it is not specified by default to NULL. Multiple local variables of the same type can be declared at the same time, separated by commas.

Example: declare an integer variable and two character variables.

DECLARE num INT (4)

DECLARE str1, str2 VARCHAR (6)

Declare n char (10) default 'abcdefg'

Description: local variables can only be in BEGIN. The declaration in the END statement block.

The local variable must be declared at the beginning of the stored procedure. After the declaration, you can declare its BEGIN. This variable is used in END statement blocks, but not in other statement blocks. Www.2cto.com

You can also declare user variables in stored procedures, but don't confuse the two. The difference between a local variable and a user variable is that the @ symbol is not used in front of the local variable, and the local variable is located in the BEGIN. The END statement block disappears after processing, while the user variable exists throughout the session.

5. Assign a value using a set statement

To assign a value to a local variable, you can use the set statement, which is also part of the SQL itself. The syntax format is SET var_name = expr [, var_name = expr].

Example: assign a value to a local variable in a stored procedure.

SET num=1, str1= 'hello'

Description: unlike when declaring user variables, the variable name here is not preceded by the @ symbol. Note that this statement in the example cannot be executed alone and can only be used in stored procedures and stored functions.

6. SELECT...INTO statement (emphasis)

Use this SELECT... The INTO syntax stores selected column values directly into variables. Therefore, the result returned can only have one row. The syntax format is:

SELECT col_name [,...] INTO var_name [,...] Table_expr

Description: col_name is the column name and var_name is the variable name to be assigned. Table_expr is the FROM clause and the following part of the SELECT statement, which is not described here.

Example: in the body of the stored procedure, the student name and major name of the student number 081101 in the XS table are assigned to the variables name and project, respectively.

SELECT name, professional name INTO name, project

FROMXS; WHERE student number = '081101'

7. Flow control statement

In MySQL, the common procedural SQL statements can be used in a stored procedure body. For example: IF statement, CASE statement, LOOP statement, WHILE statement, iterate statement, and LEAVE statement.

(1) IF statement

IF-THEN-ELSE statements can perform different actions according to different conditions.

The syntax format is:

IF judgment condition THEN one or more SQL statements

[the condition judged by ELSEIF is THEN one or more SQL statements].

[ELSE one or more SQL statements]

END IF

Description: when the judgment condition is true, the corresponding SQL statement is executed.

The IF statement is different from the system's built-in function IF () function, and the IF () function can only judge two cases, so don't be confused.

Example: create a stored procedure for the XSCJ database to determine which of the two input parameters is larger.

DELIMITER $$www.2cto.com

CREATE PROCEDURE XSCJ.COMPAR

(IN K1INTEGER, IN K2 INTEGER, OUT K3 CHAR (6))

BEGIN

IFK1 > K2 THEN

SET K3 = 'greater than'

ELSEIFK1=K2 THEN

SET K3 = 'equals'

ELSE

SET K3 = 'less than'

ENDIF

END$$

DELIMITER

Note: in the stored procedure, K1 and K2 are input parameters, and K3 is output parameters.

(2) CASE statement

As I've already explained, the use of CASE statements in stored procedures is slightly different. The syntax format is:

CASE case_value

WHEN when_value THEN statement_list

[WHEN when_value THEN statement_list]...

[ELSE statement_list]

END CASE

Or:

CASE

WHEN search_condition THEN statement_list

[WHEN search_condition THEN statement_list]...

[ELSE statement_list] www.2cto.com

END CASE

Description: a CASE statement can often act as an IF-THEN-ELSE statement.

In the first format, case_value is the value or expression to be judged, followed by a series of WHEN-THEN blocks, each with a when_value parameter that specifies the value to compare with case_value, and if true, executes the SQL statement in statement_list. If none of the preceding blocks match, the statement specified in the ELSE block is executed. The CASE statement ends with END CASE.

In the second format, there are no arguments after the CASE keyword, and in the WHEN-THEN block, search_condition specifies a comparison expression that executes statements after THEN when the expression is true. Compared with the first format, this format can achieve more complex condition judgment and is more convenient to use.

Example: create a stored procedure that returns different results according to different parameters.

DELIMITER $$

CREATE PROCEDURE XSCJ.RESULT

(IN str VARCHAR (4), OUT sex VARCHAR (4))

BEGIN

CASE str

WHEN'M' THEN SET sex=' Man'

WHEN'F' THEN SET sex=' girl'

ELSE SET sex=' none'

ENDCASE

END$$

DELIMITER

Example: create the above stored procedure with a CASE statement in the second format. The program snippet is as follows:

CASE

WHENstr='M' THEN SET sex=' Man'

WHENstr='F' THEN SET sex=' girl'

ELSE SET sex=' none'

END CASE

(3) Loop statement

MySQL supports three statements used to create loops: while, repeat, and loop statements. Zero, one, or more loop statements can be defined in a stored procedure.

The syntax format of the ● WHILE statement is:

[begin_label:] WHILE search_condition DO

Statement_list www.2cto.com

END WHILE [end_label]

Statement: first of all, the statement determines whether search_condition is true, if it is not true, execute the statement in statement_list, and then judge again. If it is true, it continues the loop, and if it is not true, it ends the cycle. Begin_label and end_label are annotations for WHILE statements. End_label cannot be given unless begin_label exists, and if both appear, their names must be the same.

Example: create a stored procedure with a WHILE loop.

DELIMITER $$

CREATE PROCEDURE dowhile ()

BEGIN

DECLARE v1 INT DEFAULT5

WHILE v1 > 0 DO

SET v1 = v1-1

END WHILE

END $$

DELIMITER

The format of the ● repeat statement is as follows:

[begin_label:] REPEAT

Statement_list

UNTIL search_condition

END REPEAT [end_label]

Description: the REPEAT statement first executes the statement in statement_list, and then determines whether the search_condition is true. If it is true, it stops the loop, and if it is not true, the loop continues. REPEAT can also be tagged.

Example: create a stored procedure such as example 7.9 with a repeat statement. The program snippet is as follows:

REPEAT

V1=v1-1

UNTIL v1

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.