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,'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 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,'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: 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)
