MySQL操作之存储过程

一、概念

在开发过程中，经常会用到某一功能重复使用，为此MySQL引入了存储过程。

是一条或者多条的SQL语句的集合，存储过程就这些SQL封装成一个代码块，以便重复使用。

二、存储过程的创建

2.1、创建存储过程

使用create PROCEDURE语句创建存储过程。

CREATE PROCEDURE sp_name ([proc_parameter])
[characters ...]routine_body

• CREATE PROCEDURE：创建存储过程的关键字。
• sp_name：为存储过程的名称。
• proc_parameter：存储过程的参数列表。
• **characters：**用于指定存储过程的特性。
• routine_body：是SQL代码的内容。也可以只是用begin ...end来表示SQL代码的开始和结束。

proc_parameter参数列表：

[IN|OUT|INOUT]param_name type

• IN： 表示输入参数。
• OUT： 输出参数
• INOUT： 既可表示输入，也可表示输出参数。
• param_name： 表示参数名称。
• type： 表示参数的类型(可以是MySQL中任意一种类型)。

characters参数列表：

• Language SQL：说明routine_body部分是由SQL语句组成的，当前系统支持语句为SQL，SQL是language的唯一值。
• [Not]Deterministic： 指明存储过程执行的结果是否确定。Not Deterministic不确定，为默认值。
• {CONTAINS SQL|NO SQL |READS sql data|modifies sql data} : 指明自语句使用SQL语句的限制。表示自语句中含有sql。
• SQL security{definer|invoker}： 指明谁有权限来执行。definer：只有定义者才能执行。invoker：表示拥有权限的调用者可以执行。默认为：definer。
• COMMENT'String：注释信息。

案例：

CREATE PROCEDURE Proc ()
BEGIN
SELECT * FROM student;
END

2.2、变量的使用

在编写存储过程中，会使用变量保存数据处理过程中的值。MySQL中，变量可以在子程序中声明并使用，变量的作用范围是在BEGIN...END程序中。

想要在存储过程中使用变量，首先需要定义变量。使用declare语句定义变量。语法如下：

DECLARE var_name [,varname]...date_type[DEFAULT value]

• var_name：为局部变量的名称。
• DEFAULT value：子句给变量提供一个默认值。该值除了可以被声明为一个常数之外，还可以被指定为一个表达式。
• 如果没有Default子句，变量的初始值为null。

案例：

DECLARE myvariable INT DEFAULT 100;

更改变量值SET：

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

案例：

DECLARE var1,var2,var3 INT ;
SET var1=10,var2=20;
SET var3=var1+var3;

还可以通过SELECT ... INTO为一个或多个变量赋值。

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

• col_name：表示字段名称。
• var_name：表示定义的变量名称。
• table_expr：表示查询条件表达式，包括表名称和WHERE子句。

案例：

DECLARE s_grade FLOAT;
DECLARE s_grader CHAR(2);
SELECT grade,gender INTO s_grade,s_gender FROM student WHERE name='rose';

2.3、定义条件和处理程序

定义条件是实现定义程序执行过程中遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程中遇到警告或错误时能继续执行下去。

1、定义条件

在编写存储过程中，用DECLARE语句。

DECLARE condition_name CONDITION FOR [condition_type];
// conditoin_type的两种形式：
[condition_type]
SQLSTATE [VALUE] sqlstate_value|mysql_error_code

• condition_name：表示所定义的条件的名称。
• condition_type：表示条件的类型。
• sqlstate_value和 mysql_error_code：**都可以表示MySQL的错误。
• sqlstate_value：表示长度为5的字符串类型的错误代码。
• mysql_error_code：为数值类型的错误代码。

案例：

//方式一：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE `42000`;
//方式二：使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR SQLSTATE 1148;

2、定义处理程序

定义完条件以后，还需要定义针对此条件的处理程序。MySQL中使用DECLARE语句处理程序。

DECLARE handler_type HANDLER FOR condition_value[,...]sp_statement 
handler_type: CONTINUE|EXIT|UNDO
condition_value:
    |condition_name
    |SQLWARNING
    |NOT FOUND
    |SQLEXCEPTION
    |mysql_error_code

• handler_type：为错误处理方式：参数取三个值CONTINUE、EXIT、UNDO。

CONTINUE：表示遇到错误不处理，继续执行。

EXIT：表示遇到错误马上退出。

UNDO：表示遇到错误后撤回之前的操作，MySQL不支持这样的操作。

• sp_statement：参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程。
• condition_value：表示错误类型。可以有

SQLSTATE[VALUE]sqlstate_value：包含5个字符的字符串错误值。

condition_name：表示DECLARE CONTINUE 定义的错误条件名称。

SQLWARNING：匹配所有以01开头的SQLSTATE错误代码。

