简单来说,存储过程就是一条或者多条SQL语句的集合,可视为批文件,但是其作用不仅限于批处理
- 存储程序何以分为存储过程和函数
- MySQL中村建存储过程和函数使用的语法分别是CREATE PROCEDURE和CREATE FUNCTION
- 使用CALL语句来调用存储过程,只能用输出变量返回值
- 函数可以从语句外调用(即通过引用函数名),也能返回标量值
- 存储过程也能调用其他存储过程
1.创建存储过程
CREATE PROCEDURE sp_name ([proc_parameter])
[characteristics...] routine_body
- CREATE PROCEDURE 为用来创建存储函数的关键字
- sp_name为存储过程的名称
- proc_parameter 为指定存储过程的参数列表;形式如:
[IN|OUT|INOUT] param_name type- IN表示输入参数
- OUT表示输出参数
- INOUT表示即可以输入也可以输出
- param_name表示参数名称
- type表示参数的类型,该类型可以是MySQL数据库中的任意类型
- characteristics 指定存储过程的特性,有以下取值:
- LANGUAGE SQL:说明routine_body部分是有SQL语句组成的,当前系统支持的语言为SQL,SQL是LANGUAGE特性的唯一值
- [NOT] DETERMINISTIC:指明存储过程执行的结果是否确定;
- DETERMINISTIC表示结果是确定的;每次执行存储过程时,相同的输入会得到相同的输出;
- NOT DETERMINISTIC表示结果是不确定的,相同的输入可能的带不同的输出;如果没有指定任意一个值,默认为NOT DETERMINISTIC
- {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}:知名子程序使用SQL语句的限制;
- CONTAINS SQL表明子程序包含SQL语句,但是不包含读写数据的语句;
- NO SQL表明子程序不包含SQL语句;
- READS SQL DATA说明子程序包含读数据的语句;
- MODOFIES SQL DATA表明子程序包含写数据的语句;
- 默认情况下,系统指定为CONNTAINS SQL
- SQL SECUTITY {DEFINER|INVOKER}:指定谁有权限来执行
- DEFINER表示只有定义者才能执行
- INVOKER表示拥有权限的调用者可以执行
- 默认情况下,系统指定为DEFINER
- COMMENT'string':注释消息,可以用来描述存储过程或者函数
- routine_body是SQL代码的内容,可以用BEGIN...END来表示SQL代码的开始和结束
例子:
CAREATE PRODCEDURE AvgFruitPrice()
BEGIN
SELECT AVG(f_price) AS avgprice
FROM fruits
END;
CREATE PROCEDURE Proc()
BEGIN
SELECT * FROM fruits;
END;
这行代码创建了一个查看fruits表的存储过程,每次调用这个存储过程的适合都会执行SELECT语句查看表的内容,执行过程如下: 
"DELIMITER//"语句的作用是将MySQL的结束符设置为//,因为MySQL默认的语句结束符号为';',为了避免与存储过程中SQL语句结束符想冲突,需要使用DELIMITER改变存储过程的结束符,并已"END//"结束存储过程,存储过程定义完毕后在使用"DELIMITER;"恢复默认结束符,DELIMITER也可以指定其他符号做为结束符
当使用DELIMITER命令时,应该避免使用反斜杠('')字符,因为反斜线时MySQL的转义字符
创建存储函数
CREATE FUNCTION func_name([func_parameter])
RETURNS type
[characteristic ...] routine_body
例子:
CREATE FUNCTION NameByZip()
RETURNS CHAR(50)
RETURN (SELECT s_name FROM suppliers WHERE s_call='48075');
指定参数为IN,OUT或INOUT支队PRODEDURE是合法的;(FUNCTION中总是默认IN参数);RETURNS子句只能对FUNCTION做指定,对函数而言这是强制的;它用来指定函数的返回类型,并且函数体必要包含一个RETURN value语句
3.变量的使用
- 定义变量
DECLARE var_name [,varname]...date_type [DEFAULT value];
如果没有DEFAULT子句,初始值为NULL
例子:
DECLARE myparam INT DEFAULT 100;
- 定义变量
SET var_name =expr[, var_name=expr]...;
在存储程序中的SET语句是一般SET语句的扩展版本
例子:
DECLARE var1,var2,var3 INT;
SET var1=10,var2=20;
SET var3=var1+var2;
MySQL还可以通过SELECT...INTO为一个或多个变量赋值
SELECT col_name[...] INTO var_name[,...] table_expr;
这个SELECT语法把选定的列直接存储到对应位置的变量,
- col_name表示字段名称
- var_name表示定义的变量名称
- table_expr表示查询条件表达式,包括表名称和WHERE子句 例子:
DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);
SELECT f_name,f_price INTO fruitname,fruitprice FROM fruits WHERE f_id="a1"
4.定义条件和处理程序
定义条件是事先定义程序执行过程中遇到的问题,处理程序定义了在遇到这些问题时应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行;这样可以增强存储程序处理问题的能力,避免程序异常停止运行
- 定义条件
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为数值类型错误代码
- sqlstate_value 为长度为5的字符串类型错误代码
- mysql_error_code为数值类型错误代码
- 例如:ERROR 1142(42000)中,sqlstate_value的值时42000,MySQL_error_code的值时1142
例子:定义"ERROR 1148(42000)"错误,名称为command_not_allowed
//方法一:使用sqlstate_value
DECLATE command_not_allow CONDITION FOR SQLSTATE '42000';
//方法二:使用mysql_error_code
DECLARE command_not_allow 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为错误处理方式,参数取3个值:CONTINUE,EXIT和UNDO
- CONTINUE表示遇到错误不处理,继续执行
- EXIT 表示遇到错误马上退出
- UNDO表示晕倒错误后撤回之前的操作,MySQL中暂不支持
- condition_type表示错误类型
- SQLSTATE[VALUE]sqlstate_value:包含5个字符的字符串错误值
- condition_name:表示DECLARE CONDOTION定义的错误条件名称
- SQLWARING:匹配所有以01开头的SQLSTATE错误代码
- NOTFOUND:匹配所有以02开头的SQLSTATE错误代码
- SQLEXCEPTION:匹配所有没有被SQLWATING或NOTFOUND捕获的SQLSTATE错误代码
例子:
//方法一:捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQ:STATE '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 FOUND SET @info='NO_SUCH_TABLE';
//方法六
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';
- 第一种方法是捕获sqlstate_value值;如果遇到sqlstate_value值为'42S02',执行CONTINUE操作,并且输出'NOT_SUCH_TABLE'信息
- 第二种方法时捕获MySQL_error_code值;如果遇到MySQL_error_code值为1146.执行CONTINUE操作,并且输出"NO_SUCH_TABLE"信息
- 第三种方法是先定义条件,然后再调用条件;这里先定义no_such_table条件,遇到1146错误就执行CONTINUE操作
- 第四种方法是使用SQLWARNING;SQLWARNING捕获所有以01开头的sqlstate_value值,然后执行EXIT操作,并且输出'ERROR'信息
- 第五种方法是使用NOTFOUND;NOTFOUND捕获所有以02开头的sqlstate_value值,然后执行EXIT操作,并且输出"NO_SUCH_TABLE"信息
- 第六种方法是使用SQLEXCEPTION;SQLEXCEPTION捕获所有没有被SQLWARNING或NOTFOUND捕获的sqlstate_value值,然后执行EXIT操作,并且输出"ERROR"信息
例子:
CREATE TABLE test.t(s1 int,primary key(s1));
DELIMITER //
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;
//
DELIMITER ;
/*调用存储过程*/
CALL handlerdemo();
/*查看调用过程结果*/
SELECT @x;
@var_name表示用户变量,使用SET语句为其赋值,用户变量和连接有关,一个客户端定义的变量不能被其他客户端看到或使用;当客户端退出时,该客户端连接的所有变量将自动释放
5.光标的使用
查询语句可能返回多条记录,如果数据量非常大,需要再存储过程和储存函数中使用光标来逐条读取查询结果集中的记录;应用程序可以根据需要滚动或浏览其中的数据
- 声明光标
DELACRE cursor_name CURSOR FOR select_statement
- cursor_name:表示光标的名称
- select_statement:表示SELECT语句的内容,放回一个用于创建光标的结果集
例子:
DECLARE cursor_fruit CURSOR FOR SELECT f_name,f_price FROM fruits;
- 打开光标
OPEN cursor_name{光标名称}
例子:
OPEN cursor_fruit
- 使用光标
FETCH cursor_name INTO var_name [,var_name] ...{参数名称}
var_name表示将光标中的SELECT语句查询出来的信息存入该参数中,var_name必须再声明光标之前就定义好
例子:
FETCH cursor_fruit INTO fruit_name,fruit_price;
- 关闭光标
CLOSE corsor_name{光标名称}
例子:
CLOSE corsor_fruit;
6.流程控制的使用
流程控制语句用来根据条件控制语句的执行
- IF语句
IF expr_condition THEN statement list
[ELSEIF expr_condition THEN statement_list] ...
[ELSE statement_list]
END IF
MySQL还有一个IF()函数,它不用于这里描述的IF语句
- CASE语句
CASE case_expr
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
CASE
WHEN expr_confition WHEN statement_list
[WHEN expr_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
该语句中,WHEN语句将会被逐个执行,知道某个expr_condition表达式为真,则执行对应THEN关键字后面的statement_list语句;如果没有条件匹配,ELSE子句里的语句被执行
这里结束的用在存储成立里的CASE语句与"控制流程函数"里描述的SQL CASE表达式的CASE语句有轻微不同,这里的CASE语句不能有ELSE NULL子句,并且用END CASE替代END来终止
- LOOP语句 LOOP循环语句用来重复执行某些语句
[loop_label:] LOOP
statement_list
END LOOP [loop_label]
- loop_label表示LOOP语句的标注名称,可省略
- statement_list表示需要循环执行的语句
例子:
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 语句用来退出任何被标注的流程控制构造
LEAVE label
例子:
add_num:LOOP
SET @count=@count+1;
IF @count=50 THEN LEAVE add_num;
END LOOP add_num;
- ITERATE INEARTH 语句将执行顺序转到语句段开头处
ITERATE label;
- INERATE只可以出现在LOOP,REPAEAT和WHILE语句内
- INTRATE的意思为"再次循环",label参数表示循环的标志
- 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语句创建一个带条件判断的循环过程,每次语句执行完毕之后,会对条件表达式进行判断,如果表达式为真,则循环结束,斗则重复执行循环中的语句
[repeat_label:] REPEAT
statement_list
UNTLL expr_condition
END REPEAT [repeat_label]
例子:
DECLARE id INT DEFAULT 0;
REPEAT
SET id=id+1;
UNTIL id>=10
END REPEAT;
- WHILE语句 WHILE语句创建一个带条件判断的循环过程,与REPEAT不用,WHILE在执行语句时,先对制定的表达式进行判断,如果未真免责执行循环内的语句,否则跳出循环
[while_label:] WHILE expr_confition DO
statement_list
END WHILE [while_label]
例子:
DECLARE i INT DEFAULT 0;
WHILE i<10 DO
SET i=i+1;
END WHILE
1111
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
