引言
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。 存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。 存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用,也就是下图语法分析的过程:
也就是说存储过程是 SQL 语句与控制语句的预编译集合,以一个名称存储并作为一个单元处理。
创建存储过程
DELIMITER //
CREATE PROCEDURE 存储过程名([参数类型 参数名 参数数据类型],...)
BEGIN
...
END //
DELIMITER ;
delimiter 更改结束符:
DELIMITER //
- 第一个命令是 ’ DELIMITER // ',它与存储过程语法无关。 DELIMITER语句将标准结束符分号 ’ ; ’ 更改为 ’ // '。
- 更改结束符的原因是: 存储过程是作为整体传递给服务器, 如果分号为结束符,由于存储过程主体语句是由SQL 语句组成的可能会有很多分号,就不能完整的执行整个存储过程。
- 这里DELIMITER后可以跟任意字符,一般使用 ’ $$ ’ ;
参数:
[参数类型 参数名 参数数据类型]
-
参数类型 IN、OUT、INOUT:
- IN: 默认模式,在存储过程中定义IN参数时,调用程序必须将参数传递给存储过程。
注: 另外, IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值,在存储过程结束后仍保留其原始值。换句话说,存储过程只使用IN参数的副本。 - OUT: 可以在存储过程中更改OUT参数的值,并将其更改后新值传递回调用程序。
注:存储过程在启动时无法访问OUT参数的初始值。 - INOUT: 是IN和OUT参数的组合。 既可以传递参数,并且存储过程可以修改INOUT参数并将新值传递回调用程序。
- IN: 默认模式,在存储过程中定义IN参数时,调用程序必须将参数传递给存储过程。
变量:
DECLARE 变量名 变量数据类型 DEFAULT 默认值;
- DECLARE: 在DECLARE关键字后面要指定变量名。变量名必须遵循MySQL表列名称的命名规则。
- 指定变量的数据类型及其大小;当声明一个变量时,它的初始值为NULL;但是可以使用DEFAULT关键字为变量分配默认值。
- SET: 变量值可以通过 SET 改变;如: SET 变量名 = 值;
- 变量 作用域:
变量有自己的 作用域 ,它用来定义它的生命周期。- 如果在存储过程中声明一个变量,那么当达到存储过程的 END 语句时,它 将超出范围,因此在其它代码块中无法访问。
- 如果您在任意 BEGIN END 块内声明一个变量,那么如果达到END,它将超 出范围。 可以在不同的作用域中声明具有相同名称的两个或多个变量,因为变量仅在自己的作用域中有效。 但是,在不同范围内声明具有相同名称的变量不是很好的编程习惯。
- 以@符号开头的变量是用户变量。
注:
- 用户变量名一般以@开头
- 滥用用户变量会导致程序难以理解及管理
控制语句:
最外层的 BEGIN 和 END之间的部分称为存储过程的主体。将 SQL语句与控制语句放在主体中以处理业务逻辑。
条件语句:
-
IF 语句用法:
类似于C语言中的 if ,else if, else
IF:IF expression THEN statements; END IF;
IF ELSE :
IF expression THEN statements; ELSE else-statements; END IF;
IF ELSE IF ELSE:
IF expression THEN statements; ELSEIF elseif-expression THEN elseif-statements; ... ELSE else-statements; END IF;
-
CASE 语句用法:
类似于C语言中的 switch caseCASE case_expression WHEN when_expression_1 THEN commands WHEN when_expression_2 THEN commands ... ELSE commands END CASE;
循环语句:
-
WHILE 语句用法:
类似于C语言中的 whileWHILE expression DO statements END WHILE;`
-
REPEAT 语句用法:
类似于C语言中的 do while
REPEAT statements; UNTIL expression END REPEAT;
-
LOOP 语句用法:
loop 循环不需要初始条件,这点和 while 循环相似,同时和 repeat 循环一样不需要结束条件loop_label: LOOP ... END LOOP;
控制循环:
- LEAVE 用于立即退出循环,而无需等待检查条件。LEAVE类似于C语言的 break 。
- ITERATE 允许您跳过剩下的整个代码并开始新的迭代。ITERATE语句类似于C语言的 continue 。
例子:
DELIMITER //
CREATE PROCEDURE test1 (IN require tinyint, IN usernames varchar(16))
BEGIN
IF require = 1 THEN
SELECT * FROM user WHERE username = usernames;
ELSE IF require = 2 THEN
DELETE FROM user WHERE username = usernames;
ELSE IF require = 3 THEN
INSERT INTO user(username) VALUES (usernames);
END IF;
END //
DELIMITER ;