第七章 存储过程.doc

第七章 过程式数据库对象7.1 存储过程一、 存储过程的创建格式：a) 格式：create procedure 存储过程名称(参数列表)begin 存储过程中执行的命令序列;end 结束符b) 参数的定义(参数可有可无，但小括号必须要)，包括三部分：i. 输入|输出模式1. in 输入型参数(默认)，从外部调用环境将值输入到存储过程内部，传递的方向：实参值-形参变量2. out 输出型参数，从存储过程输出值到外部调用环境的变量，传递的方向：形参变量-实参变量3. in|out 输入|输出型参数，从外部调用环境将值输入到存储过程内部，对数据进行处理，然后将新的结果再由存储过程输出到外部调用环境的变量，传递的方向：实参变量-形参变量-实参变量ii. 参数名称iii. 数据类型iv. 例如：1. create procedure addValue1(in oper1 int,in oper2 int,out sum int)2. create procedure swapValue(inout oper1 int,inout oper2 int)c) 结束符的定义:i. 结束符定义的原因：因为在mySQL环境中 默认使用分号 作为结束符号，而每条语句输入完成后也是使用分号作为结束标记的。因此在定义存储过程的内部，如果需要写入多条语句，那么在第一条语句结束写 分号 的时候，整个存储过程的定义也就被结束了，因此，需要为mySQL环境定义额外的结束符作为结束标记。ii. 结束符定义的格式：delimiter 结束符号iii. 结束符的使用：1. 在存储过程内部（即begin end之间）仍然使用 分号 作为语句的结束标志。2. 在存储过程定义结束后(即 end 的后面) 需要使用自己定义的结束符作为整个存储过程定义结束的标志。d) 在存储过程中处理2条以上命令的时候，应该加上begin.end，如果只包含1条语句，可以省略begin.end关键字。i. 例如：create procedure pro1()set a=100;$create procedure pro2()beginset a=100;set b=1000;end;$二、 简单存储过程的例题a) 不执行任何操作的存储过程 delimiter ! create procedure pro3()beginend! b) 存储过程的执行：call 存储过程名称(实参列表); c) 在别的数据库语言中这样做是不允许的。因为它们要求begin 和 end 之间至少存在一条语句。如下面的代码：create procedure pro4()beginnull; end!使用null关键字表示不执行任何操作，但是这样做在MySQL中是不允许的。d) 查看错误信息 show errors三、 存储过程中的注释符号：a) 单行注释符i. - 该注释符需要注意：注释符和注释的内容之间要 有空格，否则语法错误ii. # 该注释符和注释的内容之间的空格可有可无b) 多行注释符i. /*/c) 例题：create procedure pro5()beginset a=100; - 定义一个用户变量aset b=1000;#定义一个用户变量bset c=1000;/*定义一个 用户变量c*/end;$四、 局部变量的定义和使用a) 局部变量的概念：在存储过程内部声明的变量，称为局部变量，这样的变量只能局限于存储过程内部使用。该变量不同于前面学的用户变量。b) 局部变量的定义：i. 在beginend之间进行定义。ii. 使用declare关键字进行定义，并且定义的同时可以赋值。格式：declare 变量名,变量2 数据类型 default 默认值;注意，此处只能使用default设置默认值，不能用等号iii. 必须遵循先定义后使用的原则。c) 局部变量与用户变量的不同：i. 命名格式不同：局部变量前面没有 ii. 使用范围不同：局部变量只能在当前定义它的存储过程内部使用；而用户变量可以在当前的整个会话范围使用，包括存储过程。iii. 用户变量不用事先定义，可以直接赋值使用，而且数据类型是随着赋予它的值而确定的。但局部变量必须先定义后使用，见下面的两个例子。iv. 赋值方式不同，如下面。d) 分别使用set和select关键字为局部变量和用户变量赋值首先要注意，为局部变量或用户变量赋值时，不能直接写成 变量名 = 值; 等号 运算符应该 配合set或 select 关键字一起使用。i. 局部变量的赋值:1. set 局部变量名=值2. set 局部变量名：=值 3. select值 into 局部变量名注意：由于局部变量的局限性，所以为局部变量的赋值的这些语句只能在存储过程内部执行。ii. 用户变量的赋值方式：1. set 用户变量名=值 2. set 用户变量名:=值 3. select 值 into 用户变量名4. select 用户变量名:=值 -这种形式只能用在用户变 -量的赋值上注意：由于用户变量可以在整个会话的范围内使用，所以这些命令既可以在存储过程内部执行，也可以在存储过程的外部执行。iii. 局部变量与用户变量的定义、赋值例题：1. 例6：局部变量的定义与赋值create procedure pro6()begin declare a,b,c,d int; set a=10; set b:=20; select 30 into c; - select d:=40;end$2. 例7：用户变量的定义与赋值create procedure pro7()begin set a=10; set b:=20; select 30 into c; -这种格式也可以直接在提示符下使用 select d:=40;end$3. 例8：局部变量与用户变量不同的使用范围call pro6( )$call pro7( )$select a$ - 局部变量a超出了使用范围，访问失败select a$ - 用户变量a可以在整个会话中使用五、 有参数存储过程的定义与调用a) 不同模式的形参对应的实参：i. in模式的形参，它的实参可以是常量、变量和表达式；ii. out模式的形参，它的实参只能是变量；iii. inout模式的形参，它的实参只能是变量；b) 输入型参数与输出型参数的使用i. 例1：create procedure addValue(in oper1 int,in oper2 int,out sum int) begin set sum=oper1+oper2; end$或者写成：set sum:=oper1+oper2; select oper1+oper2 into sum;也就是说，形参本质与局部变量相同，所以赋值方式也相同。注意：add是关键字 sum不是关键字上面存储过程的调用：delimiter ;set number;call addValue(10,20,number);select number;ii. 例2：使用用户变量保存存储过程的值delimiter $create procedure addValue1(in x int,in y int) begin select z:=x+y; end$call addValue1(1,2)$select z$c) 输入输出型参数的使用i. 写法一：delimiter$create procedure swapValue(inout oper1 int,inout oper2 int)beginset oper1=oper1+oper2;set oper2=oper1-oper2;set oper1=oper1-oper2;end$ii. 写法二：create procedure swapValue1(inout oper1 int,inout oper2 int)begindeclare temp int;set temp=oper1;set oper1=oper2;set oper2=temp;end$iii. 调用：set a=10,b=20$ call swapValue(a,b)$ select a,b$六、 标准SQL语句在存储过程中的使用a) DML语句在存储过程中的使用i. insert 语句在存储过程中的使用create procedure insert_student(in xh int,xm varchar(30),birth date,pro varchar(30),xf int,beizhu int )begininsert into student(id,stuname,birthday,profession,score,comment) values(xh,xm,birth,pro,xf,beizhu);end$调用插入的存储过程：call insert_student(27,张三, 1990-9-8,软件开发,103,null)$call insert_student(28,李四, 1990-9-8,软件开发,107,null)$ii. update语句在存储过程中的使用create procedure update_student(in xh int,zym varchar(30)beginupdate student set profession=zym where id=xh;end$调用修改的存储过程：call update_student(27,计算机网络)$iii. delete语句在存储过程中的使用create procedure delete_student_byID(in xh int)begindelete from student where id=xh;end$调用删除的存储过程：call delete_student_byID(4)$iv. select语句在存储过程中的使用方式一：直接执行select语句create procedure select_student ( )beginselect * from student;end$-调用存储过程，完成查询的功能。call select_student();方式二：将select 语句写成select 字段|表达式 into 变量 from 表例1：create procedure select_student_byID(out name varchar(30)beginselect stuname into name from student;end$ - 存储过程创建成功-调用存储过程select_xs_byIDcall select_student_byid(a)$ERROR 1172 (42000): Result consisted of more than one row例2：create procedure select_student_byID1(xh int,out name varchar(30)beginselect stuname into name from student where id=xh;end$-调用查询的存储过程：call select_student_byID1(111,name)$select name$注意两点：1、当执行select intofrom语句的时候，一定要注意由于要将字段或表达式的结果存入用户变量或局部变量中，因此必须保证查询结果只返回一行记录。 2、select intofrom这种格式也可以在MySQL的会话环境下直接执行，也就是说不定义存储过程仍然可以在提示符下执行，只是在into子句的后面只能写用户变量名。如下例：mysql select 姓名 into name from xs where 学号=081101$Query OK, 1 row affected (0.00 sec)mysql select name$七、 流程控制语句在存储过程中的使用a) if语句的使用i. 格式：if 判断条件 then 要执行的语句序列elseif 条件 then 语句序列else 语句序列end if;ii. 例题7.6：create procedure par(in k1 integer,in k2 integer,out k3 char(6)beginif k1k2 thenset k3=大于;elseif k1=k2 then set k3=等于;elseset k3=小于;end if;end$存储过程的调用：call compar(10,20,result)$select result$当某个条件满足后，要执行一组命令时，直接写这组命令就可以了，不需要加beginend关键字，当然写上也没错。见下面的写法(三)上面的例子还可以改为：（一）create procedure par1(in k1 integer,in k2 integer )begindeclare k3 char(6);if k1k2 thenset k3=大于;select k3; elseif k1=k2 then set k3=等于; - 当然该程序可以优化只写一次select k3的语句此处- 主要为了试验当某个条件满足后执行多条语句的写法。select k3;elseset k3=小于;select k3;end if;end$调用：call compar1(10,20)$(二)create procedure par2(in k1 integer,in k2 integer,out k3 char(6) )beginif k1k2 thenset k3=大于;select k3;elseif k1=k2 then set k3=等于;select k3;elseset k3=小于;select k3;end if;end$调用：call compar2(10,20,result)$（三）create procedure par3(in k1 integer,in k2 integer,out k3 char(6) )beginif k1k2 thenbeginset k3=大于;select k3;end;elseif k1=k2 then beginset k3=等于;select k3;end;elsebeginset k3=小于;select k3;end;end if;end$调用：call compar2(20,20,result)$b) case语句的使用i. 格式： case 表达式 when 值1 then 语句序列; when 值2 then 语句序列; else 语句序列; end case; 或者是： case when 逻辑表达式或关系表达式1 then 语句序列; when逻辑表达式或关系表达式2 then 语句序列; else 语句序列; end case;ii. 例题：例题7.7：create procedure xscj.result(in str varchar(4),out sex varchar(4)begincase str when M then set sex=男; when F then set sex=女; else set sex=无; end case;end$调用存储过程：call xscj.result(M,sex)$select sex$call xscj.result(m,sex)$select sex$改进后的例题7.7create procedure xscj.result1(in str varchar(4),out sex varchar(4)begincase upper(str) - 使用upper函数将字符串大写 when M then set sex=男; when F then set sex=女; else set sex=无; end case;end$call xscj.result1(m,sex)$select sex$call xscj.result1(M,sex)$select sex$例题7.8create procedure xscj.result2(in str varchar(4),out sex varchar(4)begincase when str=M then set sex=男; when str=F then set sex=女; else set sex=无; end case;end$调用存储过程：call xscj.result2(M,sex)$select sex$call xscj.result2(m,sex)$ - 默认字符串比较不区分大小写select sex$ - 结果也是 “男”如果将例7.8做如下的改动，那么字符串比较时将区分大小写：create procedure xscj.result3(in str varchar(4),out sex varchar(4)begincase when cast(str as binary)=M then set sex=男; - 将str转换为二进制 when cast(str as binary)=F then set sex=女; else set sex=无; end case;end$调用存储过程：call xscj.result3(M,sex)$select sex$call xscj.result3(m,sex)$select sex$c) 循环语句i. while循环语句1. 格式：看格式，注意与其他语言中while循环的格式的区别begin_label: while 循环条件 do循环体中的语句序列;end while end_label;其中，begin_label 和end_label是while语句的标注。除非begin_label存在，否则end_label不能被给出，也就是说它们是成对出现的。如果出现，名字必须相同。2. 例题：例题7.9：create procedure dowhile()begin declare v1 int default 5; while v10 do set v1=v1-1; select v1; end while;end$调用存储过程：call dowhile()$将循环用在数据表的操作中：思考，下面的存储过程执行什么功能？create procedure dowhile1()begin declare v1 int default 5; while v10 do set v1=v1-1; select * from student; end while;end$调用存储过程：call dowhile1()$利用循环执行对表的操作，阅读并理解以下程序：create procedure pro1(in no int)begin declare avg double; declare zfx int; set avg=(select avg(score) from student); set zfx=(select score from student where id=no); while (zfxavg) do update xs set score= score +5 where id=no; set avg=(select avg(score) from student); set zfx=(select score from student where id=no); end while;end$调用存储过程：call pro1(27)$select avg(score) from student$select score from student where 学号=27$ii. repeat循环语句1. 格式：begin_label:repeat 循环体中的语句序列;until循环的退出条件 end repeat end_label;2. 例题：例题7.10：create procedure pro2( ) begin declare v1 int default 5; repeatset v1=v1-1; select v1; until v11 end repeat;end$该语句使用的时候注意两点：（1）until 表达式 的后面不能加分号 （2）until子句应是循环体中的最后一个子句，它后面不能再有任何语句了。调用存储过程：call pro2();iii. loop循环语句1. 格式：begin_label: loop 循环体中的语句序列;end loop end_label;通过该格式可以看出，loop循环没有退出条件，因此该循环一般会和leave语句一起使用。leave语句的格式:leave label;2. 例题：例题7.11create procedure doloop( ) begin set a=10; label:loop set a=a-1; if a0 then leave label; end if; end loop label;end$调用存储过程：call doloop( )$select a$iv. iterate语句的应用。该语句的作用是结束本次循环并开始一个新一轮的循环。如下例：分析以下程序的功能是什么？总结iterate语句的作用。create procedure pro5( ) begin set a=10; label:loop set a=a-1; if a0 then leave label; end if; if a%2!=0 then iterate label; else select a; end if; end loop label;end$调用存储过程：call pro5();八、 处理程序和条件a) 处理程序的概念：存储过程执行中，有错误或异常发生时，处理这些异常情况的代码段，称为处理程序。i. 条件指的是执行时发生的异常或错误。b) 处理程序的使用包括两个阶段：i. 处理程序的声明ii. 处理程序的调用1. 当对应的异常或错误发生时，处理程序由系统自动调用。c) 处理程序声明的格式：declare 处理程序类型 handler for 异常发生的条件值, 异常发生后要执行的语句i. 处理程序类型1. continue 异常发生后，错误的代码被终止，但该错误命令后面的代码继续被执行2. exit 异常发生后，程序退出3. undo 表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式。ii. 异常发生的条件值1. SQLSTATE value值a) 在mysql中，每个错误消息都有一个唯一代码和一个SQLSTATE代码。一个SQLSTATE代码可能会对应着多个错误消息。如，书上的SQLSTATE 23000b) 见下面的例题：create table t1(id int primary key);insert into t1 values(1);insert into t1 values(1);2. condition_namea) 为了提高可读性，为SQLSTATE代码或错误消息定义一个名字，在处理程序中使用这个名字3. SQLWARNING :是对所有的以01开头的SQLSTATE代码的速记。4. NOT FOUND：是对所有的以02开头的SQLSTATE代码的速记。5. SQLEXCEPTION：是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记。6. mysql_error_code : 表示SQL错误代码。如ERR