存储过程和函数

1.创建存储过程和函数

1.1创建存储过程

     create procedure sp_name([proc_parameter[,...]]) [characteristic ...] routine_body

     sp_name :存储过程名称

     proc_parameter ：存储过程参数列表

     每个参数由三部分组成：输入输出类型+参数名称+参数类型：[in|out|inout] param_name type

     characteristic ：指定存储过程特性,有多个取值：

1.      language sql :说明routine_body部分是由SQL语言的语句组成（默认）
2.      [not] deterministic :指明执行结果是否确定（默认确定）
3.      {contains sql|no sql|reads sql data|modifies sql data} :指明子程序使用SQL语句的限制。

              contains sql：包含SQL语句，但不包含读或写数据的语句（默认）

              no sql ：不包含SQL语句

              reads sql data ：包含读数据的语句

              modifies sql data ：包含写数据语句

      4.     sql security{definer|invoker} :指明谁有权限来执行。

              definer :只有定义者自己（默认）

              invoker :调用者  

      5.     comment 'string' :注释信息 

     routine_body ：SQL代码的内容，可用 begin ... end 来标志SQL代码的开始和结束

    例：

   delimiter &&
   create procedure num_from_employee(in emp_id int,out count_num int) reads sql data
   begin
       select count(*) into count_num from employee where d_id=emp_id;
   end&&
   delimiter ;

1.2创建存储函数

     create function sp_name([func_parameter[,...]]) returns type [characteristic...] routine_body

     sp_name :存储函数名称

     func_parameter ：存储函数参数列表，每个参数由参数名称和参数类型组成：param_name type

     returns type ：返回值类型

     characteristic ：存储函数特性，与上述存储过程中参数取值相同

     routine_body ：SQL代码的内容，可用 begin ... end 来标志SQL代码的开始和结束

    例：

   delimiter &&
   create function name_from_employee(emp_id int) returns varchar(20)
   begin
       return (select name from employee where num=emp_id);
   end&&
   delimiter ;

1.3变量的使用

     作用范围：begin ... end 程序段中

（1）定义变量

      declare var_name[,...] type [default value]

（2）为变量赋值

      set var_name=expr[,var_name=expr]...

 或 select col_name[,...] into var_name[,...] from 表名 where condition

1.4定义条件和处理程序

（1）定义条件

      declare condition_name condition for condition_value

      其中，condition_value有如下两种取值：

                  sqlstate[value] sqlstate_value|mysql_error_code

       condition_name:参数名称

       condition_value:条件类型

       sqlstate_value和mysql_error_code都可表示mysql的错误。!!例：error 1146(42s02)中，sqlstate_value 值                                                                                                                   为42s02,mysql_error_code值为1146

例：

declare can_not_find condition for sqlstate '42s02'
或
declare can_not_find condition for 1146

（2）定义处理程序

           declare handler_type handler for condition_value[,...] sp_statement

       其中，handler_type:    !!指明错误处理方式

                        continue | exit | undo   不处理继续向下执行||遇到错误马上退出||遇到错误撤回之前的操作（不支持）

                  condition_value:

                        sqlstate[value] sqlstate_value|condition_name|sqlwarning|not found|sqlexception|mysql_error_code

                          condition_name : declare定义的条件名称

                          sqlwarning :所有以01开头的sqlstate_value值

                          not found :所有以02开头的sqlstate_value值

                          sqlexception :所有没被sqlwarning或not found捕获的sqlstate_value值

                   sp_statement :表示一些存储过程或函数的执行语句

例：

//方法一：捕获sqlstate_value
declare continue handler for sqlstate '42s02' set @info='can not find'
//方法二：捕获mysql_error_code
declare continue handler for 1146 set @info='can not find'
//方法三：先定义条件，然后调用
declare can_not_find condition for 1146;
declare continue handler for can_not_find set @info='can not find';
//方法四：使用sqlwarning
declare exit handler for sqlwarning set @info='error';
//方法五：使用not found
declare exit handler for not found set @info='can not find';
//方法六：使用sqlexception
declare exit handler for sqlexception set @info='error';

