对数据库中存储过程的了解
存储过程的解释以及优缺点
1.存储过程的意义:
存储过程(stored procedure)是一组为了完成特定功能的SQL语句集合,经编译后存储在服务器端的数据库中,利用存储过程可以加速SQL语句的执行。
2.存储过程的优点
①重复使用。存储过程可以重复使用,从而可以减少数据库开发人员的工作量。
②减少网络流量。存储过程位于服务器上,调用的时候只需要传递存储过程的名称以及参数就可以了,因此降低了网络传输的数据量。
③安全性。参数化的存储过程可以防止SQL注入式攻击,而且可以将Grant、Deny以及Revoke权限应用于存储过程。
3.存储过程的缺点
①调试麻烦,但是用 PL/SQL Developer 调试很方便!弥补这个缺点。
②移植问题,数据库端代码当然是与数据库相关的。但是如果是做工程型项目,基本不存在移植问题。
③重新编译问题,因为后端代码是运行前编译的,如果带有引用关系的对象发生改变时,受影响的存储过程、包将需要重新编译(不过也可以设置成运行时刻自动编译)。
④如果在一个程序系统中大量的使用存储过程,到程序交付使用的时候随着用户需求的增加会导致数据结构的变化,接着就是系统的相关问题了,最后如果用户想维护该系统可以说是很难很难、而且代价是空前的,维护起来更麻烦。
存储过程的语法使用
①声明语句结束符,可以自定义:
DELIMITER $$
或
DELIMITER //
②声明存储过程:
CREATE PROCEDURE demo_in_parameter(IN p_in int)
③存储过程开始和结束符号:
BEGIN .... END
④变量赋值:
SET @p_in=1
⑤变量定义:
DECLARE l_int int unsigned default 4000000;
⑥创建mysql存储过程、存储函数:
create procedure 存储过程名(参数)
⑦存储过程体:
create function 存储函数名(参数)
创建存储过程
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
1、in 输入参数
mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
-> begin
-> select p_in;
-> set p_in=2; //注入参数
-> select P_in;
-> end$$
mysql> delimiter ;
mysql> set @p_in=1; //先传入一个参数值
mysql> call in_param(@p_in); //在调用存储过程
+------+
| p_in |
+------+
| 1 |
+------+
+------+
| P_in |
+------+
| 2 |
+------+
mysql> select @p_in;
+-------+
| @p_in |
+-------+
| 1 |
+-------+
以上可以看出,p_in 在存储过程中被修改,但并不影响 @p_id 的值,因为前者为局部变量、后者为全局变量。
2、out输出参数
mysql> delimiter //
mysql> create procedure out_param(out p_out int)
-> begin
-> select p_out;
-> set p_out=2;
-> select p_out;
-> end
-> //
mysql> delimiter ;
mysql> set @p_out=1;
mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
| NULL |
+-------+
#因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
| 2 |
+-------+
mysql> select @p_out;
+--------+
| @p_out |
+--------+
| 2 |
+--------+
#调用了out_param存储过程,输出参数,改变了p_out变量的值
3、inout输入参数
mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
-> begin
-> select p_inout;
-> set p_inout=2;
-> select p_inout;
-> end
-> $$
mysql> delimiter ;
mysql> set @p_inout=1;
mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
| 1 |
+---------+
+---------+
| p_inout |
+---------+
| 2 |
+---------+
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
| 2 |
+----------+
#调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量
修改存储过程
ALTER PROCEDURE
更改用 CREATE PROCEDURE 建立的预先指定的存储过程,其不会影响相关存储过程或存储功能。
删除存储过程
删除一个存储过程比较简单,和删除表一样:
DROPPROCEDURE
从 MySQL 的表格中删除一个或多个存储过程。