mysq存储过程学习笔记

一个存储过程的实例:
 create procedure  procedure1 /*存储过程名*/
 (IN parameter1 INTEGER)      /*参数*/
 
 BEGIN
   DECLARE variable1 CHAR(10);  /*变量声明*/
   IF parameter1 =17 THEN       /*IF条件开始*/
      SET variable1='birds';    /*赋值*/
   ELSE 
      SET variable1='beasts';   /*赋值*/
   END IF;
   INSERT INTO table1 VALUES (variable); /* statement SQL语句*/
 END                            /*语句块结束*/






查看mysql版本信息  \s
 
选择分隔符
  DELIMITER //
  DELIMITER ;//
存储过程简单实例
create procedure p1() select * from t;//


如果代码中包含mySql的扩充功能，那么代码将不能移植


调用存储过程
 call p1 ()//




 特征子句
   create procedure p2 ()
    language sql              //说明下面语句使用sql语言
    not deterministic         //传递给系统的信息,每次输入一样输出也一样
    sql security definer      //创建过程用户的权限（如果过程已被调用，
                                 就不检查 
    (或是sql security invoker) //当过程已经被调用，仍要检查调用者的权限
    comment 'A Procedure'      //注释说明
    select current_date,rand () from t;
 特征子句的默认值:
    language sql
    not determinstic
    sql security definer
    comment


 mysql在过程创建军时会自动保持运行环境






parameters 参数
   示例代码如下：
   create procedure p5 ()
   create procedure p5 ([in] name data-type) //可选input 输入参数 
   create procedure p5 (out name data-tpye)  //输出参数
   create procedure p5 (inout name data-type) //即能输出又能输入






 in example  输入的例子
   crueat procedure p5 (p int) set @x=p//
    
   call p5 (12345)//


   select @x//






 out example 输出的例子
   create procedure p6 (out p int) set p=-5//
   
    call p6 (@y)//
   
    select @y //




 复合语句compund statements
  create procedure p7 ()
    begin
       set @a=5;
       set @b=5;
       insert into t values (@a);
       select s1 * @a from t where s1>=@b;
    end;//




新sql语句
   variables 变量: 在复合语句中声明变量的指令是declare
    示例代码如下： (没有默认子句)
    create procedure p8 ()
    begin
       declare a int;    /*没有默认值,初始值为null*/
       declare b int;
       set a=5;
       set b=5;
        insert into t values (a);
        select s1*a from t where s1>=b;
       end;//
     


     示例代码：（含有默认子句）
      create procedure p10 ()
       begin
       declare a,b int default 5; /*设置默认值*/ 
       insert into t values (a);
       select sl*a from t where sl>=b;
       end;//  


     
      scope作用域
      create procedure p11 ()
         begin
            declare x1 char(5) default 'outer';
             begin
                declare x1 char(5) default 'inner';
                select x1;
             end;
             select x1;
          end;//
 


 


     条件表达示 if then  else
  
   示例代码：
      create procedure p12 (in parameter1 int)
        begin
          declare varlable1 int;
          set varlable1=parameter1+1;
          if varlable1=0 then
             insert into t values (17);
          end if;
          if parameter1=0 then
             update t set sl=sl+1;
          else
             update t set sl=sl+2;
          end if;
        end;//




  
     case 指令
       示例代码如下:
       create procedure p13 (in parameter int)
          begin
            declare variable1 int;
            set variable1=parameter1+1;
            case variable1
                when 0 then 
                      insert into t values (17);
                when 1 then 
                      insert into t values (18);
                else 
                      insert into t values (19);
             end case;
          end;//
     
      
     loops循环语句
        三种标准循环:
        while   end  while
                   示例代码如下：
                      create procedure p14()
                         begin
                            declare v int;
                            set v=0;
                            while v<5 do
                               insert into t values(v);
                               set v=v+1;
                            end while;
                          end;//
        loop   end  loop
                     示例代码如下：
                       create procedure p16 ()
                          begin
                            declare v int;
                            set v=0;
                            loop_label:loop
                                insert into t values (v);
                                set v=v+1;
                                if v>=5 then
                                leave loop_label;
                                end if;
                            end loop;
                           end;//
        repeat  end  repeat
                     示例代码如下:
        create procedure p15 ()
                            begin
                              declare v int;
                              set v=0;
                              repeat
                                  insert into t values (v);
                                  set v=v+1;
                                  vntil v>=5;
                               end repeat;
                             end;//






               标号  labels
                 示例代码如下： 
                     create procedure p17 ()
                        label_1:begin
                            label_2:while 0=1 do 
                                       leave label_2;
                                    end while;
                            label_3:repeat
                                       leave label_3;
                                       until 0=0
                                    end repeat;
                            label_4:loop leave label_4;end loop;
                         end;//
                             
              标号码结束符  end labels
                  create procedure p18 ()
                     label_1:begin
                        label_2:while 0=1 do
                                     leave label_2;
                                end while label_2;
                        label_3:repeat 
                                   leave label_3;
                                until 0=0
                                end repeat label_3;
                        label_4:loop 
                                   leave label_4;
                                end loop label_4;
                      end label_1;   


             跳出标号 leave labels
                 示例代码如下：
                    create procedure p19 (parameter1 char)
                       label_1:begin
                           label_2:begin
                              label_3:begin
