数据库应用技术教程第五章面向过程的SQL扩展

1、数据库应用技术第五章面向过程的sql扩展内容概要v5.1 服务器端程序v5.2 pl/sql的基本语法v5.3 控制流v5.4 过程与函数v5.5 游标v5.6 错误与异常处理v5.7 触发器5.1 服务器端程序v存储过程：用户创建，用sql和其他语言(如pl/sql)编写的过程或函数，存储在数据库内部，用来完成一个特定的任务。oracle： pl/sqlmssql：transaction-sqlvpl/sql：oracle对sql进行扩展的过程式语言，具有通用程序设计语言的绝大部分特性，能够完成较为复杂和完整的功能可以用来编写存储过程。5.1 服务器端程序v存储过程的优点安全：使用创建者权限

2、、调用者不需要了解内部结构，不需要内部权限。性能：v网络开销小，不传送中间结果v节约sql代码分析时间代码可重用完整性和一致性v特性：可以使用变量、游标、控制结构，具有模块化、数据抽象、信息隐藏、错误处理等特性。5.2 pl/sql的基本语法v基本规则每条语句可以写在多行每条语句都以;结尾。语句保留字和变量不区分大小写v一、块结构v二、变量的定义与使用v三、内嵌sqlpl/sql的基本结构-块结构v块结构一个块的语法地位等价于一条语句v块的整体构成declare声明部分begin执行部分exception错误处理部分end;变量的定义与使用v变量类型简单变量类型v和字段的变量类型相同记录变量类

3、型v一组具有不同类型的变量的集合，类似c中的struct或pascal中的record集合变量类型变量的定义与使用v变量声明必须先声明，后使用在declare段声明不允许前向引用一行中只能定义一个变量大小写不敏感变量的定义与使用v简单示例emp_count number(4); (初始值为null)v缺省值blood_type char := 0; blood_type char default 0;vnot null，必须有缺省值emp_count number(3) not null := 0;变量的定义与使用v%type类型定义使用其它变量或列的数据类型，但不继承not null属性vc

4、redit number(7,2); vdebit credit%type;常用于变量与表中字段类型一致时vmy_dname sales.aname%type;v%rowtype记录变量类型与表（或游标）的定义相一致emp_rec emp%rowtype;v优点: 不需要知道精确类型；在表定义发生变化时，不用修改程序变量的定义与使用v变量使用使用 := 作为赋值运算符其他计算、比较方法类似于普通语言v作用域外层定义的变量可以在子块中使用可以在不同块中定义同名变量子块可以通过外层的块名来引用外层变量内嵌sqlv可以直接在pl/sql中使用dql和dml，但不能使用ddl和dclv在sql语句中使

5、用变量的值可以出现表达式的地方都可以使用变量直接写变量名，不需要额外的语法要素v容易和字段混淆v在mssql中，使用:作为前缀通常出现在where子句、select子句内嵌sqlv将查询结果赋值给一个变量select into from 子句 其他子句v要求select语句必须返回必须返回1行结果行结果，若返回多行或0行会在运行时报错必须在逻辑上保证返回一条记录，例如使用主键、聚合函数。与子查询不同，返回0行也是错误如何进行错误处理，见后面章节5.3 控制流语句v条件语句简单条件语句多重条件语句v循环语句loop循环while循环for循环v跳转语句exitgotov空语句控制流语句-条件vi

6、f thenend if;v vif then else end if;vif then elsif then elsif then else end if;控制流语句-循环vloop . loop . exit when end loop; .end loop;控制流语句-循环vwhile loopend loop;vfor in . reverse loop end loop;控制流语句-其他v定义标号 v跳转goto 标号;v空语句null;v返回return; 5.4 过程与函数v过程与函数的作用v维护过程与函数v外部过程与内部过程v调用与参数传递过程与函数-维护v过程(procedu

7、re)create or replace procedure () as | is ;v := in | out | in out default v：无长度和精度alter procedure compile; drop procedure ;过程与函数-维护v函数(function)create or replace function () return as | is ;alter function compile; drop function ;创建过程v计算某顾客在给定时间前一年的总购买金额create procedure p_1 (v_cid in char, v_sum out

8、number, v_date in date default sysdate) as begin select sum(dollars) into v_sum from orders where cid = v_cid and buy_date between v_date - 365 and v_date; end;vcreate function f_1 (v_cid in char, v_date in date) return number as declare v_sum number; begin select sum(dollars) into v_sum from orders

