MySQL Stored procedures use

1. Target

Learn how to create a stored procedure

2. Syntax

CREATE PROCEDURE Sp_name ([proc_parameter])

[characteristics...] Routine_body

3. Description

• CREATE PROCEDURE is the keyword to use for creating stored procedures;

• Sp_name is the name of the stored procedure;

• Proc_parameter is the parameter list for the specified stored procedure, in the form of a parameter list: [in | Out | INOUT] Param_name Type

1. In: Indicates input parameters,
2. Out: Represents the output parameter,
3. INOUT: Indicates that both input and output can be entered;
4. Param_name indicates the name of the parameter;
5. Type represents the types of parameters that can be any type in the MySQL database.

• characteristics specifies the properties of a stored procedure, which can be evaluated in the following ways:

1. LANGUAGE sql: Description The routine_body part consists of SQL statements, the current system supports SQL, SQL is the unique value of the LANGUAGE attribute;
2. [NOT] Deterministic: Indicates whether the result of the stored procedure execution is correct. Deterministic indicates that the result is deterministic. Each time the stored procedure executes, the same input gets the same output, while not deterministic indicates that the result is indeterminate, and the same input may have different inputs. If no value is specified, the default is not deterministic.
3. {CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}: Indicates that the subroutine uses SQL statement restrictions.
4. CONTAINS SQL indicates that the subroutine contains SQL statements, but does not contain read-write data statements;
5. No SQL indicates that the subroutine does not contain SQL statements;
6. READS SQL Data Description subroutine contains read-write data statements;
7. Modifies SQL data indicates that the subroutine contains the statement that writes the data;
8. By default, the system is specified as contains SQL;
9. SQL SECURITY {definer| INVOKER}: Indicates who has permission to execute. Definer indicates that only the stored procedure is defined to execute, and Invoker indicates that the caller with permission can execute. By default, the system is specified as Definer.
10. COMMENT ' string ': note information that can be used to describe a stored procedure or function.

• Routine_body is the SQL code content, you can use the BEGIN ... End to represent the beginning and end of the SQL code.

4. Example

1) Create a sample database

Create DATABASE Hr;use hr;

2) Create the table used by the sample and insert the sample data

CREATE TABLE employees (employee_id int) primary key NOT NULL Auto_increment,employee_name varchar (a) Not Null,employe E_sex varchar (Ten) Default ' Male ', hire_date datetime NOT NULL default Current_timestamp,employee_mgr int (one), employee_ Salary float default 3000,department_id int (11));

