文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL学习笔记(11):存储过程和函数

2020-01-18 14:41

关注

MySQL学习笔记(11):存储过程和函数

本文更新于2019-06-23,使用MySQL 5.7,操作系统为Deepin 15.4。

目录

创建存储过程或函数

创建存储过程:

CREATE PROCEDURE name ({[IN|OUT|INOUT] param type}[, ...])
[characteristic]
body

创建函数:

CREATE FUNCTION name ({param type}[, ...]) RETURNS type
[characteristic]
body

type表示数据类型。

characteristic表示特征值,特征值目前只是提供信息给服务器,定义如下:

LANGUAGE SQL
|[NOT] DETERMINISTIC
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT "string"

body由BEGINEND包起,其中可以定义变量、条件、处理、游标,以及使用流程控制语句跳转逻辑。

存储过程和函数的CREATE语法不支持OR REPLACE

存储过程和函数的区别:函数一定有返回值,存储过程一定没有返回值;函数的参数只能使用IN类型,存储过程的参数可使用INOUTINOUT类型。

MySQL的存储过程和函数中允许包含DDL语句,允许执行提交或回滚,允许调用其他存储过程或函数,但是是不允许执行LOAD DATA INFILE语句。

通常在执行创建存储过程或函数前,使用DELIMITER ;;命令将语句的结束符从;修改成其他符号(示例使用;;)。在创建完毕后,通过DELIMITER ;命令将结束符还原。这样可避免存储过程定义中的;被错误解析为结束符。

修改存储过程或函数

ALTER PROCEDURE|FUNCTION name
[characteristic]

characteristic特征值如下:

{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT "string"

删除存储过程或函数

DROP PROCEDURE|FUNCTION [IF EXISTS] name

一次只能删除一个存储过程或函数。

调用存储过程或函数

CALL name(param[, ...])

如果是输出参数,需传入变量,如:@xxx

变量、条件、处理、游标都通过DECLARE定义,但它们之间是有先后顺序的:变量和条件必须在最前面声明,然后才是游标的声明,最后才是处理的声明。

变量

变量不区分大小写,作用范围只能在BEGIN ... END块中,可以用在嵌套的块中。变量的定义必须写在复合语句的开头,并且在其他语句的前面。

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

变量直接赋值:

SET {var = value}[, ...]

变量通过查询赋值,这要求查询返回的结果只有一行:

SELECT colname[, ...] INTO var[, ...] FROM ...

FROM后面的子句同普通的SELECT查询,在此省略。

条件

DECLARE condition_name CONDITION FOR
{SQLSTATE [VALUE] value}|mysql_error_code

条件枚的含义如下:

处理

DECLARE CONTINUE|EXIT|UNDO HANDLER FOR
{SQLSTATE [VALUE] value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code}[, ...]
sp_statement

处理类型枚举值的含义:

条件枚的含义如下:

游标

声明游标:

DECLARE cursor_name CURSOR FOR select_statement

打开游标:

OPEN cursor_name

读取游标:

FETCH cursor_name INTO var[, ...]

关闭游标:

CLOSE cursor_name

IF

IF condition1 THEN statement_list1
[ELSEIF condition2 THEN statement_list2]
[...]
[ELSE statement_list3]
END IF

CASE

CASE case_value
WHEN value1 THEN statement_list1
[...]
[ELSE statement_list2]
END CASE

或:

CASE
WHEN condition1 THEN statement_list1
[...]
[ELSE statement_list2]
END CASE

LEAVE

从标注的流程中退出,通常和BEGIN ... END或循环一起使用。

LEAVE label

ITERATE

ITERATE必须用在循环中,作用是跳过当前循环的剩下语句,进入下一轮循环。

ITERATE label

LOOP

通常需在statement_list中指定退出条件,否则为死循环。

[begin_label:] LOOP
	statement_list
END LOOP [end_label]

WHILE

当满足条件时执行循环。会在首次循环执行前判断条件,故循环最少执行0次。

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

REPEAT

当满足条件时退出循环。会在首次循环执行后判断条件,故循环最少执行1次。

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

存储过程定义示例如下:

DELIMITER ;;

CREATE PROCEDURE sp_test(IN id INT, OUT sum INT)
BEGIN
	DECLARE var_value, var_sum INT DEFAULT 0;
	DECLARE flag INT DEFAULT 1;
	DECLARE cur CURSOR FOR SELECT value FROM t WHERE tid = id;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 0;
	OPEN cur;
	loop_cur: LOOP
		FETCH cur INTO var_value;
		IF flag = 0 THEN
			LEAVE loop_cur;
		END IF;
		var_sum = var_sum + var_value;
	END LOOP;
	CLOSE cur;
END;;

DELIMITER ;
阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