MySQL 5.0 版本开始支持存储过程。
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
优点
-
存储过程可封装,并隐藏复杂的商业逻辑。
-
存储过程可以回传值,并可以接受参数。
-
存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。
-
存储过程可以用在数据检验,强制实行商业逻辑等。
缺点
-
存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
-
存储过程的性能调校与撰写,受限于各种数据库系统。
一、存储过程的创建和调用
-
存储过程就是具有名字的一段代码,用来完成一个特定的功能。
-
创建的存储过程保存在数据库的数据字典中。
创建存储过程
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type 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]
DEFINER:定义者。
MYSQL 存储过程中的关键语法
声明语句结束符,可以自定义:
DELIMITER $$ 或 DELIMITER //
注意:如果是在mysql客户端中执行存储过程,在定义过程时,使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$,使得过程体中使用的分号被直接传递到服务器,而不会被客户端(如mysql)解释。
声明存储过程:
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 存储过程名(参数)
创建mysql存储函数:
create function 存储函数名(参数)
实例
在本地数据库zwh中新建一个表matches,如下:
1、选择函数右键,选择新建函数
点击“过程”,进入如下页面:
2、输入参数
3、写过程体
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_matches`(IN `p_playerno` integer) BEGIN delete from matches where playerno=p_playerno; END
4、执行SQL
点击新建查询,输入命令如下
call delete_matches(57)
执行后结果如下
结果发现最后一行数据被删除。
存储过程体
-
存储过程体包含了在过程调用时必须执行的语句,例如:dml、ddl语句,if-then-else和while-do语句、声明变量的declare语句等
-
过程体格式:以begin开始,以end结束(可嵌套)
BEGIN
BEGIN
BEGIN
statements;
END
END
END
注意:每个嵌套块及其中的每条语句,必须以分号结束,表示过程体结束的begin-end块(又叫做复合语句compound statement),则不需要分号。
为语句块贴标签:
[begin_label:] BEGIN
[statement_list]
END [end_label]
例如:
label1: BEGIN
label2: BEGIN
label3: BEGIN
statements;
END label3 ;
END label2;
END label1
标签有两个作用:
-
1、增强代码的可读性
-
2、在某些语句(例如:leave和iterate语句),需要用到标签
二、存储过程的参数
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
-
IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
-
OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
-
INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
1、in 输入参数
创建存储过程:
CREATE DEFINER=`root`@`localhost` PROCEDURE `in_param`(IN `p_in` int) BEGIN #Routine body goes here... select p_in; set p_in=2; select p_in; END
点击新建查询,在窗口中输入:
set @p_in=1; call in_param(@p_in); select @p_in;
结果分别为:1,2,1
以上可以看出,p_in 在存储过程中被修改,但并不影响 @p_in 的值,因为前者为局部变量、后者为全局变量。
2、out输出参数
创建存储过程:
CREATE DEFINER=`root`@`localhost` PROCEDURE `out_param`(OUT `p_out` int) BEGIN #Routine body goes here... select p_out; set p_out=2; select p_out; END
点击新建查询,在窗口中输入:
set @p_out=1; call out_param(@p_out); select @p_out;
结果分别为:null,2,2
第一个值为null,是因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
第三个值为2,是因为调用了out_param存储过程,输出参数,改变了p_out变量的值
3、inout输入参数
创建存储过程:
CREATE DEFINER=`root`@`localhost` PROCEDURE `inout_param`(INOUT `p_inout` int) BEGIN #Routine body goes here... select p_inout; set p_inout=2; select p_inout; END
点击新建查询,在窗口中输入:
set @p_inout=1; call inout_param(@p_inout); select @p_inout;
结果分别为1,2,2
调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量
注意:
1、如果过程没有参数,也必须在过程名后面写上小括号
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……
2、确保参数的名字不等于列的名字,否则在过程体中,参数名被当做列名来处理
建议:
-
输入值使用in参数。
-
返回值使用out参数。
-
inout参数就尽量的少用。
三、变量
1. 局部变量
1)、局部变量声明
局部变量声明一定要放在存储过程体的开始:
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';
2. 局部变量赋值
使用set赋值
SET 变量名 = 表达式值 [,variable_name = expression ...]
使用into赋值
create procedure proc_var02() begin declare create_time datetime; select now() into create_time; select create_time; end;
2. 用户变量
用户自定义的变量,在当前会话有效。用户变量名一般以@开头
@var_name #无需提前声明,使用即声明
赋值
create procedure proc_var03() begin set @create_time = now(); select @create_time; end;
可看出,并未声明就直接赋值,但变量名前面必须带@符号。
在MySQL客户端使用用户变量:
SELECT 'Hello World' into @x; SELECT @x;
SET @y='Goodbye Cruel World'; SELECT @y;
SET @z=1+2+3; SELECT @z;
在存储过程中使用用户变量
创建存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `GreetWorld`() SELECT CONCAT(@greeting,' World')
相当于:
CREATE DEFINER=`root`@`localhost` PROCEDURE `GreetWorld`() BEGIN #Routine body goes here... SELECT CONCAT(@greeting,' World'); END
点击新建查询,在窗口中输入:
SET @greeting='Hello'; CALL GreetWorld( );
在存储过程间传递全局范围的用户变量
创建存储过程p1
CREATE PROCEDURE p1() SET @last_procedure='p1';
相当于
CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() BEGIN #Routine body goes here... SET @last_procedure='p1'; END
创建存储过程p2
CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);
相当于
CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`() BEGIN #Routine body goes here... SELECT CONCAT('Last procedure was ',@last_procedure); END
点击新建查询,在窗口中输入:
CALL p1( );
CALL p2( );
结果如下:
注意:
-
1、用户变量名一般以@开头
-
2、滥用用户变量会导致程序难以理解及管理
3、会话变量
系统提供的变量,当前会话有效。由于用的不多,在此略。
在MySQL中要修改会话(session)变量,可以使用session关键字
set session autocommit=1;
修改session变量配置,仅仅是对本session的变量配置有效,对于其他session无效。
SET SESSION group_concat_max_len = 102400;
4、全局变量
系统提供的变量,整个MySQL服务有效。由于用的不多,在此略。使用关键字global设置全局变量
如将autocommit变量的值设置为ON
set global autocommit=1;
需要注意的是,使用此方法对global全局变量的设置仅对于新开启的会话才是有效的,对已经开启的会话不生效。
SET GLOBAL group_concat_max_len = 102400;
修改全局(global)变量方式二:修改my.ini配置文件,如果要设置全局变量最简单的方式是在my.ini文件中直接写入变量配置。重启数据库服务就可以使全局变量生效。虽然设置了global变量、session变量,但是在mysql服务重启之后,数据库的配置又会重新初始化,一切按照my.ini的配置进行初始化,global和session的配置都会失效了。
四、注释
MySQL 存储过程可使用两种风格的注释
两个横杆--:该风格一般用于单行注释。
c 风格: 一般用于多行注释。
创建存储过程:往表中插入数据
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc1`(IN `parameter1` integer) BEGIN #Routine body goes here... DECLARE variable1 CHAR(10); IF parameter1 = 17 THEN SET variable1 = 'birds'; ELSE SET variable1 = 'beasts'; END IF; INSERT INTO table1 VALUES (variable1); END
调用存储过程
CALL proc1(17);
table1表中多一条记录
MySQL存储过程的调用
用call和你过程名以及一个括号,括号里面根据需要,加入参数,参数包括输入参数、输出参数、输入输出参数。具体的调用方法可以参看上面的例子。
MySQL存储过程的查询
select name from mysql.proc where db='数据库名';
或者
select routine_name from information_schema.routines where routine_schema='数据库名';
或者
show procedure status where db='数据库名';
查看存储过程的详细
SHOWCREATE PROCEDURE 数据库.存储过程名;
MySQL存储过程的删除
drop procedure procedure_name
五、MySQL存储过程的控制语句
1、变量作用域
内部的变量在其作用域范围内享有更高的优先权,当执行到 end时,内部变量消失,此时已经在其作用域外,变量不再可见了,应为在存储过程外再也不能找到这个申明的变量,但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc3`() BEGIN declare x1 varchar(5) default 'outer'; begin declare x1 varchar(5) default 'inner'; select x1; end; select x1; END
结果分别为:inner,outer
2、条件语句
1)、if-then-else 语句
CREATE DEFINER=`root`@`localhost` 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
2)、case语句
CREATE DEFINER = CURRENT_USER PROCEDURE `procedure3`(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;
3、循环语句
while ···· end while
语法:
while 条件 do --循环体 endwhile
例
CREATE DEFINER = CURRENT_USER 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;
repeat···· end repeat
语法:
repeat --循环体 until 循环条件 end repeat;
例:
CREATE DEFINER = CURRENT_USER 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;
loop ·····endloop
loop 循环不需要初始条件,这点和 while 循环相似,同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。
CREATE DEFINER = CURRENT_USER 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;
标号可以用在 begin repeat while 或者 loop 语句前,语句标号只能在合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。
4、ITERATE迭代
CREATE DEFINER = CURRENT_USER 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;
六、存储过程使用游标
数据准备
创建表并添加数据
CREATE TABLE `emp` ( `id` int(11) NOT NULL, `name` varchar(200) DEFAULT NULL COMMENT '姓名', `dept_no` varchar(32) DEFAULT NULL COMMENT '部门编号', `sal` decimal(10,2) DEFAULT NULL COMMENT '工资', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `dept` ( `dept_no` varchar(32) NOT NULL COMMENT '部门编号', `dept_name` varchar(200) DEFAULT NULL COMMENT '部门名称', PRIMARY KEY (`dept_no`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A001', '策划部'); INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A002', '开发部'); INSERT INTO `dept`(`dept_no`, `dept_name`) VALUES ('A003', '人事部'); INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (1, '张飒', 'A002', 5000.00); INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (2, '李敏', 'A003', 4500.00); INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (3, '赵虹', 'A001', 4350.00); INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (4, '赵敏敏', 'A002', 6530.00); INSERT INTO `emp`(`id`, `name`, `dept_no`, `sal`) VALUES (5, '孙慧', 'A002', 7150.00);
2)查询开发部的所有员工的基本信息,包含工资:
select emp.* from emp,dept where emp.dept_no = dept.dept_no and dept.dept_name = '开发部';
使用存储过程给开发部所有员工加120块的工资,如何去做?
游标语法
#声明游标 declare cursor_name for select_statement #打开游标 open cursor_name #取一行记录赋值给var_name fetch cursor_name into var_name ... #关闭游标,使用完后需关闭 close cursor_name
游标可以存储查询返回的多条数据。故在声明游标时for后面就是select的查询语句。
使用fetch关键字获取一行记录,但当结果集遍历到最后一条时,它会报错,错误码是1329,错误状态是02000,那么就需要使用Handler(句柄)来判断异常进行处理
需要注意的是,声明必须按照顺序,变量声明、游标声明、句柄声明的先后顺序不能错。
使用游标
给开发部的员工涨工资的存储过程:
create procedure proc_var14(in dept_name varchar(200),in add_sal decimal) begin declare user_id int; declare finished int default 0; declare cur_emp cursor for select e.id from emp e,dept d where e.dept_no = d.dept_no and d.dept_name = dept_name; #声明句柄, declare continue handler for 1329 set finished = 1; open cur_emp; emp_loop:loop fetch cur_emp into user_id; if finished = 1 then leave emp_loop; else update emp e set sal = e.sal + add_sal where e.id = user_id; end if; end loop emp_loop; close cur_emp; end;
声明句柄的注意事项:
(1)声明类型为continue时:当未出现1329时,执行后续语句,当出现1329时,就执行后面的set语句
(2)声明类型为exit时:直接退出存储过程,后续的语句不会执行
除了使用错误码外,还可以使用错误状态,声明如下,二选一即可:
declare continue handler for sqlstate '02000' set finished = 1;
调用存储过程:
call proc_var14('开发部',120);
调用后查询,工资已涨。
项目使用:
CREATE DEFINER=`root`@`%` PROCEDURE `p_org_delete`( p_orgNo VARCHAR ( 30 ) ) BEGIN DECLARE child_orgNo VARCHAR ( 30 ); DECLARE has_org boolean DEFAULT 1; DECLARE org_cursor CURSOR FOR SELECT org_no FROM t_org WHERE parent_no = p_orgNo; -- 当出现02000错误时把局部变量的值设为true DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET has_org = 0; SET @@max_sp_recursion_depth = 100; -- 设置递归深度 OPEN org_cursor; FETCH next FROM org_cursor INTO child_orgNo; WHILE has_org DO CALL p_org_delete (child_orgNo ); -- 递归调用 FETCH next FROM org_cursor INTO child_orgNo; END WHILE; CLOSE org_cursor; delete from t_org WHERE org_no = p_orgNo; END
mysql中的存储过程是可以递归调用的,如果报错Recursive limit 0 请使用 set @@max_sp_recursion_depth = 100 修改递归深度。
max_sp_recursion_depth 参数范围是 [0~255] ,该值设置超出 255 后会报错;
当递归层数参数 max_sp_recursion_depth 设置为 255 的情况下,需要相应的调大gcluster 层的 thread_stack 参数(测试环境调整为 2M ),然后重启 gclusterd ;否则可能会栈溢出从而引起 gclusterd 的宕机。