Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > mysql学习记录（十）--存储过程_MySQL

mysql学习记录（十）--存储过程_MySQL

WBOY
Release： 2016-05-30 17:10:55
Original
1029 people have browsed it

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)
Copy after login

Related labels：
process
source：php.cn
Previous article：mysql: unknown variable 'character-set-client=utf8'_MySQL Next article：linux mint 下mysql中文支持问题_MySQL
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
Move the content to the left by adding the Width property I have provided margins to the body. main {left margin: 200px; right margin: 200px; text a...
From 2024-04-06 22:01:35
0
3
816
The page suddenly cannot pull css or bootstrap So I'm developing a page, I did part of it yesterday and it worked great, and today I cont...
From 2024-04-06 21:58:04
0
1
800
How to trigger render (with filter and pagination) on sort method in React? Here is my Sort.js style component: <SortWrapper><SortText>SortBy</SortText...
From 2024-04-06 21:28:37
0
1
2257
Dynamically modify inline CSS styles in Javafx I'm trying to change the background image of a pane when the application is maximized. My ...
From 2024-04-06 20:57:16
0
1
487
Creating this specific design using a grid: a step-by-step guide I'm trying to create a layout similar to this image, I've tried using this grid but can't ...
From 2024-04-06 20:29:08
0
2
492
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template