1.5光标的使用

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

     （1）声明光标

            declare cursor_name cursor for select_statement;

             cursor_name :光标名称

             select_statement :select语句内容

      例：

   declare cur_employee cursor for select name,age from employee;

      （2）打开光标

             open cursor_name;

       例：

   open cur_employee;

     （3）使用光标

             fetch cursor_name into var_name[,var_name...];

                var_name :将光标中的select语句查询出来的信息存入该参数中。!!必须在声明光标之前就定义好

       例：

       fetch cur_employee into emp_name,emp_age;

     （4）关闭光标

              close cursor_name;

              关闭之后就不能使用fetch来使用光标了。

!!若存储过程或函数中执行select语句，并且select语句会查询出多条记录。这种情况最好使用光标来逐条读取记录。光标必须声明在处理程序之前，变量和条件之后。!!光标使用完后一定要关闭。

1.6流程控制的使用

（1）if 语句

       if search_condition then statement_list

           [elseif search_condition2 then statement_list2]...

           [else statement_list]

       end if

 例：

if age>20 then set @count1=@count1+1;
    elseif age=20 then @count2=@count2+1;
    else @count3=@count3+1;
end if;

（2）case 语句

      case case_value

             when when_value then statement_list

             [when when_value then statement_list]...

             [else statement_list]

      end case

或

       case

          when search_condition then statement_list

          [when search_condition then statement_list]...

          [else statement_list]

       end case

例：

case age 
    when 20 then set @count1=@count1+1;
    else set @count3=@count3+1;
end case;
或
case 
    when age=20 then set @count1=@count1+1;
    else set @count3=@count3+1;
end case;

（3）loop 语句

        本身没有停止循环的语句，必须是遇到 leave 语句等才能停止。

      [begin_label:] loop

                 statement_list

      end loop [end_label]

例：

add_num:loop
       set @count=@count+1;
end loop add_num;
!!该循环为死循环

（4）leave 语句                                                       !!类似于 break

       leave label     !!用于跳出循环控制

例：

add_num:loop
       set @count=@count+1;
       if @count=100 then
             leave add_num;
end loop add_num;

（5）iterate 语句                                                      !!类似于 continue

       iterate label      !!用于跳出本次循环

（6）repeat 语句                                                      !!类似于 do...while...

    有条件控制的循环语句

        [begin_label:] repeat

                statement_list

                until search_condition

        end repeat [end_label]

例：

add_num:repeat
       set @count=@count+1;
       until @count=100
end repeat add_num;

（7）while 语句                                                       !!类似于 while...do...

        [begin_label:] while search_condition do

               statement_list

        end while [end_label]

例：

while @count<100 do
       set @count=@count+1;
end while;

2.调用存储过程和函数

2.1调用存储过程

      call sp_name([parameter[,...]]);

例：

//创建存储过程<pre name="code" class="sql">delimiter &&
   create procedure num_from_employee(in emp_id int,out count_num int) reads sql data
   begin
       select count(*) into count_num from employee where d_id=emp_id;
   end&&
delimiter ;

//调用存储过程
call num_from_employee(1002,@n);

2.2调用存储函数

例：

   //创建存储函数
   delimiter &&
   create function name_from_employee(emp_id int) returns varchar(20)
   begin
       return (select name from employee where num=emp_id);
   end&&
   delimiter ;
//调用存储函数
select name_from_employee(3);

3.查看存储过程和函数

3.1 show status 语句

        show {procedure | function} status [like 'pattern'];

例：

show procedure status like 'num_from_employee'\G

3.2 show create 语句

        show create{procedure | function} sp_name;

例：

show create procedure num_from_employee \G

3.3 从 information_schema.Routines 表中查看存储过程和函数信息

        select * from information_schema.Routines where routine_name='sp_name';

4.修改存储过程和函数

        alter {procedure | function} sp_name [characteristic...]

        characteristic:

             {contains sql | no sql | reads sql data | modifies sql data}

             | sql security{definer | invoker}

             | comment 'string'

例：

alter procedure num_from_employee modifies sql data sql security invoker;

alter function name_from_employee reads sql data comment 'find name';

5.删除存储过程和函数

        drop {procedure | function} sp_name;