Mysql存储过程------存储过程的基本概念和基本使用方法

一、 数据库存储过程的基本概念:

1、 存储过程的概念：

百度百科对存储过程的概述是这样的: 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，
     经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。

2、存储过程的种类:

①、系统存储过程:

以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作。

②、 本地存储过程:

用户创建的存储过程是由用户创建并完成某一特定功能的存储过程，事实上一般所说的存储过程就是指本地存储过程。

③、 临时存储过程:

其中所谓临时存储过程分为两种:
其一:一是本地临时存储过程，以井字号(#)作为其名称的第一个字符，则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程，且
   只有创建它的用户才能执行它;

其二: 全局临时存储过程，以两个井字号(##)号开始，则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程，全局临时存储过程
   一旦创建，以后连接到服务器的任意用户都可以执行它，而且不需要特定的权限。

④、 远程存储过程:

在SQL Server2005中，远程存储过程(Remote Stored Procedures)是位于远程服务器上的存储过程，通常可以使用分布式查询和EXECUTE命令执行
   一个远程存储过程。

⑤、 扩展存储过程:

扩展存储过程(Extended Stored Procedures)是用户可以使用外部程序语言编写的存储过程，而且扩展存储过程的名称通常以xp_开头。

二、存储过程的基本使用方法:

1、 存储过程的创建:

存储过程的创建过程如下：

CREATE PROCEDURE proc_name ([proc_parameter[,...]])    [characteristic]   routine_body  

说明如下:

 proc_name代表存储过程名称；

        proc_parameter代表存储过程参数列表。该列表中的每个参数由3部分组成，即输入输出类型、参数名称和参数类型。其形式如下

： [ IN | OUT | INOUT ] param_name type ，其中[ IN | OUT | INOUT ]表示输出类型（IN表示输入参数；OUT表示输出参数； I

NOUT表示既可以是输入，也可以是输出。输入输出类型也可以去掉，默认为in）；param_name表示参数名称(注意：MySQL数

据库存储过程的参数名前不允许“@”，SQL Server数据库中可以)；type表示参数类型，该类型可以是MySQL数据库的任意数据

类型。

        注意：MySQL数据库存储过程不需要在参数列表括号后面“as”关键字，但SQL Server数据库中的存储过程必须加“as”关键字。

        characteristic指定存储过程的特性；该参数有多个值：
                LANGUAGE SQL：说明routine_body部分是由SQL语言的语句组成，数据库系统默认值。
                [NOT] DETERMINISTIC：指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的，这时当每次执行存

储过程时相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是非确定的，这时相同的输入可能得到不同的输出。默认

为非确定。
                { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL语句的限制。CONTAINS

SQL表示子程序包含SQL语句，但不包含读或写数据的语句；NO SQL表示子程序中不包含SQL语句；READS SQL DATA表示子程

序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句。默认为CONTAINS SQL。
                SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行；INVOKER表示

调用者可以执行。默认为DEFINER。
                COMMENT 'string'：存储过程注释信息。

        routine_body参数为存储过程体，BEGIN…END标志存储过程体的开始和结束。存储过程体可以是SELECT、UPDATE、INSERT、

DELETE、CREATE TABLE等SQL语句，也可以嵌入调用其它存储过程的代码，还可以是其它代码（参见博客：《数据库中的控制语句》）。

        注意：不能在 MySQL 存储过程中使用 “return” 关键字。

   

2、 存储过程的具体实例:

①、 使用存储过程创建表结构:

DELIMITER &&CREATE PROCEDURE CREATE_TABLE()LANGUAGE SQLBEGINCREATE TABLE test_procedure(id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',real_name VARCHAR(20) NOT NULL COMMENT '真实姓名',age INT(3) NOT NULL COMMENT '年龄',PRIMARY KEY(id))ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;END &&DELIMITER ;

说明：上面创建了一个名称为create_table的存储过程；
        注意：MySQL中默认的语句结束符为分号（;），存储过程中的SQL语句需要分号来结束，为了避免冲突，首先用"DELIMITER &&"将MySQL的结束符设置为&&，最后再

用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器是一样的。
       

调用存储过程：

--调用存储过程CREATE_TABLE()生成表结构CALL CREATE_TABLE();

②、 向创建好的表中添加数据:

--编写存储过程向创建好的表中添加数据DELIMITER &&CREATE PROCEDURE INSERT_DATA(IN U_REAL_NAME VARCHAR(20), IN U_AGE INT(3), OUT u_id INT(11))LANGUAGE SQLBEGIN INSERT INTO test_procedure(real_name,age) VALUES(u_real_name, u_age);SET u_id=LAST_INSERT_ID();#set u_id=@@identityEND &&DELIMITER ;

说明：上面创建的存储过程参数列表中u_real_name和u_age为输入变量，u_id为输出变量，该输出变量返回所添加数据对应的
主键值；

调用存储过程插入数据并查询结果:

#调用执行插入数据的存储过程CALL insert_data('曹操',51, @u_id);#查询数据插入情况SELECT *FROM test_procedure;

③、 修改表中的数据:

#修改表中的数据DELIMITER &&CREATE PROCEDURE updata_data(IN u_id INT(11), IN u_real_name VARCHAR(20), IN u_age INT(3))LANGUAGE SQLBEGINUPDATE test_procedure SET real_name = u_real_name, age=u_age WHERE id = u_id; END &&DELIMITER ;#调用存储过程修改数据CALL updata_data(5,'张学友', 56);#查看修改情况SELECT * FROM test_procedure;

④、 根据姓名进行模糊查询获得用户信息：

#根据姓名模糊查询出所有满足条件的用户信息DELIMITER &&CREATE PROCEDURE SELECT_ALL_DATA(IN u_real_name VARCHAR(20),OUT COUNT_NUM INT(11))READS SQL DATABEGIN SELECT COUNT(*) INTO COUNT_NUM FROM test_procedure WHERE real_name LIKE u_real_name;END &&DELIMITER ;#调用模糊查询存储过程CALL SELECT_ALL_DATA('周%', @count_num);SELECT @count_num AS total;

⑤、 依据用户名模糊查询删除数据:

#依据用户名模糊查询删除数据DELIMITER &&CREATE PROCEDURE delete_datas(IN u_real_name VARCHAR(20), OUT effect_num INT(11))LANGUAGE SQLBEGIN DELETE FROM test_procedure WHERE real_name LIKE u_real_name;SET effect_num = ROW_COUNT();#该函数用于返回受影响的行END &&DELIMITER ;#调用存储过程删除数据CALL delete_datas('周%', @effect_num);SELECT @effect_num;#查询数据删除情况SELECT * FROM test_procedure;

3、查看存储过程:

存储过程的查看可以通过如下语句实现：SHOW PROCEDURE STATUS  WHERE db='数据库名';或SHOW CREATE PROCEDURE 数据库名.存储过程名;或者使用如下的语句可以查看所有数据库的存储过程。

SHOW PROCEDURE STATUS;

4、删除存储过程:

删除存储过程可以使用如下语句: DROP PROCEDURE 存储过程名；

#删除存储过程DROP PROCEDURE updata_data;

三、存储过程的优点:

1、存储过程增强了SQL语言灵活性。存储过程可以使用控制语句编写，可以完成复杂的判断和较复杂的运算，有很强的灵活性；

        2、减少网络流量，降低了网络负载。存储过程在数据库服务器端创建成功后，只需要调用该存储过程即可，而传统的做法是每次都将大量

的SQL语句通过网络发送至数据库服务器端然后再执行；

        3、存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
        4、系统管理员通过设定某一存储过程的权限实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。


  
以上博客内容部分参照博文: http://blog.csdn.net/gaohuanjie/article/details/50996175(感谢博主的分享)