MySQL stored procedure usage _ MySQL

1. learn how to create a stored procedure 2. syntax CREATEPROCEDUREsp_name ([proc_parameter]) [characteristics] routine_body3: CREATEPROCEDURE is the key word used to create a stored procedure; sp_name is Stored Procedure 1. goal

Learn how to create a stored procedure

2. Syntax

CREATE PROCEDURESp_name ([proc_parameter])

[Characteristics...] routine_body

3. note: create procedure is the keyword used to CREATE a stored PROCEDURE; sp_name is the name of the stored PROCEDURE; proc_parameter is the parameter list of the specified stored PROCEDURE, in the form of the parameter list: [IN | OUT | INOUT] param_name type

IN: indicates the input parameter, OUT: indicates the output parameter, INOUT: indicates both input and output. param_name indicates the name of the parameter, and type indicates the type of the parameter, this type can be any type in the MySQL database.

Characteristics specifies the features of a stored procedure, which can be set in the following ways:

Language SQL: indicates that the routine_body part is composed of SQL statements. Currently, the supported LANGUAGE is SQL, and SQL is the unique value of the LANGUAGE feature. [NOT] DETERMINISTIC: specifies whether the execution result of the stored procedure is correct. DETERMINISTIC indicates that the result is correct. Each time a stored procedure is executed, the same input will get the same output. NOT terministic indicates that the result is uncertain, and the same input may get different inputs. If no value is specified, the default value is not deterministic. {Contains SQL | no SQL | reads SQL data | modifies SQL data}: specifies the SQL statement restrictions for subprograms. Contains SQL indicates that the subprogram contains SQL statements but does not contain statements for reading and writing DATA. no SQL indicates that the subprogram does not contain SQL statements. reads SQL DATA indicates that the subprogram CONTAINS statements for reading and writing DATA; modifies SQL data indicates that the subroutine CONTAINS the statement for writing DATA. by default, the system specifies contains SQL; SQL SECURITY {DEFINER | INVOKER}: Specifies who has the permission to execute the statement. DEFINER indicates that the stored procedure can be executed only by defining the stored procedure; INVOKER indicates that the caller with the permission can execute the stored procedure. By default, the system specifies DEFINER. COMMENT 'string': COMMENT Information, which can be used to describe stored procedures or functions.

Routine_body is the content of SQL code. you can use BEGIN... END to indicate the start and END of SQL code. 4. example

1) create a sample database

create database hr;use hr;

2) create the table used in the example and insert the sample data.

Create table employees (employee_id int (11) primary key not null auto_increment, employee_name varchar (50) not null, employee_sex varchar (10) default 'male', hire_date datetime not null default current_timestamp, employee_mgr int (11), employee_salary float default 3000, department_id int (11 ));

Insert into employees (employee_name, employee_sex, employee_mgr, region, region) values ('David tianc', 'male', 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 ', 'femal', 1); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('sunshine M', 'female ', 2 ); insert into employees (employee name, employee) values ('Scott gao', 'male', 10, 9500, 2); insert into employees (employee name, employee _ SEX, employee _ Mgr, employee_salary, department_id) values ('Warren Si', 'male', 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', 3); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('brown guany', 'male', 3 ); insert into employees (employee_name, employee_sex, employee_mgr, numbers, numbers) values ('Eleven Chen ', 'female', 2); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('cherry Zhou ', 'female', 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', 6); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('stryanwang ', 'female', 7 ); insert into employees (employee name, employee) values ('Jerry guo', 'male', 10, 8500, 1); insert into employees (employee name, employee _ SEX, employee _ Mgr, employee_salary, department_id) values ('gerardo Garza ', 'male', 8); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('Derek Wu', 'male', 5 );

3) view the inserted sample data

select * from employees;

4) Create a storage process for calculating the average salary

DELIMITER //create procedure calculate_emp_sal_avg_p()beginselect AVG(employee_salary) as average_salaryfrom employees;end//DELIMITER ;

Description

DELIMETER //: This statement is used to set the MySQL result Terminator to //, because the default MySQL statement Terminator is a semicolon ";", to avoid conflict with the ending character of the SQL statement in the stored procedure, you must use DELIMETER to change the ending character of the stored procedure and END the stored procedure with "END. After the stored procedure is defined, use "DELIMETER;" to restore the default Terminator. DELIMETER can also specify other symbols as Terminator. 5. call the stored procedure

The stored procedure is called using the CALL statement. The syntax is as follows:

CALL sp_name ([parameter [,...])

The CALL statement calls a stored PROCEDURE created with create procedure. sp_name indicates the stored PROCEDURE name and parameter indicates the stored PROCEDURE parameter.

CALL calculate_emp_sal_avg_p();

6. View stored procedures

1) view the stored procedure using the show status statement.

Syntax

Show procedure status [LIKE 'pattern']

This statement is an extension of MySQL. it returns features of subprograms, such as databases, names, types, creators, creation dates, and modification dates.

The LIKE statement matches the name of a stored procedure;

2) view the stored procedure definition using the show create statement.

Syntax

Show create procedure sp_name

This statement is an extension of MySQL, similar to show create table. it returns an exact string that can be used to recreate a named stored procedure.

3) view the stored procedure from the information_schema.Routines table

Syntax

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'sp _ name ';

The ROUTINE_NAME field stores the name of the stored procedure or function; sp_name indicates the name of the stored procedure or function;

If you encounter any problems during your attempt or my code is incorrect, please correct me. thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!