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

《数据库应用技术第五章面向过程的SQL扩展.ppt》由会员分享，可在线阅读，更多相关《数据库应用技术第五章面向过程的SQL扩展.ppt（67页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库应用技术第五章面向过程的SQL扩展 Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望内容概要v5.1服务器端程序v5.2PL/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

2、进行扩展的过程式语言，具有通用程序设计语言的绝大部分特性，能够完成较为复杂和完整的功能可以用来编写存储过程。5.1 服务器端程序v存储过程的优点安全：使用创建者权限、调用者不需要了解内部结构，不需要内部权限。性能：v网络开销小，不传送中间结果v节约SQL代码分析时间代码可重用完整性和一致性v特性：可以使用变量、游标、控制结构，具有模块化、数据抽象、信息隐藏、错误处理等特性。5.2 PL/SQL的基本语法v一、块结构v二、变量的定义与使用v三、内嵌SQLPL/SQL的基本规则每条语句可以写在多行每条语句都以;结尾。语句保留字和变量不区分大小写PL/SQL的基本结构-块结构v块结构一个块的语法地位

3、等价于一条语句v块的整体构成DECLAREBEGINEXCEPTIONEND;变量的定义与使用v变量类型简单变量类型v和字段的变量类型相同记录变量类型v一组具有不同类型的变量的集合，类似C中的STRUCT或PASCAL中的RECORD集合变量类型变量的定义与使用v变量声明必须先声明，后使用在declare段声明不允许前向引用一行中只能定义一个变量大小写不敏感变量的定义与使用v简单示例emp_countNUMBER(4);(初始值为NULL)v缺省值blood_typeCHAR:=0;blood_typeCHARDEFAULT0;vNOTNULL，必须有缺省值emp_countNUMBER(3)

4、NOTNULL:=0;变量的定义与使用%TYPEv使用其它变量或列的数据类型，但不继承NOTNULL属性vcreditNUMBER(7,2);vdebitcredit%TYPE;经常用于表中字段的数据类型vmy_dnamesalers.sname%TYPE;优点：不需要知道精确类型；在表定义发生变化时，不用修改程序变量的定义与使用%ROWTYPEvDECLAREvemp_recemp%ROWTYPE;vCURSORc1ISSELECTdeptno,dname,locFROMdept;vdept_recc1%ROWTYPE;变量的定义与使用v赋值与计算、比较使用:=作为赋值运算符其他计算、比较方

5、法类似于普通语言v作用域外层定义的变量可以在子块中使用可以在不同块中定义同名变量子块可以通过外层的块名来引用外层变量内嵌SQLv可以直接在PL/SQL中使用DQL和DML，但不能使用DDL和DCLv在SQL语句中使用变量的值可以出现表达式的地方都可以使用变量直接写变量名，不需要额外的语法要素v容易和字段混淆v在MSSQL中，使用:作为前缀通常出现在where子句、select子句内嵌SQLv将查询结果赋值给一个变量SELECTINTOFROM子句其他子句v要求SELECT语句必须返回必须返回1行结果行结果，若返回多行或0行会在运行时报错必须在逻辑上保证返回一条记录，例如使用主键、聚合函数。与子

6、查询不同，返回0行也是错误如何进行错误处理，见后面章节5.3 控制流语句v条件语句简单条件语句多重条件语句v循环语句LOOP循环WHILE循环FOR循环v跳转语句EXITGOTOv空语句控制流语句-条件vIFTHENENDIF;vvIFTHENELSEENDIF;vIFTHENELSIFTHENELSIFTHENELSEENDIF;控制流语句-循环vLOOP.LOOP.EXITWHEN ENDLOOP;.ENDLOOP;控制流语句-循环vWHILELOOPENDLOOP;vFORIN.REVERSELOOPENDLOOP;控制流语句-其他v定义标号v跳转GOTO;v空语句NULL;v返回RET

7、URN;5.4 过程与函数v过程与函数的作用v维护过程与函数v外部过程与内部过程v调用与参数传递过程与函数-维护v过程(procedure)CREATEORREPLACEPROCEDURE()AS|IS;v:=IN|OUT|INOUTDEFAULTv：无长度和精度ALTERPROCEDURECOMPILE;DROPPROCEDURE;过程与函数-维护v函数(function)CREATEORREPLACEFUNCTION()RETURNAS|IS;ALTERFUNCTIONCOMPILE;DROPFUNCTION;创建过程v计算某顾客在给定时间前一年的总购买金额CREATE PROCEDURE

