1. 创建实例数据库
create database db5;
use db5;
2.创建一个简单的工作表,并插入数据
create table t(s1 int);
insert into t values(5);
3.创建程序实例 create procedure Example
create procedure p1() select * from t;
SQL语句存储过程的第一部分是 create procedure
第二部分是过程名:上面新存储过程的名字是p1。
第三部分书参数列表(),第四部分是程序的主体,“select * from t”
*****什么样的MySQL语句在存储过程体中是合法的?
在存储过程体中可以包含所有的合法SQL数据库定义语言,insert,update,delete,drop,create,replace等等语句
包括(set,commit,rollback)但是,在代码中如果包含MYSQL的扩充功能,那么代码将不能移植。
4. 调用存储过程,所需要输入的就是call和你过程名以及一个括号。
call p1();
5.过程中的特征子句
create procedure p2()
language SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
select current_date,rand() from t;
反映存储过程特性的子句.子句内容在括号之后,主体之前.这些
子句都是可选的,他们有什么作用呢
LANGUAGE SQL子句是没有作用的.仅仅是为了说明下面过程的主体使用SQL语
言编写.这条是系统默认的,但你在这里声明是有用的,因为某些DBMS(IBM的DB2)需要
它,如果你关注DB2的兼容问题最好还是用上.此外,今后可能会出现除SQL外的其他语言
支持的存储过程
NOT DETERMINISTIC,是传递给系统的信息.这里一个确定过程的定义就是那
些每次输入一样输出也一样的程序
SQL SECURITY,可以定义为SQL SECURITY DEFINER或SQL SECURITY
INVOKER. 这就进入了权限控制的领域了,SQL SECURITY DEFINER意味着在调用时检查创建过程用户的权限(另一个选项是SQL SECURITY INVOKER). 在而言,使用SQL SECURITY DEFINER指令告诉MySQL服务器检查创建过程的用户就可 以了,当过程已经被调用,就不检查执行调用过程的用户了.而另一个选项(INVOKER)
则是告诉服务器在这一步仍然要检查调用者的权限.
6.Parameters参数
create procedure p5()------; //参数列表是空的
create procedure p5([IN] name data-type)---- //输入参数in可选,默认为参数为in
create procedure p5(out name data-type)----- //输出参数out
create procedure p5(inout 那么data-type)----- //即可以做输入参数也可以做输出参数
----输入参数in 例子。
create procedure p5(p int) set @x=p;
call p5(12345);
select @x;
----输出参数out 例子
create procedure p6(out p int)set p=-5;
call p6(@y);
select @y;
7. 复合语句:如果你的过程中有多条语句,那么你需要begin/end块。在这里你可以进行变量的定义和流程的控制
首先执行命令 delimiter //
create procedure p7()
BEGIN
set @a=6;
set @b=5;
insert into t values (@a);
select s1 * @a from t where s1>= @b;
END;//
-----在复合语句中申明变量
create procedure p8()
begin
DECLARE a INT;
DECLARE b INT;
SET a=5;
SET b=5;
insert into t values (a);
select s1*a from t where s1>=b;
end;//
含有default默认语句舌设定语句的例子
create procedure p9()
begin
declare a ,b int default 5;
insert into t values(a);
select s1*a from t where s1>=b;
end;//
8. scope作用域的问题:内部的变量在其作用域范围内享有更高的优先权,当执行到end
变量时,内部变量消失,此时已经在其作用域外,变量不再可见了,应为在存储
过程外再也不能找到这个申明的变量,但是你可以通过out参数或者将其值指派
给会话变量来保存其值。
create procedure p11()
begin
declare x1 char(5) default 'outer';
begin
declare x1 char(5) default 'inner';
select x1;
end;
select x1;
end;//
/********************************* 存储过程中的条件式语句 ***************************************/
1. if-then -else语句
create procedure p12(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指令:如果需要进行更多条件真假的判断我们可以使用case语句
create procedure p13(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;//
/***************************** 循环语句 ************************************/
1. while ···· end while; 循环语句
create procedure p14()
begin
declare var int;
set var=0;
while var<6 do
insert into t values(var);
set var=var+1;
end while;
end;//
2. repeat···· end repeat ;它在执行操作后检查结果,而while则是执行前进行检查
create procedure p15()
begin
declare v int;
set v=0;
repeat
insert into t values(v);
set v=v+1;
until v>=5
end repeat;
end;//
3. loop ·····end loop; loop 循环不需要初始条件,这点和while 循环相似,同时和repeat
循环一样不需要结束条件, leave语句的意义是离开循环,
create procedure p16()
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;//
4. LABLES 标号:标号可以用在begin repeat while 或者loop 语句前,语句标号只能在
合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。
/***************************** ITERATE迭代 ***************************************/
1. ITERATE:通过引用复合语句的标号,来从新开始复合语句
create procedure p20()
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存储过程 ERROR Handler 异常处理
问题样例:当insert失败时,我希望将其记录在日志文件中,在这里需要创建一个主键表,以及一个外键表,我们使用的是Innodb ,因此外键关联检查是打开的,当我向外键表中插入非主键表中的值时,动作将会失败,创建的数据表如下:
create table t2(s1 int primary key)engine=innodb;//
create table t3(s1 int,
key(s1),
foreign key (s1) references t2(s1))engine=innodb;//
create table error_log(error_message char(80));//
1. 建立一个过程,第一个语句 DECLARE EXIT HANDLER是用来处理异常的,意思是如果错误 1216发生,这个程序将会在错误记录表中插入一行,EXIT的意思是 当动作成功提交后推出这个复合语句。
create procedure p22(parameter int)
begin
declare exit Handler for 1452
insert into error_log values(concat('Time: ',current_date,'.Foreign key reference failure for value=',parameter));
insert into t3 values(parameter);
end;//
2. 申明异常处理的语法 DECLARE HANDLER syntax:
DECLARE {EXIT|CONTINUE} HANDLER FOR {error_number|{SQLSTATE error-string}|condition} SQL Statement
上面就是错误处理的用法,也就是一段当程序出错后自动触发的代码,MYSQL允许两种处理器,一种是exit处理,另外一种是 continue处理,与exit
不同的是在于他执行后,原主程序仍然继续运行,那么该复合语句就没有出口了。
----continue处理的例子:
create table t4(s1 int primary key);//
create procedure p23()
begin
declare continue handler for SQLSTATE '23000' set @x2=1;
set @x=1;
insert into t4 values (1);
set @x=2;
insert into t4 values(1);
set @x=3;
select @x, @x2;
end;//
call p23();//
---- rollback(回滚事务),定义自己的错误处理名字 declare '错误处理名' condition for SQLSTATE'23000';
create procedure p24()
begin
declare ViolationSelf condition for SQLSTATE'23000';
DECLARE EXIT HANDLER for ViolationSelf rollback;
start transaction;
insert into t2 values(1);
insert into t2 values(1);
commit;
end;//
/******************************************** Cursor游标 **********************************************************/
游标实现功能的摘要: 声明游标, 打开游标,从游标里读取,关闭游标
DECLARE cursor-name CURSOR FOR SELECT ······
OPEN cursor-name;
FETCH cursor-name INTO variable;
CLOSE cursor-name;
1. create procedure p25(out return_val int)
begin
DECLARE a,b,c int;
DECLARE cur_1 CURSOR for select s1 from t;
DECLARE continue handler for not found set b=1;
open cur_1;
set c=0;
repeat
fetch cur_1 into a;
until b=1
end repeat;
close cur_1;
set return_val=a;
end;//
2. create procedure p25_1(out return_val int)
begin
DECLARE a,b,c int;
DECLARE cur_1 CURSOR for select s1 from t;
DECLARE continue handler for not found set b=1;
open cur_1;
set c=0;
lable_1:loop
fetch cur_1 into a;
if b=1 then
leave lable_1;
end if;
set c=c+1;
end loop;
close cur_1;
set return_val=c;
end;//
create procedure p34(in va int)
begin
delete from t where s1=va;
end;//