存储过程（mysql）

mysql存储过程详解
我们现在MySql中创建一个bjpowernode的数据库,然后倒入备份表

1. mysql> create database bjpowernode;
2. Query OK, 1 row affected (0.00 sec)
3. mysql> use bjpowernode;
4. Database changed
5. mysql> source d:/bjpowernode.sql;
6. Query OK, 0 rows affected, 1 warning (0.00 sec)
7. Query OK, 0 rows affected, 1 warning (0.00 sec)
8. Query OK, 0 rows affected, 1 warning (0.02 sec)
9. Query OK, 0 rows affected (0.08 sec)
10. Query OK, 0 rows affected (0.05 sec)
11. Query OK, 0 rows affected (0.05 sec)
12. Query OK, 1 row affected (0.03 sec)

查询数据库中的表,会显示三个表,是我们刚刚导入的备份表

1. mysql> show tables;

2. ±----------------------+

3. | Tables_in_bjpowernode |

4. ±----------------------+

5. | dept |

6. | emp |

7. | salgrade |

8. ±----------------------+

9. 3 rows in set (0.00 sec)

10.  存储过程简介
    

我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
存储过程通常有以下优点：
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

2.  关于MySQL的存储过程
   

存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。
3. MySQL存储过程的创建

(1). 格式
MySQL存储过程创建的格式：CREATE PROCEDURE 过程名 ([过程参数[,…]])
[特性 …] 过程体

这里先举个例子：

1. mysql> DELIMITER //
2. mysql> CREATE PROCEDURE proc1(OUT s int)

3.  -> BEGIN 
   

4.  -> SELECT COUNT(*) INTO s FROM user;  
   

5.  -> END ; 
   

6.  -> //  
   

7. Query OK, 0 rows affected (0.01 sec)
8. mysql> DELIMITER ;

注：
（1）这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";“为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

例如:MySql默认的情况下以分号作为语句的结束,输入SQL语句,在末尾加上分号,这样MySql会执行这行代码
我们可以改变MySql的分隔符,把分号改成 // ,输入语句
mysql> delimiter //

这时候MySql的结束语句分隔符就改变成了// ,原来的分号就不起作用了
在结束语句的时候需要加上 //,这样SQL语句才会执行

1. mysql> select * from emp//
2. ±------±-------±----------±-----±-----------±--------±--------±-------+
3. | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
4. ±------±-------±----------±-----±-----------±--------±--------±-------+
5. | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
6. | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
7. | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
8. | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
9. | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
10. | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
11. | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
12. | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
13. | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
14. | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
15. | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
16. | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
17. | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
18. | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
19. ±------±-------±----------±-----±-----------±--------±--------±-------+
20. 14 rows in set (0.00 sec)

最后,我们把MySql的结束分隔符恢复成 ; ,这样的话,在结束语句的末尾还是用分号结束,SQL语句才会被执行

1. mysql> delimiter ;
2. mysql> select * from emp;
3. ±------±-------±----------±-----±-----------±--------±--------±-------+
4. | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
5. ±------±-------±----------±-----±-----------±--------±--------±-------+
6. | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
7. | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
8. | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
9. | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
10. | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
11. | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
12. | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
13. | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
14. | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
15. | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
16. | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
17. | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
18. | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
19. | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
20. ±------±-------±----------±-----±-----------±--------±--------±-------+
21. 14 rows in set (0.00 sec)

（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。
（3）过程体的开始与结束使用BEGIN与END进行标识。
这样，我们的一个MySQL存储过程就完成了，是不是很容易呢?看不懂也没关系，接下来，我们详细的讲解。

(2). 声明分割符

其实，关于声明分割符，上面的注解已经写得很清楚，不需要多说，只是稍微要注意一点的是：如果是用MySQL的Administrator管理工具时,例如NaviCat for MySql，可以直接创建，不再需要声明。

NaviCat for MySql工具的使用:我们下载的是绿色版,解压缩之后,输入注册码就可直接使用,非常方便,使用界面如下:

我们需要建立和数据库服务器的连接.点击 文件->新建连接,填入数据库的相关信息之后,点击确定按钮.

建立好的连接如下,双击本机,打开连接,我们可以直接看到MySql中的数据库
双击我们要使用的数据库,然后点击查询->新建查询,弹出窗口,我们可以在这个创建中编辑SQL语句,非常方便

在编辑窗口中编辑SQL语句,然后选中该语句,点击右键->运行已选择的,即可执行选中的SQL语句,并且显示查询结果

在NaviCat中编辑存储过程非常方便,不需要在声明分隔符,直接编辑代码,然后选中代码 ,即可执行,是不是被命令行客户端爽多了^_