9、 where cid = v_cid and buy_date between v_date - 365 and v_date; return v_sum; end;创建和函数复杂过程举例create or replace procedure ins_split_word(word in varchar2, p_kind in number) asbegin declare cc number; i number; j number; ss varchar2(100); sz number; begin delete from tmp_split where kind= p_kind; if

10、word is null then return; end if; i:=1; sz:=length(word); cc:=0; loop cc:=cc+1; j :=instr(word, ;, i); if j=0 then j:=sz+1; end if; ss:= substr(word, i, j-i); insert into tmp_split (seq, kind, sp) values (cc, p_kind, ss); exit when jsz; i:=j+1; end loop; end;end ins_split_word;v内部子过程在过程或函数的declare部分

11、定义，只供该过程或函数调用不能被外部使用声明时不使用关键字create放在declare中的最后部分先声明后使用，可前向声明。不单独存放在数据库中外部过程与内部过程内部过程举例create procedure p_3 as begin declare v1 number; prodecure lp_1 as begin end; procedure lp_2 as begin lp_1; end; begin lp_1; lp_2; end; end;sql*plus 中，使用execute命令。在其他pl/sql程序中，直接写出过程及参数vcreate procedure p_2 as be

12、gin declare v1 number(10); begin p_1(a01, v1, sysdate); /全局过程p_1 end; end;执行方法位置表示vcredit_acct (acct, amt);名称表示v credit_acct(amount = amt, acct_no = acct);v credit_acct(acct_no = acct, amount = amt);混和表示vcredit_acct(acct, amount = amt);如果默认值在前，后面有非默认值，必须使用名称调用方式。参数传递语法inoutin out指定指定缺省类型作用作用传递参数传送返回

13、值传送参数和返回值形式参数形式参数如同一个常数未初始化变量已初始化变量不能赋值不能用于表达式、必须赋值应该赋值实际参数实际参数可以是常量、变量、表达式变量变量传递方式传递方式引用传送值传送值传送参数类型包（package）v由多个过程可以组成包v调试使用的包dbms_output.put_line();vdbms_output：包名vput_line：过程名5.5 游标v游标的作用v使用游标v游标for循环v游标属性v修改游标中数据游标的作用v用来处理从数据库中查询出来的一组数据的机制。v游标查询返回的数据称为结果集(result set)。v游标的使用类似文件操作，包括open、fetch、

14、close等操作。v游标的类型显式游标隐含游标vpl/sql为每一个dml语句隐含定义了一个游标包游标使用游标v声明在declare段v打开v循环读取读取当前行的数据到变量中判断是否读到末尾v关闭关闭后才可以重新打开游标声明v声明cursor () return is ;： in := | default 参数只能是基本类型、必须是in类型，可以有缺省值。declare cursor c1 is select sid, aname, salary from sales where salary 2000; cursor c2 return sales%rowtype is select * f

15、rom sales where salary 2000; cursor c3 (v_salary)is select sid, aname, salary from sales where salary v_salary; 游标声明使用游标v打开open cur_name ()；可以使用位置表示法和名称表示法。不能打开已经打开的游标。打开游标时带入的参数已经在打开时被固定。v读取数据fetch into |;使用游标v循环读loop fetch c1 into my_record; exit when c1%notfound; end loop;v关闭close ;举例：计算方差create

16、function(p_lid char) isbegin declarecursor c1(v_lid char) isselect salary from sales where cid=v_lidv_avg number;v_sum number default 0;vi number; beginselect avg(salvary) into v_avg from sales where lid=p_lidopen c1(p_lid);loop fetch c1 into vi;exit when c1%notfound;v_sum:=v_sum+(vi-v_avg)*(vi-v_av

17、g);end loopclose c1;return v_sum; end;end游标for循环v自动声明一个和游标返回值相同类型的循环变量、自动打开游标，在每一次循环中读取一行数据，在出错或没有数据时结束循环，关闭游标。同时，当在循环中使用exit、goto语句或发生错误而跳出循环时，自动关闭游标。v语法for in () loopend loop;举例：计算方差create function(p_lid char) isbegin declarecursor c1(v_lid char) isselect salary from sales where lid=v_lidv_avg num

18、ber;v_sum number default 0;vi number; beginselect avg(salvary) into v_avg from sales where lid=p_lidif v_avg is null thenreturn nullend iffor rec_1 in c1(p_lid) loopv_sum:=v_sum+(vi-v_avg)*(vi-v_avg);end loopreturn v_sum; end;end游标属性显式显式隐式（隐式（sql%）%isopen是否打开false%found是否成功查询数据。如无fetch为空。上一个修改语句是否影响

19、了表中的数据。或者select into 返回数据。如无dml，则为null。%notfound与上相反与上相反%rowcount最近一次提取的行序号，当未提取数据时为0。修 改 语 句 影 响 的 行 数 ， 或select语句返回的行数。对于显式游标，只有%isopen可以在游标未打开时使用。否则产生错误。修改游标中数据v在update和delete中使用where current of 子句，直接修改当前行的数据。vfor update锁在游标定义时使用for update子句。for update of nowaitfor update子句锁住该游标查询出来的所有行，避免其他用户对相关

20、内容的修改。特别在一个长时间的事务过程中，避免前后出现不一致的情况5.6 错误与异常处理v错误与异常v两类处理机制的对比v异常的定义与分类v结构化异常处理语法流程举例错误与异常v各种非正常的状态称为错误。v不能够处理非正常情况的程序是不可靠的，不够鲁棒(robust)v异常(exception)是对错误情况的包装，在错误传播、处理、返回等操作过程中标明错误内容。v异常也可以指进行错误处理的机制。两种处理机制v处理错误的方式通常有两类，一类是c语言等的实现方式，包括：返回值v发生错误的程序段不一定能够解决问题，它可以通过返回值或者全局标志将错误的状态传递给调用者，但调用者完全可能忽视错误。全局状

21、态标志v错误处理的代码混杂在正常的程序处理流程中，使得程序结构显得比较混乱。两种处理机制v另外一种方式，是在pl/1语言中提出的，并在pl/sql中应用，在c+和java语言中被采用的“结构化异常处理”的方法。try /*正常流程*/catch() /*根据不同错误原因进行处理*/错误与异常处理v优点发生错误的程序段只负责把错误抛出，而由有能力的高层调用者（包括最终用户）来解决问题。程序流程清晰。程序员必须处理错误。v缺点需要对错误进行分类，有一个明确的分类体系。在c+语言中，内存释放是一个问题。错误分类和定义v在oracle中，错误分为:内部错误或系统错误用户定义错误所有错误均有一个特定的错

22、误代码一些错误有预定义的名字，也可以为其他错误给出名字。系统系统用户定义用户定义通用无名变量编号触发方式自动自动raiseraise_application_error定义方式名称/编号编号 exception消息文本，编号：-20000,-20999定义域全局全局块局部整个程序一些预定义错误预定义异常预定义异常 描述描述 oracle错误错误 错误代错误代码码cursor_already_open 试图打开一个已经打开的游标，一个游标在它重新打开前必须被关闭。试图打开一个已经打开的游标，一个游标在它重新打开前必须被关闭。一个游标一个游标for循环会自动地打开所涉及的游标，所以在游标循环里不循

23、环会自动地打开所涉及的游标，所以在游标循环里不能打开游标能打开游标 ora-06511 -6511dul_val_on_index 试图在一个有惟一性约束的数据库列中存储重复的值试图在一个有惟一性约束的数据库列中存储重复的值 ora-00001 -1invalid_cursor 试图执行一个无效的游标操作试图执行一个无效的游标操作 ora-01001 -1001invalid_number 试图将一个看起来不像是一个有效的数字的字符串转换成数字失败时，试图将一个看起来不像是一个有效的数字的字符串转换成数字失败时，而在过程性语句中，将会引发而在过程性语句中，将会引发value_error错误，代

