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

数据库应用技术 第五章 面向过程的 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/SQL MSSQL：Transaction-SQL vPL/SQL：ORACLE对SQL进行扩展的过程式语言 ，具有通用程序设计语言的绝大部分特性， 能够完成较为复杂和完整的功能可以用来编 写存储过程。 5.1 服务器端程序 v存储过程的优点 安全：使用创建者权限、调用者不需要了解内部 结构，不需要内部权限。 性能： v网络开销小，不传送中间结果 v节约SQL代码分析时间 代码可重用 完整性和一致性 v特性：可以使用变量、游标、控制结构，具 有模块化、数据抽象、信息隐藏、错误处理 等特性。 5.2 PL/SQL的基本语法 v基本规则 每条语句可以写在多行 每条语句都以;结尾。 语句保留字和变量不区分大小写 v一、块结构 v二、变量的定义与使用 v三、内嵌SQL PL/SQL的基本结构-块结构 v块结构 一个块的语法地位等价于一条语句 v块的整体构成 DECLARE 声明部分 BEGIN 执行部分 EXCEPTION 错误处 理部分 END; 变量的定义与使用 v变量类型 简单变 量类型 v和字段的变量类型相同 记录变 量类型 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 属性 vcredit NUMBER(7,2); vdebit credit%TYPE; 常用于变量与表中字段类型一致时 vmy_dname sales.aname%TYPE; v%ROWTYPE 记录变 量类型与表（或游标）的定义相一致 emp_rec emp%ROWTYPE; v优点: 不需要知道精确类型；在表定义发生变化 时，不用修改程序 变量的定义与使用 v变量使用 使用 := 作为赋值 运算符 其他计算、比较方法类似于普通语言 v作用域 外层定义的变量可以在子块中使用 可以在不同块中定义同名变量 子块可以通过外层的块名来引用外层变量 内嵌SQL v可以直接在PL/SQL中使用DQL和DML，但不能 使用DDL和DCL v在SQL语句中使用变量的值 可以出现表达式的地方都可以使用变量 直接写变量名，不需要额外的语法要素 v容易和字段混淆 v在MSSQL中，使用:作为前缀 通常出现在where子句、select子句 内嵌SQL v将查询结果赋值给一个变量 SELECT INTO FROM 子句 其他子句 v要求SELECT语句必须返回1行结果，若返回多 行或0行会在运行时报错 必须在逻辑上保证返回一条记录，例如使用主 键、聚合函数。 与子查询不同，返回0行也是错误 如何进行错误处 理，见后面章节 5.3 控制流语句 v条件语句 简单 条件语句 多重条件语句 v循环语句 LOOP循环 WHILE循环 FOR循环 v跳转语句 EXIT GOTO v空语句 控制流语句-条件 vIF THEN END IF; v vIF THEN ELSE END IF; vIF THEN ELSIF THEN ELSIF THEN ELSE END IF; 控制流语句-循环 v LOOP . LOOP . EXIT WHEN END LOOP; . END LOOP; 控制流语句-循环 vWHILE LOOP END LOOP; vFOR IN REVERSE LOOP END LOOP; 控制流语句-其他 v定义标号 v跳转 GOTO 标号; v空语句 NULL; v返回 RETURN; 5.4 过程与函数 v过程与函数的作用 v维护过程与函数 v外部过程与内部过程 v调用与参数传递 过程与函数-维护 v过程(procedure) 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 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 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) AS BEGIN 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 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部分定义，只供该过程 或函数调用 不能被外部使用 声明时不使用关键字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 BEGIN 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 指定缺省类型 作用传递 参数传送返回值传送参数和返回 值 形式参数如同一个常 数 未初始化变量已初始化变量 不能赋值不能用于表达式、必须赋 值 应该赋值 实际实际 参数可以是常量 、变量、表达 式 变量变量 传递传递 方式引用传送值传 送值传 送 参数类型 包（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、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 * FROM 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 FUNCTION(P_LID CHAR) IS BEGIN DECLARE CURSOR C1(V_LID CHAR) IS SELECT SALARY FROM sales WHERE CID=V_LID V_AVG NUMBER; V_SUM NUMBER DEFAULT 0; VI NUMBER; BEGIN SELECT AVG(SALVARY) INTO V_AVG FROM sales WHERE LID=P_LID OPEN C1(P_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、 GOTO语句或发生错误而跳出循环时，自动关 闭游标。 v语法 FOR IN () LOOP END LOOP; 举例：计算方差 CREATE FUNCTION(P_LID CHAR) IS BEGIN DECLARE CURSOR C1(V_LID CHAR) IS SELECT SALARY FROM sales WHERE LID=V_LID V_AVG NUMBER; V_SUM NUMBER DEFAULT 0; VI NUMBER; BEGIN SELECT AVG(SALVARY) INTO V_AVG FROM sales WHERE LID=P_LID IF V_AVG IS NULL THEN RETURN NULL END IF FOR REC_1 IN C1(P_LID) LOOP V_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语句返回的行数。 对于显式游标，只有%ISOPEN可以在游标未打开时使用。否则产生错误。 修改游标中数据 v在UPDATE和DELETE中使用WHERE CURRENT OF 子句，直接修改当前行的数据。 vFOR UPDATE锁 在游标定义时使用FOR UPDATE子句。 FOR UPDATE OF NOWAIT FOR UPDATE子句锁住该游标查询出来的所有行， 避免其他用户对相关内容的修改。 特别在一个长时间的事务过程中，避免前后出现 不一致的情况 5.6 错误与异常处理 v错误与异常 v两类处理机制的对比 v异常的定义与分类 v结构化异常处理 语法 流程 举例 错误与异常 v各种非正常的状态称为错误。 v不能够处理非正常情况的程序是不可靠的， 不够鲁棒(Robust) v异常(exception)是对错误情况的包装，在错误 传播、处理、返回等操作过程中标明错误内 容。 v异常也可以指进行错误处理的机制。 两种处理机制 v处理错误的方式通常有两类，一类是C语言等 的实现方式，包括： 返回值 v发生错误的程序段不一定能够解决问题，它可以通 过返回值或者全局标志将错误的状态传递给调 用者 ，但调用者完全可能忽视错误 。 全局状态标志 v错误处 理的代码混杂在正常的程序处理流程中，使 得程序结构显得比较混乱。 两种处理机制 v另外一种方式，是在PL/1语言中提出的，并 在PL/SQL中应用，在C+和Java语言中被采用 的“结构化异常处理”的方法。 try /*正常流程*/ catch() /*根据不同错误原因进行处理*/ 错误与异常处理 v优点 发生错误的程序段只负责把错误抛出，而由有 能力的高层调用者（包括最终用户）来解决问 题。 程序流程清晰。 程序员必须处理错误。 v缺点 需要对错误进 行分类，有一个明确的分类体系 。 在C+语言中，内存释放是一个问题。 错误分类和定义 v在Oracle中，错误分为: 内部错误或系统错误 用户定义错误 所有错误均有一个特定的错误代码 一些错误有预定义的名字，也可以为其他错误给出名字 。 系统用户定义 通用无名变量编号 触发方式自动自动RAISERAISE_APPLICATION_ERROR 定义方式名称/编号编号 EXCEPTION 消息文本，编号： -20000,-20999 定义域全局全局块局部整个程序 一些预定义错误 预定义异常 描述 Oracle错误 错误代 码 CURSOR_ALREADY_OPEN 试图打开一个已经打开的游标，一个游标在它重新打开前必须被关闭 。一个游标FOR循环会自动地打开所涉及的游标，所以在游标循环里 不能打开游标 ORA-06511 -6511 DUL_VAL_ON_INDEX 试图在一个有惟一性约束的数据库列中存储重复的值 ORA-00001 -1 INVALID_CURSOR 试图执行一个无效的游标操作 ORA-01001 -1001 INVALID_NUMBER 试图将一个看起来不像是一个有效的数字的字符串转换成数字失败时 ，而在过程性语句中，将会引发VALUE_ERROR错误，代替 INVALID_NUMBER错误 ORA-01722 -1722 LOGIN_DENIED 用一个无效的用户名或口令去登陆Oracle ORA-01017 -1017 NO_DATA_FOUND 一个SELECT INTO语句没有返回数据或者程序引用一个嵌套表中被删除 的元素或索引表中一个没有被初始化的元素 ORA-01403 100 TIMEOUT_ON_RESOURCE Oracle在等待资源时发生超时现象 ORA-00051 -51 TOO_MANY_ROWS SELECT INTO语句返回了多行数据 ORA-01422 -1422 VALUE_ERROR 一个算法、转换、截断或者大小约束错误发生，如果在SQL语句中发 生错误则会引发INVALID_ERROR错误，替代了VALUE_ERROR错误 ORA-06502 -6502 ZERO_DIVIDE 发生被零除 ORA-01476 -1476 用户自定义错误 v自定义的错误，不需要声明。 Raise_application_error (, , TRUE |FALSE); v BETWEEN -20000,-20999 v CHAR(2048); v-20000应用于一般性错误。 如果第三个参数为假（缺省值），替换现有的 错误。如果为真，加在现有错误之上。这种错 误也可以被捕捉。 异常处理语法结构 v这个语法只能处理有名字的异常 v对于系统无名错误和用户自定义编号错误 或者使用用户定义方式为其起名 或者在OTHERS里面，检查错误 代码来判断 EXCEPTION段 WHEN OR THEN WHEN OR THEN WHEN OTHERS THEN 异常处理过程 v执行体内出现异常，转到本块的异常处理程 序。在声明段和错误处理段发生异常时，转 到上一层的异常处理程序。 v如果在这个位置没有找到异常处理程序，和 处理程序中没有找到对应的错误号，且没有 OTHERS段，则继续向上传播。如直到最外层 都没有找到，则结束当前程序，返回用户。 v执行完异常处理程序后，控制权转移到异常 处理程序的外层块的下一条语句执行。如异 常处理在最外层，则结束程序。 异常处理举例 CREATE OR REPLACE FUNCTION ISDATE (str varchar2, fmt varchar2 default null) RETURN DATE IS v_date date; v_fmt varchar2(100) default fmt; BEGIN IF fmt IS NULL THEN SELECT value INTO v_fmt FROM v$nls_parameters WHERE parameter=NLS_DATE_FORMAT; END IF; v_date := to_date(str, v_fmt); RETURN v_date ; EXCEPTION WHEN OTHERS THEN RETURN NULL; END; / 异常处理举例 DECLARE pe_ratio NUMBER(3,1); BEGIN DELETE FROM stats WHERE symbol = XYZ; BEGIN - 子块开始 SELECT price / earnings INTO pe_ratio FROM stocks WHERE symbol = XYZ; EXCEPTION WHEN ZERO_DIVIDE THEN pe_ratio := 0; END; - 子块结束 INSERT INTO stats (symbol, ratio) VALUES (XYZ, pe_ratio); EXCEPTION WHEN NO_DATA_FOUND Raise_application_error(-20010,数据不存在); -不进行插入，报告错误 END; 5.7 触发器 v触发器的基本概念 定义 用途与限制 v创建和维护 v触发器举例 v视图触发器 v触发器是一种特殊的存储过程。它不是由用 户显式调用的，而适当满足某个触发事件时 自动执行的。 v触发器包括：触发事件，触发器约束和触发 器动作。 触发事件：DML语句，DDL语句，数据库系统事 件和用户事件。 触发约束：在何种条件下触发。 动作：一个PL/SQL程序。 触发器的基本概念 v触发器的用途 高级的存取限制(如在特定时间修改) 复杂的数据一致性检查 自动产生关联的数据 自动建立事件日志 v限制 为全局性的操作定义触发器 尽量使用完整性约束 尽量不使用多重触发器、不要产生递归 不要过长( BEFORE DELETE AFTER INSERTOR INSTEAD OF UPDATE OF ON | REFERENCING OLD AS | NEW AS FOR EACH ROW STATEMENT WHEN () ; 创建和维护触发器 触发器 创建和维护触发器 vBEFORE和AFTER。BEFORE用于可能修改语句 带来的数值的情况。 v条件谓词：INSERTING，DELETING， UPDATING，UPDATING() v缺省为FOR EACH STATEMENT v:new和:old。可以在BEFORE触发器中向:new 记录中赋值，但不能在AFTER触发器中赋值。 如果表名和new，old冲突，可以使用 REFERENCE子句指定其他名称。 创