存储过程是什么
存储过程是一组为了完成特定功能的SQL语句集,存储在数据库中,一次编译多次使用,用户通过指定存储过程的名字并传入必要的参数来调用执行它;
存储过程就是数据库语言层面的代码封装与重用;
存储过程的优点
- 将重复性很高、复杂的一些操作,封装到一个存储过程中,简化了对这些SQL的调用;
- 可以重复使用,减少数据库开发人员的工作量;
- 一次编译多次调用执行,可以提高数据库执行速度;
存储过程只在创建的时候进行编译,以后每次调用的时候不需要再编译,一般的SQL语句每次执行都要先编译; - 可以使用SQL+循环进行批处理;
- 存储过程存放在服务器上,调用的时候只需要传递存储过程的名称和必要的参数就可以了,降低了网络传输的数据量;
- 参数化的存储过程可以防止SQL注入,也可以给存储过程设置权限,确保了数据的安全;
存储过程的缺点
- 可移植性差:不同数据库的存储过程的语法不同,切换数据库时需要重新编写存储过程;
- 每个数据库的存储过程语法几乎都不一样,十分难以维护(不通用);
- 调试麻烦;
存储过程分类
1、系统存储过程:以sp_
开头,用来进行系统的各项设定、取得信息、相关管理工作;
2、本地存储过程:一般说的存储过程就是本地存储过程,是由用户创建的,用来完成某一特定功能;
3、临时存储过程:(sql server)
本地临时存储过程:以#
作为名称的开头;只有创建的用户自己能使用;
全局临时存储过程:以##
作为名称的开头;连接到数据库服务器的用户都能调用;
4、远程存储过程:(sql server)
5、扩展存储过程:用户可以使用外部程序语言编写的存储过程,名字通常以xp
开头;
存储过程的查询语句
查看某个数据库下面的存储过程:
select pName from mysql.proc where db='数据库名';
select routine_name from information_schema.routines where routine_schema='数据库名';
show procedure status where db='数据库名';
查看存储过程的详细信息:show create procedure dbName.pName;
修改存储过程:alter procedure procedureName;
删除存储过程:drop procedure procedureName;
存储过程的创建和调用
https://www.runoob.com/w3cnote/mysql-stored-procedure.html
语法
delimiter $$
create [definer = { user | current_user}]
procedure sp_name (in/out/inout 参数名 参数类型)
[characteristic ...]
routine_body
characteristic:
comment 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
routine_body:
Valid SQL routine statement
[begin_label:] BEGIN
[statement_list]
……
END [end_label]
delimiter ;
1、delimiter $$
:修改语句结束符;
语句结束符表示语句已经结束,MySQL可以执行语句了;
在定义存储过程前,将语句结束符临时修改为$$
(符号随意定),使得存储过程的过程体中使用的;
被直接传递到服务器,而不会被客户端解释;
存储过程结束后,要将语句结束符改为默认的;
:delimiter ;
2、create procedure p_name(IN/OUT/INOUT p_in int, ...)
:声明存储过程;
默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀;
如果过程没有参数,也必须在过程名后面写上小括号;
IN
: 输入参数,表示调用者向过程传入值;
用来接收调用者调用存储过程时传入的参数;可以传入字面值、或变量;OUT
:输出参数,表示调用者向过程传入值;
用来向调用者返回值,传出值只能是变量;INOUT
:输入输出参数,既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量);尽量少使用inout;
3、begin ... end$$
:定义存储过程体;
- 存储过程体包含了在过程调用时必须执行的语句,例如:dml、ddl语句,if-then-else和while-do语句、声明变量的declare语句等;
- 过程体格式:以begin开始,以end结束(可嵌套);
- 可以为语句块帖标签:增强代码的可读性,在某些语句(例如:leave和iterate语句),需要用到标签;
4、set @p_in=1;
:变量赋值;
5、declare id int unsigned default 100;
:声明变量;
过程体中声明的是局部变量,直接使用sql语句声明的是全局变量;使用set为变量赋值时,在过程体中修改变量的值,不影响全局变量的值;
6、call pName(参数列表);
:调用存储过程;
7、变量的作用域:
内部的变量在其作用域范围内享有更高的优先权,当执行到 end 变量时,内部变量消失,此时已经在其作用域外,变量不再可见了,应为在存储过程外再也不能找到这个申明的变量,但是可以通过 out 参数或者将其值指派给会话变量来保存其值;
实例 - IN、OUT、INOUT
1、删除给定球员参加的所有比赛:
mysql> delimiter $$ #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
-> BEGIN
-> DELETE FROM MATCHES WHERE playerno = p_playerno;
-> END$$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter; #将语句的结束符号恢复为分号
2、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);
输出1, 2
mysql> select @p_in;
输出1
以上可以看出,p_in 在存储过程中被修改,但并不影响 @p_id 的值,因为前者为局部变量、后者为全局变量;
3、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);
先输出NULL,因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
再输出2,因为过程体中使用set为输出参数赋值为2;
mysql> select @p_out;
输出2;调用了out_param存储过程,输出参数,改变了p_out变量的值;
4、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);
输出1,2
mysql> select @p_inout;
输出2
#调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量
5、变量定义与赋值:
- 在客户端使用变量:
- 在存储过程中使用用户变量:
- 在存储过程间传递全局范围的用户变量:
条件语句
1、if-then-else
语句:
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc2(IN parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> if var=0 then
-> insert into t values(17);
-> end if;
-> if parameter=0 then
-> update t set s1=s1+1;
-> else
-> update t set s1=s1+2;
-> end if;
-> end;
-> //
mysql > DELIMITER ;
2、case
语句:
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc3 (in parameter int)
-> begin
-> declare var int;
-> set var=parameter+1;
-> case var
-> when 0 then
-> insert into t values(17);
-> when 1 then
-> insert into t values(18);
-> else
-> insert into t values(19);
-> end case;
-> end;
-> //
mysql > DELIMITER ;
case
when var=0 then
insert into t values(30);
when var>0 then
when var<0 then
else
end case
循环语句
1、while ···· end while
:
while 条件 do
--循环体
endwhile
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc4()
-> begin
-> declare var int;
-> set var=0;
-> while var<6 do
-> insert into t values(var);
-> set var=var+1;
-> end while;
-> end;
-> //
mysql > DELIMITER ;
2、repeat···· end repea
:在执行操作后检查结果,而 while 则是执行前进行检查;
repeat
--循环体
until 循环条件
end repeat;
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc5 ()
-> begin
-> declare v int;
-> set v=0;
-> repeat
-> insert into t values(v);
-> set v=v+1;
-> until v>=5
-> end repeat;
-> end;
-> //
mysql > DELIMITER ;
3、loop ·····endloop
:loop 循环不需要初始条件,这点和 while 循环相似,同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环;
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc6 ()
-> begin
-> declare v int;
-> set v=0;
-> LOOP_LABLE:loop
-> insert into t values(v);
-> set v=v+1;
-> if v >=5 then
-> leave LOOP_LABLE;
-> end if;
-> end loop;
-> end;
-> //
mysql > DELIMITER ;
4、LABLES
标号:标号可以用在 begin repeat while 或者 loop 语句前,语句标号只能在合法的语句前面使用;可以跳出循环,使运行指令达到复合语句的最后一步;
迭代
ITERATE 通过引用复合语句的标号,来从新开始复合语句:
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc10 ()
-> begin
-> declare v int;
-> set v=0;
-> LOOP_LABLE:loop
-> if v=3 then
-> set v=v+1;
-> ITERATE LOOP_LABLE;
-> end if;
-> insert into t values(v);
-> set v=v+1;
-> if v>=5 then
-> leave LOOP_LABLE;
-> end if;
-> end loop;
-> end;
-> //
mysql > DELIMITER ;