MySQL Tutorial/Procedure Function/Create Table

Create a table in a procedure

   <source lang="sql">

mysql> delimiter $$ mysql> mysql> mysql> CREATE PROCEDURE myProc()

   -> BEGIN
   ->      DECLARE i INT DEFAULT 1;
   ->
   ->      SET autocommit=0;
   ->
   ->      DROP TABLE IF EXISTS test_table ;
   ->      CREATE TABLE test_table (
   ->           id        INT PRIMARY KEY,
   ->            some_data VARCHAR(30))
   ->      ENGINE=innodb;
   ->
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> call myProc(); Query OK, 0 rows affected, 1 warning (0.08 sec) mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> desc test_table; +-----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | | | | some_data | varchar(30) | YES | | NULL | | +-----------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> drop table test_table; Query OK, 0 rows affected (0.03 sec) mysql></source>

Create a TEMPORARY table and save data into it in 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.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(3,"James",    "Smith",    "19781212", "19900315", 6544.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(4,"Celia",    "Rice",     "19821024", "19990421", 2344.78, "Vancouver","Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(5,"Robert",   "Black",    "19840115", "19980808", 2334.78, "Vancouver","Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(6,"Linda",    "Green",    "19870730", "19960104", 4322.78,"New York",  "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(7,"David",    "Larry",    "19901231", "19980212", 7897.78,"New York",  "Manager");

Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)

   ->               values(8,"James",    "Cat",     "19960917",  "20020415", 1232.78,"Vancouver", "Tester");

Query OK, 1 row affected (0.00 sec) mysql> mysql> select * from Employee; +------+------------+-----------+------------+------------+---------+-----------+-------------+ | id | first_name | last_name | start_date | end_date | salary | city | description | +------+------------+-----------+------------+------------+---------+-----------+-------------+ | 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer | | 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester | | 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester | | 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager | | 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester | | 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester | | 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager | | 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester | +------+------------+-----------+------------+------------+---------+-----------+-------------+ 8 rows in set (0.00 sec) mysql> mysql> mysql> mysql> DELIMITER $$ mysql> mysql> CREATE PROCEDURE myProc(in_id INT)

   ->     MODIFIES SQL DATA
   -> BEGIN
   ->     DECLARE l_employee_id INT;
   ->     DECLARE l_salary      NUMERIC(8,2);
   ->     DECLARE l_new_salary  NUMERIC(8,2);
   ->     DECLARE done          INT DEFAULT 0;
   ->
   ->     DECLARE cur1 CURSOR FOR
   ->             SELECT id, salary
   ->               FROM employee
   ->              WHERE id=in_id;
   ->
   ->     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
   ->
   ->     CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises
   ->       (employee_id INT, new_salary NUMERIC(8,2));
   ->
   ->     OPEN cur1;
   ->     emp_loop: LOOP
   ->
   ->       FETCH cur1 INTO l_employee_id, l_salary;
   ->
   ->       IF done=1 THEN       /* No more rows*/
   ->          LEAVE emp_loop;
   ->       END IF;
   ->
   ->       set l_new_salary = 1233.12;
   ->
   ->       UPDATE employee
   ->             SET salary=l_new_salary
   ->           WHERE id=l_employee_id;
   ->          /* Keep track of changed salaries*/
   ->          INSERT INTO emp_raises (employee_id,new_salary)
   ->           VALUES (l_employee_id,l_new_salary);
   ->
   ->
   ->     END LOOP emp_loop;
   ->     CLOSE cur1;
   ->     /* Print out the changed salaries*/
   ->
   ->     SELECT id,salary from employee;
   ->
   ->     SELECT employee_id,new_salary from emp_raises
   ->      ORDER BY employee_id;
   -> END $$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> DELIMITER ; mysql> call myProc(1); +------+---------+ | id | salary | +------+---------+ | 1 | 1233.12 | | 2 | 6661.78 | | 3 | 6544.78 | | 4 | 2344.78 | | 5 | 2334.78 | | 6 | 4322.78 | | 7 | 7897.78 | | 8 | 1232.78 | +------+---------+ 8 rows in set (0.01 sec) +-------------+------------+ | employee_id | new_salary | +-------------+------------+ | 1 | 1233.12 | +-------------+------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> drop procedure myProc; 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>

CREATE TEMPORARY TABLE in a procedure

   <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.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.01 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> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()

   ->
   -> BEGIN
   ->   DROP TEMPORARY TABLE IF EXISTS employeeTemp;
   ->   CREATE TEMPORARY TABLE employeeTemp AS
   ->   SELECT id,start_date
   ->     FROM employee;
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> call myProc(); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select * from employeeTemp; +------+------------+ | id | start_date | +------+------------+ | 1 | 1996-07-25 | | 2 | 1976-03-21 | | 3 | 1978-12-12 | | 4 | 1982-10-24 | | 5 | 1984-01-15 | | 6 | 1987-07-30 | | 7 | 1990-12-31 | | 8 | 1996-09-17 | +------+------------+ 8 rows in set (0.00 sec) mysql> drop TEMPORARY TABLE employeeTemp; Query OK, 0 rows affected (0.00 sec) mysql> drop procedure myProc; 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>