MySQL Tutorial/Procedure Function/Parameters

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

Check input parameter

   <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.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)</source>


DateTime parameter

   <source lang="sql">

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></source>


Declare and use the OUT parameter

   <source lang="sql">

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></source>


Pass status code and message out of a procedure

   <source lang="sql">

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></source>


Pass variable to a procedure as the OUT parameter

   <source lang="sql">

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></source>


Save status to an OUT parameter

   <source lang="sql">

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)</source>


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

   <source lang="sql">

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)</source>


Verify the input parameter

   <source lang="sql">

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></source>