MySQL - 存储过程和函数

存储过程和函数

• 存储过程和函数是在数据库中定义的一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户器端和服务端的数据传输。

1. 创建存储过程
   创建存储过程和函数是指将经常使用的一组SQL语句组合在一起，并将这些SQL语句当作一个整体存储在MySQL服务器中。存储程序可以分为存储过程和函数。在MySQL中创建存储过程使用的语句CREATE PROCEDURE。其语法形式如下：

   CREATE PROCEDURE procedure_name([proc_param[,…]])    
            routine_body   
   

   在上述语句中，
   参数procedure_name表示所要创建的存储过程名字，
   参数proc_param表示存储过程的参数，
   参数routine_body表示存储过程的SQL语句代码，可以用BEGIN…END来标志SQL语句的开始和结束。
   提示: 在具体创建存储过程时，存储过程名不能与已经存在的存储过程名重名，实战中推荐存储过程名命名为procedure_xxx或者proc_xxx。
   proc_param中每个参数的语法形式如下：

   [IN|OUT|INOUT] param_name type
   

   在上述语句中，每个参数由三部分组成，分别为输入/输出类型、参数名和参数类型。其中，输入/输出类型有三种类型，分别为IN（表示输入类型）、OUT（表示输出类型）、INOUT（表示输入/输出类型）。param_name表示参数名；type表示参数类型，可以是MySQL软件所支持的任意一个数据类型。

   mysql>  use school;   #选择数据库school                                             
   mysql> DELIMITER $$                                                                                
   mysql> create PROCEDURE  proc_delete_student (IN sid int )                                   
       BEGIN 
        declare cid  int ;   #定义变量cid                            
        Select class_id into cid from student where id = sid;    #通过查询语句设置变量                                                                       
        delete from grade where id = sid;  #删除成绩表中的记录  
        delete from student where id = sid;   #删除学生表中的记录                                                    
        update class set count=count-1 where id = cid; #更新班级表中的记录   
       END;                                                                        
       $$                          
   

   在存储过程中使用变量
   在存储过程和函数中，可以定义和使用变量。用户可以使用关键字DECLARE来定义变量，然后为变量赋值。这些变量的作用范围是在BEGIN…END程序段中。
   (1). 定义变量
   在MySQL中，可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

   DECLARE var_name[,…] type [DEFAULT value] 
    
   如:定义了int类型变量a1,a2,a3, 初始值为6
   declare a1, a2, a3 int DEFAULT 6;
   

   其中，关键字DECLARE是用来声明变量的；参数var_name是变量的名称，可以同时定义多个变量；参数type用来指定变量的类型；DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。
   (2). 为变量赋值
   在MySQL中可以使用关键字SET来为变量赋值，SET语句的基本语法如下：

      SET var_name=expr[,var_name=expr]…
      
      如:将变量tmp_id赋值为88
      declare set tmp_id=88;
   

   其中，关键字SET用来为变量赋值；参数var_name是变量的名称；参数expr是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。
   在MySQL中，还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：

   SELECT col_name[,…] INTO var_name[,…]     
        FROM table_name WHERE condition         
   

   其中，参数col_name表示查询的字段名称；参数var_name是变量的名称；参数table_name指表的名称；参数condition指查询条件。

   //创建存储过程, 输入一个学生的id,输出学生的班级名称和班级人数
   mysql> create procedure proc_select_student(IN sid int, OUT cname varchar(64), OUT ccount int)
       -> 	begin
       -> 		declare tmp_name varchar(