MySQL存储过程和函数怎么创建
这篇文章主要介绍“MySQL存储过程和函数怎么创建”的相关知识，小编通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“MySQL存储过程和函数怎么创建”文章能帮助大家解决问题。
1.0 创建存储过程和函数
创建存储过程和函数就是将经常使用的一组 SQL 语句组合在一起，并将这些 SQL 语句当作一个整体存储在 MySQL 服务器
1. 创建存储过程
CREATE PROCEDURE procedure_name ([proc_param[,...]]) [characteristic ...] routine_body
procedure_name 表示所要创建的存储过程的名字
proc_param 表示存储过程的参数
characteristic 表示存储过程的特性
routine_body 表示存储过程的 SQL 语句
procedure_name 不能出现重名
proc_param 中每个参数的语法形式如下，每个参数由三部分组成，分别为输入/输出类型、参数名和参数类型。输入/输出类型有三种类型，分别是 IN（输入类型）、OUT（输出类型）、INOUT（输入/输出类型）。param_name 表示参数名，type 表示参数类型
[ IN | OUT | INOUT ] param_name type
characteristic 指定存储过程的特性，有以下取值：
LANGUAGE SQL：说明存储过程中使用的是 SQL 语言编写的
[NOT] DETERMINISTIC：说明存储过程执行的结果是否正确，DETERMINISTIC 表示结果是确定的，即每次执行存储过程，相同的输入会得到相同的输出；NOT DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出，默认为 NOT DETERMINISTIC
{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}：指明子程序使用 SQL 语句的限制，CONTAINS SQL 表示子程序不包含读或者写数据的语句，NO SQL 表示子程序不包含 SQL，READS SQL DATA 表示子程序包含读数据的语句，但是不包含写数据的语句，MODIFIES SQL DATA 表示子程序包含写数据的语句
SQL SECURITY {DEFINER | INVOKER}：指明谁有权限执行，DEFINER 表示只有定义者才能执行，INVOKER 表示拥有权限的调用者可以执行，默认为 DEFINER
COMMENT 'string'：注释信息，可以用来描述存储过程或函数
示例：创建存储过程，查询表 t_employee 中所有员工的薪水的存储过程
DELIMITER $$ CREATE PROCEDURE proc_employee() COMMENT '查询员工薪水' BEGIN SELECT salary FROM t_employee END; $$ DELIMITER ;
MySQL 中默认以分号 ; 作为语句结束符，存储过程中的 SQL 语句需要分号来结束，为了避免冲突，先用 DELIMITER $$ 将 MySQL 的结束符设置为 $$，再用 DELIMITER ; 将结束符恢复为分号
2. 创建存储函数
CREATE FUNCTION fun_name([func_param,[,...]]) [characteristic ...] routine_body
fun_name 表示函数名字
func_param 表示函数从参数
characteristic 表示函数特性，取值与存储过程的取值相同
routine_body 表示函数的 SQL 语句
func_param 中每个参数的语法形式如下，分别为参数名和参数类型
param_type type
还是之前的示例
DELIMITER $$ CREATE FUNCTION func_employee(id INT(4)) RETURNS INT(6) COMMENT '查询员工薪水' BEGIN SELECT salary FROM t_employee END; $$ DELIMITER ;
2|0变量
在 MySQL 中，可以定义和使用变量，这些变量的作用范围在 BEGIN ... END 程序段中
1. 定义变量
DECLARE var_name[,...] type [DEFAULT value]
关键字 DECLARE 用来声明变量
参数 var_name 是变量的名称，可以同时定义多个变量
参数 type 用来指定变量的类型
DEFAULT value 子句将变量默认值设置为 value，没有使用 DEFAULT 子句时，默认值为 NULL
示例：定义变量 sql_test，数据类型为 INT 型，默认值为 10
DECLARE test_sql INT DEFAULT 10;
2. 变量赋值
SET var_name=expr[,var_name=expr]...
关键字 SET 用来为变量赋值
参数 var_name 是变量的名称
参数 expr 是赋值表达式
示例：将变量 sql_test 赋值为 30
SET test_sql = 30;
3|0定义条件和处理程序
定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的能力
1. 定义条件
DECLARE condition_name CONDITION FOR condition_value condition_value: SQLSTATE[VALUE] sqlstate_value|mysql_error_code
参数 condition_name 表示条件名称
参数 condition_value 表示条件类型
参数 sqlstate_value 和 参数 mysql_error_code 都可以表示 MySQL 的错误
示例：定义 ERROR 1146(42S02) 错误，名称为 can_not_find，可以用两种不同的方法来定义
// 方法一:使用sqlstate_value DECLARE can_not_find CONDITION FOR SQLSTATE '42S02'; // 方法二:使用mysql_error_code DECLARE can_not_find CONDITION FOR 1146;
2. 定义处理程序
DECLARE handler_type HANDLER FOR condition_value[,...] proc_statement handler_type: CONTINUE|EXIT|UNDO condition_value: SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
handler_type 指明错误的处理方式：该参数有三个取值：
CONTINUE：表示遇到错误不处理，继续向下执行
EXIT：表示遇到错误马上退出
UNDO：表示遇到错误后撤回之前的操作
condition_value 表示错误类型，可以有以下取值：
SQLSTATE[VALUE]sqlstate_value 包含 5 个字符的字符串错误值
condition_name 表示 DECLARE CONDITION 定义的错误条件名称
SQLWARNING 匹配所有 01 开头的 SQLSTATE 错误代码
NOT FOUND 匹配所有 02 开头的 SQLSTATE 错误代码
SQLEXCEPTION 匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码
mysql_error_code 匹配数值类型错误代码
proc_statement 匹配数值类型错误代码
下面是定义处理程序的几种示例方式：
// 方法一:捕获sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NOT FOUND'; // 方法二:使用mysql_error_code DECLARE CONTINUE HANDLER FOR 1146 SET @info='NOT FOUND'; // 方法三:先定义条件,然后调用 ECLARE can_not_find CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR can_not_find SET @info='NOT FOUND'; // 方法四:使用SQLWARNING DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; // 方法五:使用NOT FOUND DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NOT FOUND'; // 方法六:使用SQLEXCEPTION DECLARE EXIT HANDLER FOR 使用SQLEXCEPTION SET @info='ERROR';
4|0光标
查询语句可能查询出多条记录，在存储过程和函数使用光标来逐条读取查询结果集中的记录。光标必须声明在处理程序之前，声明在变量和条件之后
1. 声明光标
DECLARE cursor_name CURSOR FOR select_statement;
参数 cursor_name 表示光标名称
参数 select_statement 表示 SELECT 语句
下面声明一个名为 cur_employee 的光标
DECLARE cur_employee CURSOR FOR SELECT name,age FROM t_employee;
2. 打开光标
OPEN cursor_name;
参数 cursor_name 表示光标的名称
下面打开一个名为 cur_employee 的光标
OPEN cur_employee;
3. 使用光标
FETCH cursor_name INTO var_name[,var_name...]
cursor_name 表示光标的名称
var_name 表示将光标中的 SELECT 语句查询出来的信息存入该参数，该参数必须在声明光标之前就定义好
下面打开一个名为 cur_employee 的光标，将查询的数据存入 emp_name 和 emp_age 这两个变量中
FETCH cur_employee INTO emp_name,emp_age;
4. 关闭光标
CLOSE cursor_name
cursor_name 表示光标的名称
5|0流程控制
1. IF 语句
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list]... [ELSE statement_list] END IF
参数 search_condition 表示条件判断语句
参数 statement_list 表示不同条件的执行语句
下面是一个 IF 语句的示例
IF age>20 THEN SET @count1=@conut1+1; ELSEIF age=20 THEN @count2=@conut2+1; ELSE @count3=@count3+1; END IF;
2. CASE 语句
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... [ELSE statement_list] END CASE
参数 case_value 表示条件判断的变量
参数 when_value 表示变量的取值
参数 statement_list 表示不同 when_value 的执行语句
下面的是一个 CASE 语句的示例
CASE age WHEN 20 THEN SET @count1=@count1+1; ELSE SET @count2=@count2+1; END CASE;
3. LOOP 语句
[begin_label:]LOOP statement_list END LOOP [end_label]
参数 begin_label 和参数 end_label 分别表示循环开始和结束的标志，必须相同，而且都可以省略
参数 statement_list 表示需要循环执行的语句
下面是一个 LOOP 语句的示例
add_num:LOOP SELECT @count1=@count1+1; END LOOP add_num
4. LEAVE 语句
主要用于跳出循环
LEAVE label
参数 label 表示循环的标志
下面是一个 LEAVE 语句的示例
add_num:LOOP SELECT @count1=@count1+1; IF @count1=100 THEN LEAVE add_num; END LOOP add_num
5. ITERATE 语句
也是用于跳出循环，不过是跳出本次循环，直接进入下次循环，类似 Java 的 continue
ITERATE label
ITERATE label
参数 label 表示循环的标志
下面是一个 ITERATE 语句的示例
add_num:LOOP SELECT @count1=@count1+1; IF @count1=100 THEN LEAVE add_num; ELSE IF MOD(@count1,3)=0 THEN ITERATE add_num; SELECT * FROM employee; END LOOP add_num
6. REPEAT 语句
REPEAT 语句是由条件的控制循环语句，当满足特定条件就会退出循环语句
[begin_label:]REPEAT statement_list UNTIL search_condition END REPEAT [end_label]
参数 statement_list 表示需要循环执行的语句
参数 search_condition 表示结束循环的条件，满足该条件即结束循环
下面是一个 REPEAT 语句的示例
add_num:REPEAT SELECT @count1=@count1+1; UNTIL @count1=100 END REPEAT add_num
7. WHILE 语句
WHILE 语句也是有条件控制的循环语句，与 REPEAT 语句不同的是，WHILE 语句是当满足条件时执行循环内的语句
[begin_label:]WHILE search_condition DO statement_list END WHILE [end_label]
参数 statement_list 表示需要循环执行的语句
参数 search_condition 表示结束循环的条件，满足该条件即结束循环
下面是一个 WHILE 语句的示例
WHILE @count<100 DO SET @count=@count+1; END WHILE;
6|0操作存储过程和函数
1. 调用存储过程
在 MySQL 中，使用 CALL 语句来调用存储过程
CALL proc_name([parameter[,...]])
proc_name 是存储过程的名称
paramter 是指存储过程的参数
2. 调用存储函数
在 MySQL 中，存储函数的使用方法和 MySQL 内部函数是一样的
3. 使用 SHOW STATUS 语句查看存储过程和函数的状态
SHOW {PROCEDURE|FUNCTION} STATUS{LIKE 'pattern'}参数 PROCEDURE 表示查询存储过程，FUNCTION 表示存储函数
参数 LIKE 'pattern' 用来匹配存储过程或函数的名称
4. 使用 SHOW CREATE 语句查看存储过程和函数的定义
SHOW CREATE {PROCEDURE|FUNCTION} proc_name5. 从 information_schema.Routine 表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routine WHERE ROUTINE_NAME='proc_name'
6. 修改存储过程和函数
ALTER {PROCEDURE|FUNCTION} proc_name[characteristic...];
characteristic:
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}
|COMMENT 'string'参数 proc_name 表示存储过程或函数的名称
参数 characteristic 指定存储过程的特性：
CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句
NO SQL 表示子程序不包含 SQL 语句
READS SQL DATA 表示子程序中包含读数据的语句
MODIFIES SQL DATA 表示子程序中包含写数据的语句
SQL SECURITY{DEFINER|INVOKER} 指明谁有权限执行：
DEFINER 表示只有定义者才能执行
INVOKER 表示调用者可以执行
COMMENT 'string' 是注释信息
7. 删除存储过程和函数
通过 DROP 语句删除存储过程
DROP PROCEDURE proc_name;
通过 DROP FUNCTION 语句删除存储函数
DROP FUNCTION func_name;
关于“MySQL存储过程和函数怎么创建”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识，可以关注蜗牛博客行业资讯频道，小编每天都会为大家更新不同的知识点。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：niceseo99@gmail.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
评论