if parameter1 is not null then
                                     if parameter1='a' then
                                          leave label_1;
                                     else begin
                                         if parameter1='b' then
                                           leave label_2;
                                          else
                                           leave label_3;
                                         end if;
                                        end;
                                     end if;
                                 end if;
                              end; 
                           end;
                       end; 
         
              迭代 iterate
                 create procedure p20 ()
                   begin
                     declare v int;
                     set v=0;
                     loop_label:loop
                        if v=3 then
                          set v=v+1;
                          iterater loop_label; 
                          end if;
                          insert into t values (v);
                          set v=v+1;
                          if v>=5 then
                             leave loop_label;
                             end if;
                       end loop;
                     end;//                  
                       
        非标准循环：goto
            示例代码如下：
            create procedure 过程名
                begin
                   label label_name;
                   goto label_name;
                end; 
        
            大组合
              create procedure p21
                (in parameter_1 int,out parameter_2 int)
                  language sql deterministic
                  sql security invoker
                  begin
                    declare v int;
                  label goto_label;
                        start_label:loop
                               if v=v then
                                   leave start_label;
                               else 
                                   iterate start_label;
                               end if;
                         end loop start_label;
                  repeat
                      while 1=0 do
                           begin end;
                      end while;
                      until v=v end repeat;
                  goto goto_label;
                  end;//
          
      


 异常处理
     故障记录
     
     示例代码如下：建立两个关连表
        create table t2 (sl int,primary key (sl))
        engine=innodb;//  /*事务安全数据库引擎*/
        create table t3 (sl int,key (sl),froeign key (sl) references t2 (sl)) engine=innodb;//
       
         错误信息表：(存放错误信息的表)
             create table error_log(error_message char(80))//
  


      create procedure p22 (parameter int)
         begin
            declare exit handler for 1452
            insert into error_log values (concat('Time:',current_date,'.Foreign Key Reference Failure For Value=',parameter1));
            insert into t3 values (parameter1);
         end;//


     
      声明异常处理的语法
        declare
           {exit|continue}
           handler for
           {error-number|{sql state error-string}|condition}
           sql statement


     处理示例代码：
        create table t4 (sl int,primary key (sl));//
   
        create procedure p23 ()
            begin
               declare continue handler
               for sqlstate '23000' set @x2=1;  /*当外键约束出错或主键约束出错就被调用*/
               set @x=1;
               insert into t4 values (1);
               set @x=2;
               insert into t4 values (1);
               set @x=3;
             end;//
         


  
       自定义错误处理的名字
         create procedure p24 ()
              begin
                 declare 'Constaint Violation'
                  condition for sqlstate '23000';
                 declare exit handler for 'Constaint Violation' rollback;
                 state transaction;
                   insert into t2 values (1);
                   insert into t2 values (1);
                 commit;
              end;//
      


    
  游标 curaors
      declar cursor-name cursor for select * from 表名 ;
      open cursor-name;
      fetch cursor-name into variable[,variable];
      close cursor-name;
    
 
    示例代码如下：
        create procedure p25 (out return_val int)
           begin
             /* 声明变量*/
             declare a,b int;
             /* 声明游标致*/
             declare cur_1 cursor for select s1 from t;
             /* 声明错误处理器*/
             declare continue handler for not found 
               set b=1;
              open cur_1;    /*返回一个结果集*/
              repeat
                 fetch cur_1 into a;
                 until b=1
              end repeat;
                close cur_1;
              set return_val=a;
            end;//
              




        游标的特性  cursor characteristice 
          read only 只读
          not scrollable 顺序读取
          asensitive  敏感




 
        可以从游标中取值，但不能对其更新
         如果想完成更新动作
          fetch cursor1 into variable1;
          update t1 set column1='value1' where current cursor1;


       security 安全措施


