数据库基本篇（一）存储过程

1.存储过程简介

    存储过程是在数据库中定义一些SQL语句的集合，用户可以直接调用存储过程来执行这些已经定义好的SQL语句。创建存储过程其实就是将经常使用的一些SQL语句

组合在一起，然后将这些SQL语句当做一个整体存储在数据库（如：MySql）服务器中。

2.存储过程的优势

1）可以避免重复的编写相同的SQL语句。

2）由于存储过程是在数据库（如：MySql）服务器中存储和执行，减少了客户端和服务器端的数据传输。

3.存储过程实例演示

    接下来将结合实例在MySql数据库服务器的基础上讲解存储过程的创建过程。

3.1创建一个存储过程，实现查询在某一范围内的记录数。

DELIMITER &&

CREATE PROCEDURE QueryEmployeeCount(IN startID INT,IN endID INT,OUT CountNum INT)

READS SQL DATA

BEGIN

select count(*) into CountNum

from employee

where ID BETWEEN startID AND endID;

END

&&

DELIMITER;

注释： READS SQL DATA是该存储过程的一个特性，它表示读取数据。

       INTO CountNum 表示将count(*)的值写入到CountNum这个输出变量中。

       DELIMITER &&  表示将结束符改为&&。

       注意在写存储函数或者是存储过程的时候，第一步是先改变结束符，最后一步是将结束符改变成分号。

 

3.2调用存储过程

    在MySql中通过CALL语句来调用存储过程，当存储过程被调用后，数据库系统将执行存储过程中的SQL语句，然后将执行的接口返回给

被OUT修饰的输出参数，然后，我们只需通过select语句就可以获取到该值，具体实现过程，如下：

CALL QueryEmployeeCount(1,12,@GetCount);

SELECT @GetCount;

在MySql数据库中执行上面的代码就可以得到ID的值在1到12之间有多少条记录。

    另外，需要注意的是执行存储过程需要拥有EXECUTE权限，EXECUTE权限的信息存储在information_schema数据库下面的UESR_PRIVILEGES表中。

 

3.3在存储过程中定义和使用变量

    在存储过程中可以使用DECLARE关键字定义变量，然后可以为变量赋值。这些变量的作用范围是在BEGIN.....END程序段中，接下来通过程序来演示如何定义变量和为变量赋值。

1）定义一个数据类型为INT型的变量，变量名为startNum，默认值为5

DECLARE startNum INT DEFAULT 5;

DECLARE endNum INT DEFAULT 15;

2)通过SET关键字为变量赋值

SET start=10;

注释：为变量赋值还有另外一种方法，就是在select语句中通过INTO关键字，上面的例子中有提到。

3.4 存储过程中的流程控制语句

在存储过程中可以使用流程控制来控制程序语句的执行。在MySQL中可以使用的流程控制语句有IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句以及WHILE语句等。接来下将对这些流程控制语句进行一一介绍。

1）IF语句

IF express THEN statements

ELSEIF express THEN statements

ELSEIF express THEN statements

END IF;

注释：express代表条件判断语句

      Statement代表所要执行的语句

2）case语句（第一种形式）

CASE case_value

WHEN value_a THEN statements

WHEN value_b THEN statements

ELSE statements

END CASE;

注释：case_value 是条件判断的变量。

       value_a、value_b是变量的值，即当条件判断变量与该值相等时，则执行它后面的statements语句。

case语句（第二种形式）

CASE

WHEN express THEN statements

WHEN express THEN statements

ELSE statements

END CASE;

注释：express代表条件判断语句

            Statement代表所要执行的语句

3）LOOP语句

[begin_label]:LOOP

statements

END LOOP [end_label]

注释：LOOP是一个循环语句，[begin_label]和[end_label]

分别代表开始标签和结束标签，需要注意的是开始标签和结束标签是

成对出现的，也就是说，如果没有开始标签，那么就不用写结束标签了。

4）LEAVE语句

    LEAVE语句的作用是用来跳槽循环。接下来将结合上面的LOOP语句，以一个简单的例子来展示，它在程序中的应用。如下：

add_Count:LOOP

SET @num=@num+1;

if @num=50 THEN LEAVE add_Count;

END LOOP add_Count

注释：上面代码的意思是在一个LOOP循环中，如果变量num的值达到50，那么就通过LEAVE

语句跳出循环。

5）ITERATE语句

ITERATE语句也是一个用来跳出循环的语句，它与LEAVE语句的不同时，ITERATE跳出的是本次循环，而LEAVE是直接跳出循环。

接下来将通过一个简单的示例，展示它的使用：

add_num:LOOP

SET @num=@num+1;

if @num=50 THEN LEAVR add_num;

else if @num=25 then INTERATE add_num;

select * from employee;

END LOOP add_num;

注释：上面的这段代码的功能是在一个循环中执行select * from employee这段SQL语句，当变量num的值达到50的时候跳出循环，当num的值等于25的时候不执行select * from employee 这段SQL语句。

6）REPEAT语句

[begin_label]:REPEAT

statements

UNTIL express

END REPEAT [begin_label]

注释：REAPEAT是一个循环控制语句，它与LOOP不同的是，只有在满足特定条件的时候才跳出循环。

接下来通过一个实例，来展示它的用法：

REPEAT

SET @num=@num+1;

UNTIL @>=20

END REPEAT;

注释：上面代码段的意思是当变量num的值大于等于20的时候，则跳出循环。（在该语句中开始标签和结束标签可以省略）

WHILE语句也是由条件控制的循环语句，它和repeat语句有些不一样，它是当满足条件的时候，

才执行statements语句，实例如下：

WHILE @num<20 DO

SET @num=@num+1;

END WHILE;

注释：上面代码段的意思是，当num的值小于20的时候，执行变量num加1操作，当num变量的值，大于等于20的时候，则跳出while循环。

3.5删除存储过程

删除存储过程是在从数据库中删除已经存在的存储过程，以MySql为例，在MySql中，使用DROP PROCEDURE语句删除存储过程。示例如下：

DROP PROCEDURE p_Name;

注释：p_Name代表存储过程的名字。

3.6查看存储过程状态

当存储过程创建后，开发人员可以查看存储过程的状态和定义，以MySql为例，查看存储过程的状态有以下三种方式：

1）通过SHOW STATUS语句来查看存储过程的状态

查询的基本形式如下：

SHOW PROCEDURE STATUS LIKE 'p_Name';

注释：LIKE 'p_Name'表示匹配存储过程的名称。

2）通过SHOW CREATE语句查看存储过程的状态

show create procedure  p_Name;

注释：p_Name表示匹配存储过程的名称。

3）从information_schema.Routines表中查看存储过程的信息

由于在MySql数据库中存储过程的相关信息存储在information_schema.Routines表中，所以可以通过查询该表来获取存储过程的状态，如下：

select * from  information_schema.Routines where ROUTINE_NAME='p_Name';

未完待续----