1、什么是存储过程
存储过程是一组为了完成特定功能的SQL语句集,是SQL语句和流程控制语句的集合。
存储过程经过编译后存储在数据库中。
用户通过制定存储过程的名字并给出参数(若有)来执行它。
2、存储过程的创建过程
2.1、使用mysql命令行
2.1.1、登录数据库,选择数据库;
2.1.2、更改语句结束符号
因为存储过程中会用到系统默认的结束符号";"。可以将语句结束符改成你喜欢的符号,此处改成"//";
delimiter //
2.1.3、创建存储过程,结束后将结束符号改回";";
2.1.4、通过call语句调用存储过程。
2.2、使用图形化数据库管理工具,如Navicat。
3、存储过程语法
3.1、创建存储过程
多个参数用逗号","隔开。CREATE PROCEDURE 存储过程名(参数列表) BEGIN SQL语句代码块 END
参数类型有IN、OUT、IN、OUT。
IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值。
OUT 输出参数:该值可在存储过程内部被改变,并可返回。
INOUT 输入输出参数:调用时指定,并且可被改变和返回。
3.2、修改存储过程
3.3、删除存储过程CREATE PROCEDURE 存储过程名(参数列表) BEGIN SQL语句代码块 END
3.4、查看一个存储过程(查看语句)DROP PROCEDURE IF EXISTS 存储过程名;
3.5、查看所有存储过程的状态SHOW CREATE PROCEDURE 存储过程名;
3.6、调用存储过程SHOW PROCEDURE STATUS;
存储过程名后面必须加括号,哪怕改存储过程没有参数传递。CALL 存储过程名(参数列表);
3.7、BEGIN...END语句BEGIN...END中包含存储过程中的处理过程,可以包含多个语句。
3.8、声明变量(DECLARE)
3.9、变量赋值(SET)DECLARE var[,var] type [DEFAULT value]; DECLARE x,y INT DEFAULT 0;
3.10、IF判断SET var_name = expr[,var_name = expr]; SET x=1, y=2;
3.11、CASE判断IF expression THEN commands [ELSEIF expression THEN commands] [ELSE commands] END IF;
3.12、WHILE循环CASE case_expression WHEN when_expression THEN commands WHEN when_expression THEN commands ... ELSE commands END CASE;
3.13、REPEAT循环WHILE expression DO Statements END WHILE
3.14、LOOP循环REPEAT Statements; UNTIL expression END REPEAT
LOOP用来标记循环;LEAVE用来离开循环,类似break;ITERATE用来继续循环,类似continue。
3.15、游标Cursorloop_name:LOOP IF expression THEN LEAVE loop_name; END IF; ... END LOOP
游标的作用就是用于对查询数据库所返回的记录进行遍历,以便进行相应的操作;游标有下面这些属性:
游标是只读的,也就是不能更新它;
游标是不能滚动的,也就是只能在一个方向上进行遍历,不能在记录之间随意进退,不能跳过某些记录;
避免在已经打开游标的表上更新数据。
如何使用游标
a、声明一个游标;
b、使用OPEN语句打开游标;DECLARE cursor_name CURSOR FOR SELECT_statement;
c、使用FETCH语句来获取下一行数据,类似于iterator;OPEN cursor_name;
d、结束操作后释放游标。FETCH cursor_name INTO variable list;
e、实例,获取库存了小于100的产品,用于演示cursor的用法CLOSE cursor_name;
3.16、SELECT...INTO语句DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`CursorProc` $$ CREATE PROCEDURE `test`.`CursorProc` () BEGIN DECLARE no_more_products, quantity_in_stock INT DEFAULT 0; DECLARE prd_code VARCHAR(255); DECLARE cur_product CURSOR FOR SELECT code FROM products; /*First: Delcare a cursor,首先这里对游标进行定义*/ DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1; /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/ /* for loggging information 创建个临时表格来保持*/ CREATE TEMPORARY TABLE infologs ( Id int(11) NOT NULL AUTO_INCREMENT, Msg varchar(255) NOT NULL, PRIMARY KEY (Id) ); OPEN cur_product; /*Second: Open the cursor 接着使用OPEN打开游标*/ FETCH cur_product INTO prd_code; /*Third: now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/ REPEAT SELECT quantity INTO quantity_in_stock FROM products WHERE code = prd_code; IF quantity_in_stock < 100 THEN INSERT INTO infologs(msg) VALUES (prd_code); END IF; FETCH cur_product INTO prd_code; UNTIL no_more_products = 1 END REPEAT; CLOSE cur_product; /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/ SELECT * FROM infologs; DROP TABLE infologs; END $$ DELIMITER ;
3.17、条件处理/错误、异常处理(待完善)SELECT col_name[,...] INTO var_name[,...] table_expr; SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
a、DECLARE...HANDLER语句
b、DECLARE...CONDITION语句DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement handler_action: CONTINUE | EXIT | UNDO condition_value: mysql_error_code | SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | NOT FOUND | SQLEXCEPTION
DECLARE duplicate_key CONDITION FOR SQLSTATE '23000'; DECLARE CONTINUE HANDLER FOR duplicate_key BEGIN -- body of handler END;
4、一个实例
DELIMITER $$
CREATE PROCEDURE databaseName.procedureName()
BEGIN
DECLARE done int DEFAULT 0;
DECLARE userID int DEFAULT 0;
DECLARE userCursor CURSOR for select YongHuID from databaseName.table1Name;
DECLARE CONTINUE HANDLER FOR NOT FOUND set done = TRUE;
OPEN userCursor;
user_loop:LOOP
fetch userCursor INTO userID; //将游标赋值给变量,开始循环
IF done THEN LEAVE user_loop;
end if;
......各种操作....
END LOOP;
close userCursor;
END $$
DELIMITER ;