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_ge
mysql学习记录（十）--存储过程(一)
