Mysql 存储过程

转换结束符:

delimiter /

存储过程:

 

 
   create 
     
   procedure 
    test 

()#或者是(a  
   int 
   ,b  
   int 
   ,out b  
   int 
   ) 

 
   begin 
    

#这里写你的操作 

 
   end 
     
   / 
     
  

 

调用:call test()/

存储函数:

 

 
   create 
     
   function 
    test 

()#或者是(a  
   int 
   ,b  
   int 
   ) 

 
   returns 
     
   int 
    #函数体必须包含一个RETURN value语句 

 
   begin 
    

#这里写你的操作 

 
   return 
     
   1 
   ; 

 
   end 
   / 
  

 

调用:select test()/

备注:存储函数内不能查表(不能使用select 语句)储过程可以

语法:

1. 存储过程中的局部变量前面无@符号用declare定义,且只能在begin end块中,全局变量为@var,前面带有@符号

    

    
        create 
          
        procedure 
         test(out a  
        int 
        ) 
   
    
        begin 
         
   
    
        declare 
         ab  
        int 
          
        default 
          
        1 
        ; 
   
    
        set 
         a 
        = 
        100 
        ; 
   
    
        set 
         a 
        = 
        ab; 
   
    
        end 
          
       

    

   declare 定义的变量的优先级最高,所有当out a 等变量和局部变量相同名的时候,该变量只在该begin块中有效

   SELECT id,data INTO x,y FROM test.t1 通过查询赋值变量

   begin不能并列使用

2.返回值通过OUT参数得到

3.判断

if条件 then

操作

ELSEIF#中间无空格

 

end if;

case 值 (可选)

when 条件 then 操作

when 条件 then 操作

end

4.循环

while 条件 do … end while

loop … end loop

repeat … end repeat

goto

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 
   ;  
   // 
     
  

 

 

labels 使用

lab1 :begin

操作

end lab1;

 

lab2:while 条件 do

#操作

#可以使用leave lab2;跳出循环

#可以使用iterate lab2;跳过当次循环

end while lab2

 

GOTO 使用

 

 
   b1: 
   begin 
    

 
   declare 
    i  
   int 
   ; 

 
   set 
    i 
   = 
   1 
   ; 

 

label lab1; 

 
   select 
     
   ' 
   hi 
   ' 
   ; 

 
   set 
    i 
   = 
   i 
   + 
   1 
   ; 

 
   if 
   (i 
   < 
   2 
   ) 

 
   goto 
    lab1; 



 
   end 
    b1;    
  

     

5.错误处理

declare continue handler for sqlstate 'error number' 操作 end;

declare exit handler for sqlstate 'error number' 操作 end;

sqlstate 'error number' 还可以是

not found #空行

sqlexception #发生错误

sqlwarning #发生警告

以上语句均在发送错误的时候才触发

6.光标

只读的及不滚动, 声明处理程序之前被声明, SELECT语句不能有INTO子句。

例:

 

 
   CREATE 
     
   PROCEDURE 
    curdemo() 

 
   BEGIN 
    

 
   declare 
    a,b,done  
   int 
   ; 

 
   DECLARE 
    cur1  
   CURSOR 
     
   FOR 
     
   SELECT 
    id,data  
   FROM 
    test.t1; 

 
   DECLARE 
     
   CONTINUE 
    HANDLER  
   FOR 
    SQLSTATE  
   ' 
   02000 
   ' 
     
   SET 
    done  
   = 
     
   1 
   ;#声明错误处理 

 
   OPEN 
    cur1;#打开光标 

 
   while 
    done 
   != 
   1 
    do 

 
   fetch 
    cur1  
   into 
    a,b; 

 
   if 
    a 
   > 
   b  
   then 
     
   select 
     
   ' 
   a 
   ' 
   ; 

 
   else 
     
   select 
     
   ' 
   b 
   ' 
   ; 

 
   end 
     
   if 
    ; 

 
   end 
     
   while 
   ; 

 
   close 
    cur1; 

 
   end 
    ; 

  
  

 