(3). 参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形…])
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回
Ⅰ. IN参数例子
在NaviCat中创建存储过程:
CREATE PROCEDURE PROC2(P_IN INT)
BEGIN
SELECT P_IN; – 查询P_IN参数的值
SET P_IN = 3; – 设置P_IN参数的值
SELECT P_IN ; – 查询P_IN参数的值
END;

语句执行的过程如下图:

调用存储过程,查询结果
SET @P_IN = 10; – 设置@P_IN =10
CALL PROC2(@P_IN); – 调用存储过程,并且传递@P_IN的参数
SELECT @P_IN; – 查询@P_IN的结果

语句的执行过程如下图所示

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

Ⅱ.OUT参数例子
创建:
CREATE PROCEDURE PROC3(OUT P_OUT INT)
BEGIN
SELECT P_OUT;
SET P_OUT = 10;
SELECT P_OUT;
END;

执行过程如下:

调用存储过程,查询结果
SET @P_OUT = 1000; – 设置@P_OUT变量为1000
CALL PROC3(@P_OUT); – 调用存储过程并且传递@P_OUT参数
SELECT @P_OUT; – 查询P_OUT的结构

执行结果如下:

Ⅲ. INOUT参数例子
创建:
– 注意:INOUT之间没有空格
CREATE PROCEDURE PROC4(INOUT P_IN_OUT INT) BEGIN
SELECT P_IN_OUT;
SET P_IN_OUT = 10;
SELECT P_IN_OUT;
END;

执行结果如下:

调用存储过程:
SET @P_IN_OUT = 50;
CALL PROC3(@P_IN_OUT);
SELECT @P_IN_OUT;

执行结果如下:

(4). 变量
Ⅰ. 变量定义
DECLARE variable_name [,variable_name…] datatype [DEFAULT value];
其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)
例如:
CREATE PROCEDURE PROC4()
BEGIN
– 声明变量.注意:声明变量的语句只能写在BEGIN END之间
DECLARE SCHOOL VARCHAR(100) DEFAULT “动力节点”;
DECLARE L_INT INT DEFAULT 40;
DECLARE L_NUMERIC DOUBLE(8,2) DEFAULT 9.95;
DECLARE L_DATE DATE DEFAULT ‘1999-12-31’;
DECLARE L_DATETIME DATETIME DEFAULT ‘1999-12-31 23:59:59’;
DECLARE L_VARCHAR VARCHAR(255) DEFAULT ‘THIS WILL NOT BE PADDED’;

SELECT SCHOOL ;-- 查询变量的值
SET SCHOOL = “动力节点北京北部校区”,L_INT =100;-- 给变量重新赋值
SELECT SCHOOL;-- 再次查询变量
END;

Ⅱ. 变量赋值
SET 变量名 = 表达式值 [,variable_name = expression …]

Ⅲ. 用户变量

ⅰ. 在MySQL客户端使用用户变量
SELECT “Hello World” INTO @S;
SELECT @S;

SET @S = “Hello World”;
SELECT @S;

SET @RES = 1+2+3;
SELECT @RES;

ⅱ. 在存储过程中使用用户变量
– 声明存储过程
CREATE PROCEDURE PROC5()
BEGIN
SELECT CONCAT(@STR1,“World”);
END;

– 首先定义一个变量,然后调用存储过程
SET @STR1 = “Hello”;
CALL PROC5();

执行效果如下

ⅲ. 在存储过程间传递全局范围的用户变量
– 声明存储过程P1,在P1中定义变量@LAST_PROC;
CREATE PROCEDURE P1()
BEGIN
SET @LAST_PROC = “P1”;
END;

– 声明存储过程P2,在P2中使用P1中定义的变量@LAST_PROC
CREATE PROCEDURE P2()
BEGIN
SELECT CONCAT(“最后一个存储过程是”,@LAST_PROC);
END;

– 调用存储过程P1(),P2();
CALL P1();
CALL P2();

执行结果如下:

注意:
用户变量名一般以@开头

(5). 注释

MySQL存储过程可使用两种风格的注释
双模杠：–
该风格一般用于单行注释
c风格： /一般用于多行注释/
例如：

4.  MySQL存储过程的调用
   

用call和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。
5. MySQL存储过程的查询
我们像知道一个数据库下面有那些表，我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面的存储过程，但是是令一钟方式。
show procedure status where db=‘数据库名’;

具体语句:
SHOW PROCEDURE STATUS WHERE DB=“bjpowernode”;

执行结果如下:

