Mysql存储过程

一、分隔符

由于存储过程中的语句的结束符为;，所以要使用delimiter语句定义在存储过程定义结束所使用的分隔符。如：

delimiter //
delimiter ;

 

二、存储过程语法

create procedure sp_name(in name type,out name type,inout name type)
language sql
[not] deterministic
sql security [invoker|definer]
comment 'string'
[sql-statements]

 

第一行：存储过程名，in、out、inout参数（name指参数名，type指参数类型）

第二行：存储过程的语言

第三行：此过程是否每次执行结果是确定的

第四行：是采用调用者的权限，还是定义者的权限

第五行：注释

第六行：SQL过程语句

 

三、MYSQL语句语法

1.变量定义(会话变量、过程变量)

DECLARE var_name [, var_name] ... type [DEFAULT value]

SET var_name = expr [, var_name = expr] ...

例如:

declare var_a varchar(100) default '';

set var_a='10000';

set @var_b='20000';

 

2.条件语句

(1)IF

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

例如：

IF var=1 THEN

...

ELSEIF var=2 THEN

...

ElSE

...

END IF;

 

(2)CASE

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

例如：

CASE var

WHEN 0 THEN ...

WHEN 1 THEN ...

ELSE ...

END CASE;

或

CASE

WHEN var=0 THEN ...

WHEN var=1 THEN ...

ELSE ...

END CASE;

 

3.循环语句

(1)Loop
[begin_label:] LOOP
    statement_list
    if done=1 then
        leave [label]
    end if;
END LOOP [end_label]

例如：

cur_loop:LOOP

      statements;

      if done=1 then

          leave cur_loop;

      end if;

END LOOP cur_loop;

 

(2)While

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

例如：

declare var int(10) default 1;

declare sum int(10) default 0;

cur_loop:WHILE var <=100 DO

    set sum=sum+var;

    set var=var+1;
END WHILE cur_loop;

 

(3)Repeat

[begin_label:] REPEAT
    statement_list
UNTIL search_condition
END REPEAT [end_label]

例如：

declare var int(10) default 1;

declare sum int(10) default 0;

cur_loop:REPEAT

    set sum=sum+var;

    set var=var+1;

UNTIL var > 100

END REPEAT cur_loop;

 

4.其他语句

ITERATE label   ==== continue
LEAVE label      ==== break

以上语句用在循环中，相当于continue和break的意义。

 

5.给变量赋值

SELECT col_name [, col_name] ...
    INTO var_name [, var_name] ...
    table_expr

例如：

declare var varchar(100) default '';

SELECT name INTO var from examples;

 

6.处理器

DECLARE handler_type HANDLER
    FOR condition_value [, condition_value] ...
    statement

 

handler_type:
    CONTINUE
  | EXIT
  | UNDO

 

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

 

例如：

declare done int(1) default 0;

declare continue handler for not found set done=1;

 

7.处理条件

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

 

例如：

declare 'Constraint Violation' dondition for sqlstate '23000';

 

8、游标

DECLARE cursor_name CURSOR FOR select_statement
OPEN cursor_name
FETCH cursor_name INTO var_name [, var_name] ...
CLOSE cursor_name

 

只读：不可更新
不可滚动：只能顺序读取
敏感

 

9.权限
SQL SECURITY INVOKER  使用调用者权限
SQL SECURITY DEFINER  使用定义者权限

 

10.函数

限制：不能在函数中访问表

create function func_name(i int)
returns int
deterministic
begin
...
return some_var;
end

 

11.显示存储过程

show create procedure p1;
show procedure status like '';
show create function f1;
show function status like '';
mysql.proc表(procedure+function)
infomation_schema.routines (sql标准)

 

12.错误传递

如果过程1调用过程2，过程2调用了过程3，过程3中的错误就会传递到过程1

 

13.定义顺序

必须顺序为：

（1）局部变量variables

（2）游标cursor

（3）处理器handler

 

示例：

显示数据库中所有表的总行数

CREATE PROCEDURE `NewProc`(OUT `cnt` int)
BEGIN
 declare tab_name varchar(100);
 declare done int(1) default 0;
 declare res int(10) default 0;
 declare cur cursor for show tables;
 declare continue handler for not found set done=1;
 open cur;
 cursor_loop:loop
  fetch cur into tab_name;
  if done = 1 then
   leave cursor_loop;
  end if;
  set @tmp=0;
  set @sql_str=concat('select count(1) into @tmp from ',tab_name);
  prepare count_sql from @sql_str;
  execute count_sql;
  set res=res+@tmp;
 end loop cursor_loop;
 close cur;
 set cnt=res;
END;