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

1、数据库应用技术第五章 面向过程的SQL扩展数据库应用技术第五章 面向过程的SQL扩展内容概要5.1 服务器端程序5.2 PL/SQL的基本语法5.3 控制流5.4 过程与函数5.5 游标5.6 错误与异常处理5.7 触发器内容概要5.1 服务器端程序5.1 服务器端程序存储过程：用户创建，用SQL和其他语言(如PL/SQL)编写的过程或函数，存储在数据库内部，用来完成一个特定的任务。Oracle： PL/SQLMSSQL：Transaction-SQLPL/SQL：ORACLE对SQL进行扩展的过程式语言，具有通用程序设计语言的绝大部分特性，能够完成较为复杂和完整的功能可以用来编写存储过程。5

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

3、条语句块的整体构成DECLARE声明部分BEGIN执行部分EXCEPTION错误处理部分END;PL/SQL的基本结构-块结构块结构变量的定义与使用变量类型简单变量类型和字段的变量类型相同记录变量类型一组具有不同类型的变量的集合，类似C中的STRUCT或PASCAL中的RECORD集合变量类型变量的定义与使用变量类型变量的定义与使用变量声明必须先声明，后使用在declare段声明不允许前向引用一行中只能定义一个变量大小写不敏感变量的定义与使用变量声明变量的定义与使用简单示例emp_count NUMBER(4); (初始值为NULL)缺省值blood_type CHAR := 0; blood

4、_type CHAR DEFAULT 0;NOT NULL，必须有缺省值emp_count NUMBER(3) NOT NULL := 0;变量的定义与使用简单示例变量的定义与使用%TYPE类型定义使用其它变量或列的数据类型，但不继承NOT NULL属性credit NUMBER(7,2); debit credit%TYPE;常用于变量与表中字段类型一致时my_dname sales.aname%TYPE;%ROWTYPE记录变量类型与表（或游标）的定义相一致emp_rec emp%ROWTYPE;优点: 不需要知道精确类型；在表定义发生变化时，不用修改程序变量的定义与使用%TYPE类型定义

5、变量的定义与使用变量使用使用 := 作为赋值运算符其他计算、比较方法类似于普通语言作用域外层定义的变量可以在子块中使用可以在不同块中定义同名变量子块可以通过外层的块名来引用外层变量变量的定义与使用变量使用内嵌SQL可以直接在PL/SQL中使用DQL和DML，但不能使用DDL和DCL在SQL语句中使用变量的值可以出现表达式的地方都可以使用变量直接写变量名，不需要额外的语法要素容易和字段混淆在MSSQL中，使用:作为前缀通常出现在where子句、select子句内嵌SQL可以直接在PL/SQL中使用DQL和DML，但不能内嵌SQL将查询结果赋值给一个变量SELECT INTO FROM 子句 其他

6、子句要求SELECT语句必须返回1行结果，若返回多行或0行会在运行时报错必须在逻辑上保证返回一条记录，例如使用主键、聚合函数。与子查询不同，返回0行也是错误如何进行错误处理，见后面章节内嵌SQL将查询结果赋值给一个变量5.3 控制流语句条件语句简单条件语句多重条件语句循环语句LOOP循环WHILE循环FOR循环跳转语句EXITGOTO空语句5.3 控制流语句条件语句控制流语句-条件IF THENEND IF;IF THEN ELSE END IF;IF THEN ELSIF THEN ELSIF THEN ELSE END IF;控制流语句-条件IF THENIF T控制流语句-循环LOOP

7、. LOOP . EXIT WHEN END LOOP; .END LOOP;控制流语句-循环控制流语句-循环WHILE LOOPEND LOOP;FOR IN . REVERSE LOOP END LOOP;控制流语句-循环WHILE LOOP控制流语句-其他定义标号 跳转GOTO 标号;空语句NULL;返回RETURN; 控制流语句-其他定义标号 5.4 过程与函数过程与函数的作用维护过程与函数外部过程与内部过程调用与参数传递5.4 过程与函数过程与函数的作用过程与函数-维护过程(procedure)CREATE OR REPLACE PROCEDURE () AS | IS ; := I

