MySQL Stored Procedure

MySQL Stored Procedure
1. Objectives

Learn how to create a stored procedure

2. Syntax

CREATE PROCEDURESp_name ([proc_parameter])

[Characteristics...] routine_body

3. Description

• 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. The parameter list is IN the form of [IN | OUT | INOUT] param_name type.

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

• 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!

How can I call a mysql stored procedure?

How php calls mysql stored procedures and functions
Stored Procedures and functions are just introduced by MySql5.0. This operation is not directly supported in PHP. However, due to the design of the Mysql php api, we can support calling stored procedures and functions in the mysql PHP api of the previous php version.

Call stored procedures and functions in php.

1. The method that calls the stored procedure.

A. If the stored procedure has the IN/INOUT parameter, declare a variable and input the parameter to the stored procedure. The variable is a pair,

A php variable (or not required, but there is no way to enter the php variable dynamically), a Mysql

Variable.

B. If the stored procedure has an OUT variable, declare a Mysql variable.

The declaration of mysql variables is special. the mysql server must know the existence of this variable, that is, to execute a mysql statement.

Input set @ mysqlvar = $ phpvar;

C. Use mysql_query ()/mysql_db_query () to execute the mysql variable declaration statement.

Mysql_query ("set @ mysqlvar = $ pbpvar ");

In this way, there is a variable in the mysql server, @ mysqlar. If it is an IN parameter, its value can be passed IN by phpar.

D. For stored procedures.

1. Execute the call procedure () statement.

That is, mysql_query ("call proceduer ([var1]...)");

2. If a return value exists, run select @ ar to return the execution result.

Mysql_query ("select @ var )"

The next operation is the same as executing a mysql statement in php. You can use functions such as mydql_fetch_row () to obtain the result.

If the time function. You can directly execute select function.
$ Host = "localhost ";
$ User = "root ";
$ Password = "11212 ";
$ Db = "samp_db ";
$ Dblink = mysql_connect ($ host, $ user, $ password)
Or die ("can't connect to mysql ");
Mysql_select_db ($ db, $ dblink)
Or die ("can't select samp_db ");
$ Res = mysql_query ("set @ a = $ password", $ dblink );
$ Res = mysql_query ("call aa (@ a)", $ dblink );
$ Res = mysql_query ("select @ a", $ dblink );
$ Row = mysql_fetch_row ($ res );
Echo $ row [0];... remaining full text>

In MySQL, how does one write SQL statements to call stored procedures?

Call sp_add ();
Is there a problem with the process you defined, and the returned results are not pointed out?
Like me:

Create procedure sp_add (a int, B int, out c int)
Begin

Set c = a + B;

End;
Call process:
Call sp_add (1, 2, @ );
Select @;