MySQL Tutorial/Procedure Function/Parameters
Содержание
- 1 Check input parameter
- 2 DateTime parameter
- 3 Declare and use the OUT parameter
- 4 Pass status code and message out of a procedure
- 5 Pass variable to a procedure as the OUT parameter
- 6 Save status to an OUT parameter
- 7 Using OUT parameter to return the status code and message from a procedure
- 8 Verify the input parameter
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>