Mysql learning record (10) -- Stored Procedure _ MySQL

Mysqlusetest1; ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangemysql

mysql> use test1;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changemysql> 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: empCreate 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=utf81 row in set (0.01 sec)ERROR: No query specifiedmysql> 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,'aaaaaaaa'), (3,4,'bbbb'), (5,6,'cccccccccc'), (7,8,'dddddd'), (9,10,'fff');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 DATAbegin  select film_id  from inventory  where film_id = p_film_id  and store_id = p_store_id;  select found_rows() into p_film_count;endcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci1 row in set (0.01 sec)ERROR: No query specifiedmysql> 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,'Test',201);    -> set @x = 2;    -> insert into actor(actor_id,first_name,last_name) values(1,'Test','1');    -> 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 '201' for key 'PRIMARY'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 '23000' SET @x2 = 1;    -> set @x = 1;    -> insert into actor(actor_id,first_name,last_name) values (201,'Test',201);    -> set @x = 2;    -> insert into actor(actor_id,first_name,last_name) values(1,'Test','1');    -> 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: paymentCreate Table: CREATE TABLE `payment` (  `staff_id` int(11) DEFAULT NULL,  `amount` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.01 sec)ERROR: No query specifiedmysql> 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)