MySQL Tutorial/Procedure Function/Procedure
Содержание
- 1 ALTER PROCEDURE and ALTER FUNCTION Syntax
- 2 Calling a Single-Statement Procedure
- 3 Creating a Multistatement Stored Procedure
- 4 Creating a Single-Statement Procedure
- 5 DROP PROCEDURE and DROP FUNCTION Syntax
- 6 Stored Procedure Permissions
- 7 The ALTER statement lets you change the characteristics of a stored procedure
- 8 The CREATE PROCEDURE Statement
- 9 The stored procedure characteristics include a number of options for how the stored procedure behaves.
- 10 To remove a stored procedures, use the DROP statement
- 11 Using an OUT parameter.
- 12 You can set parameters for a stored procedure
ALTER PROCEDURE and ALTER FUNCTION Syntax
This statement can be used to change the characteristics of a stored procedure or function.
More than one change may be specified in an ALTER PROCEDURE or ALTER FUNCTION statement.
<source lang="sql">
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...] characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT "string"</source>
Calling a Single-Statement Procedure
<source lang="sql">
mysql> mysql> mysql> CREATE TABLE Employee(
-> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> );
Query OK, 0 rows affected (0.08 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.02 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> create procedure myProc ()
-> SELECT id,first_name FROM employee;
Query OK, 0 rows affected (0.00 sec) mysql> mysql> call myProc (); +------+------------+ | id | first_name | +------+------------+ | 1 | Jason | | 2 | Alison | | 3 | James | | 4 | Celia | | 5 | Robert | | 6 | Linda | | 7 | David | | 8 | James | +------+------------+ 8 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec)</source>
Creating a Multistatement Stored Procedure
Change the DELIMITER to something other than a semicolon (;).
MySQL will allow you to enter a ; without having the client process the input.
<source lang="sql">
mysql> mysql> mysql> CREATE TABLE Employee(
-> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> );
Query OK, 0 rows affected (0.03 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.01 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> DELIMITER // mysql> CREATE PROCEDURE merge_employee (IN old_id INT, IN new_id INT, OUT error VARCHAR(100))
-> SQL SECURITY DEFINER -> COMMENT "This is the comment" -> BEGIN -> DECLARE old_count INT DEFAULT 0; -> DECLARE new_count INT DEFAULT 0; -> DECLARE addresses_changed INT DEFAULT 0; -> -> ## check to make sure the old_id and new_id exists -> SELECT count(*) INTO old_count FROM employee WHERE id = old_id; -> SELECT count(*) INTO new_count FROM employee WHERE id = new_id; -> -> IF !old_count THEN -> SET error = "old id does not exist"; -> ELSEIF !new_count THEN -> SET error = "new id does not exist"; -> ELSE -> UPDATE employee SET id = new_id WHERE id = old_id; -> SELECT row_count() INTO addresses_changed; -> -> DELETE FROM employee WHERE id = old_id; -> -> SELECT addresses_changed; -> -> END IF; -> END -> //
Query OK, 0 rows affected (0.00 sec) mysql> DELIMITER ; mysql> mysql> mysql> call merge_employee (1,4,@error); +-------------------+ | addresses_changed | +-------------------+ | 1 | +-------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> mysql> select @error; +--------+ | @error | +--------+ | NULL | +--------+ 1 row in set (0.00 sec) mysql> mysql> drop procedure merge_employee; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec)</source>
Creating a Single-Statement Procedure
<source lang="sql">
mysql> mysql> mysql> create procedure myProc ()
-> SELECT id,first_name FROM employee;
Query OK, 0 rows affected (0.00 sec) mysql> mysql> call myProc (); mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec)</source>
DROP PROCEDURE and DROP FUNCTION Syntax
<source lang="sql">
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name</source>
Stored Procedure Permissions
Specific to procedures, the MySQL permissions scheme has the CREATE ROUTINE, ALTER ROUTINE, and EXECUTE privilege.
The permissions required for working with stored procedures are as follows:
Viewing permissions
To view stored procedures with SHOW PROCEDURE STATUS, you must have SELECT access to the mysql.proc table.
Or you must have the ALTER ROUTINE privilege for that particular procedure.
Calling permissions
To call a stored procedure
You need the ability to connect to the server and have the EXECUTE permission for the procedure.
EXECUTE permissions can be granted globally in the mysql.user table.
EXECUTE permissions can be granted at the database level in the mysql.db table
EXECUTE permissions can be granted for a specific routine in the mysql.procs_priv table.
Creating and altering permissions
To govern creating and altering a stored procedure, MySQL uses the CREATE ROUTINE and ALTER ROUTINE privilege.
Permissions for creating or changing procedures can be granted globally in the mysql.user table.
Permissions for creating or changing procedures can be granted at the database level in the mysql.db table.
Permissions for creating or changing procedures can be granted for a specific routine in the mysql.procs_priv table.
Dropping permissions
To drop a procedure, you must have the ALTER ROUTINE privilege.
Permissions for dropping procedures can be granted globally in the mysql.user table.
Permissions for dropping procedures can be granted at the database level in the mysql.db table.
Permissions for dropping procedures can be granted for a specific routine in the mysql.procs_priv table.
11. 5. Procedure 11. 5. 1. <A href="/Tutorial/MySQL/0201__Procedure-Function/TheCREATEPROCEDUREStatement.htm">The CREATE PROCEDURE Statement</a> 11. 5. 2. <A href="/Tutorial/MySQL/0201__Procedure-Function/Youcansetparametersforastoredprocedure.htm">You can set parameters for a stored procedure</a> 11. 5. 3. <A href="/Tutorial/MySQL/0201__Procedure-Function/UsinganOUTparameter.htm">Using an OUT parameter.</a> 11. 5. 4. <A href="/Tutorial/MySQL/0201__Procedure-Function/Thestoredprocedurecharacteristicsincludeanumberofoptionsforhowthestoredprocedurebehaves.htm">The stored procedure characteristics include a number of options for how the stored procedure behaves.</a> 11. 5. 5. <A href="/Tutorial/MySQL/0201__Procedure-Function/CreatingaSingleStatementProcedure.htm">Creating a Single-Statement Procedure</a> 11. 5. 6. <A href="/Tutorial/MySQL/0201__Procedure-Function/CallingaSingleStatementProcedure.htm">Calling a Single-Statement Procedure</a> 11. 5. 7. <A href="/Tutorial/MySQL/0201__Procedure-Function/CreatingaMultistatementStoredProcedure.htm">Creating a Multistatement Stored Procedure</a> 11. 5. 8. <A href="/Tutorial/MySQL/0201__Procedure-Function/TheALTERstatementletsyouchangethecharacteristicsofastoredprocedure.htm">The ALTER statement lets you change the characteristics of a stored procedure</a> 11. 5. 9. <A href="/Tutorial/MySQL/0201__Procedure-Function/ToremoveastoredproceduresusetheDROPstatement.htm">To remove a stored procedures, use the DROP statement</a> 11. 5. 10. <A href="/Tutorial/MySQL/0201__Procedure-Function/ALTERPROCEDUREandALTERFUNCTIONSyntax.htm">ALTER PROCEDURE and ALTER FUNCTION Syntax</a> 11. 5. 11. <A href="/Tutorial/MySQL/0201__Procedure-Function/DROPPROCEDUREandDROPFUNCTIONSyntax.htm">DROP PROCEDURE and DROP FUNCTION Syntax</a> 11. 5. 12. Stored Procedure Permissions
The ALTER statement lets you change the characteristics of a stored procedure
It has the following syntax:
<source lang="sql">
ALTER PROCEDURE [<database>.]<procedure name> <characteristics></source>
The CREATE PROCEDURE Statement
Here is the syntax:
<source lang="sql">
CREATE [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] Valid SQL procedure statement
proc_parameter:
[ IN | OUT | INOUT ] param_name type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT "string"</source>
By default, the procedure is associated with the default database.
To associate explicitly with a given database, specify the name as db_name.sp_name.
The name must be followed by parentheses.
If the database is not provided, MySQL creates the procedure in the current database or gives a No database selected error.
Procedure names can be up to 64 characters long.
If you must have a procedure with the same name as a MySQL function, putting a space between the name and the parentheses will help MySQL differentiate between the two.
The stored procedure characteristics include a number of options for how the stored procedure behaves.
The following table lists the available options.
Characteristic Value Description LANGUAGE SQL The language used to write the stored procedure. SQL SECURITY DEFINER or INVOKER The SQL SECURITY tells MySQL which user to use when running the procedure. The default is DEFINER. COMMENT The COMMENT is a place to enter notes. The comment is displayed in SHOW CREATE PROCEDURE commands.
To remove a stored procedures, use the DROP statement
<source lang="sql">
DROP PROCEDURE [database.]<procedure name></source>
Using an OUT parameter.
<source lang="sql">
mysql>
mysql> mysql> mysql> CREATE TABLE Employee(
-> id int, -> first_name VARCHAR(15), -> last_name VARCHAR(15), -> start_date DATE, -> end_date DATE, -> salary FLOAT(8,2), -> city VARCHAR(10), -> description VARCHAR(15) -> );
Query OK, 0 rows affected (0.02 sec) mysql> mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> mysql> delimiter // mysql> mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN -> SELECT COUNT(*) INTO param1 FROM employee; -> END; -> //
Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> mysql> CALL simpleproc(@a); Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT @a; +------+ | @a | +------+ | 8 | +------+ 1 row in set (0.00 sec) mysql> mysql> drop procedure simpleproc; Query OK, 0 rows affected (0.00 sec) mysql> mysql> mysql> drop table Employee; Query OK, 0 rows affected (0.00 sec) mysql></source>
You can set parameters for a stored procedure
<source lang="sql">
[IN|OUT|INOUT] <name> </source>
If you don"t specify IN, OUT, or INOUT for the parameter, it will default to IN.
An IN parameter is passed into the stored procedure to use internally.
An OUT parameter is set within the procedure, but accessed by the caller.
An INOUT parameter is passed into the procedure for internal use, but is also available to the caller after the procedure has completed.
The name and data type of the parameter are used in the stored procedure for referencing and setting values going in and out of the procedure.
The data type can be any valid data type for MySQL.