8、N | OUT | IN OUT DEFAULT ：无长度和精度ALTER PROCEDURE COMPILE; DROP PROCEDURE ;过程与函数-维护过程(procedure)过程与函数-维护函数(function)CREATE OR REPLACE FUNCTION () RETURN AS | IS ;ALTER FUNCTION COMPILE; DROP FUNCTION ;过程与函数-维护函数(function)创建过程计算某顾客在给定时间前一年的总购买金额CREATE PROCEDURE p_1 (v_cid IN CHAR, v_sum OUT NUMBER, v_d

9、ate 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;创建过程计算某顾客在给定时间前一年的总购买金额CREATE CREATE FUNCTION f_1 (v_cid IN CHAR, v_date IN DATE) RETURN NUMBER AS DECLARE v_sum NUMBER; BEGIN SELECT SUM(dollars) INTO

10、 v_sum FROM orders WHERE cid = v_cid AND buy_date BETWEEN v_date - 365 AND v_date; RETURN v_sum; END;创建和函数CREATE FUNCTION f_1创建和函数复杂过程举例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 DEL

11、ETE FROM TMP_SPLIT WHERE KIND= P_KIND; IF 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

12、;END INS_SPLIT_WORD;复杂过程举例CREATE OR REPLACE PROCED内部子过程在过程或函数的DECLARE部分定义，只供该过程或函数调用不能被外部使用声明时不使用关键字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

13、_2; END; END;内部过程举例CREATE PROCEDURE p_3 ASSQL*PLUS 中，使用EXECUTE命令。在其他PL/SQL程序中，直接写出过程及参数CREATE PROCEDURE p_2 AS BEGIN DECLARE v1 NUMBER(10); BEGIN p_1(A01, v1, SYSDATE); /全局过程p_1 END; END;执行方法SQL*PLUS 中，使用EXECUTE命令。执行方法位置表示credit_acct (acct, amt);名称表示 credit_acct(amount = amt, acct_no = acct); credit

14、_acct(acct_no = acct, amount = amt);混和表示credit_acct(acct, amount = amt);如果默认值在前，后面有非默认值，必须使用名称调用方式。参数传递语法位置表示参数传递语法INOUTIN OUT指定缺省类型作用传递参数传送返回值传送参数和返回值形式参数如同一个常数未初始化变量已初始化变量不能赋值不能用于表达式、必须赋值应该赋值实际参数可以是常量、变量、表达式变量变量传递方式引用传送值传送值传送参数类型INOUTIN OUT指定缺省类型作用传递参数传送返回值传送包（Package）由多个过程可以组成包调试使用的包DBMS_OUTPUT.P

15、UT_LINE();DBMS_OUTPUT：包名PUT_LINE：过程名包（Package）由多个过程可以组成包5.5 游标游标的作用使用游标游标FOR循环游标属性修改游标中数据5.5 游标游标的作用游标的作用用来处理从数据库中查询出来的一组数据的机制。游标查询返回的数据称为结果集(result set)。游标的使用类似文件操作，包括OPEN、FETCH、CLOSE等操作。游标的类型显式游标隐含游标PL/SQL为每一个DML语句隐含定义了一个游标包游标游标的作用用来处理从数据库中查询出来的一组数据的机制。使用游标声明在DECLARE段打开循环读取读取当前行的数据到变量中判断是否读到末尾关闭关闭

16、后才可以重新打开使用游标声明游标声明声明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 * FROM sales WHERE salary 2000; CURSOR c3 (v_salary)IS SELECT sid, aname, salary FROM sales

17、WHERE salary v_salary; 游标声明DECLARE游标声明使用游标打开OPEN cur_name ()；可以使用位置表示法和名称表示法。不能打开已经打开的游标。打开游标时带入的参数已经在打开时被固定。读取数据FETCH INTO |;使用游标打开使用游标循环读LOOP FETCH c1 INTO my_record; EXIT WHEN c1%NOTFOUND; END LOOP;关闭CLOSE ;使用游标循环读举例：计算方差CREATE FUNCTION(P_LID CHAR) ISBEGIN DECLARECURSOR C1(V_LID CHAR) ISSELECT SA

18、LARY 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_AVG);END LOOPCLOSE C1;RETURN V_SUM; END;END举例：计算方差CREATE FUNCTION(P_LID

19、 C游标FOR循环自动声明一个和游标返回值相同类型的循环变量、自动打开游标，在每一次循环中读取一行数据，在出错或没有数据时结束循环，关闭游标。同时，当在循环中使用EXIT、GOTO语句或发生错误而跳出循环时，自动关闭游标。语法FOR IN () LOOPEND LOOP;游标FOR循环自动声明一个和游标返回值相同类型的循环变量、自举例：计算方差CREATE FUNCTION(P_LID CHAR) ISBEGIN DECLARECURSOR C1(V_LID CHAR) ISSELECT SALARY FROM sales WHERE LID=V_LIDV_AVG NUMBER;V_SUM N

20、UMBER 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举例：计算方差CREATE FUNCTION(P_LID C游标属性显式隐式（SQL%）%ISOPEN是否打开FALSE%FOUND是否成功查询数据。如无FETCH

21、为空。上一个修改语句是否影响了表中的数据。或者SELECT INTO 返回数据。如无DML，则为NULL。%NOTFOUND与上相反与上相反%ROWCOUNT最近一次提取的行序号，当未提取数据时为0。修改语句影响的行数，或SELECT语句返回的行数。对于显式游标，只有%ISOPEN可以在游标未打开时使用。否则产生错误。游标属性显式隐式（SQL%）%ISOPEN是否打开FALSE修改游标中数据在UPDATE和DELETE中使用WHERE CURRENT OF 子句，直接修改当前行的数据。FOR UPDATE锁在游标定义时使用FOR UPDATE子句。FOR UPDATE OF NOWAITFOR

22、 UPDATE子句锁住该游标查询出来的所有行，避免其他用户对相关内容的修改。特别在一个长时间的事务过程中，避免前后出现不一致的情况修改游标中数据在UPDATE和DELETE中使用WHERE 5.6 错误与异常处理错误与异常两类处理机制的对比异常的定义与分类结构化异常处理语法流程举例5.6 错误与异常处理错误与异常错误与异常各种非正常的状态称为错误。不能够处理非正常情况的程序是不可靠的，不够鲁棒(Robust)异常(exception)是对错误情况的包装，在错误传播、处理、返回等操作过程中标明错误内容。异常也可以指进行错误处理的机制。错误与异常各种非正常的状态称为错误。两种处理机制处理错误的方式

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

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

25、错误分为:系统用户定义通用无一些预定义错误预定义异常 描述 Oracle错误 错误代码CURSOR_ALREADY_OPEN 试图打开一个已经打开的游标，一个游标在它重新打开前必须被关闭。一个游标FOR循环会自动地打开所涉及的游标，所以在游标循环里不能打开游标 ORA-06511 -6511DUL_VAL_ON_INDEX 试图在一个有惟一性约束的数据库列中存储重复的值 ORA-00001 -1INVALID_CURSOR 试图执行一个无效的游标操作 ORA-01001 -1001INVALID_NUMBER 试图将一个看起来不像是一个有效的数字的字符串转换成数字失败时，而在过程性语句中，将会

26、引发VALUE_ERROR错误，代替INVALID_NUMBER错误 ORA-01722 -1722LOGIN_DENIED 用一个无效的用户名或口令去登陆Oracle ORA-01017 -1017NO_DATA_FOUND 一个SELECT INTO语句没有返回数据或者程序引用一个嵌套表中被删除的元素或索引表中一个没有被初始化的元素 ORA-01403 100TIMEOUT_ON_RESOURCE Oracle在等待资源时发生超时现象 ORA-00051 -51TOO_MANY_ROWS SELECT INTO语句返回了多行数据 ORA-01422 -1422VALUE_ERROR 一个算

27、法、转换、截断或者大小约束错误发生，如果在SQL语句中发生错误则会引发INVALID_ERROR错误，替代了VALUE_ERROR错误 ORA-06502 -6502ZERO_DIVIDE 发生被零除 ORA-01476 -1476一些预定义错误预定义异常 描述 Oracle错误 错误用户自定义错误自定义的错误，不需要声明。Raise_application_error (, , TRUE |FALSE); BETWEEN -20000,-20999 CHAR(2048);-20000应用于一般性错误。如果第三个参数为假（缺省值），替换现有的错误。如果为真，加在现有错误之上。这种错误也可以被捕

28、捉。用户自定义错误自定义的错误，不需要声明。异常处理语法结构这个语法只能处理有名字的异常对于系统无名错误和用户自定义编号错误或者使用用户定义方式为其起名或者在OTHERS里面，检查错误代码来判断EXCEPTION段 WHEN OR THEN WHEN OR THEN WHEN OTHERS THEN 异常处理语法结构这个语法只能处理有名字的异常EXCEPTIO异常处理过程执行体内出现异常，转到本块的异常处理程序。在声明段和错误处理段发生异常时，转到上一层的异常处理程序。如果在这个位置没有找到异常处理程序，和处理程序中没有找到对应的错误号，且没有OTHERS段，则继续向上传播。如直到最外层都没有

29、找到，则结束当前程序，返回用户。执行完异常处理程序后，控制权转移到异常处理程序的外层块的下一条语句执行。如异常处理在最外层，则结束程序。异常处理过程执行体内出现异常，转到本块的异常处理程序。在声明异常处理举例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_fmtFROM v$nls_paramet

30、ersWHERE parameter=NLS_DATE_FORMAT;END IF;v_date := to_date(str, v_fmt);RETURN v_date ;EXCEPTIONWHEN OTHERS THENRETURN NULL;END;/异常处理举例CREATE OR REPLACE FUNCTI异常处理举例DECLARE pe_ratio NUMBER(3,1);BEGINDELETE FROM stats WHERE symbol = XYZ; BEGIN - 子块开始 SELECT price / earnings INTO pe_ratio FROM stocksW

31、HERE symbol = 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;异常处理举例DECLARE5.7 触发器触发器的基本概念定义用途与限制创建和维护触发器举例视图触发器5.7 触发器触发器的基本概念触发器是一种特殊的存储过程。它不是由用户显式调

32、用的，而适当满足某个触发事件时自动执行的。触发器包括：触发事件，触发器约束和触发器动作。触发事件：DML语句，DDL语句，数据库系统事件和用户事件。触发约束：在何种条件下触发。动作：一个PL/SQL程序。触发器的基本概念触发器是一种特殊的存储过程。它不是由用户显式调用的，而适当满触发器的用途高级的存取限制(如在特定时间修改)复杂的数据一致性检查自动产生关联的数据自动建立事件日志限制为全局性的操作定义触发器尽量使用完整性约束尽量不使用多重触发器、不要产生递归不要过长(60行)用途与限制触发器的用途用途与限制CREATE OR REPLACE TRIGGER BEFORE DELETE AFTER

33、 INSERTORINSTEAD OF UPDATE OF ON | REFERENCING OLD AS | NEW AS FOR EACH ROWSTATEMENTWHEN ();创建和维护触发器CREATE OR REPLACE TRIGGER 触触发器创建和维护触发器触发器创建和维护触发器BEFORE和AFTER。BEFORE用于可能修改语句带来的数值的情况。条件谓词：INSERTING，DELETING，UPDATING，UPDATING()缺省为FOR EACH STATEMENT:new和:old。可以在BEFORE触发器中向:new记录中赋值，但不能在AFTER触发器中赋值。如果表名和new，old冲突，可以使用REFERENCE子句指定其他名称。创建和维护触发器BEFORE和AFTER。BEFORE用于可能修改语句带来的维护触发器ALTER TRIGGER ENABLE