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)
版权声明：本博客原创文章欢迎转载，请转载的朋友最好注明出处，谢谢大家。