MySQL stored procedures

1.1 CREATE PROCEDURE
Create

CREATE Procedure Stored Procedure name (parameter list) BEGIN SQL Statement code block END

Attention:

The parameter column surrounded by parentheses must always exist. If there are no parameters, you should also use an empty parameter column (). Each parameter is an in parameter by default. To be specified as a different parameter, use the keyword out or inout before the parameter name

Use the delimiter command when the MySQL client defines the stored procedure to change the statement delimiter from;

When using the delimiter command, you should avoid using the backslash (' "') character, because that is the escape character of MySQL.

Such as:

Delimiter// CREATE PROCEDURE Simpleproc (out param1 INT) BEGIN SELECT COUNT (*) to param1 from T; END // Query OK, 0 rows Affected (0.00 sec)

1.2 ALTER PROCEDURE
Modify

ALTER PROCEDURE Stored Procedure name SQL statement code block

This statement can be used to change the characteristics of a stored program.

1.3 DROP
PROCEDURE (delete)

DROP PROCEDURE IF exists stored procedure name Eg:drop PROCEDURE IF EXISTS proc_employee (Proc_employee stored procedure name)

This statement is used to remove a stored program. You cannot delete another stored procedure in one stored procedure, only another stored procedure can be called

1.4 SHOW CREATE
PROCEDURE (similar to show CREATE TABLE to view an existing stored procedure)

SHOW CREATE PROCEDURE Stored procedure name

1.5 SHOW PROCEDURE
STATUS (all stored procedures are listed)

SHOW PROCEDURE STATUS

1.6 Call statements (calls to stored procedures)

Pager Stored Procedure name (parameter list)

The call statement invokes a program that was previously created with CREATE procedure.

The call statement can return a value to its caller with a parameter declared as out or the inout parameter.

The stored procedure name must be appended with parentheses, even if the stored procedure has no parameters passed

1.7 BEGIN ...
END (compound statement)

[Begin_label BEGIN [statement_list] [End_label]

The storage subroutine can use the begin ... End Compound statement to contain multiple statements.

Statement_list represents a list of one or more statements. Each statement within Statement_list must be terminated with a semicolon (;).

Compound statements can be marked. Unless Begin_label exists, End_label cannot be given, and if both exist, they must be the same.

1.8 Declare statement (used to declare a local variable)

Declare statements are used to place different items into a sub-program: Local Variables

Declare is only used in the begin ... End Compound statement, and must precede any other statement at the beginning of the compound statement.

1.9 Variables in the stored program

1.1 Declare local variables

DECLARE var_name[,...] Type [DEFAULT value] This statement is used to declare a local variable. To provide a default value for a variable, include one of the defaults clauses. The value can be specified as an expression and does not need to be a constant. If there is no default clause, the initial value is null. The scope of the local variable in its declared begin ... End block. It can be used in nested blocks, except those that declare variables with the same name.

1.2 Variable SET statement

The SET statement in the stored program is an extended version of the generic SET statement. The referenced variable may be a variable declared within a subroutine, or a global server variable. The SET statement in the stored program is implemented as part of the pre-existing set syntax. This allows set a=x, B=y, ... Such an extension syntax. The different types of variables (local declaration variables and global and collective variables) can be mixed together. This also allows the merging of local variables and some options that are meaningful only for system variables.

1.3 SELECT ... INTO statement

SELECT col_name[,...] Into var_name[,...] table_expr This select syntax stores the selected columns directly into the variable. Therefore, only a single row can be retrieved. SELECT id,data into X, y from test.t1 LIMIT 1; Note that the user variable name is case insensitive in MySQL 5.1.

important : The SQL variable name cannot be the same as the column name. If Select ... into an SQL statement that contains a reference to a column and contains a local variable with the same name as the column, and MySQL currently interprets the reference as the name of a variable.

1.10 MySQL Stored procedure parameter type (in, out, inout)

MySQL stored procedure parameters (in)

MySQL stored procedure "in" parameter: similar to the value of the C language function parameter, this parameter may be modified inside the MySQL stored procedure, but modifications to the in type parameter are not visible to the caller (caller).

MySQL stored procedure parameters (out)

The MySQL stored procedure "out" parameter: passes the value from inside the stored procedure to the caller. Inside the stored procedure, the initial value of the parameter is NULL, regardless of whether the caller sets a value for the stored procedure parameter

MySQL Stored procedure parameters (InOut)

The MySQL stored procedure inout parameter is similar to out, and can be passed from inside the stored procedure to the caller. The difference is that the caller can also pass the value to the stored procedure through the InOut parameter.

Summarize

If you just want to pass the data to the MySQL stored procedure, use the "in" type parameter, and if you only return the value from the MySQL stored procedure, use the "out" type parameter, and if you need to pass the data to the MySQL stored procedure, and then pass it back to us after some calculation, we use " InOut "type parameter.

1.11 Examples:

1.1 Creating a stored procedure

Stored procedure with the return value (output parameter):

--Delete stored procedures DROP PROCEDURE IF EXISTS Proc_employee_getcount --Create a stored procedure CREATE PROCEDURE Proc_employee_getcount (out n int) BEGIN SELECT COUNT (*) from employee; END --mysql Calling stored Procedures Pager Proc_employee_getcount (@n);

Stored procedure with input parameters:

--Delete stored procedures DROP PROCEDURE IF EXISTS Proc_employee_findbyid; --Create a stored procedure CREATE PROCEDURE Proc_employee_findbyid (in n int) BEGIN SELECT * from employee where id=n; END --Defining variables SET @n=1; --Call the stored procedure Pager Proc_employee_findbyid (@n);

MySQL stored procedures