mysql stored procedure statement

MySQL stored procedure is a reusable database program that can execute a series of SQL statements to complete specific functions. Compared with some complex SQL statements or SQL statements in applications, the advantage of stored procedures is that they can greatly improve the operating efficiency and security of the program.

Syntax

MySQL stored procedures are defined using the "CREATE PROCEDURE" statement. Here is a simple example:

CREATE PROCEDURE procedure_name ([IN/OUT] parameter_name data_type)
BEGIN 
    SQL statement; 
    SQL statement;
    ...
END

• procedure_name: The name of the stored procedure.
• parameter_name: the name of the input and output parameters of the stored procedure, data_type refers to the data type.
• SQL statement: The executed SQL statement segment can include control structures.

The stored procedure statement starts with "BEGIN" and ends with "END", with SQL statements and program control structures in the middle. Parameters can be input, output, or input-output modes.

Example

The following is an example of a stored procedure that outputs employee position information:

CREATE PROCEDURE employee_role(IN emp_id INT)
BEGIN
    SELECT employee_name, department_name, role_name 
    FROM employee, department, role
    WHERE employee.department_id = department.department_id
    AND employee.role_id = role.role_id
    AND employee_id = emp_id;
END

In this stored procedure, the input parameter is emp_id and the type is INT. The program will query the required data and output the employee's name, department name and job title.

Calling stored procedures

After the stored procedure is defined, it can be called using the CALL statement. The following is a simple example:

CALL procedure_name (parameter1, parameter2, ...);

Example:

CALL employee_role(1001);

This call will output the name, department name and job title of employee 1001.

Input, output, input and output parameters

You can define input, output and input and output parameters by adding the parameters "IN", "OUT" and "INOUT" keywords.

IN parameters refer to the input parameters in the stored procedure, that is, the parameter values ​​passed in when the program is executed. Use the following syntax:

CREATE PROCEDURE procedure_name(IN parameter1 data_type)

OUT parameters refer to the output parameters in the stored procedure. A given value is returned at the end of the stored procedure. The syntax is as follows:

CREATE PROCEDURE procedure_name(OUT parameter1 data_type)

INOUT parameters refer to parameters that can be modified both through input and output. The following syntax is used:

CREATE PROCEDURE procedure_name(INOUT parameter1 data_type)

Loop structure

MySQL stored procedures support a variety of loop structures, including WHILE, REPEAT/UNTIL, LOOP and FOR. Among them, the WHILE and REPEAT/UNTIL structures are the most commonly used.

WHILE structure:

WHILE condition DO
   statement(s);
END WHILE;

REPEAT/UNTIL structure:

REPEAT
   statement(s);
UNTIL condition;

The "condition" in the loop statement is a logical expression. As long as the condition is true, the loop will continue. implement.

Conditional statements

MySQL stored procedures also support conditional statements, including IF, IF-ELSE and CASE WHEN statements. Here is an example of a simple IF statement:

IF salary > 10000 THEN
   SET bonus = 500;
ELSE
   SET bonus = 200;
END IF;

This IF statement will set the bonus variable to 500 or 200.

Summary

MySQL stored procedure is a reusable database program used to execute a series of SQL statements and complete specific functions. Stored procedures can improve the running efficiency and safety of the program. Its syntax includes definition, call, input and output parameters, loop structure and conditional statements, etc. Learning MySQL stored procedures is very useful for improving programming efficiency and writing more powerful SQL query statements.

The above is the detailed content of mysql stored procedure statement. For more information, please follow other related articles on the PHP Chinese website!