|
neral_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> delimit |