8、 p_1 (v_cid IN CHAR,v_sum OUT 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;vCREATEFUNCTIONf_1(v_cidINCHAR,v_dateINDATE)RETURNNUMBER ASDECLAREv_sumNUMBER;BEGINSELECTSUM(dollars)INTOv_sumFROMorde

9、rsWHEREcid=v_cidANDbuy_dateBETWEENv_date-365ANDv_date;RETURNv_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 WORD IS NULL THEN RETU

10、RN;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部分定义，只供该过程或函数调用不能被外部使用声明时不使用关键字CREATE放在DECLARE中的最

11、后部分先声明后使用，可前向声明。不单独存放在数据库中外部过程与内部过程内部过程举例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程序中，直接写出过程及参数vCREATEPROCEDUREp_2ASBEGINDECLAREv1NUMBER(10);BEGINp_1(C001,v1,SYSDATE);/全局过程p_1EN

12、D;END;执行方法位置表示vcredit_acct(acct,amt);名称表示vcredit_acct(amount=amt,acct_no=acct);vcredit_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游标查询返回的数据称为结果集(resultset)。v游标的使用类似文件操作，包括OPEN、FETCH、CLOSE等操作。v游标的类型显式游标隐含游标vPL/SQL为每一个DML语句隐含定义了一个游标包游标使用游标v声明在DECLARE段v打开v循环读取读取当前行的数据到变量中判断

14、是否读到末尾v关闭关闭后才可以重新打开游标声明v声明CURSOR()RETURNIS;：IN:=|DEFAULT参数只能是基本类型，必须是IN类型，可以有缺省值。DECLARE CURSOR c1 IS SELECT sid,sname,salary FROM salers WHERE salary 2000;CURSOR c2 RETURN salers%ROWTYPE IS SELECT*FROM salers WHERE salary 2000;CURSOR c3(v_salary)IS SELECT sid,sname,salary FROM salers WHERE salary v

15、_salary;游标声明使用游标v打开OPEN()；可以使用位置表示法和名称表示法。不能打开已经打开的游标。打开游标时带入的参数已经在打开时被固定。v读取数据FETCHINTO|;使用游标v循环读LOOP FETCHc1INTOmy_record;EXITWHENc1%NOTFOUND;ENDLOOP;v关闭CLOSE;举例：计算方差CREATE FUNCTION(f_lid CHAR)ISBEGIN DECLARECURSOR c1(v_lid CHAR)ISSELECT salary FROM salers WHERE lid=v_lid;v_avg NUMBER;v_sum NUMBER

16、 DEFAULT 0;vi NUMBER;BEGINSELECT AVG(salary)INTO v_avg FROM salers WHERE lid=f_lid;OPEN c1(f_lid);LOOP FETCH c1 INTO vi;EXIT WHEN c1%NOTFOUND;v_sum:=v_sum+(vi v_avg)*(vi v_avg);END LOOP;CLOSE c1;RETURN v_sum;END;END;游标FOR循环v自动声明一个和游标返回值相同类型的循环变量、自动打开游标，在每一次循环中读取一行数据，在出错或没有数据时结束循环，关闭游标。同时，当在循环中使用EXIT

17、、GOTO语句或发生错误而跳出循环时，自动关闭游标。v语法FORIN()LOOPENDLOOP;举例：计算方差CREATE FUNCTION(f_lid CHAR)ISBEGIN DECLARECURSOR c1(v_lid CHAR)ISSELECT salary FROM salers WHERE lid=v_lid;v_avg NUMBER;v_sum NUMBER DEFAULT 0;vi NUMBER;BEGINSELECT AVG(salary)INTO v_avg FROM salers WHERE lid=f_lid;Ifv_avg IS NULL THENRETURN NUL

18、L;END IF;FOR rec1 IN c1(f_lid)LOOPv_sum:=v_sum+(vi v_avg)*(vi v_avg);END LOOP;RETURN v_sum;END;END;游标属性显式式隐式（式（SQL%）%ISOPEN是否打开FALSE%FOUND是否成功查询数据。如无FETCH为空。上一个修改语句是否影响了表中的数据。或者SELECT INTO 返回数据。如无DML，则为NULL。%NOTFOUND与上相反与上相反%ROWCOUNT最近一次提取的行序号，当未提取数据时为0。修 改 语 句 影 响 的 行 数，或SELECT语句返回的行数。对于显式游标，只有%ISO

19、PEN可以在游标未打开时使用。否则产生错误。修改游标中数据v在UPDATE和DELETE中使用WHERECURRENTOF子句，直接修改当前行的数据。vFORUPDATE锁在游标定义时使用FORUPDATE子句。FORUPDATEOFNOWAITFORUPDATE子句锁住该游标查询出来的所有行，避免其他用户对相关内容的修改。特别在一个长时间的事务过程中，避免前后出现不一致的情况。5.6 错误与异常处理v错误与异常v两类处理机制的对比v异常的定义与分类v结构化异常处理语法流程举例错误与异常v各种非正常的状态称为错误。v不能够处理非正常情况的程序是不可靠的，不够鲁棒(Robust)。v异常(exc

20、eption)是对错误情况的包装，在错误传播、处理、返回等操作过程中标明错误内容。v异常也可以指进行错误处理的机制。两种处理机制v处理错误的方式通常有两类，一类是C语言等的实现方式，包括：返回值v发生错误的程序段不一定能够解决问题，它可以通过返回值或者全局标志将错误的状态传递给调用者，但调用者完全可能忽视错误。全局状态标志v错误处理的代码混杂在正常的程序处理流程中，使得程序结构显得比较混乱。两种处理机制v另外一种方式，是在PL/1语言中提出的，并在PL/SQL中应用，在C+和Java语言中被采用的“结构化异常处理”的方法。try/*正常流程*/catch()/*根据不同错误原因进行处理*/错误

21、与异常处理v优点发生错误的程序段只负责把错误抛出，而由有能力的高层调用者（包括最终用户）来解决问题。程序流程清晰程序员必须处理错误v缺点需要对错误进行分类，有一个明确的分类体系。在C+语言中，内存释放是一个问题。错误分类和定义v在Oracle中，错误分为:内部错误或系统错误用户定义错误所有错误均有一个特定的错误代码一些错误有预定义的名字，也可以为其他错误给出名字。系统系统用户定义用户定义通用无名变量编号触发方式自动自动RAISERAISE_APPLICATION_ERROR定义方式名称/编号编号EXCEPTION消息文本，编号：-20000,-20999定义域全局全局块局部整个程序一些预定义错

22、误预定义异常预定义异常 描述描述 Oracle错误错误 错误错误代码代码CURSOR_ALREADY_OPEN 试图打开一个已经打开的游标，一个游标在它重新打开前必试图打开一个已经打开的游标，一个游标在它重新打开前必须被关闭。一个游标须被关闭。一个游标FOR循环会自动地打开所涉及的游标，循环会自动地打开所涉及的游标，所以在游标循环里不能打开游标所以在游标循环里不能打开游标 ORA-06511-6511DUL_VAL_ON_INDEX 试图在一个有惟一性约束的数据库列中存储重复的值试图在一个有惟一性约束的数据库列中存储重复的值 ORA-00001-1INVALID_CURSOR 试图执行一个无效

23、的游标操作试图执行一个无效的游标操作 ORA-01001-1001INVALID_NUMBER 试图将一个看起来不像是一个有效的数字的字符串转换成数试图将一个看起来不像是一个有效的数字的字符串转换成数字失败时，而在过程性语句中，将会引发字失败时，而在过程性语句中，将会引发VALUE_ERROR错误，错误，代替代替INVALID_NUMBER错误错误 ORA-01722-1722LOGIN_DENIED 用一个无效的用户名或口令去登陆用一个无效的用户名或口令去登陆Oracle ORA-01017-1017NO_DATA_FOUND 一个一个SELECT INTO语句没有返回数据或者程序引用一个嵌

24、套语句没有返回数据或者程序引用一个嵌套表中被删除的元素或索引表中一个没有被初始化的元素表中被删除的元素或索引表中一个没有被初始化的元素 ORA-01403 100TIMEOUT_ON_RESOURCE Oracle在等待资源时发生超时现象在等待资源时发生超时现象 ORA-00051-51TOO_MANY_ROWS SELECT INTO语句返回了多行数据语句返回了多行数据 ORA-01422-1422VALUE_ERROR 一个算法、转换、截断或者大小约束错误发生，如果在一个算法、转换、截断或者大小约束错误发生，如果在SQL语句中发生错误则会引发语句中发生错误则会引发INVALID_ERROR

25、错误，替代了错误，替代了VALUE_ERROR错误错误 ORA-06502-6502ZERO_DIVIDE 发生被零除发生被零除 ORA-01476-1476用户自定义错误v自定义的错误，不需要声明。RAISE_APPLICATION_ERROR(,TRUE|FALSE);vBETWEEN-20000,-20999vCHAR(2048)v-20000应用于一般性错误如果第三个参数为假（缺省值），替换现有的错误。如果为真，加在现有错误之上。这种错误也可以被捕捉。异常处理语法结构v这个语法只能处理有名字的异常v对于系统无名错误和用户自定义编号错误或者使用用户定义方式为其起名或者在OTHERS里面，

26、检查错误代码来判断EXCEPTION段 WHEN OR THEN WHEN OTHERS THEN 异常处理过程v执行体内出现异常，转到本块的异常处理程序。在声明段和错误处理段发生异常时，转到上一层的异常处理程序。v如果在这个位置没有找到异常处理程序，和处理程序中没有找到对应的错误号，且没有OTHERS段，则继续向上传播。如直到最外层都没有找到，则结束当前程序，返回用户。v执行完异常处理程序后，控制权转移到异常处理程序的外层块的下一条语句执行。如异常处理在最外层，则结束程序。异常处理举例CREATE OR REPLACE FUNCTION isdate(str VARCHAR2,fmt VAR

27、CHAR2 DEFAULT NULL)RETURN DATE ISBEGINDECLAREv_date DATE;v_fmt VARCHAR2(100)DEFAULT fmt;BEGINIF fmt IS NULL THENSELECT value INTO v_fmtFROM v$nls_parametersWHERE parameter=NLS_DATE_FORMAT;END IF;v_date:=to_date(str,v_fmt);RETURN v_date;EXCEPTIONWHEN OTHERS THENRETURN NULL;END;END;异常处理举例DECLARE pe_ra

28、tio NUMBER(3,1);BEGINDELETE FROM stats WHERE symbol=XYZ;BEGIN -子块开始 SELECT price/earnings INTO pe_ratio FROM stocksWHERE symbol=XYZ;EXCEPTIONWHEN ZERO_DIVIDE THENpe_ratio:=0;END;-子块结束INSERT INTO stats(symbol,ratio)VALUES(XYZ,pe_ratio);EXCEPTIONWHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20010,数

29、据不存在);-不进行插入，报告错误END;5.7 触发器v触发器的基本概念定义用途与限制v创建和维护v触发器举例v视图触发器v触发器是一种特殊的存储过程。它不是由用户显式调用的，而是当满足某个触发事件时自动执行的。v触发器包括：触发事件，触发器约束和触发器动作。触发事件：DML语句，DDL语句，数据库系统事件和用户事件。触发约束：在何种条件下触发。动作：一个PL/SQL程序。触发器的基本概念v触发器的用途高级的存取限制(如在特定时间修改)复杂的数据一致性检查自动产生关联的数据自动建立事件日志v限制为全局性的操作定义触发器尽量使用完整性约束尽量不使用多重触发器、不要产生递归不要过长(60行)用途