/* 可以创建存储过程、函数、视图和表*/
        grant create routine
             on database-name * to user(s) [with grant option]
    
      /* 是否可以使用和执行存储过程的特权,过程创建者默认有这个特权*/
       grant execute on p to peter [with grant option]
      /* 控制视图的特权*/
       grant show routine on db6.* to joey [with grant option]




    特权调用者和定义者


     示例代码如下：
      create procedure p26()
         sql secrity invoker
         select count(*) from t//
      
      create procedure p27()
         sql security definer
         select count(*) from t//
 
      grant insert on db5.* to peter;//


 
    function 函数


     创建函数
     create function factorial (n decimal(3,0)) 
        returns decimal (20,0)
        deterministic
      begin
        declare factorial decimal(20,0) default 1;
        declare counter decimal(3,0);
        set counter =n;
        factorial_loop:repeat
              set factorial=factorial*counter;
              set counter =counter -1;
           until counter =1;
        end repeat;
       return factorial;
      end;//
   


    注：不能在函数中访问表
      示例代码：
        insert into t values (factorial(pi))//
        select sl,factorial (sl) from t//
        update t set sl = factorial (sl)
        where factorial (sl) <5//
    
     限制
     不能进行数据操作、数据描述、特权转化、或事物控制
 
     元数据
  显示元数据有以下四个方法:
    两种show方法
     /*显示存储过程的元信息*/
     show create procedure   
        示例代码：
          show create procedure 过程名//
     /*显示更多元数据*/ 
     show procedure status  
        示代代码：
           show procedure stats like 'p6'//   
  
     show create function
     show function status




   两种select方法 (可以计算表达示,分组，排序，产生可以取得信息的结果集)
     select from mysql.proc
      示例代码：
       select * from mysql.proc where name='过程名';
     select from information_schema
       示例代码：
       select table_name,column_name,column_type from information_schema.columns where table_name=routines;//


       查看数据库中的存储过程
         示例代码如下；
         select count(*) from information_schema.routines
           where routine_schema='db6';//
       
     
      列的访问控制
         /*如果使用它的用户 不是创建他的用户，将返回空值*/
         select count(*) from information_scheama.routines.definer
      


   其他需要注意的细节
        /*修改*/
        alert procedure p6 comment 'Unfinished'//
        /*删除*/ 
        drop procedure p6//




   与oracle 的比较
     常见区别：
       oracle 允许在打开后再声明
       mysql必须在开始的时候声明
  
       oracle 对游标的声明：cursor 游标名 is
       mysql 只能用 declare 游标名 cursor
 
       oracle不强制需要(),mysql必须有()
    
       oracle允许在函数中访问表元素
       mysql不允许在函数中访问表元素
    
       oracle支持package ,mysql不支持






   oracle 


      create procedure  return;


   mysql


      create procedure()  
         label_at_start:begin
              leave label_at_start;
         end;//
         


 
 


       平行比较
         oracle                       mysql
     create procedure             create procedure 
       sp_name                       sp_name ()
       as                            begin
       variabel integer              declare variabel integer;
       variabel :=55                 set variabel =55;
       end                           end
    


      与sql server 的对比
        sql server 必须以@ 开头
        mysql 是常规标识符
 
       sql server 可以同时进行多个声明 declare v1 int, v2 int
       mysql 只允许声明一个 declare v1; declare v2;
 
       sql server  中没有begin end 
       mysql 中必须有
   
       sql server 不须要;结束
       mysql  中除了最后一然，必须以;作为语句结束标志
 
       sql server 可以进行set nocount 和if @@rowcount判断
       mysql可以使用found_rows()进行判断


       sql server中使用while begin
       mysql 中使用while do


       sql server中允许使用select 进行指派
       mysql 只允许set 
   
       sql server 中允许在函数中访问表
       mysql 不允许
 


 
       平行对比
         sql server                          mysql
     create procedure                    create procedure  
       sp_name                             sp_name
       as                                  begin
         declare @x varchar(100)             declare v_x varchar(100);


         excute sp_name @x                   call sp_name (v_x);
           declare c cursor for               select * from t;
           declare c cursor for            end
           select * from t;
       end


       


    与db2 的对比( 未看)


   
 
    编程风格   
       CREATE PROCEDURE p()
         BEGIN
            UPDATE t SET sl=5;
            ...
         END;//    
       


    字符串连接的函数
    create procedure tables_concat(out parameter1 varchar(100))
    begin
       declare variable2 char(100);
       declare c cursor for
           select table_name frominformation_schema.tables;
       declare exit handler for not found begin end;
       set sql_mode='ansi';
       set parameter1='';
       open c;
         loop
            fetch c into variable2;
            set parameter1=parameter1||variable2||'.';
         end loop;
       close c;
     end;     




     
    获取整型包含行的数量的结果集


     create function rno()
        returns int
      begin
          set @rno=@rno+1;
          return @rno;
      end;


      调用程序
     set @rno=0;
     select rno() sl from t;//
 
    将rno置零
    create function rno_reset()
       returns integer
      begin
         set @rno=0;
         return 1;
      end;
    select rno() sl from t where rno_reset()=1;






    
     累加的函数
       create function running_total (in adder int)
           returns int
          begin
            set @running_total=@running_total+adder;
            return @running_total;
          end;//
 
       set @running_total=0;
       select sl,running_total(sl) from t order by sl;






     myisam 外键插入  ( 引擎不支持外键)
     可以将这个逻辑加入存储过程引擎中进行检查
      
       create procedure fk_insert(p_fk int,p_animal varchar(10))
          begin
             declare v int;
             begin
                declare exit handler for sqlexception,not found
                set v=0;
                if p_fk is not null then
                     select 1 into v from tpk where cpk=p_fk limit 1;                        insert into tfk values (p_fk,p_animal);
                else
                     set v=1;
                end if;
             end;
             if v<>1 then
                 drop table 'the insertion failed'; /*错误信息*/
             end if;
           end;//   






   
     错误传递
     create procedure procedure1()
       begin
          call procedure2();
          set @x=1;
       end;
     create procedure procedure2()
        begin
           call procedure3();
           set @x=2;
        end;
     create procedure procedure3()
        begin
            /*错误信息*/
            drap table error.'error #7815';
           set @x=3;
        end;








    库library
    设置用户调用过程的权限
     grant all on database-name.* to user-name;


     示例代码（对check限制的替代）
   
      create procedure add_book
        (p_book_id int,p_book_title varchar(100))
        sql security definer /* 其他用户有使用的权限*/
        begin
           if p_bood_idd<0 or p_book_title='' then
              select 'warning:bad parameters';
           end if;
           insert into book values (p_book_id,p_book_title);
        end;
  


     /*检查记录数*/
     create procedure add_patron
      (p_patron_id int,p_parton_name varchar(100))
       sql security definer /*其他用户有使用的权限*/
       begin
          declare v int default 0;
          select count(*) from fatrons into v;
          if v>2 then
             select 'warning:already there are',v,'patrons!';
          end if;
          insert into patrons values (p_patron_id,p_patron_name);
       end;


                




      分层次 hierarchy 
        在begin end 中嵌套begin end


  
 
 
     书写较长存储过程时的技巧
 
      用文本编辑器编辑
      排错可进行每删除一行，进行排错
      每句后加入'select n;'(n可以是0,1,2),调用后可以追控制流来诊断


  
      漏洞 bug