NOT FOUND：匹配所有以02开头的SQLSTATE错误代码。

SQLEXCEPTION：匹配所有没有被SQLWARING和NOT FOUND捕获的SQLSTATE错误代码。

mysql_error_code：匹配数值类型错误代码。

案例：

//方法一：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE `42s02` SET @info=`NO_SUCH_TABLE`;
//方法二：捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info=`NO_SUCH_TABLE`;
//方法三：先定义条件，然后调用
DECLARE no_such_table CONTINUE FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='ERROR';
//方法四：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
//方法五：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info ='NO_SUCH_FOUND';
//方法六：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

2.4、光标的使用

在编写存储过程时，查询语句可能返回多条记录，如果数据量过大，则需要使用光标来逐条读取查询结果集中的记录。

在使用光标前需要先声明光标。光标必须声明在声明变量、条件之后、声明处理程序之前。

1、光标的声明

MySQL中使用DECLARE关键字来声明光标。

DECLARE cursor_name CURSOR FOR select_statement

• cursor_name：表示光标的名称。
• select_statement：表示select语句的内容，返回一个创建光标的结果集。

案例(声明cursor_student的光标)：

DECLARE cursor_name CURSOR FOR select s_name,s_gender FROM student;

2、光标的使用

OPEN cursor_name
FETCH cursor_name INTO var_name[，var_name]...

• cursor_name：表示参数的名称。
• var_name：表示将光标中SELECT语句查询出来的信息存入该参数中。
• var_name：必须在声明光标之前就定义好。

案例：

FETCH cursor_student INTO s_name ,s_gender;

3、光标的关闭

使用完光标后，要将其关闭。

CLOSE cursor_name

2.5、流程控制的使用

将多个SQL语句划分或者组合成符合业务逻辑的代码块。

包括：If语句、case语句、loop语句、while语句、leave语句、iterate语句、repeat语句。

1、IF语句

if语句是满足某个条件，则执行某个操作。

IF expr_condition THEN statement_list
  [ELSEIF expr_condition THEN statement_list]
  [ELSE statement_list]
END IF

• expr_condition：表示判断条件。
• statement_list：表示SQL语句列表，可以包括一个或多个语句。

案例：

IF val IS NULL
  THEN SELECT 'val is NULL‘;
  ELSE SELECT 'val is NOT NULL';
END IF;

判断val值是否为空，假如val为空，输出字段’val is NULL’；否则，输出"val is NOT NULL"。

2、CASE语句

格式一：

CASE case_expr
  WHEN when_value THEN statement_list
  [WHEN when_value THEN statement_list]...
  [ELSE statement_list]
END IF

案例：

CASE val
  WHEN 1 THEN SELECT ’val is 1‘
  WHEN 2 THEN SELECT 'val is 2'
  ELSE SELECT ’val is not 1 or 2'
END IF

SELECT
  last_name,job_id,salary,
CASE job_id 
      WHEN 'IT_PROG'  THEN 1.10 * salary 
      WHEN 'ST_CLERK' THEN 1.15 * salary 
      WHEN 'SA_REP'   THEN 1.20 * salary 
      ELSE salary   END  "REVISED_SALARY" 
FROM employees;

格式二：

CASE 
  WHEN expr_codition THEN statement_list
  [WHEN expr_codition THEN statement_list]
  [ELSE statement_list]
END IF

3、LOOP语句

LOOP循环语句用来重复执行某些语句，与if和case语句相比，loop只是创建一个循环操作的过程，并不进行条件判断。

LOOP内的语句一直重复执行，直到跳出循环语句。

[loop_label:]LOOP
  statement_list
END LOOP [loop_label]

• loop_label：表示LOOP语句的标注名称，该参数可以省略。
• statement_list：表示需要循环执行的语句。

案例：

DECLARE id INT DEFAULT 0:
add_loop:LOOP
SET id=id +1;
  IF id>=10  THEN LEAVE add_loop;
  END IF;
END LOOP add_loop;

循环执行了id+1的操作。当id的值小于10时，循环重复执行；当id的值大于或等于10时，使用LEAVE语句退出循环。

4、LEAVE语句

当不满足循环条件时，需要使用LEAVE语句退出循环。

LEAVE label

5、ITERATE语句

ITERATE是再次循环，用于将执行顺序顺序转到语句段的开头处。

ITERATE lable

• lable：表示循环的标志。
• ITERATE语句只可以出现在LOOP、REPEAT和while语句内。

CREATE PROCEDURE doiterate()
BEGIN
DECLARE p1 INT DEFAULT 0;
my_loop:LOOP
  SET p1=p1+1;
  IF p1<10 THEN ITERATE my_loop;
  ELSEIF p1>20 THEN LEAVE my_loop;
  END IF;
  SELECT 'p1 is between 10 and 20';
