1.存储过程和函数

概念

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

创建存储过程

CREATE PROCEDURE procedure_name([proc_param[,…]])    
         routine_body                                     
参数procedure_name表示所要创建的存储过程名字，
参数proc_param表示存储过程的参数，
参数routine_body表示存储过程的SQL语句代码，可以用BEGIN…END来标志SQL语句的开始和结束。

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;                                                                        
        $$                                                                           
         DELIMITER ;                                                                 
mysql>  call proc_delete_student(2);    #调用存储过程 

定义变量

DECLARE var_name[,…] type [DEFAULT value]   
关键字DECLARE是用来声明变量的；
参数var_name是变量的名称，可以同时定义多个变量；
参数type用来指定变量的类型；
DEFAULT value子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。    

定义变量cid，数据类型为INT型，默认值为10：
  DECLARE cid INT DEFAULT 10;

为变量赋值

1.
SET var_name=expr[,var_name=expr]…

关键字SET用来为变量赋值；
参数var_name是变量的名称；
参数expr是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

2.在MySQL中，还可以使用SELECT…INTO语句为变量赋值：
     SELECT col_name[,…] INTO var_name[,…]     
     FROM table_name WHERE condition         

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

举例：

光标的使用

查询语句可能查询出多条记录，在存储过程和函数中使用光标来逐条读取查询结果
集中的记录。

声明光标

光标必须声明在处理程序之前，并且声明在变量和条件之后。

  DECLARE cursor_name CURSOR         
    FOR select_statement;   

参数cursor_name表示光标的名称；
参数select_statement表示SELECT语句的内容

举例：

mysql>  use school;   #选择数据库school                                                                                                                         
mysql> DELIMITER $$                                                                                
mysql> create procedure  query_student (IN sid int, OUT cname varchar(128), OUT class_id  int )                                      
        BEGIN                                                  
            DECLARE cur_student CURSOR                     
                FOR SELECT name, class_id FROM  student;    
         END;                                                  
         $$                                                                           
         DELIMITER ;                                                                                                                         

光标的名称为cur_student；SELECT语句部分是从表student中查询出字段name和class_id的值。

打开光标

 OPEN cursor_name;
参数cursor_name表示光标的名称。

使用光标

 FETCH cursor_name
      INTO var_name[,var_name…];
参数cursor_name表示光标的名称；
参数var_name表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name必须在声明光标之前就定义好。

声明一个名为cur_student的光标：
mysql>  use school;   #选择数据库school                                                                                                                         
mysql> DELIMITER $$                                                                                
mysql> create procedure query_student (IN sid int, OUT cname varchar(128), OUT cid int)                                                                                    
        BEGIN                                                                             
            declare tmp_name varchar(128);    #必须定义在声明光标之前                                                                                             
            declare tmp_cid  int;                                                           
            declare  done int default 0;                                                                                                                                                
            declare cur_student CURSOR FOR SELECT name, class_id FROM  student where id = sid;                                                                                      
            declare continue handler for not found set done = 1; #将结束标志绑定到游标上                                                                             
            open  cur_student;                                                             
            select done;                                                                    
            fetch cur_student into tmp_name, tmp_cid;                                        
            select done;                                                                                      
            select tmp_name, tmp_cid;         #打印从光标中获取到的值                                                
            close cur_student;                                                              
            set cname = tmp_name, cid = tmp_cid;                                                                                                                      
         END;                                                                              
mysql>  $$                                                                                 
mysql>  DELIMITER ;                                                                                                                         

关闭光标

 CLOSE cursor_name;