mysql的存储过程和函数小结_mysql 存储过程练习实训报告小结-优快云博客

本文链接：https://blog.youkuaiyun.com/Yu_csdnstory/article/details/105227732

本文详细介绍了MySQL的存储过程和函数的创建、定义变量、条件处理、光标使用及流程控制，包括IF、CASE、LOOP等语句的用法，并讲解了如何查看、修改和删除存储过程和函数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

创建存储过程

基本语法如下：
create procedure sp_name ( [proc_parameter]) [characteristics ... ] routine_body
(1)create procedure 用来创建存储过程的关键字。
(2)sp_name 为存储过程的名称
(3) proc_parameter 为存储过程的参数列表，列表形式如下：

[in |out |inout] param_name type

in为输入参数，out为输出参数；inout为既可以输入也可以输出参数；param_name 为参数名称 type为参数的类型，可以是mysql的参数的任意类型。

(4) characteristics 指定存储过程的特征，取值较多。
LANGUAGE SQL
存储过程语言，默认是sql，说明存储过程中使用的是sql语言编写的，暂时只支持sql，后续可能会支持其他语言

NOT DETERMINISTIC
是否确定性的输入就是确定性的输出，默认是NOT DETERMINISTIC，只对于同样的输入，输出也是一样的，当前这个值还没有使用

CONTAINS SQL
提供子程序使用数据的内在信息，这些特征值目前提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况，说白了就是没有使用的
包括以下四种选择
1.CONTAINS SQL表示子程序不包含读或者写数据的语句
2.NO SQL 表示子程序不包含sql
3.READS SQL DATA 表示子程序包含读数据的语句，但是不包含写数据的语句
4.MODIFIES SQL DATA 表示子程序包含写数据的语句。

SQL SECURITY DEFINER
用来指定存储过程是使用创建者的许可来执行，还是执行者的许可来执行，默认值是DEFINER
DEFINER 创建者的身份来调用，对于当前用户来说：如果执行存储过程的权限，且创建者有访问表的权限，当前用户可以成功执行过程的调用的
说白了就是当前用户调用存储过程，存储过程执行的具体操作是借助定义存储过程的user的权限执行的。
INVOKER 调用者的身份来执行，对于当前用户来说：如果执行存储过程的权限，以当前身份去访问表，如果当前身份没有访问表的权限，即便是有执行过程的权限，仍然是无法成功执行过程的调用的。
说白了就是当前用户调用存储过程，只有当前用户有执行存储过程中涉及的对象的操作的权限的时候，才能成功执行。

COMMENT 'string'
存储过程的注释性信息写在COMMENT里面，这里只能是单行文本，多行文本会被移除到回车换行等

(5) routine_body 是SQL的代码部分，用begin和end来代表开始和结束。
例如创建名为 checksttu()的存储函数

delimiter //
>  create procedure checksttu()
>  begin
>  select avg(grade) as avggrade from sc;
> end //

deliliter 语句用来确认结束符，由于sql语句用分号来结尾，创建存储过程时有sql语句，分号来结尾会和创建过程的结束冲突，所以需要改变结束符的方式。

创建存储函数

crate function func_name( [func_parameter]) returns type [characteristic ...] routine_body

returns type 是函数返回的数据类型，characteristic和存储过程的一样。
例如：

> delimiter //
> create function Sumer()
> returns int(11)
> return (select sum(grade) from sc where sno='201708034101');
> //

定义变量

declare var_name[,varname] ... data ...type [default value];
用set语句为变量赋值
例如：

declare var1,var2,var3 INT;
set var1=10,var2=20;
set var3=var1+var2;

也可以用select...into语句进行赋值
select col_name[,...] into var_name[,...] table_expr

declare fruit_name char(50);
declare fruit_price decimal(8,2);

select f_name,f_price into fruitname,fruiitprice
 from fruits f_id='a1'

定义条件和处理程序

定义条件是实现定义程序执行过程中遇到的问题，处理程序遇到这些问题的处理方式，并且保证存储过程或函数在遇到警告或者错误的时候能正常的执行，增强程序处理错误的能力。

1.定义条件

DECLARE condition_name CONDITION FOR [condition_type]
[contion_type] :
SQLSTATE [VALUE] sqlstate_value| mysql_error_code