进行查询。
如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样用desc 表名进行查看呢？
答案是：我们可以查看存储过程的详细，但是需要用另一种方法：
SHOW CREATE PROCEDURE 数据库.存储过程名;
就可以查看当前存储过程的详细。

具体语句如下:
SHOW CREATE PROCEDURE BJPOWERNODE.P1;

执行结果如下:

6.  MySQL存储过程的修改
   

目前，MySQL还不提供对已存在的存储过程的代码修改 如果，一定要修改存储过程的代码，必须，先将存储过程删除之后，在重新编写代码，或者创建一个新的存储过程

7.  MySQL存储过程的删除
   

删除一个存储过程比较简单，和删除表一样：
DROP PROCEDURE [IF EXISTS] 存储过程名称

从MySQL的表格中删除一个或多个存储过程。
DROP PROCEDURE IF EXISTS P1;
DROP PROCEDURE IF EXISTS P2;

8.  MySQL存储过程的控制语句
   

(1). 变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到end时，内部变量消失，此时已经在其作用域外，变量不再可见了，因为在存储过程外再也不能找到这个声明的变量，但是我们可以通过out参数或者将其值指派给会话变量来保存其值。

– 创建存储过程
CREATE PROCEDURE PROC6()
BEGIN
DECLARE X1 VARCHAR(5) DEFAULT “OUTER”;
– ----------嵌套块-------------------------
BEGIN
DECLARE X1 VARCHAR(5) DEFAULT “INNER”;
SELECT X1; – 结果是"INNER"
END;

---

SELECT X1; – 结果是"OUTER"
END;

– 调用存储过程
CALL PROC6();

执行结果如下:

(2). 条件语句
Ⅰ. if-then -else语句
创建一个表
CREATE TABLE TEMP (ID INT(4));

创建存储过程
CREATE PROCEDURE PROC7(IN PARAM INT)
BEGIN
DECLARE VAR INT;
SET VAR = PARAM + 1;
– ----------IF语句---------------
– 注意:判断var是否等于1,要使用=,而不是==;
IF VAR = 1 THEN
INSERT INTO TEMP VALUES (100);
END IF;
– ----------IF ELSE语句---------------
IF PARAM = 0 THEN
UPDATE TEMP SET ID = ID + 1;
ELSE
UPDATE TEMP SET ID = ID + 2;
END IF;
END;

调用存储过程
CALL PROC7(0);

查询结果:
SELECT * FROM TEMP;

语句执行结果如下:

Ⅱ. case语句：
清空TEMP表中的记录
DELETE FROM TEMP;

创建存储过程
CREATE PROCEDURE PROC8(IN PARAM INT)
BEGIN
DECLARE VAR INT;
SET VAR = PARAM + 1;

CASE VAR
WHEN 1 THEN
INSERT INTO TEMP VALUES (1);
WHEN 2 THEN
INSERT INTO TEMP VALUES (2);
ELSE
INSERT INTO TEMP VALUES (100);
END CASE;
END;

调用存储过程
CALL PROC8(10);

查询结果:
SELECT * FROM TEMP;

语句执行结果如下

(3). 循环语句
Ⅰ. while •••• end while：

清空TEMP表中的记录
DELETE FROM TEMP;

创建存储过程
CREATE PROCEDURE PROC9()
BEGIN
DECLARE VAR INT DEFAULT 0;
WHILE VAR < 10 DO
INSERT INTO TEMP VALUES (VAR);
SET VAR = VAR + 1;
END WHILE;
END;

调用存储过程
CALL PROC9();

查询结果:
SELECT * FROM TEMP;

语句执行结果如下:

Ⅱ. repeat•••• end repeat：
它在执行操作后检查结果，而while则是执行前进行检查。

清空TEMP表中的记录
DELETE FROM TEMP;

创建存储过程
CREATE PROCEDURE PROC10()
BEGIN
DECLARE VAR INT DEFAULT 0;
REPEAT
INSERT INTO TEMP VALUES (VAR);
SET VAR = VAR + 1;
UNTIL VAR >= 10 – 末尾没有分号
END REPEAT;
END;

调用存储过程
CALL PROC10();

查询结果:
SELECT * FROM TEMP;

语句执行结果:

Ⅲ. loop •••••end loop:
loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

创建存储过程
CREATE PROCEDURE PROC11()
BEGIN
DECLARE VAR INT DEFAULT 0;
LOOP1:LOOP
INSERT INTO TEMP VALUES (VAR);
SET VAR = VAR + 1;
IF VAR > 10 THEN – 循环终止条件
LEAVE LOOP1;
END IF;
END LOOP;
END;

CALL PROC11(); – 调用存储过程
SELECT * FROM TEMP; – 查询结果

语句执行结果如下: