MySQL Tutorial/Procedure Function/Parameters

Материал из SQL эксперт
Перейти к: навигация, поиск

Check input parameter

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.02 sec)
mysql>
mysql>
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE sp_customer_search_dyn
    ->     (in_Description VARCHAR(30),
    ->      in_contact_surname VARCHAR(30),
    ->      in_contact_firstname VARCHAR(30),
    ->      in_city VARCHAR(10))
    ->
    -> BEGIN
    ->   DECLARE l_where_clause VARCHAR(1000) DEFAULT "WHERE";
    ->
    ->   IF in_Description IS NOT NULL THEN
    ->       SET l_where_clause=CONCAT(l_where_clause,
    ->          " description="",in_Description,""");
    ->   END IF;
    ->   select l_where_clause;
    ->   IF in_contact_surname IS NOT NULL THEN
    ->      IF l_where_clause<>"WHERE" THEN
    ->         SET l_where_clause=CONCAT(l_where_clause," AND ");
    ->      END IF;
    ->      SET l_where_clause=CONCAT(l_where_clause,
    ->          " last_name="",in_contact_surname,""");
    ->   END IF;
    ->   select l_where_clause;
    ->
    ->   IF in_contact_firstname IS NOT NULL THEN
    ->      IF l_where_clause<>"WHERE" THEN
    ->         SET l_where_clause=CONCAT(l_where_clause," AND ");
    ->      END IF;
    ->      SET l_where_clause=CONCAT(l_where_clause,
    ->          " first_name="",in_contact_firstname,""");
    ->   END IF;
    ->   select l_where_clause;
    ->
    ->   IF in_city IS NOT NULL THEN
    ->      IF l_where_clause<>"WHERE" THEN
    ->         SET l_where_clause=CONCAT(l_where_clause," AND ");
    ->      END IF;
    ->      SET l_where_clause=CONCAT(l_where_clause,
    ->          " city="",in_city,""");
    ->   END IF;
    ->   select l_where_clause;
    ->
    ->   SET @sql=CONCAT("SELECT * FROM employee ", l_where_clause);
    ->
    ->   select @sql;
    ->
    ->   PREPARE s1 FROM @sql;
    ->   EXECUTE s1;
    ->   DEALLOCATE PREPARE s1;
    ->
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> call sp_customer_search_dyn("Tester","Martin","Jason","Toronto");
+----------------------------+
| l_where_clause             |
+----------------------------+
| WHERE description="Tester" |
+----------------------------+
1 row in set (0.02 sec)
+----------------------------------------------------+
| l_where_clause                                     |
+----------------------------------------------------+
| WHERE description="Tester" AND  last_name="Martin" |
+----------------------------------------------------+
1 row in set (0.02 sec)
+----------------------------------------------------------------------------+
| l_where_clause                                                             |
+----------------------------------------------------------------------------+
| WHERE description="Tester" AND  last_name="Martin" AND  first_name="Jason" |
+----------------------------------------------------------------------------+
1 row in set (0.02 sec)
+------------------------------------------------------------------------------------------------+
| l_where_clause                                                                                 |
+------------------------------------------------------------------------------------------------+
| WHERE description="Tester" AND  last_name="Martin" AND  first_name="Jason" AND  city="Toronto" |
+------------------------------------------------------------------------------------------------+
1 row in set (0.24 sec)
+-----------------------------------------------------------------------------------------------------------------------+
| @sql                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------+
| SELECT * FROM employee WHERE description="Tester" AND  last_name="Martin" AND  first_name="Jason" AND  city="Toronto" |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.24 sec)
Empty set (0.24 sec)
Query OK, 0 rows affected (0.24 sec)
mysql>
mysql> drop procedure sp_customer_search_dyn;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)


DateTime parameter

mysql>
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.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.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.01 sec)
mysql>
mysql>
mysql>
mysql> delimiter $$
mysql> CREATE FUNCTION myFunction (in_dob datetime) returns int
    -> NO SQL
    -> BEGIN
    ->   DECLARE l_age INT;
    ->   IF DATE_FORMAT(NOW(),"00-%m-%d") >= DATE_FORMAT(in_dob,"00-%m-%d") THEN
    ->      SET l_age=DATE_FORMAT(NOW(),"%Y")-DATE_FORMAT(in_dob,"%Y");
    ->   ELSE
    ->      SET l_age=DATE_FORMAT(NOW(),"%Y")-DATE_FORMAT(in_dob,"%Y")-1;
    ->   END IF;
    ->   RETURN(l_age);
    ->
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql>
mysql> select myFunction(start_date) from employee;
+------------------------+
| myFunction(start_date) |
+------------------------+
|                     10 |
|                     31 |
|                     28 |
|                     24 |
|                     23 |
|                     19 |
|                     16 |
|                     10 |
+------------------------+
8 rows in set (0.00 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>


Declare and use the OUT parameter

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.05 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.01 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> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc(p_id INT, p_dob DATE, OUT p_status varchar(30))
    -> BEGIN
    ->      IF DATE_SUB(curdate(), INTERVAL 16 YEAR) <p_dob THEN
    ->           SET p_status="Employee must be 16 years or older";
    ->      ELSE
    ->           UPDATE employee
    ->              SET start_date=p_dob
    ->            WHERE id=p_id;
    ->            SET p_status="Ok";
    ->      END IF;
    -> END$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> delimiter ;
mysql> set @result = "";
Query OK, 0 rows affected (0.00 sec)
mysql> call myProc(999,"1999-01-01",@result);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @result;
+--------------------------------+
| @result                        |
+--------------------------------+
| Employee must be 16 years or o |
+--------------------------------+
1 row in set (0.02 sec)
mysql>
mysql> call myProc(999,"1979-01-01",@result);
Query OK, 0 rows affected (0.00 sec)
mysql> select @result;
+---------+
| @result |
+---------+
| Ok      |
+---------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
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>
mysql> drop table Employee;
Query OK, 0 rows affected (0.01 sec)
mysql>


Pass status code and message out of a procedure

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.01 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.02 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 myProc
    ->        (from_account int, to_account int,tfer_amount numeric(10,2),
    ->         OUT status int, OUT message VARCHAR(30))
    -> BEGIN
    ->      DECLARE from_account_balance NUMERIC(10,2);
    ->
    ->      SELECT salary
    ->        INTO from_account_balance
    ->        FROM employee
    ->       WHERE id=from_account;
    ->
    ->      IF from_account_balance >= tfer_amount THEN
    ->
    ->           START TRANSACTION;
    ->
    ->           UPDATE employee
    ->              SET salary=salary-tfer_amount
    ->            WHERE id=from_account;
    ->
    ->           UPDATE employee
    ->              SET salary=salary+tfer_amount
    ->            WHERE id=to_account;
    ->           COMMIT;
    ->
    ->           SET status=0;
    ->           SET message="OK";
    ->      ELSE
    ->           SET status=-1;
    ->           SET message="Insufficient funds";
    ->      END IF;
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> set @myMessage = "";
Query OK, 0 rows affected (0.00 sec)
mysql> set @myCode =0;
Query OK, 0 rows affected (0.00 sec)
mysql> call myProc(1,2,1000,@myCode, @myMessage);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select @myMessage;
+------------+
| @myMessage |
+------------+
| OK         |
+------------+
1 row in set (0.00 sec)
mysql>
mysql> select @myCode;
+---------+
| @myCode |
+---------+
| 0       |
+---------+
1 row in set (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 |  234.56 | Toronto   | Programmer  |
|    2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 7661.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> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>


Pass variable to a procedure as the OUT parameter

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc
    ->     (in_due_date DATE,
    ->      OUT status_code INT,
    ->      OUT status_message VARCHAR(30))
    -> BEGIN
    ->   DECLARE days_past_due INT;
    ->
    ->   SET days_past_due=FLOOR(DATEDIFF(now(),in_due_date));
    ->   IF days_past_due>90 THEN
    ->     SET status_code=-2;
    ->     SET status_message="more than 90 days overdue";
    ->   ELSEIF days_past_due >30 THEN
    ->     SET status_code=-1;
    ->     SET status_message="more than 30 days overdue";
    ->   ELSE
    ->     SET status_code=0;
    ->     SET status_message="OK";
    ->
    ->   END IF;
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql> set @myCode = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set @myMessage="";
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> call myProc("1999-01-01",@myCode,@myMessage);
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql> select @myCode;
+---------+
| @myCode |
+---------+
| -2      |
+---------+
1 row in set (0.00 sec)
mysql>
mysql> select @myMessage;
+---------------------------+
| @myMessage                |
+---------------------------+
| more than 90 days overdue |
+---------------------------+
1 row in set (0.00 sec)
mysql>


Save status to an OUT parameter

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.05 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.02 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.02 sec)
mysql>
mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc
    ->          (in_first_name   VARCHAR(30),
    ->           in_last_name    VARCHAR(30),
    ->           in_city         VARCHAR(30),
    ->           in_description  VARCHAR(10),
    ->           OUT out_status  VARCHAR(30))
    ->     MODIFIES SQL DATA
    -> BEGIN
    ->    DECLARE CONTINUE HANDLER FOR 1062
    ->       SET out_status="Duplicate Entry";
    ->
    ->    SET out_status="OK";
    ->    INSERT INTO employee
    ->      (first_name,last_name,city,description)
    ->     VALUES
    ->      (in_first_name,in_last_name,in_city,in_description);
    -> END$$
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> set @myMessage = 0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> call myProc("Jason","Martin","New City","New Description",@myMessage);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>
mysql> select @myCode, @myMessage;
+---------+------------+
| @myCode | @myMessage |
+---------+------------+
| 0       | OK         |
+---------+------------+
1 row in set (0.00 sec)
mysql>
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
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      |
| NULL | Jason      | Martin    | NULL       | NULL       |    NULL | New City  | New Descri  |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
9 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.01 sec)


Using OUT parameter to return the status code and message from a procedure

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE PROCEDURE myProc
    ->     (in_dob DATE,
    ->      OUT status_code INT,
    ->      OUT status_message VARCHAR(30))
    -> BEGIN
    ->
    ->   IF DATE_SUB(now(), INTERVAL 18 YEAR) <in_dob THEN
    ->     SET status_code=-1;
    ->     SET status_message="Error: employee is less than 18 years old";
    ->   ELSE
    ->     SET status_code=0;
    ->     SET status_message="OK";
    ->   END IF;
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> set @myCode = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> set @myMessage="";
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> call myProc("1999-01-01",@myCode,@myMessage);
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> drop procedure myProc;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select @myCode;
+---------+
| @myCode |
+---------+
| -1      |
+---------+
1 row in set (0.02 sec)
mysql>
mysql> select @myMessage;
+--------------------------------+
| @myMessage                     |
+--------------------------------+
| Error: employee is less than 1 |
+--------------------------------+
1 row in set (0.00 sec)


Verify the input parameter

mysql>
mysql> delimiter $$
mysql>
mysql> CREATE FUNCTION myFunction (in_string varchar(80) )
    ->  RETURNS VARCHAR(256)
    ->  NO SQL
    -> BEGIN
    ->    DECLARE i INT DEFAULT 1;
    ->       DECLARE string_len INT;
    ->       DECLARE out_string VARCHAR(256) DEFAULT "";
    ->
    ->       SET string_len=length(in_string);
    ->       WHILE (i<string_len) DO
    ->          SET out_string=CONCAT(out_string,ASCII(substr(in_string,i,1))," ");
    ->          SET i=i+1;
    ->       END WHILE;
    ->       RETURN (out_string);
    ->
    -> END$$
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ;
mysql>
mysql> select myFunction("ABCDEFGHI");
+--------------------------+
| myFunction("ABCDEFGHI")  |
+--------------------------+
| 65 66 67 68 69 70 71 72  |
+--------------------------+
1 row in set (0.00 sec)
mysql>
mysql> drop function myFunction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
Источник — «http://sqle.ru/index.php?title=MySQL_Tutorial/Procedure_Function/Parameters&oldid=732»