The stored procedure starts with the definition

1. The advantages of using stored procedures are:

(1) The stored procedure runs on the server side, and executes faster.

(2) Once the stored procedure executes once, its execution plan resides in the buffer memory, in the future operation, it simply calls the compiled binary code execution from the buffer memory, and improves the system performance.

(3) Ensure the security of the database. You can use stored procedures to complete all database operations and programmatically control access to database information for these operations.

2. Creating a stored procedure can use the CREATE PROCEDURE statement.

to create a stored procedure in MySQL 5.1, you must have the 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, you can use the Showcreate PROCEDURE sp_name command, where Sp_name is the name of the stored procedure.

The syntax format for CREATE procedure:

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

[characteristic ...] routine_body

among them, the parameters of Proc_parameter are as follows:

[In | Out | INOUT] Param_name type

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, which is created by default in the current database. 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 important to note that this name should try to avoid the same name as MySQL's built-in function, or an error will occur.

Proc_parameter: The parameters of the stored procedure, Param_name is the parameter name, type is the argument, and when there are multiple arguments, it is separated by commas. A stored procedure can have 0, one, or more parameters. The MySQL stored procedure supports three types of parameters: input parameters, output parameters, and input/output parameters, respectively, in, out, and inout. Input parameters enable data to be passed to a stored procedure. The stored procedure uses output parameters when it is necessary to return an answer or result. The input/output parameters can act as input parameters or as output parameters. Stored procedures can also be non-parametric, but the parentheses after the name cannot be omitted.

Note: The name of the parameter does not equal the name of the column, otherwise the error message is not returned, but the SQL statement in the stored procedure takes the parameter name as the column name, causing unpredictable results.

characteristic: Some feature settings for stored procedures, described below:

language sql: Indicates that the language in which this stored procedure is written is the SQL language, and currently, the MySQL stored procedure cannot be written in an external programming language, that is, this option can be unspecified. It will be expanded in the future, most likely the first supported language is PHP.

deterministic: Set to deterministic indicates that the stored procedure produces the same result for the same input parameter, and that setting to not deterministic indicates an indeterminate result. The default is notdeterministic.

contains SQL: Represents a statement in which a stored procedure does not contain read or write data. No SQL indicates that the stored procedure does not contain SQL statements. Reads SQL data represents the statement that the stored procedure contains read data, but does not contain statements that write data. Modifies SQL data indicates that the stored procedure contains statements that write data. If these features are not explicitly given, the default is contains SQL.

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

COMMENT ' string ': A description of the stored procedure, string describing the content. This information can be displayed using the Showcreate procedure statement.

Routine_body: This is the body part of the stored procedure, also called the stored procedure body. It contains statements that must be executed at the time of the procedure call, which always begins with begin and ends with end. Of course, the BEGIN-END flag can be omitted when there is only one SQL statement in the stored procedure body.

3. Before you begin creating a stored procedure, introduce a very useful command, the delimiter command. In MySQL, the server handles the statement with a semicolon-terminated flag. However, when creating a stored procedure, the stored procedure body may contain multiple SQL statements, each of which ends with a semicolon, when the server handler encounters the first semicolon and considers the program to be complete, which is certainly not possible. So here you use the delimiter command to modify the end sign of the MySQL statement to a different symbol.

the DELIMITER syntax format is: DELIMITER $$

Description: $$ is a user-defined terminator, usually this symbol can be some special symbols, such as two "#", a "￥", numbers, letters, etc. can be. 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 that implements the ability to delete a specific student's information.

DELIMITER $$

CREATE PROCEDURE delete_student (in XH CHAR (6))

BEGIN

DELETE from XS WHERE study number =xh;

END $$

DELIMITER;

NOTE: When this stored procedure is called, MySQL deletes the data corresponding to the XS table based on the value of the supplied parameter xh.

The stored procedure body is specified between the keyword BEGIN and end, of course, begin-end compound statements can also be nested.

4. Local Variables

Local variables can be declared in a stored procedure, and they can be used to store temporary results. To declare a local variable, 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]

Description: Var_name is the variable name; Type is a variable, and the default clause assigns a value to the variable, if you do not specify NULL by default. You can declare multiple local variables of the same type at the same time, separated by commas.

example: Declaring an integer variable and a two-character variable.

DECLARE num INT (4);

DECLARE str1, str2 VARCHAR (6);

declare n char (Ten) Default ' ABCDEFG ';

Description: Local variables can only be used at begin ... Declaration in the end statement block.

A local variable must be declared at the beginning of the stored procedure, and after it is declared, it can be declared at the begin ... The variable is used in the end statement block, and it cannot be used in other statement blocks.

You can also declare user variables in a stored procedure, but do not confuse the two. The difference between a local variable and a user variable is that the local variable is not preceded by the @ symbol, where the local variable is at the begin ... When the end statement block is processed, it disappears, and the user variable exists throughout the session.

5. Assigning values using SET statements

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: Assigning a value to a local variable in a stored procedure.

SET num=1, str1= ' Hello ';

Description: Unlike declaring a user variable, there is no @ symbol in front of the variable name. Note that this statement in the example cannot be executed separately, and can only be used in stored procedures and stored functions.

6. SELECT ... into statement (emphasis)

Use this Select ... into syntax you can store the selected column values directly into a variable. Therefore, there can be only one row for the returned result. 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 assign the value. TABLE_EXPR is the FROM clause and later part of the SELECT statement, which is not described here.

example: In the stored procedure body, the values of student names and professional names in the XS table of 081101 are assigned to the variable name and project respectively.

SELECT name, professional name into name, Project

Fromxs; WHERE study number = ' 081101 ';

7. Process Control Statements

in MySQL, a common procedure-style SQL statement 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

The if-then-else statement can perform different operations depending on the conditions.

The syntax format is:

IF The condition then one or more SQL statements

[ElseIf Judging condition then one or more SQL statements] ...

[Else one or more SQL statements]

END IF

Note: When the condition is true, the corresponding SQL statement is executed.

If statements differ from the system's built-in function if () functions, the if () function can only judge two cases, so please do not confuse.

Example: Create a stored procedure for a XSCJ database, and determine which of the two input parameters is larger.

DELIMITER $$

CREATE PROCEDURE Xscj.compar

(in K1integer, in K2 integers, 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;

Description: The K1 and K2 are input parameters in the stored procedure, and K3 are output parameters.

(2) Case Statement

As mentioned earlier, the use of case statements in stored procedures is described here in a slightly different way than before. 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]

END Case

Description: A case statement can often act as a 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 when_value parameter specifies the value to compare to Case_value, and if true, executes Statement_ The SQL statement in the list. If each of the preceding blocks does not match, the statement specified by the Else block is executed. The case statement ends with the 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 the statement after then, when the expression is true. Compared with the first format, this format enables more complex conditional judgments and is more convenient to use.

Example: Create a stored procedure that returns different results for different parameters.

DELIMITER $$

CREATE PROCEDURE XSCJ. RESULT

(in str varchar (4), Out sex varchar (4))

BEGIN

Case Str

When the ' M ' then SET sex= ' Male ';

When the ' F ' then SET sex= ' female ';

ELSE SET sex= ' None ';

endcase;

end$$

DELIMITER;

Example: Create the above stored procedure in the second form of the case statement. The program fragment is as follows:

Case

whenstr= ' M ' then SET sex= ' man ';

whenstr= ' F ' then SET sex= ' female ';

ELSE SET sex= ' None ';

END case;

(3) Loop statement

MySQL supports 3 statements for creating loops: while, repeat, and loop statements. You can define 0, one, or more loop statements in a stored procedure.

the While statement syntax format is:

[Begin_label:] While search_condition do

statement_list

END while [End_label]

NOTE: The statement first determines whether the search_condition is true, does not really execute the statement in the Statement_list, then makes the judgment again, continues the loop for the true, does not really end the loop. Begin_label and End_label are callouts of the while statement. Unless Begin_label exists, End_label cannot be given, 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; <