备注:取得最后插入的ID 函数为last_insert_id();所有的存储过程等信息都在INFORMATION_SCHEMA库中

 

光标操作属于一个完整的语句块,所以有其他语句混合的时候用

begin

end;

分割

 

查询存储过程

 

 
   show  
   create 
     
   procedure 
    test 
   / 
    #查询存储过程详细 

show  
   create 
     
   function 
    test 
   / 
     
  

 

 

触发器: TRIGGER

类型:

INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。

UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。

DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。

使用OLD.col_name来引用更新前的某一行的列，也能使用NEW.col_name来引用更新后的行中的列

时间: BEFORE AFTER

例:

 

 
   CREATE 
     
   TRIGGER 
    testtrio BEFORE  
   INSERT 
     
   ON 
    test1 

   
   FOR 
    EACH ROW  
   BEGIN 
    

   #操作 

   
   END 
  

 

 

动态SQL:

语法:

PREPARE stmt_name FROM preparable_stmt;
EXECUTE stmt_name [USING @var_name [, @var_name] ...];
{DEALLOCATE | DROP} PREPARE stmt_name;

实例:

 

 
   mysql 
   > 
     
   SET 
     
   @a 
   = 
   1 
   ;
mysql 
   > 
     
   PREPARE 
    STMT  
   FROM 
    " 
   SELECT 
     
   * 
     
   FROM 
    tbl LIMIT ?"; 
mysql 
   > 
     
   EXECUTE 
    STMT USING  
   @a 
   ; 
mysql 
   > 
     
   SET 
     
   @skip 
   = 
   1 
   ;  
   SET 
     
   @numrows 
   = 
   5 
   ; 
mysql 
   > 
     
   PREPARE 
    STMT  
   FROM 
    " 
   SELECT 
     
   * 
     
   FROM 
    tbl LIMIT ?, ?"; 
mysql 
   > 
     
   EXECUTE 
    STMT USING  
   @skip 
   ,  
   @numrows 
   ; 
  

只能配合存储过程使用,不支持触发器或存储函数

建立以下触发器会出错误的:

 

 
   delimiter  
   // 
   

 
   CREATE 
     
   TRIGGER 
    cds_add_a AFTER  
   INSERT 
     
   ON 
    b
 
   FOR 
    EACH ROW  
   BEGIN 
   
     
   DECLARE 
    done  
   INT 
     
   DEFAULT 
     
   0 
   ;
     
   DECLARE 
    ye  
   INT 
   ;
     
   DECLARE 
    cur1  
   CURSOR 
     
   FOR 
     
   select 
    jahr  
   from 
    cds  
   where 
    id 
   = 
   1 
   ;
     
   DECLARE 
     
   CONTINUE 
    HANDLER  
   FOR 
    SQLSTATE  
   ' 
   02000 
   ' 
     
   SET 
    done  
   = 
     
   1 
   ;
     
   OPEN 
    cur1;
    REPEAT
         
   FETCH 
    cur1  
   INTO 
    ye;
         
   IF 
     
   NOT 
    done  
   THEN 
   
            
   IF 
    ye 
   = 
   ' 
   1990 
   ' 
     
   THEN 
   
                 
   PREPARE 
    STMT  
   FROM 
    " 
   INSERT 
     
   INTO 
    `a` (`a`)     
   VALUES 
    (?)";
                 
   EXECUTE 
    STMT USING  
   @ye 
   ;
                 
   SET 
    done  
   = 
     
   1 
   ;
            
   END 
     
   IF 
   ;
         
   END 
     
   IF 
   ;
    UNTIL done  
   END 
    REPEAT;
     
   CLOSE 
    cur1;
 
   END 
   ; 
   // 
   
delimiter ; 
  

 

(ERROR 1336 (0A000): Dynamic SQL is not allowed in stored function or trigger)

编写储存过程的时候习惯把每个独立块用begin end 分割