MySQL存储过程和函数

一.存储过程和函数的语法

查看存储过程SHOW PROCEDURE/FUNCTION STATUS

 

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
    
    proc_parameter:
    [ IN | OUT | INOUT ] param_name type
    
    func_parameter:
    param_name type
 
type:
    Any valid MySQL data type
 
characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'
 
routine_body:
    Valid SQL procedure statement or statements

　　

LANGUAGE SQL：用来说明语句部分是SQL语句，未来可能会支持其它类型的语句

[NOT] DETERMINISTIC：如果程序或线程总是对同样的输入参数产生同样的结果，则被认为它是“确定的”，否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC，默认的就是NOT DETERMINISTIC（非确定的）CONTAINS SQL：表示子程序不包含读或写数据的语句。

CONTAINS SQL:包含子程序包含SQL语句
NO SQL：表示子程序不包含SQL语句。
READS SQL DATA：表示子程序包含读数据的语句，但不包含写数据的语句。
MODIFIES SQL DATA：表示子程序包含写数据的语句。

SQL SECURITY:权限相关
SQL SECURITY DEFINER：表示执行存储过程中的程序是由创建该存储过程的用户的权限来执行。(谁创建了存储过程,就以该用户的权限执行) 默认
SQL SECURITY INVOKER：表示执行存储过程中的程序是由调用该存储过程的用户的权限来执行。（看当前调用者有没有执行存储过程中涉及的表的权限,如没有,则报错）

COMMENT 'string':注释，和创建表的字段注释一样。

 

二.存储过程和函数的区别

1.存储过程实现要复杂些,函数功能比较单一
2.存储过程有IN,OUT,INOUT参数,不支持默认值设置,函数只能有输入参数,而且不能带IN
2.存储过程没有返回值,至于out,可以理解为对外部变量的引用;函数必须有返回值
3.存储过程调用用call proc_fun();函数用select fun_fun();

 

三.示例

1.函数示例

DELIMITER $$
CREATE FUNCTION test(a int(4)) RETURNS int
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
BEGIN
DECLARE b int DEFAULT 0;
SELECT COUNT(*) INTO b FROM test2 WHERE id>a;
RETURN b;
END $$
DELIMITER ;

　　

调用:SELECT test(0)

 

2.存储过程示例:

查询test2的指定id有没有记录,没有记录就插入一条记录,设置out变量a为-1;如果有,不做任何操作,设置out变量a为0

DELIMITER $$
CREATE PROCEDURE proc(OUT a int(11), IN id int(11))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
COMMENT '这是一个注释'

begin_label:BEGIN
SELECT  id FROM test2 WHERE test2.`id`=id;
IF FOUND_ROWS() > 0 THEN
set a=0;
LEAVE begin_label;#退出存储过程
ELSE
INSERT INTO test2(`time`) VALUES(NOW());
set a=-1;
END IF;
END
$$
DELIMITER ;

　　

 

调用:
call proc(@a, 2979);
取出变量a
SELECT @a;

ps:FOUND_ROWS()另一个技巧
可以使用SQL_CALC_FOUND_ROWS + LIMIT和FOUND_ROWS进行分页
SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT 10;
SELECT FOUND_ROWS();//这将返回上面查询的总记录数,不受limit影响

 

四.存储过程中的错误处理

如果语句块产生一个错误,该如何处理?是退出,还是继续?

1.语法:

 

DECLARE handler_action HANDLER  
        FOR condition_value [, condition_value] ...  
        statement  
      
    handler_action:  
        CONTINUE  
      | EXIT  
      | UNDO  
      
    condition_value:  
        mysql_error_code  
      | SQLSTATE [VALUE] sqlstate_value  
      | condition_name  
      | SQLWARNING  
      | NOT FOUND  
      | SQLEXCEPTION  

 

 

 

2.示例
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET a=-1;
如果执行sql语句时产生错误,将变量a设置为-1;如果没有产生错误,则保持原值

DELIMITER $$
CREATE PROCEDURE proc2(OUT a int(11))
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET a=-1;
SET @x=0;
INSERT INTO test2(`time`) VALUES(10);
END
$$
DELIMITER ;

　　

基本解释:mysql存储过程不支持为OUT和IN参数赋默认值,默认值即为null,null寓意不太明确,所以将a设置为0;如果你将
SET a=0;放在DECLARE CONTINUE HANDLER上面,将会引发一个错误.DECLARE CONTINUE HANDLER上面只可以再放置DECLARE定义的局部变量.放置set @x=10;等用户变量,也会引发一个错误.

 

五.游标

 

1.定义游标
DECLARE cursor_name CURSOR FOR select_statement

2.打开游标
OPEN cursor_name

3.FETCH 游标
FETCH cursor_name INTO var_name[,var_name]

4.关闭游标
CLOSE cursor_name;

5.示例:

DELIMITER $$
CREATE PROCEDURE proc3()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE myid INT;
DECLARE my_cursor CURSOR FOR (SELECT `id` FROM test2 ORDER BY `id` DESC);
DECLARE CONTINUE HANDLER FOR NOT FOUND CLOSE my_cursor;
OPEN my_cursor;
FETCH my_cursor INTO myid;
CLOSE my_cursor;
SELECT myid; #打印结果
END
$$
DELIMITER ;

ps:需要注意过程中的变量不要和查询语句中的字段名重名了,对字段名应该用``包裹.

 

由于以上没有用循环,所以只读取了一行就退出了.

 

六,循环

1.while循环

DELIMITER $$
CREATE PROCEDURE proc3()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE myid INT; #定义变量
DECLARE my_cursor CURSOR FOR (SELECT `id` FROM test2 ORDER BY `id` DESC);#定义游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET myid=0;#如果没有数据的时候,将myis设置为0
OPEN my_cursor; #打开游标
FETCH my_cursor INTO myid;#获取一行数据
WHILE myid > 0 DO #while循环开始
SELECT myid; #打印结果
FETCH my_cursor INTO myid;  #向下取一行数据
END WHILE; #结束循环标志
CLOSE my_cursor; #关闭游标
END
$$
DELIMITER ;

 

2.LOOP循环:

DELIMITER $$
CREATE PROCEDURE proc5()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
BEGIN
DECLARE myid INT; #定义变量
DECLARE my_cursor CURSOR FOR (SELECT `id` FROM test2 ORDER BY `id` DESC);#定义游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET myid=0;#如果没有数据的时候,将myis设置为0
OPEN my_cursor; #打开游标
FETCH my_cursor INTO myid;#获取一行数据

ins : LOOP #ins为循环体名字

IF myid = 0 THEN #退出循环
	LEAVE ins; #类似break
END IF;

IF myid = 2980 THEN
	ITERATE ins;#跳过当前循环,直接执行下一次循环
END IF;

SELECT myid;
FETCH my_cursor INTO myid;
END LOOP ins;

CLOSE my_cursor; #关闭游标
END
$$
DELIMITER ;

　　

3.repeat循环

BEGIN
    DECLARE cid int(11) DEFAULT 0;
    DECLARE rs CURSOR FOR (SELECT `id` from test1 ORDER BY `id` DESC);
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE rs;
    OPEN rs;
    REPEAT FETCH rs INTO cid;
        SELECT cid;
    UNTIL 0 END REPEAT;
    CLOSE rs;
END