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.

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT "string"

Calling a Single-Statement Procedure

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)

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.

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)

Creating a Single-Statement Procedure

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)

DROP PROCEDURE and DROP FUNCTION Syntax

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

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:

ALTER PROCEDURE [<database>.]<procedure name> <characteristics>

The CREATE PROCEDURE Statement

Here is the syntax:

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"

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

DROP PROCEDURE [database.]<procedure name>

Using an OUT parameter.

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>

You can set parameters for a stored procedure

[IN|OUT|INOUT] <name> <data type>

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.

MySQL Tutorial/Procedure Function/Procedure

Содержание

ALTER PROCEDURE and ALTER FUNCTION Syntax

Calling a Single-Statement Procedure

Creating a Multistatement Stored Procedure

Creating a Single-Statement Procedure

DROP PROCEDURE and DROP FUNCTION Syntax

Stored Procedure Permissions

The ALTER statement lets you change the characteristics of a stored procedure

The CREATE PROCEDURE Statement

The stored procedure characteristics include a number of options for how the stored procedure behaves.

To remove a stored procedures, use the DROP statement

Using an OUT parameter.

You can set parameters for a stored procedure

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты