存储程序可以分为存储过程和函数,MySQL中创建存储过程和函数使用的语句分别为CREATE PROCEDURE
和CREATE FUNCTION
。使用CALL
语句调用存储过程智能用输出变量返回值。函数可以从语句外调用(通过引用函数名),也能返回标量值。存储过程也可以调用其他存储过程。
基本语法如下:
CREATE PROCEDURE sp_name([proc_parameter])
[characteristics ...] routine_body
参数 | 含义 | 备注 | ||||||||||||
CREATE PROCEDURE | 用来创建存储函数的关键字 | |||||||||||||
sp_name | 为存储过程的名称 | |||||||||||||
proc_parameters | 指定存储过程的参数列表 | [IN|OUT|INOUT] param_name type
| ||||||||||||
characteristics | 指定存储过程的特性 |
| ||||||||||||
routine_body | 是SQL代码的内容,可以用BEGIN...END表示SQL代码的开始和结束 |
例:
mysql> DELIMITER //
mysql> CREATE PROCEDURE Proc()
BEGIN
SELECT * FROM fruits;
END;
//
mysql> DELIMITER ;
注:"DELIMITER //“语句的作用是将MySQL的结束符设置为//,因为MySQL默认的语句结束符号为分号”;"为了避免与存储过程中的SQL语句结束符相冲突,需要使用DELIMITER
改变存储过程的结束符。存储过程结束后再用DELIMITER;
恢复默认结束符。
基本语法:
CREATE FUNCTION func_name([func_parameter])
RETURNS type
[characteristic ...] routine_body
参数 | 含义 | 备注 | ||||||||||||
CREATE FUNCTION | 用来创建存储函数的关键字 | |||||||||||||
func_name | 表示存储函数名称 | |||||||||||||
func_parameter | 存储过程的参数列表 | [IN|OUT|INOUT] param_name type
| ||||||||||||
RETURN type | 表示函数返回数据的类型 | |||||||||||||
characteristic | 指定存储函数的特性,取值与创建存储过程时相同 |
例:
mysql> DELIMITER //
mysql> CREATE FUNCTION NameByZip()
RETURNS CHAR(50)
RETURN (SELECT s_name FROM suppliers WHERE s_call='48075');
//
mysql> DELIMITER ;
注:指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。(FUNCTION中总是默认为IN参数)。RETURNS子句只能对FUNCTION指定,对函数而言这是强制的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。
在存储过程中使用DECLARE
语句定义变量:
DELARE var_name[,varname]... date_type [DEFAULT value]
参数 | 含义 |
var_name | 为局部变量的名称 |
DEFAULT value | 变量的默认值。值除了可以被声明为一个常数外,还可以被指定为一个表达式。如果没有DEFAULT子句,初始值为NULL |
定义变量之后,为变量赋值可以改变变量的默认值。
SET var_name=expr[,var_name=expr]...;
例:
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 fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);
SELECT f_name,f_price INTO fruitname,fruitprice
FROM fruits WHERE f_id='a1';
DECLARE condition_name CONDITION FOR [condition_type]
[condition_type]:
SQLSTATE [VALUE] sqlstate_value | mysql_error_code
参数 | 含义 |
condition_name | 表示条件的名称 |
condition_type | 表示条件的类型 |
sqlstate_value | 长度为5的字符串类型错误代码 |
mysql_error_code | 数值类型错误代码 |
例:
//方法一:使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000';
//方法二:
DECLARE command_not_allowed CONDITION FOR 1148;
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type: CONTINUE|EXIT|UNDO
condition_value: SQLSTATE[VALUE] sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
参数 | 含义 | 备注 | ||||||||||||
handler_type | 错误处理方式 |
| ||||||||||||
condition_value | 表示错误类型 |
| ||||||||||||
sp_statement | 参数为程序语句段,表示在遇到定义错误时,需要执行的存储过程或函数 |
例:
//方法一:捕获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 CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';
//方法四:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';
//方法五:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';
//方法六:使用SQLEXCPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
定义条件和处理过程,例:
mysql> CREATE TABLE test.t(s1 int,primary key (s1));
mysql> DELIMITER //
mysql>CREATE PROCEDURE handlerdemo()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
SET @x=1;
INSERT INTO test.t VALUES(1);
SET @x=2;
INSERT INTO test.t VALUES(1);
SET @x=3;
END;
//
mysql> DELIMITER ;
mysql> CALL handlerdemo();
mysql> SELECT @x;
DECLARE cursor_name CURSOR FOR select_statement
参数 | 含义 |
cursor_name | 光标名称 |
select_statement | SELECT语句返回一个用于创建光标的结果集 |
OPEN cursor_name{光标名称}
FETCH cursor_name INTO var_name[,var_name]...{参数名称}
CLOSE cursor_name{光标名称}
IF expr_condition THEN statement_list
[ELSEIF expr_condition THEN statement_list]...
[ELSE statement_list]
END IF
IF实现了一个基本的条件构造,如果expr_condition求值为真(TRUE),相应的SQL语句列表被执行;如果没有expr_condition匹配,则ELSE子句里的语句被执行
例:
IF val IS NULL
THEN SELECT 'val is NULL';
ELSE SELECT 'val is not NULL';
END IF;
CASE case_expr
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
参数 | 含义 |
case_expr | 参数表示条件判断的表达式 |
when_value | 表示表达式可能的值 |
statement_list | 表示不同when_value值得执行语句 |
CASE
WHEN expr_condition THEN statement_list
[WHEN expr_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
参数 | 含义 |
expr_condition | 表示条件判断语句 |
statement_list | 表示不同条件的执行语句 |
例:
CASE
WHEN val is NULL THEN SELECT 'val is NULL';
WHEN val<0 THEN SELECT 'val is less then 0';
WHEN val>0 THEN SELECT 'val is greater then 0';
ELSE SELECT 'val is 0';
END CASE;
LOOP循环语句用来重复执行某些语句,与IF和CASE语句相比,LOOP只是创建一个循环操作的过程,并不进行条件判断。
[loop_label:] LOOP
statement_list
END LOOP [loop_label]
例:
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 label
例:
add_num: LOOP
SET @count=@count+1;
IF @count=50 THEN LEAVE add_num;
END LOOP add_num;
ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE的意思为“再次循环”
例:
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
REPEAT语句创建一个带条件判断的循环过程,每次语句执行完毕之后,会对条件表达式进行判断,如果表达式为真,就结束循环;否则重复执行循环中的语句。
[repeat_label:] REPEAT
statement_list
UNTIL expr_condition
END REPEAT [repeat_label]
repeat_label为REPEAT语句的标注名称,该参数可以省略;REPEAT语句内的语句货语句群被重复,直至expr_condition
为真
例:
DECLARE id INT DEFAULT 0;
REPEAT
SET id=id+1;
UNTIL id>=10
END REPEAT;
WHILE语句创建一个带条件判断的循环过程,与REPEAT不同,WHILE语句执行时,先对指定的表达式进行判断。
[while_label:] WHILE expr_condition DO
statement_list
END WHILE [while_label]
例:
DECLARE i INT DEFAULT 0;
WHILE i<10 DO
SET i=i+1;
END WHILE;
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
SHOW CREATE {PROCEDURE | FUNCTION} sp_name
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';
ALTER {PROCEDURE|FUNCTION} sp_name [characteristic ...]
参数 | 含义 | 备注 | ||||||||||||||||||
sp_name | 参数表示存储过程或函数的名称 | |||||||||||||||||||
characteristic | 指定存储函数特性 |
|
例:
ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER;
例:
ALTER FUNCTION CountProc
READS SQL DATA
COMMENT 'FIND NAME';
DROP [PROCEDURE|FUNCTION] [IF EXISTS] sp_name
例:
DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;