mysql存储过程_procedure databasename-CSDN博客

本文链接：https://blog.csdn.net/half1/article/details/19766439

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、修改存储过程
CREATE PROCEDURE 存储过程名(参数列表)
    BEGIN
        SQL语句代码块
    END
3.3、删除存储过程
DROP PROCEDURE IF EXISTS 存储过程名;
3.4、查看一个存储过程(查看语句)
SHOW CREATE PROCEDURE 存储过程名;
3.5、查看所有存储过程的状态
SHOW PROCEDURE STATUS;
3.6、调用存储过程
CALL 存储过程名(参数列表);
存储过程名后面必须加括号，哪怕改存储过程没有参数传递。
3.7、BEGIN...END语句

BEGIN...END中包含存储过程中的处理过程，可以包含多个语句。

3.8、声明变量(DECLARE)
DECLARE var[,var]  type  [DEFAULT value];
DECLARE x,y INT DEFAULT 0;
3.9、变量赋值(SET)
SET var_name = expr[,var_name = expr];
SET x=1, y=2;
3.10、IF判断
IF expression THEN commands
    [ELSEIF  expression THEN  commands]
    [ELSE  commands]
    END IF;
3.11、CASE判断
CASE  case_expression
   WHEN when_expression THEN commands
   WHEN when_expression THEN commands
   ...
   ELSE commands
END CASE;
3.12、WHILE循环
WHILE expression DO
   Statements
END WHILE
3.13、REPEAT循环
REPEAT
Statements;
UNTIL expression
END REPEAT
3.14、LOOP循环

LOOP用来标记循环；LEAVE用来离开循环，类似break；ITERATE用来继续循环，类似continue。
loop_name:LOOP
   IF expression THEN
       LEAVE  loop_name;
   END IF;
   ...
END LOOP
3.15、游标Cursor

游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作；游标有下面这些属性：

游标是只读的，也就是不能更新它；

游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；

避免在已经打开游标的表上更新数据。

如何使用游标

a、声明一个游标；
DECLARE cursor_name CURSOR FOR SELECT_statement;
b、使用OPEN语句打开游标；
OPEN cursor_name;
c、使用FETCH语句来获取下一行数据，类似于iterator；
FETCH cursor_name INTO variable list;
d、结束操作后释放游标。
CLOSE cursor_name;
e、实例，获取库存了小于100的产品，用于演示cursor的用法
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.16、SELECT...INTO语句
SELECT col_name[,...] INTO var_name[,...]  table_expr;
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
3.17、条件处理/错误、异常处理(待完善）
a、DECLARE...HANDLER语句
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
b、DECLARE...CONDITION语句
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 ;