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 分割

转载于:https://www.cnblogs.com/liushannet/archive/2011/02/13/1953850.html