mysql 存储过程 用处_MySQL存储过程学习

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;//