mysql学习记录（十）--存储过程_MySQL

mysql> use test1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database change

mysql> select * from emp;
+------------+----------+------+--------+
| ename      | hiredate | sal  | deptno |
+------------+----------+------+--------+
| aaaaa      | NULL     | NULL |      1 |
| cccccccccc | NULL     | NULL |      2 |
| ddddddddd  | NULL     | NULL |      3 |
| ffffff     | NULL     | NULL |      4 |
| ggg        | NULL     | NULL |      5 |
| a1         | NULL     | NULL |      5 |
+------------+----------+------+--------+
6 rows in set (0.00 sec)

mysql> show create table emp \G;
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `ename` varchar(10) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(10,2) DEFAULT NULL,
  `deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> DELIMITER &&  
mysql> CREATE  PROCEDURE  num_from_employee (IN input_deptno int, OUT count_num INT )  
    -> READS SQL DATA  
    ->     BEGIN  
    ->     SELECT  COUNT(*) FROM emp WHERE  deptno=input_deptno ;  
    -> END  &&
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> call num_from_employee(5,@a);
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> call num_from_employee(1,@a);
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> create table inventory(
    -> film_id int(11),
    -> store_id int(11),
    -> inventory_in_stock varchar(50)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into inventory(film_id,store_id,inventory_in_stock) values  (1,2,&＃39;aaaaaaaa&＃39;), (3,4,&＃39;bbbb&＃39;), (5,6,&＃39;cccccccccc&＃39;), (7,8,&＃39;dddddd&＃39;), (9,10,&＃39;fff&＃39;);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> select * from inventory;
+---------+----------+--------------------+
| film_id | store_id | inventory_in_stock |
+---------+----------+--------------------+
|       1 |        2 | aaaaaaaa           |
|       3 |        4 | bbbb               |
|       5 |        6 | cccccccccc         |
|       7 |        8 | dddddd             |
|       9 |       10 | fff                |
+---------+----------+--------------------+
5 rows in set (0.00 sec)

mysql> delimiter $$
mysql> create procedure film_in_stock(in p_film_id int,in p_store_id int,out p_film_count int) 
    -> reads sql data
    -> begin
    ->   select film_id
    ->   from inventory
    ->   where film_id = p_film_id
    ->   and store_id = p_store_id;
    ->   select found_rows() into p_film_count;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call film_in_stock(5,6,@a);
+---------+
| film_id |
+---------+
|       5 |
+---------+
1 row in set (0.01 sec)

Query OK, 1 row affected (0.01 sec)

mysql> show create procedure film_in_stock \G;
*************************** 1. row ***************************
           Procedure: film_in_stock
            sql_mode: 
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `film_in_stock`(in p_film_id int,in p_store_id int,out p_film_count int)
    READS SQL DATA
begin
  select film_id
  from inventory
  where film_id = p_film_id
  and store_id = p_store_id;
  select found_rows() into p_film_count;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> create table actor(
    -> actor_id int(11)  NOT NULL AUTO_INCREMENT ,  
    -> first_name varchar(30),
    -> last_name varchar(30),
    ->   PRIMARY KEY (actor_id)  
    -> ) engine = innodb charset = utf8;
Query OK, 0 rows affected (0.02 sec)


mysql> delimiter $$
mysql> create procedure actor_insert()
    -> begin 
    -> set @x = 1;
    -> insert into actor(actor_id,first_name,last_name) values (201,&＃39;Test&＃39;,201);
    -> set @x = 2;
    -> insert into actor(actor_id,first_name,last_name) values(1,&＃39;Test&＃39;,&＃39;1&＃39;);
    -> set @x = 3;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> call actor_insert();
Query OK, 0 rows affected (0.02 sec)

mysql> call actor_insert();
ERROR 1062 (23000): Duplicate entry &＃39;201&＃39; for key &＃39;PRIMARY&＃39;
mysql> select @x;
+------+
| @x   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> delimiter $$
mysql> create procedure actor_insert_new()
    -> begin 
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE &＃39;23000&＃39; SET @x2 = 1;
    -> set @x = 1;
    -> insert into actor(actor_id,first_name,last_name) values (201,&＃39;Test&＃39;,201);
    -> set @x = 2;
    -> insert into actor(actor_id,first_name,last_name) values(1,&＃39;Test&＃39;,&＃39;1&＃39;);
    -> set @x = 3;
    -> end $$
Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

mysql> call actor_insert_new();
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> call actor_insert_new();
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @x,@x2;
+------+------+
| @x   | @x2  |
+------+------+
|    3 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> show create table payment \G;
*************************** 1. row ***************************
       Table: payment
Create Table: CREATE TABLE `payment` (
  `staff_id` int(11) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql> select * from payment;
+----------+--------+
| staff_id | amount |
+----------+--------+
|        1 |  10000 |
|        2 |  20000 |
|        3 |  30000 |
|        4 | 400000 |
|        5 | 500000 |
+----------+--------+
5 rows in set (0.01 sec)

mysql> delimiter $$
mysql> create procedure payment_stat()
    -> begin
    -> DECLARE i_staff_id int;
    -> DECLARE d_amount int;
    -> declare tmp_name varchar(30) default "";
    -> DECLARE cur_payment cursor for select staff_id,amount from payment;
    -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
    -> 
    -> set @x1 = 0 ;
    -> set @x2 = 0 ;
    -> 
    -> open cur_payment;
    -> fetch cur_payment into i_staff_id,d_amount;
    -> while(i_staff_id <=3  )
    -> do
    ->     if i_staff_id <3 then 
    ->         select i_staff_id,d_amount;
    ->     end if;
    -> fetch cur_payment into  i_staff_id,d_amount;
    -> end while;
    -> close cur_payment;
    -> 
    -> select @x1,@x2;
    -> end;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> call  payment_stat();
+------------+----------+
| i_staff_id | d_amount |
+------------+----------+
|          1 |    10000 |
+------------+----------+
1 row in set (0.01 sec)

+------------+----------+
| i_staff_id | d_amount |
+------------+----------+
|          2 |    20000 |
+------------+----------+
1 row in set (0.01 sec)

+------+------+
| @x1  | @x2  |
+------+------+
|    0 |    0 |
+------+------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> drop  procedure payment_stat;
Query OK, 0 rows affected (0.00 sec)


mysql> delimiter $$
mysql> create procedure payment_stat()
    -> begin
    -> DECLARE i_staff_id int;
    -> DECLARE d_amount int;
    -> declare tmp_name varchar(30) default "";
    -> DECLARE cur_payment cursor for select staff_id,amount from payment;
    -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
    -> 
    -> set @x1 = 0 ;
    -> set @x2 = 0 ;
    -> 
    -> open cur_payment;
    -> fetch cur_payment into i_staff_id,d_amount;
    -> while(i_staff_id <=3  )
    -> do
    ->     if i_staff_id <3 then 
    ->         set @x1 = @x1+ i_staff_id;
    ->     else
    ->         set @x2 = @x2+ d_amount;
    ->     end if;
    -> fetch cur_payment into  i_staff_id,d_amount;
    -> end while;
    -> close cur_payment;
    -> 
    -> select @x1,@x2;
    -> end;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> call  payment_stat();
    -> $$
+------+-------+
| @x1  | @x2   |
+------+-------+
|    3 | 30000 |
+------+-------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)


mysql> delimiter $$
mysql> create procedure payment_stat()
    -> begin
    -> DECLARE i_staff_id int;
    -> DECLARE d_amount int;
    -> 
    -> DECLARE cur_payment cursor for select staff_id,amount from payment;
    -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
    -> 
    -> set @x1 = 0 ;
    -> set @x2 = 0 ;
    -> 
    -> open cur_payment;
    -> fetch cur_payment into i_staff_id,d_amount;
    -> while(i_staff_id <=3  )
    -> do
    ->     if i_staff_id <3 then 
    ->         set @x1 = @x1+ i_staff_id + 1;
    ->     else
    ->         set @x2 = @x2+ d_amount ;
    ->     end if;
    -> fetch cur_payment into  i_staff_id,d_amount;
    -> end while;
    -> close cur_payment;
    -> 
    -> select @x1,@x2;
    -> end;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call  payment_stat();
+------+-------+
| @x1  | @x2   |
+------+-------+
|    3 | 30000 |
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> drop  procedure payment_stat;
Query OK, 0 rows affected (0.00 sec)


mysql> delimiter $$
mysql> create procedure payment_stat()
    -> begin
    -> DECLARE i_staff_id int;
    -> DECLARE d_amount int;
    -> 
    -> DECLARE cur_payment cursor for select staff_id,amount from payment;
    -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
    -> 
    -> set @x1 = 0 ;
    -> set @x2 = 0 ;
    -> 
    -> open cur_payment;
    -> fetch cur_payment into i_staff_id,d_amount;
    -> while(i_staff_id <=3  )
    -> do
    ->     if i_staff_id <3 then 
    ->         set @x1 = @x1+ i_staff_id;
    ->     else
    ->         set @x2 = @x2+ d_amount;
    ->     end if;
    -> fetch cur_payment into  i_staff_id,d_amount;
    -> end while;
    -> close cur_payment;
    -> 
    -> select @x1,@x2;
    -> end;
    -> $$
Query OK, 0 rows affected (0.00 sec)


mysql> delimiter ;
mysql> call  payment_stat();
+------+-------+
| @x1  | @x2   |
+------+-------+
|    3 | 30000 |
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> create procedure payment_stat()
    -> begin
    -> DECLARE i_staff_id int;
    -> DECLARE d_amount int;
    -> 
    -> DECLARE cur_payment cursor for select staff_id,amount from payment;
    -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
    -> 
    -> set @x1 = 0 ;
    -> set @x2 = 0 ;
    -> 
    -> open cur_payment;
    -> fetch cur_payment into i_staff_id,d_amount;
    -> while(i_staff_id <=3  )
    -> do
    ->     if i_staff_id <3 then 
    ->         set @x1 = @x1+ i_staff_id + 1;
    ->     else
    ->         set @x2 = @x2+ d_amount;
    ->     end if;
    -> fetch cur_payment into  i_staff_id,d_amount;
    -> end while;
    -> close cur_payment;
    -> 
    -> select @x1,@x2;
    -> end;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call  payment_stat();
+------+-------+
| @x1  | @x2   |
+------+-------+
|    5 | 30000 |
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> drop  procedure payment_stat;
Query OK, 0 rows affected (0.02 sec)


mysql> delimiter $$
mysql> create procedure payment_stat()
    -> begin
    -> DECLARE i_staff_id int;
    -> DECLARE d_amount int;
    -> 
    -> DECLARE cur_payment cursor for select staff_id,amount from payment;
    -> DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment;
    -> 
    -> set @x1 = 0 ;
    -> set @x2 = 0 ;
    -> 
    -> open cur_payment;
    -> fetch cur_payment into i_staff_id,d_amount;
    -> while(i_staff_id <=3  )
    -> do
    ->     if i_staff_id <3 then 
    ->         set @x1 = @x1+ i_staff_id + 6;
    ->     else
    ->         set @x2 = @x2+ d_amount + 5;
    ->     end if;
    -> fetch cur_payment into  i_staff_id,d_amount;
    -> end while;
    -> close cur_payment;
    -> 
    -> select @x1,@x2;
    -> end;
    -> $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

mysql> call  payment_stat();
   
+------+-------+
| @x1  | @x2   |
+------+-------+
|   15 | 30005 |
+------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER $$
mysql> 
mysql> CREATE PROCEDURE addNum()
    -> BEGIN
    -> DECLARE x  INT;
    -> SET x = 0;
    -> for_loop :  LOOP
    ->   SET  x = x + 1;
    ->   IF  x > 30 THEN
    ->    LEAVE  for_loop;
    ->   END IF;
    ->   IF mod(x,2) = 0 then 
    ->     select "num:",x;
    ->   ITERATE for_loop;
    ->   END IF;
    -> END LOOP;
    -> select "count:",x;
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> call addNum();
    -> $$
+------+------+
| num: | x    |
+------+------+
| num: |    2 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |    4 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |    6 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |    8 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   10 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   12 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   14 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   16 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   18 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   20 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   22 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   24 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   26 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   28 |
+------+------+
1 row in set (0.00 sec)

+------+------+
| num: | x    |
+------+------+
| num: |   30 |
+------+------+
1 row in set (0.00 sec)

+--------+------+
| count: | x    |
+--------+------+
| count: |   31 |
+--------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter $$
mysql> create procedure repeatPractise()
    ->      begin
    ->      set @v = 0 ;
    ->      REPEAT 
    ->         set @v =  @v+ 1;
    ->      UNTIL @v >=5  
    ->      END REPEAT;
    ->  END 
    ->  $$
Query OK, 0 rows affected (0.01 sec)

mysql> call repeatPractise();
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> select @v;
    -> $$
+------+
| @v   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)