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)
版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。
欢迎大家阅读《mysql 学习记要（十）-存储过程_mysql》,跪求各位点评，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mysql 学习记要（十）-存储过程_mysql
转载请注明原文链接：mysql 学习记要（十）-存储过程_mysql
