"
A strong, positive self-image is the best possible preparation for success.
—— Joyce Brothers
"
无论是 MySQL
还是 SQL Server
数据库,最新版本都已支持『存储过程』。但是为了通用性,本文章以 MySQL
为例。
因为
SQL Server
数据库为商业软件,所以其使用率较开源的MySQL
来说要低一些。对于大数据处理来说,MySQL
更有优势,这就更使得很多中型、大型网站都使用MySQL
。二者语法上也有些许差别,考虑到学习之后的应用情况,特选MySQL
。
存储过程『Stored Procedure
』是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象,即面向对象的思想。它通过编译后存储在数据库中,用户可以像调用函数一样通过特定的方式执行存储过程。
简单来讲,存储过程就是封装了一堆 SQL
语句,方便复用。如此直白,应该不会不理解了。更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』
1) 通过封装,隐藏执行的 SQL
语句,以及逻辑操作。
2) 可以像函数一样传递参数,并回传值。
1) 存储过程根据数据库的不同而不同,移植性弱。比如切换不同厂商的数据库,由于编程语言的差别,需要重新编译。
2) 无法使用 select
指令来执行,它属于子程序,区别于用户定义的函数。
1) 自定义结束符号。一般在 mysql
中,我们使用封号来结束语句,可以临时修改为其他符号:
-- 将语句的结束符号从封号;临时改为两个$$或//(可以是自定义)DELIMITER $$或DELIMITER //
2) 创建存储过程
CREATE PROCEDURE 存储过程名称(参数...)
3) 一个存储过程开始与结束符号
BEGIN .... END
4) 创建存储函数
create function 存储函数名称(参数)
5) 调用存储过程
call 存储过程名称(参数);
6) 存储过程体以 begin
开始,以 end
结束,当然,这是可以嵌套的。
注意:每个嵌套块以及其中的每条语句,都必须以封号结尾,最外层(表示过程体结束的
begin-end
块不需要写)
7) 为每一个语句块打标签,可以增强可读性,并不易丢掉过多嵌套块中的某个结尾。
label1: BEGIN
label2: BEGIN
label3: BEGIN
statements;
END label3 ;
END label2;END label1
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; -- 将语句的结束符号恢复为封号
上面的存储过程解读:存储过程名为「delete_matches
」;存储过程需要传入的参数为 INTEGER
类型的 p_playerno
(球员编号);由 BEGIN
表示过程体开始,执行的语句为删除 MATCHES
表中字段 playerno
的值等于传入参数值的记录。以 END
表示过程体结束。
调用这一存储过程时:
mysql> call delete_matches(57);Query OK, 1 row affected (0.03 sec)
从示例中我们可以看到传入参数表示为:
CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
已知参数名称与参数类型,那么括号中 IN
代表什么意思呢?
答: IN
代表传入参数,也就是调用存储过程时,向存储过程内传入值。
除此之外还有另外两个参数:OUT
和 INOUT
OUT
:传出参数,也就是存储过程向调用者传出值。类似于程序中的 return
INOUT
:传入传出参数,比如传入一个值,存储过程中引用后再修改,向调用者传出新值。更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』
变量赋值:
SET @变量=值
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_in
的值,因为前者为局部变量、后者为全局变量。
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变量的值
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存储过程,接受了输入的参数,也输出参数,改变了变量
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
注意:局部变量声明一定要放置在存储过程体的开始。
datatype
为MySQL
的数据类型,如:int
,float
,date
,varchar(length)
示例:
DECLARE l_int int unsigned default 4000000; DECLARE l_numeric number(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';
unsigned
就是将数字类型无符号化更多精彩文章请关注公众号『Pythonnote』或者『全栈技术精选』
SET 变量名 = 表达式值 [,variable_name = expression ...]
大家一定会有些迷惑,为什么有些变量是直接为变量名赋值,而有些变量则需要在变量名前加 @
呢?那是因为添加后,变为了用户变量名.
mysql > SET @name='Ethan Yan'; mysql > SELECT @name; +---------------------+ | @name | +---------------------+ | Ethan Yan | +---------------------+
在存储过程中使用用户变量:
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql > SET @greeting='Hello'; mysql > CALL GreetWorld( ); +----------------------------+ | CONCAT(@greeting,' World') | +----------------------------+ | Hello World | +----------------------------+
单行注视:--
多行注视:
/* 此处为多行注释*/
1) 查询数据库中的所有存储过程
select name from mysql.proc where db='数据库名';
或者
select routine_name from information_schema.routines where routine_schema='数据库名';
或者
show procedure status where db='数据库名';
2) 查询某个存储过程详细内容
SHOW CREATE PROCEDURE 数据库.存储过程名;
ALTER PROCEDURE
DROP PROCEDURE
1) 变量作用域:局部变量在存储过程内优先级高,当执行到 END
时,局部变量消失。如果不想消失,可以通过参数 OUT
将其传出给调用者。
2) 条件语句1: if-then-else
if ... then... ;else...;end if;
3) 条件语句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 elseend case
4) 循环语句1 while ···· end while
while 条件 do --循环体endwhile
5) 循环语句2 repeat···· end repea
repeat --循环体until 循环条件 end repeat;
6) 循环语句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 ;
LABLES
标号:标号可以用在begin
repeat
while
或者loop
语句前,语句标号只能在合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。
7) 迭代
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 ;
参考文章: https://www.cnblogs.com/geaozhang/p/6797357.html http://blog.sina.com.cn/s/blog_86fe5b440100wdyt.html