存储过程学习笔记

存储过程学习文档

 

存储过程是存储在数据库中可重复使用的一段程序代码，一个存储过程包含名字，参数列表，以及一些sql语句集，复杂一点的还有控制结构、异常处理、游标等等

 

那么我们为什么要使用存储过程

Ø  存储过程是已经被认证的技术

Ø  存储过程是重复使用的组件

Ø  存储过程会使系统运行更快

Ø  存储过程具有可移植性

Ø  保证数据库的安全性和完整性

Ø  灵活性

 

存储过程的基本知识

1.         变量

Ø  用户变量

用户变量在整个会话期间都会存在，用@申明用户变量

Ø  局部变量

局部变量在使用之前必须先申明，局部变量可以在开始-结束块内申明，申明后，变量可以在相关的块内使用，包括所有的子块！

使用declare来申明一个局部变量，例如：

DECLARE test VARCHAR(20);

DECLARE i INT DEFAULT 20;

DECLARE t INT DEFAULT (SELECT COUNT(*) FROM test);

2.         Set赋值

使用set命令对变量进行赋值，比如：

set aaa=1;

3.         参数

Ø  输入参数，使用关键字IN。

举例：IN aaa VARCHAR(20)

Ø  输出参数，使用关键字OUT

举例：OUT aaa VARCHAR(20)

Ø  输入/输出参数，使用关键字INOUT

举例：INOUT aaa VARCHAR(20)

4.         流程控制语句

Ø  IF语句

语法格式：

IF search_condition THEN

    statement_list 

[ELSEIF search_condition THEN] 

    statement_list ... 

[ELSE

    statement_list] 

END IF

 

Ø  CASE语句

语法格式为：

CASE case_value 

    WHEN when_value THEN statement_list 

    [WHEN when_value THEN statement_list] ... 

    [ELSE statement_list] 

END CASE

或者 

CASE

    WHEN search_condition THEN statement_list 

    [WHEN search_condition THEN statement_list] ... 

    [ELSE statement_list] 

END CASE

 

Ø  WHILE语句

[begin_label:] WHILE search_condition DO

    statement_list

END WHILE [end_label]

Label可有可无

 

Ø  REPEAT语句

[begin_label:] REPEAT

    statement_list

UNTIL search_condition

END REPEAT [end_label]

Label可有可无

 

Ø  LOOP语句

[begin_label:] LOOP

    statement_list

END LOOP [end_label]

 

Ø  LEAVE语句

LEAVE label

这个语句被用来退出任何被标注的流程控制构造。它和BEGIN ... END或循环一起被使用

 

Ø  ITERATE语句

ITERATE label

ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环。”

 

5.         DECLARE处理程序

语法：

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

举例：DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET error='haha';

 

6.         游标

光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

语法：

DECLARE cursor_name CURSOR FOR select_statement   #申明游标

OPEN cursor_name                                                                     #打开先前申明的游标

FETCH cursor_name INTO var_name [, var_name] ...       #读取游标的数据，移动指针

CLOSE cursor_name                                                                    #关闭游标       

 

 

存储过程的创建过程以及实例

存储程序分别是用create procedure语句，一个程序要么是一个程序要么是一个函数，使用call语句来调用程序，并且程序只能用输出变量传回值；

要想在MySql5.1中创建子程序，必须具有create routine权限，并且alter routine和execute权限被自动授予它的创建者；

首先声明分隔符，所谓分隔符是指你通知mysql客户端你已经输入一个sql语句的字符或字符串符号，在这里我们就以“//”为分隔符；

下面看一个完整的例子：

 

DELIMITER //

#创建一个名字为add_data的存储过程，批量插入数据，并设置输入输出参数

CREATE PROCEDURE add_data (IN c_count INT,OUT result INT,OUT starttime DATETIME,OUT endtime DATETIME)

#申明存储过程开始

BEGIN

#申明变量

DECLARE c_name VARCHAR(20) DEFAULT 'zhang';

#使用select into语句设置获取执行开始的时间

SELECT NOW() INTO starttime;

#使用while循环

WHILE c_count <= 100000 DO

INSERT INTO a (id,`name`) VALUES(c_count,CONCAT(c_name,c_count));

SET c_count=c_count+1;

END WHILE;

#使用select into语句设置获取执行结束的时间

SELECT NOW() INTO endtime;

#使用输出参数获取最大的id值

SELECT MAX(id) INTO result FROM a;

#到这里，整个存储过程就算写完啦

END

//

#调用存储过程

Call add_data(1,@result,@starttime,@endtime);

#获取执行结果:

Select @result,@starttime,@endtime;

 

一个简单的关于游标使用的例子：

DELIMITER //

CREATE PROCEDURE add_data()

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE aa INT;

DECLARE bb VARCHAR(20);

DECLARE cur1 CURSOR FOR SELECT id,`name` FROM a;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET FOUND=FALSE;

OPEN cur1;

FETCH cur1 INTO aa,bb;

WHILE FOUND DO

IF aa > 3 THEN

INSERT INTO t VALUES(aa,bb);

END IF;

FETCH cur1 INTO aa,bb;

END WHILE;

CLOSE cur1;

END

//