24、替错误，代替invalid_number错误错误 ora-01722 -1722login_denied 用一个无效的用户名或口令去登陆用一个无效的用户名或口令去登陆oracle ora-01017 -1017no_data_found 一个一个select into语句没有返回数据或者程序引用一个嵌套表中被删除语句没有返回数据或者程序引用一个嵌套表中被删除的元素或索引表中一个没有被初始化的元素的元素或索引表中一个没有被初始化的元素 ora-01403 100timeout_on_resource oracle在等待资源时发生超时现象在等待资源时发生超时现象 ora-00051 -51too_

25、many_rows select into语句返回了多行数据语句返回了多行数据 ora-01422 -1422value_error 一个算法、转换、截断或者大小约束错误发生，如果在一个算法、转换、截断或者大小约束错误发生，如果在sql语句中发语句中发生错误则会引发生错误则会引发invalid_error错误，替代了错误，替代了value_error错误错误 ora-06502 -6502zero_divide 发生被零除发生被零除 ora-01476 -1476用户自定义错误v自定义的错误，不需要声明。raise_application_error (, , true |false);v b

26、etween -20000,-20999v char(2048);v-20000应用于一般性错误。如果第三个参数为假（缺省值），替换现有的错误。如果为真，加在现有错误之上。这种错误也可以被捕捉。异常处理语法结构v这个语法只能处理有名字的异常v对于系统无名错误和用户自定义编号错误或者使用用户定义方式为其起名或者在others里面，检查错误代码来判断exception段 when or then when or then when others then 异常处理过程v执行体内出现异常，转到本块的异常处理程序。在声明段和错误处理段发生异常时，转到上一层的异常处理程序。v如果在这个位置没有找到异常处

27、理程序，和处理程序中没有找到对应的错误号，且没有others段，则继续向上传播。如直到最外层都没有找到，则结束当前程序，返回用户。v执行完异常处理程序后，控制权转移到异常处理程序的外层块的下一条语句执行。如异常处理在最外层，则结束程序。异常处理举例create or replace function isdate(str varchar2, fmt varchar2 default null)return dateisv_date date;v_fmt varchar2(100) default fmt;beginif fmt is null thenselect value into v_f

28、mtfrom v$nls_parameterswhere parameter=nls_date_format;end if;v_date := to_date(str, v_fmt);return v_date ;exceptionwhen others thenreturn null;end;/异常处理举例declare pe_ratio number(3,1);begindelete from stats where symbol = xyz; begin - 子块开始 select price / earnings into pe_ratio from stockswhere symbo

29、l = xyz;exceptionwhen zero_divide thenpe_ratio := 0;end; - 子块结束insert into stats (symbol, ratio) values (xyz, pe_ratio);exceptionwhen no_data_found raise_application_error(-20010,数据不存在); -不进行插入，报告错误end;5.7 触发器v触发器的基本概念定义用途与限制v创建和维护v触发器举例v视图触发器v触发器是一种特殊的存储过程。它不是由用户显式调用的，而适当满足某个触发事件时自动执行的。v触发器包括：触发事件，

30、触发器约束和触发器动作。触发事件：dml语句，ddl语句，数据库系统事件和用户事件。触发约束：在何种条件下触发。动作：一个pl/sql程序。触发器的基本概念v触发器的用途高级的存取限制(如在特定时间修改)复杂的数据一致性检查自动产生关联的数据自动建立事件日志v限制为全局性的操作定义触发器尽量使用完整性约束尽量不使用多重触发器、不要产生递归不要过长(60行)用途与限制vcreate or replace trigger before delete after insertorinstead of update of on | referencing old as | new as for each rowstatementwhen ();创建和维护触发器触发器创建和维护触发器创建和维护触发器vbefore和after。before用于可能修改语句带来的数值的情况。v条件谓词：inserting，deleting，updating，updating()v缺省为for each statementv:new和:old。可以在before触发器中向:new记录中赋值，但不能在after触发器中赋值。如果表名和new，old冲突，可以使用reference子句指定其他名称