SQL/MySQL/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
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>