1、创建存储过程
语法格式:
CREATE PROCEDURE sp_name ([proc_paramenter]) [characteristics] routine_body
CREATE PROCEDURE:用来创建存储过程的关键字;
sp_name:存储过程的名称;
proc_parameter:指定存储过程的参数列表,形式:[IN|OUT|INOUT] param_name type
IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;
param_name表示参数名称;
type表示参数类型,该类型可以是mysql数据库中任意类型。
characteristics:指定存储过程的特性。
routine_body:是SQL代码内容,可以用BEGIN......END来表示SQL代码的开始和结束。
DELIMITER //:语句作用是将MySQL的结束符设置为//,因为MySQL默认的语句结束符合为';',为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER 改变存储过程的结束符,并以“END //”结束存储过程。存储过程定义完毕后在使用“DELIMITER ;”恢复默认结束符。也可以指定其他符号为结束符。
2、创建存储函数
语法格式:
CREATE FUNCTION func_name([func_parameter]) RETURNS type [characteristics] routine_body
CREATE FUNCTION :用来创建存储函数的关键字;
func_name:存储函数的名称;
func_parameter:指定存储函数的参数列表,形式:[IN|OUT|INOUT] param_name type
IN表示输入参数,OUT表示输出参数,INOUT表示既可以输入也可以输出;
param_name表示参数名称;
type表示参数类型,该类型可以是mysql数据库中任意类型。
RETURNS type:表示函数返回数据类型。
characteristics:指定存储函数的特性。
routine_body:是SQL代码内容,可以用BEGIN......END来表示SQL代码的开始和结束。
3、变量的使用
3.1、定义变量
语法格式:
DECLARE var_name[,var_name]... data_type [DEFAULT value];
var_name:为局部变量的名称。
DEFAULT value:给变量提供默认值,值除了可以被声明一个常数外,还可以被指定为一个表达式。如果没有该子句,初始值为NULL。
3.2、为变量赋值
1)、语法格式:
SET var_name=expr[,var_name=expr]...;
2)、MYSQL中还可以通过SELECT...INTO为一个或多个变量赋值:
SELECT col_name[,col_name,....] INTO var_name[,var_name,....] table_expr;
SELECT语法把选定的列直接存储到对应位置的变量。
col_name表示字段名称。
var_name表示定义的变量名称。
table_expr表示查询条件表达式,包括表名称和WHERE子句。
4、定义条件和处理程序
4.1、定义条件
DECLARE condition_name CONDITION FOR [condition_type];
condition_type:SQLSTATE [VALUE] sqlstate_value | mysql_error_code;
condition_name:参数表示条件名称;
condition_type:表示条件的类型;
sqlstate_value与mysql_error_code都可以表示mysql的错误,sqlstate_value表示长度为5的字符串类型错误代码,mysql_error_code为数值类型错误代码。
4.2、定义处理程序
语法格式:
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement;
handler_type:CONTINUE|EXIT|UNDO
condition_value:SQLSTATE [VALUE] sqlsate_value
| condition_name|SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_code
5、光标的使用(MYSQL中光标只能在存储过程和函数中使用)
5.1、声明光标
DECLARE cursor_name CURSOR FOR select_statement;
cursor_name:光标的名称。
select_statement:表示SELECT语句的内容,返回一个用于创建光标的结果集。
5.2、打开光标
OPEN cursor_name{光标名称}
5.3、使用光标
FETCH cursor_name INTO var_name[,var_name]...{参数名称};
var_name必须在声明光标之前就定义好。
5.4、关闭光标
CLOSE cursor_name{光标名称}
6、流程控制的使用
6.1、IF语句
IF expr_condition THEN statement_list
[ELSEIF expr_condition THEN statement_list]
[ELSE statement_list]
END IF
6.2、CASE语句
6.2.1、CASE case_expr
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]
[ELSE statement_list]
END CASE
6.2.2、CASE
WHEN expr_condition THEN statement_list
[WHEN expr_condition THEN statement_list]
[ELSE statement_list]
END CASE
6.3、LOOP语句
[loop_label:] LOOP
statement_list
END LOOP [loop_label]
loop_label:表示LOOP语句的标注名称,该参数可以省略。
statement_list:需要循环执行的语句。
6.4、LEAVE语句
LEAVE语句用来退出任何被标注的流程控制构造。
LEAVE label;
6.5、ITERATE语句
ITERATE label;
ITERATE 语句将执行顺序转到语句段开头处,只可以出现在LOOP、REPEAT和WHILE语句内。意思为“再次循环”,label参数表示循环的标志,ITERATE语句必须跟在循环标志前面。
6.6、REPEAT语句
REPEAT语句创建一个带条件判断的循环过程,每次执行完毕之后,会对条件表达式j进行判断,如果表达式为真,则循环结束;否则重复执行循环中的语句。
[repeat_label:] REPEAT
statement_list
UNTIL expr_condition
END REPEAT [repeat_label]
6.7、WHILE语句
WHILE语句创建一个带条件判断的循环过程,与REPEAT不同,WHILE在执行语句执行时,先对z指定的表达式进行判断,如果为真,则执行循环内的语句,否则推出循环。
[while_label:] WHILE expr_condition DO
statement_list
END WHILE [while_label]
7、调用存储过程和函数
7.1、调用存储过程
CALL sp_name([parameter[,...]])
7.2、调用存储函数
调用存储函数和MYSQL内部函数调用方法一样。
8、查看存储过程和函数
8.1、SHOW STATUS 语句查看存储过程和存储函数
SHOW {PROCEDURE|FUNCTION} STATUS {LIKE 'pattern'}
8.2、SHOW CREATE 语句查看存储过程和函数定义
SHOW CREATE {PROCEDUR|FUNCTION} sp_name
8.3、从infomation_schema.Routines 表中查看存储过程和函数的信息
SELECT * FROM infomation_schema.Routines WHERE ROUTINE_NAME='sp_name'
9、修改存储过程和函数
ALTER {PROCEDURE|FUNCTION} sp_name {characteristic}
10、删除存储过程和函数
DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name
11、存储过程和存储函数区别
1)、函数必须有返回值,而过程可以没有。
2)、函数可以在SELECT语句中使用,而过程不能。
3)、函数不能修改表的数据(除非使用MODIFY或ALTER权限),而过程可以。
12、存储过程和存储函数共同点
1)、都可以提高代码复用性。
2)、都可以提高数据库操作的效率。
3)、都可以通过参数传递数据。
4)、都可以包含事务控制。
13、注意事项
1)、存储过程和函数的修改可能影响到依赖它们的应用程序,因此在修改时需要谨慎。
2)、在执行存储过程或函数时,如果发生错误,可能会回滚整个事务,除非在过程中显式使用BEGIN和COMMIT来管理事务。