END LOOP my_loop;
END

p1的初始值为0，如果p1的值小于10时，重复执行p1+1的操作；当p1大于或等于10并且小于20时，打印内容"p1 is between 10 and 20";当p1大于20时，退出循环。

6、REPEAT语句

用于创建一个带有条件判断的循环过程，每次语句执行完毕后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。

[repeat_lable:]REPEAT
  statement_list
UNTIL expr_condition
END REPEAT [repeat_lable]

• repeat_lable：为REPEAT语句的标注名称，该参数是可选的。
• REPEAT语句内的语句或语句群被重复，知道expr_condition为真。

DECLARE id INT DEFAULT 0;
REPEAT
SET id=id+1;
UNTIL id>=10;
END REPEAT;

7、WHILE 语句

创建一个带条件判断的循环过程，与REPEAT不同的是，while在语句执行时，先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。

[while_lable:]WHILE expr_condition DO
  statement_list
END WHILE [while_lable]

• while_lable：为while语句的标注名称。
• expr_condition：为进行判断的表达式，如果表达式为真，WHILE语句内的语句或语句群将被执行，直至expr-condition为假，退出循环。

DECLARE i INT DEFALULT 0;
WHILE i< 10 DO
SET i=i+1;
END WHILE;

三、存储过程的使用

3.1、调用存储过程

存储过程有多种调用方法。必须使用CALL语句调用，并且存储过程和数据库相关。如果要执行其他数据库中的存储过程，需要指定数据库的名称。

CALL sp_name([parameter],...)

• sp_name：为存储过程的名称。
• parameter：为存储过程的参数。

案例：

CALL countProcl("女",@num);

3.2、查看存储过程

用户可以使用SHOW STATUS语句、SHOW CREATEE语句和从系统中的information_schema数据库中查询。

1、SHOW STATUS语句查看存储过程的状态

SHOW{PROCEDURE|FUNCTION} STATUS [LIKE'pattern']

2、SHOW CREATE语句查看存储过程的状态

SHOW CREATE{PROCEDURE|FUNCTION} sp_name

返回一个可以用来创建已命名子程序的确切字符串。

SHOW CREATE PROCEDURE chapter06.CountProc1

3、从information_schema.Routines表中查看存储过程的信息

SELECT * FROM information_schema.Routines
WHERE ROUTINE NAME='CountProc1' AND ROUTINE_TYPE='PROCEDURE'

3.3、修改存储过程

MySQL可以使用ALTER语句修改存储过程的特性。

ALTER{PROCEDURE|FUNCTION}sp_name [characteristic...]

• sp_name：表示存储过程或者函数的名称。
• characteristic：表示要修改存储过程的哪个部分。

CONTAINS SQL：表示子程序包含SQL语句，但不包含读或写数据的语句。

NO SQL：表示子程序中不包含SQL语句。

READS SQL DATA：表示子程序中包含读数据的语句。

MODIFIES SQL DATA：表示子程序中包含写数据的语句。

SQL SECURITY{DEFEINER|INVOKER}：指明谁有权限来执行。

DEFINER：表示只有定义者自己才能够执行。

INVOKER：表示调用者可以执行。

COMMENT：表示注释信息。

ALTER PROCEDURE ContProc1
MODIFIES SQL DATA
SQL SECURITY INVOKER;

目前，MySQL还不提供对已存在的数据存储的代码修改，如果一定要修改存储过程代码，必须要先将存储过程删除以后，再重新编写代码，或者创建一个新的存储过程。

3.4、删除存储过程

MySQL可以使用DROP语句删除存储过程。

DROP{PROCEDURE|FUNCTION}[IF EXISTS] sp_name

• sp_name：表示要移除的存储过程的名称
• IF EXISTS：表示如果程序不存在，它可以避免发生错误，产生一个警告。该警告可以使用SHOW WARNINGS进行查询。

案例：

DROP PROCEDURE Countroc1;

四、综合案例

4.1、创建数据库

CREATE TABLE stu(id INT,name CARCHAR(50),class varchar(50));

插入数据：

INSERT INTO stu VALUE(1,’Lucy','class1'),(2,'Tom','class1'),(3,"Rose",'class2')

4.2、创建存储过程

CREATE PROCEDURE addcount(out count INT)
BEGIN
DECLARE itmp INT;
DECLARE cur_id CURSOR FOR SELECT id FROM stu;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_id;
SELECT count(*) INTO count FROM stu;
SET @sum=0;
OPEN cur_id;
  REPEAT
  FETCH cur_id INTO itmp;
  IF itmp <10
  THEN SET @sum=@sum + itmp;
  END IF;
  UNTIL 0 END REPEAT;
CLOSE cur_id;
END;