[condition_type为条件的类型，sqlstate_value 为长度为5的字符串类型的错误代码，mysql_error_code 为数值类型的错误代码。
语句用来指定需要特殊处理的条件，可以将一个名字和指定的错误关联起来，这个名字可以直接用到处理程序中。
例如：

declare command_not_allowed CONDITION FOR SQLSTATE '42000';
DECLARE command_not allowed CONDITION FOR 1148;

2.定义处理程序

DECLARE hander_type HANDER FOR condition_value[,...] sp_statement hander_type:
CONTINUE |EXIT| UNDO

condition_value:
SQLSTATE [VALUE] sqlstate_value
|condition_name
|SQLWARING
|NOT FOUND
| SQLEXCTPTION
| mysql_error_code
参数说明
Handler_type，为错误处理方式，参数取值有三个
CONTINUE，表示遇到错误不处理，继续执行
EXIT，表示遇到错误马上退出
UNDO，表示遇到错误后，撤销之前的操作，MySQL中，暂时不支持这样的操作
Sp_statement，参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或者函数
Condition_value，表示错误类型

SQLSTATE[VALUE] sqlstate_value，包含5个字符的字符串错误值
1.Condition_name，表示DECLARE CONDITION定义的错误条件名称
2.SQLWARNING，匹配所有以01开头的SQLSTATE错误代码
3.NOT FOUND，匹配所有以02开头的SQLSTATE错误代码
4.SQLEXCEPTION，匹配所有没有被SQLWARNING或NOT FOUND捕获的 SQLSTATE错误代码
5.Mysql_error_code，匹配数值类型错误代码

定义处理程序的几种方法

1.捕获sqlstate_value

DECLARE CONTINUE HANDLER FOR SQLSTATE  '42S02' 
SET @info='NO_SUCH_TABLE';

2.捕获mysql_error_code

DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';

3.先定义条件，再查询

DECLARE no_such_table condition for 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_VALUE';

4.使用SQLWARNING

DECLARE EXIT HANDLER FOR SQLWARING SET @info='ERROR';

5.使用NOT FOUND

DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';

6.使用SQLEXECPTION

DECLARE EXIT HANDLER FOR SQLEXECPTON SET @info='ERROR';

DELIMITER //
create table t(s1,INT,PRIMARY KEY(S1));

CREATE PROCEDURE handlerdemo()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
SET @x=1;
insert into t15 values(1);
SET @x=2;
insert into t15 values(1);
SET @x=3;
END;
//

mysql> CALL handlerdemo();
Query OK, 0 rows affected (0.00 sec)

mysql> select @x;
+------+
| @x   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

@x是一个用户变量，执行的结果为@x=3,因为主键约束，插入重复数值会直接退出，后面的@x=2可能就不会执行，有了coutinue语句，还会继续执行，不会@x=3。
用户变量和连接有关，一个用户变量不会被其他的用户看到和使用，一旦连接关闭，变量自动销毁。

光标的使用

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。
定义光标
DECLARE cursor_name CURSOR FOR select_statement
select_statement参数表示select语句的内容，返回一个用于创建光标的结果集。

DECLARE cursor_fruit CURSOR FOR SELECT f_name,f_pricec FROM fruits;

打开光标

open cursor_name

open cursor_fruit;

使用光标
FETCH cursor_name INTO var_name [,var_name] ...[参数名称]
var_name 必须在声明光标之前就定义好。

FETCH cursor_friut INTO fruit_name,fruit_price;

fruit_name,fruit_price必须在前面都已经定义。
关闭光标
CLOSE cursor_name(参数名称)

close cursor_fruit(光标名称）

注：mysql中，光标只能在存储过程中使用。

流程控制的使用

IF语句

IF expr_condition THEN statement_list
[ELSEIF expr_condition THEN statement_list]...
[ELSE statement_list]
END IF
语句执行和其他语言的if语句是相似的。

IF val is NULL
	THEN SELECT 'var is NULL';
	ELSE SELECT 'var is not NULL';
EDN IF

case 语句

第一种格式
CASE case_epxr
WHEN when_value WHEN statement_list
WHEN when_value WHEN statement_list
[ELSE statement_list]

case是参数表达式，用于判断哪个WHEN语句会被执行，如果所有的when_value都和case_expr不匹配，那么就会执行else的语句。
例如：

CASE val
	WHEN 1 THEN SELECT 'val is 1';
	WHEN 2 THEN SELECT 'val is 2';
	ELSE SELECT 'val is NULL';
END CASE;

第二种格式
CASE
    WHEN expr_condition THEN statement_list
    [WHEN expr_condition THEN statement_list]...
    ELSE statement_list
END CASE
这种语句会一次执行，知道某条的判断为真时结束
例如：

CASE 
	WHEN val IS NULL THEN SELEC 'val is NULL';
	WHEN var <0 THEN SELECT 'val is less than 0';
	WHEN var>0 THEN SELECT 'val is greater then 0';
	ELSE SELECT 'val is 0';
END CASE;

loop语句

loop 语句用来执行某些循环的语句
[loop_label:] LOOP
statement_list
END LOOP [loop_label]

loop_label 是对loop的标注，可以省略

例如：

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;

LEAVE 语句

leave语句用来退出任何被标注的流程构造。
LEAVE label

add_num: LOOP
	SET @count =@count+1;
	IF @COUNT =50 THEN LEAVE add_num;
END LOOP add_num

ITERATE语句

ITERATE label
INERATE只能在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 betwween 10 and 20';
END LOOP my_loop;
END//

p1的默认值为0，如果p1的值小于10是，继续执行循环程序，当p1大于10小于20等于的时候，打印消息’p1 is between 10 and 20’;当p1大于20时，退出循环。

REPEAT语句

带条件判断的循环语句，每次语句执行完毕之后，会对条件进行判断，如果表达式为真，循环结束，否则重复执行循环中的语句。
[repeat_label:] REPEAT
statement_list
UNTIL expr_condition
END REPEAT[repeat_label]
例如：

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

WHILE

while 语句创建一个带有条件的循环过程，先有执行条件，如果为真，继续执行，否则就退出循环。
[while_label:] WHILE expr_condition DO
statement_list
END WHILE [while_label]
例如：

DECALRE i int DEFAULT 0;
WHILE i <10 DO
	SET i =i+1;
END WHILE;

调用存储过程和函数

调用存储过程
call sp_name([parameter[,...]])
parameter 为创建存储过程的参数

例如：

delimiter // 
CREATE PROCEDURE CounterProcl (IN sid INT ,OUT num INT)
BEGIN
 SELECT COUNT(*) INTO num FROM fruits WHERE s_id =sid;
 END//

delimiter ;
call CounterProcl()
select @num;

调用存储函数
例如：

 delimiter //
 CREATE FUNCTION Countproc2 (Sid INT)
 RETURNS INT
 BEGIN
 RETURN (SELECT COUNT(*) FROM t15 WHERE s1=Sid);
 END//

mysql> SELECT CountProc2(1);
+---------------+
| CountProc2(1) |
+---------------+
|             1 |
+---------------+

查看存储过程和存储函数

1.查看存储过程和函数的状态

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
LIKE 参数为匹配过程函数的名称，不用like参数将显示所有，数据非常庞大。

查看存储过程和函数的定义

SHOW CREATE {PROCEDURE |FUNCTION } sp_name
例如：

SHOW CREATE FUNCTION test.CountProc2 \G

查看存储过程和函数的信息

在MYSQL中，存储函数和过程的信息存储在information_schema数据库下的Routines表中，可以通过查询该表的信息查询存储过程和函数的信息。
例如：

SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';

SELECT * FROM information_schema.Routines WHERE 
ROUTINE_NAME='CountProc2' AND ROUNTINE_TYPE ='FUNCTION' \G

修改存储过程和函数

ALTER {PROCEDURE |FUNCTION } sp_name [characteristic ...]
characteristic 指定存储函数的特征取值有：
1.CONTAINS SQL 表示子程序包含的SQL语句，但不包含读或写的数据的语句。
2.NO SQL 表示子程序中不包含 SQL语句。
3.READ SQL DATA 表示子程序中包含读数据的语句
4.MIDIFIES SQL DATA 表示子程序中包含写数据的语句
5.SQL SECURITY {DEFINER |INVOKER} 指明谁有权限执行
6.DEFINER 只有定义者才能执行
7.INVOKER 调用者可以执行
8. COMMENT’string’表示注释信息
例如：

ALTER PROCEDURE CounterProc 
	MODIFIES SQL DATE
	SQL SECURITY INVOKER;

删除存储过程和函数

DROP {PROCEDURE|FUNCTION } {IF EXISTS} sp_name
例如：

DROP PROCEDURE CountProc;