Operation of MySQL stored procedures and functions

MySQL has a stored procedure function at the beginning of version 5.0;

Stored procedures and functions are a collection of SQL statements that have been compiled and stored in the database beforehand;

The difference between a stored procedure and a function: The function must have a return value, and the stored procedure does not;

1, 1 Create a stored procedure syntax form

Delimiter $$

CREATE PROCEDURE procedure_name ([Procedure_paramter,]) [characteristic ...]

Routine_body

$$

Delimter;

Note: Procedure_paramter is a parameter, optional, in the form of: In/out/inout paramter_name type

Routine_body as SQL statement to begin...end identify SQL statement start and end

Temporarily ends a delimiter with $$ as a statement

1, 2 create FUNCTION syntax form

Create function function_name ([Function_paramter,]) [characteristic ...]

Routine_body

Note: The grammatical form of function_paramter: Paramter_name type

Benefits of using Stored procedures: simple, secure, high performance; disadvantage: There may not be security access to create stored procedures;

1. Execute Stored Procedure

The execution of a stored procedure is called a call, with the following statement: Calling Procedure_name (Procedure_paramter,..);

Without parameters: Call Procedure_name (); Execute stored procedure created and display return results

Direct display of the SELECT statement results when a stored procedure without parameters executes

With parameters:

Create a stored procedure with outgoing parameters: Create PROCEDURE Proc_name (out proc_parameter1 int,out proc_parameter2 int)

Execute stored procedure: Call Proc_name (@proc_parameter1, @proc_parameter2); outgoing variables must begin with @

Executing this statement does not display any data, it returns the specified variable, and runs the result of 1 rows affected

Select @proc_parameter1, @proc_parameter2; Execution of this statement shows the return result

Call ... and select ... Statement supporting use

Therefore, when you execute a stored procedure, you can either display the results or not display them.

The result of the stored procedure body SELECT statement is saved to the corresponding variable, using the INTO keyword.

Example: Delimiter $$

CREATE PROCEDURE Proc_name (in Emnpo int, outsum_sal int)

Begin

Select sum (SAL) from the table where Table.emnpo=emnpo into sum_sal;

End $$

Delimiter

Call Proc_name (3,@sum_sal);

Select @sum_sal;

2. Create stored procedure: Same as 1.1

3. Delete stored procedures:

Statement format: drop procedure procedure_name;

Only the name of the stored procedure is given and no additional () is required

4. Check the stored procedure:

Show procedure status;

This statement queries the list of all stored procedures that include detailed information

Operation of MySQL stored procedures and functions