Insert into employees (EMPLOYEE_NAME,EMPLOYEE_SEX,EMPLOYEE_MGR,EMPLOYEE_SALARY,DEPARTMENT_ID) VALUES (' David Tian ', ' Male ', 10,7500,1); INSERT into employees (EMPLOYEE_NAME,EMPLOYEE_SEX,EMPLOYEE_MGR,EMPLOYEE_SALARY,DEPARTMENT_ID) values (' Black Xie ', ' Male ', 10,6600,1); INSERT into employees (Employee_name,employee_sex,employee_mgr,employee_salary, department_id) VALUES (' Moses Wang ', ' Male ', 10,4300,1); INSERT into employees (EMPLOYEE_NAME,EMPLOYEE_SEX,EMPLOYEE_MGR, employee_salary,department_id) VALUES (' Rena Ruan ', ' female ', 10,5300,1); INSERT into employees (Employee_name,employee_sex, employee_mgr,employee_salary,department_id) VALUES (' Sunshine Ma ', ' female ', 10,6500,2); INSERT into employees (employee_ name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (' Scott Gao ', ' Male ', 10,9500,2); INSERT INTO Employees (EMPLOYEE_NAME,EMPLOYEE_SEX,EMPLOYEE_MGR,EMPLOYEE_SALARY,DEPARTMENT_ID) VALUES (' Warren Si ', ' Male ', 10,7800,2); INSERT into employees (EMPLOYEE_NAME,EMPLOYEE_SEX,EMPLOYEE_MGR,EMPLOYEE_SALARY,DEPARTMENT_ID) values(' Kaishen Yang ', ' Male ', 10,9500,3); INSERT into employees (Employee_name,employee_sex,employee_mgr,employee_salary, department_id) VALUES (' Simon Song ', ' Male ', 10,5500,3), insert into employees (EMPLOYEE_NAME,EMPLOYEE_SEX,EMPLOYEE_MGR, employee_salary,department_id) VALUES (' Brown Guan ', ' Male ', 10,5000,3); INSERT into employees (employee_name,employee_sex , employee_mgr,employee_salary,department_id) VALUES (' Eleven Chen ', ' female ', 10,3500,2); INSERT into employees (employee_ name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (' Cherry Zhou ', ' female ', 10,5500,4); INSERT INTO Employees (EMPLOYEE_NAME,EMPLOYEE_SEX,EMPLOYEE_MGR,EMPLOYEE_SALARY,DEPARTMENT_ID) VALUES (' Klause He ', ' Male ', 10,4500,5); INSERT into employees (EMPLOYEE_NAME,EMPLOYEE_SEX,EMPLOYEE_MGR,EMPLOYEE_SALARY,DEPARTMENT_ID) VALUES (' Maven Ma ', ' Male ', 10,4500,6); INSERT into employees (employee_name,employee_sex,employee_mgr,employee_salary,department _id) VALUES (' Stephani Wang ', ' female ', 10,5500,7); INSERT into employees (Employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (' Jerry Guo ', ' Male ', 10,8500,1); INSERT into employees (Employee_name,employee_sex, employee_mgr,employee_salary,department_id) VALUES (' Gerardo Garza ', ' Male ', 10,25000,8); INSERT into employees (employee_ name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (' Derek Wu ', ' Male ', 10,5500,5);

3) View the inserted sample data

SELECT * FROM Employees;

4) Create a stored procedure that calculates the average wage

DELIMITER//create Procedure calculate_emp_sal_avg_p () Beginselect avg (employee_salary) as Average_salaryfrom Employees;end//delimiter;

Description

• Delimeter//: This statement is to set the result terminator of MySQL to//, because MySQL default statement terminator is a semicolon ";", in order to avoid conflicts with the SQL statements in the stored procedure terminator, you need to use Delimeter to change the terminator of the stored procedure, and " End//"ends the stored procedure.
• After the stored procedure definition is finished, use "delimeter; "Restores the default terminator.
• Delimeter can also specify other symbols as terminators.
  

5. Calling a stored procedure

The stored procedure is called through a call statement, with the following syntax:

• Call Sp_name ([parameter[,...])

The call statement invokes a stored procedure that was previously created with CREATE PROCEDURE, where sp_name is the stored procedure name and parameter is a stored procedure parameter.

Call Calculate_emp_sal_avg_p ();

6. View Stored Procedures

1) Show STATUS statement View stored procedure

Grammar

• SHOW PROCEDURE STATUS [like ' pattern ']

This statement is an extension of MySQL that returns the characteristics of the subroutine, such as the database, name, type, creator, and date of creation and modification.

The like statement represents the name of the matching stored procedure;

2) Show CREATE statement View stored procedure definition

Grammar

• SHOW CREATE PROCEDURE Sp_name

This statement is an extension of MySQL, similar to show Create TABLE, which returns an exact string that can be used to recreate the named stored procedure.

3) from INFORMATION_SCHEMA. Viewing stored procedures in the Routines table

Grammar

SELECT * from INFORMATION_SCHEMA. Routines WHERE routine_name= ' sp_name ';

• The name of the stored procedure or function is stored in the Routine_name field;
• Sp_name refers to the stored procedure or function name;

If you have any problems in the process of trying, or if my code is wrong, please correct me, thank you very much!

Contact information: [Email protected]

Copyright @: Reprint please indicate the source!

MySQL Stored procedures use