30、与限制vCREATEORREPLACETRIGGERBEFORE|AFTER|INSTEADOFDELETE|INSERT|UPDATEOFON|REFERENCINGOLDAS|NEWASFOREACHROW|STATEMENTWHEN();创建和维护触发器触发器创建和维护触发器创建和维护触发器vBEFORE和AFTER。BEFORE用于可能修改语句带来的数值的情况。v条件谓词：INSERTING，DELETING，UPDATING，UPDATING()v缺省为FOREACHSTATEMENTv:new和:old。可以在BEFORE触发器中向:new记录中赋值，但不能在AFTER触发器中赋值

31、。如果表名和new，old冲突，可以使用REFERENCE子句指定其他名称。创建和维护触发器维护触发器vALTERTRIGGERENABLE|DISABLE|COMPILE;vDROPTRIGGER;v在触发器中出现异常时，所有工作被卷回。v不能读取、修改正在变化的表。不能修改完整性约束引用的表的主键、外键和唯一值。触发器使用限制vCREATEORREPLACETRIGGERaudit_empAFTERINSERTORUPDATEORDELETEONempFOREACHROWDECLAREtime_nowDATE;terminalCHAR(10);BEGINtime_now:=SYSDATE;

32、-得到当前时间IFINSERTINGTHEN-记录新的主键INSERTINTOaudit_tableVALUES(audit_seq.NEXTVAL,USER,time_now,EMP,INSERT,:new.empno);ELSIFDELETINGTHEN-记录删除行的主键INSERTINTOaudit_tableVALUES(audit_seq.NEXTVAL,USER,time_now,EMP,DELETE,:old.empno);ELSE-记录更新行的主键INSERTINTOaudit_tableVALUES(audit_seq.NEXTVAL,USER,time_now,EMP,UP

33、DATE,:old.empno);触发器举例IFUPDATING(SAL)THEN-记录sal的新旧值INSERTINTOaudit_table_valuesVALUES(audit_seq.CURRVAL,SAL,:old.sal,:new.sal);ELSIFUPDATING(DEPTNO)THEN-记录deptno的新旧值INSERTINTOaudit_table_valuesVALUES(audit_seq.CURRVAL,DEPTNO,:old.deptno,:new.deptno);ENDIF;ENDIF;END;触发器举例v对视图进行DML时触发，可以完成替代性的DML操作。v不检查视图的CHECK约束。v无需指定BEFORE或AFTER，对于修改，无需指定列。一定是FOREACHROW。视图触发器