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

数据库应用技术 第五章 面向过程的SQL扩展,内容概要,5.1 服务器端程序 5.2 PL/SQL的基本语法 5.3 控制流 5.4 过程与函数 5.5 游标 5.6 错误与异常处理 5.7 触发器,5.1 服务器端程序,存储过程：用户创建，用SQL和其他语言(如PL/SQL)编写的过程或函数，存储在数据库内部，用来完成一个特定的任务。 Oracle： PL/SQL MSSQL：Transaction-SQL PL/SQL：ORACLE对SQL进行扩展的过程式语言，具有通用程序设计语言的绝大部分特性，能够完成较为复杂和完整的功能可以用来编写存储过程。,5.1 服务器端程序,存储过程的优点 安全：使用创建者权限、调用者不需要了解内部结构，不需要内部权限。 性能： 网络开销小，不传送中间结果 节约SQL代码分析时间 代码可重用 完整性和一致性 特性：可以使用变量、游标、控制结构，具有模块化、数据抽象、信息隐藏、错误处理等特性。,5.2 PL/SQL的基本语法,一、块结构 二、变量的定义与使用 三、内嵌SQL,PL/SQL的基本规则,每条语句可以写在多行 每条语句都以;结尾。 语句保留字和变量不区分大小写,PL/SQL的基本结构-块结构,块结构 一个块的语法地位等价于一条语句 块的整体构成 DECLARE BEGIN EXCEPTION END;,变量的定义与使用,变量类型 简单变量类型 和字段的变量类型相同 记录变量类型 一组具有不同类型的变量的集合，类似C中的STRUCT或PASCAL中的RECORD 集合变量类型,变量的定义与使用,变量声明 必须先声明，后使用 在declare段声明 不允许前向引用 一行中只能定义一个变量 大小写不敏感,变量的定义与使用,简单示例 emp_count NUMBER(4); (初始值为NULL) 缺省值 blood_type CHAR := 0; blood_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 salers.sname%TYPE; 优点：不需要知道精确类型；在表定义发生变化时，不用修改程序,变量的定义与使用,%ROWTYPE DECLARE emp_rec emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec c1%ROWTYPE;,变量的定义与使用,赋值与计算、比较 使用 := 作为赋值运算符 其他计算、比较方法类似于普通语言 作用域 外层定义的变量可以在子块中使用 可以在不同块中定义同名变量 子块可以通过外层的块名来引用外层变量,内嵌SQL,可以直接在PL/SQL中使用DQL和DML，但不能使用DDL和DCL 在SQL语句中使用变量的值 可以出现表达式的地方都可以使用变量 直接写变量名，不需要额外的语法要素 容易和字段混淆 在MSSQL中，使用:作为前缀 通常出现在where子句、select子句,内嵌SQL,将查询结果赋值给一个变量 SELECT INTO FROM 子句 其他子句 要求SELECT语句必须返回1行结果，若返回多行或0行会在运行时报错 必须在逻辑上保证返回一条记录，例如使用主键、聚合函数。 与子查询不同，返回0行也是错误 如何进行错误处理，见后面章节,5.3 控制流语句,条件语句 简单条件语句 多重条件语句 循环语句 LOOP循环 WHILE循环 FOR循环 跳转语句 EXIT GOTO 空语句,控制流语句-条件,IF THEN END IF; IF THEN ELSE END IF;,IF THEN ELSIF THEN ELSIF THEN ELSE END IF;,控制流语句-循环, LOOP . LOOP . EXIT WHEN END LOOP; . END LOOP;,控制流语句-循环,WHILE LOOP END LOOP; FOR IN REVERSE LOOP END LOOP;,定义标号 跳转 GOTO ; 空语句 NULL; 返回 RETURN;,5.4 过程与函数,过程与函数的作用 维护过程与函数 外部过程与内部过程 调用与参数传递,过程与函数-维护,过程(procedure) CREATE OR REPLACE PROCEDURE () AS | IS ; := IN | OUT | IN OUT DEFAULT ：无长度和精度 ALTER PROCEDURE COMPILE; DROP PROCEDURE ;,过程与函数-维护,函数(function) CREATE OR REPLACE FUNCTION () RETURN AS | IS ; ALTER FUNCTION COMPILE; DROP FUNCTION ;,创建过程,计算某顾客在给定时间前一年的总购买金额,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;,CREATE 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;,内部子过程 在过程或函数的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程序中，直接写出过程及参数 CREATE PROCEDURE p_2 AS BEGIN DECLARE v1 NUMBER(10); BEGIN p_1(C001, v1, SYSDATE); /全局过程p_1 END; END;,执行方法,位置表示 credit_acct (acct, amt); 名称表示 credit_acct(amount = amt, acct_no = acct); credit_acct(acct_no = acct, amount = amt); 混和表示 credit_acct(acct, amount = amt); 如果默认值在前，后面有非默认值，必须使用名称调用方式。,参数传递语法,参数类型,包（Package）,由多个过程可以组成包 调试使用的包 DBMS_OUTPUT.PUT_LINE(); DBMS_OUTPUT：包名 PUT_LINE：过程名,5.5 游标,游标的作用 使用游标 游标FOR循环 游标属性 修改游标中数据,游标的作用,用来处理从数据库中查询出来的一组数据的机制。 游标查询返回的数据称为结果集(result set)。 游标的使用类似文件操作，包括OPEN、FETCH、CLOSE等操作。 游标的类型 显式游标 隐含游标 PL/SQL为每一个DML语句隐含定义了一个游标 包游标,使用游标,声明 在DECLARE段 打开 循环读取 读取当前行的数据到变量中 判断是否读到末尾 关闭 关闭后才可以重新打开,游标声明,声明 CURSOR () RETURN IS ; ： 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_salary;,游标声明,使用游标,打开 OPEN ()； 可以使用位置表示法和名称表示法。 不能打开已经打开的游标。 打开游标时带入的参数已经在打开时被固定。 读取数据 FETCH INTO |;,使用游标,循环读 LOOP FETCH c1 INTO my_record; EXIT WHEN c1%NOTFOUND; END LOOP; 关闭 CLOSE ;,举例：计算方差,CREATE FUNCTION(f_lid CHAR) IS BEGIN DECLARE CURSOR c1(v_lid CHAR) IS SELECT salary FROM salers WHERE lid = v_lid; v_avg NUMBER; v_sum NUMBER DEFAULT 0; vi NUMBER; BEGIN SELECT 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循环,自动声明一个和游标返回值相同类型的循环变量、自动打开游标，在每一次循环中读取一行数据，在出错或没有数据时结束循环，关闭游标。同时，当在循环中使用EXIT、GOTO语句或发生错误而跳出循环时，自动关闭游标。 语法 FOR IN () LOOP END LOOP;,举例：计算方差,CREATE FUNCTION(f_lid CHAR) IS BEGIN DECLARE CURSOR c1(v_lid CHAR) IS SELECT salary FROM salers WHERE lid = v_lid; v_avg NUMBER; v_sum NUMBER DEFAULT 0; vi NUMBER; BEGIN SELECT AVG(salary) INTO v_avg FROM salers WHERE lid = f_lid; Ifv_avg IS NULL THEN RETURN NULL; END IF; FOR rec1 IN c1(f_lid) LOOP v_sum := v_sum + ( vi v_avg ) * ( vi v_avg ); END LOOP; RETURN v_sum; END; END;,游标属性,对于显式游标，只有%ISOPEN可以在游标未打开时使用。否则产生错误。,修改游标中数据,在UPDATE和DELETE中使用WHERE CURRENT OF 子句，直接修改当前行的数据。 FOR UPDATE锁 在游标定义时使用FOR UPDATE子句。 FOR UPDATE OF NOWAIT FOR UPDATE子句锁住该游标查询出来的所有行，避免其他用户对相关内容的修改。 特别在一个长时间的事务过程中，避免前后出现不一致的情况。,5.6 错误与异常处理,错误与异常 两类处理机制的对比 异常的定义与分类 结构化异常处理 语法 流程 举例,错误与异常,各种非正常的状态称为错误。 不能够处理非正常情况的程序是不可靠的，不够鲁棒(Robust)。 异常(exception)是对错误情况的包装，在错误传播、处理、返回等操作过程中标明错误内容。 异常也可以指进行错误处理的机制。,两种处理机制,处理错误的方式通常有两类，一类是C语言等的实现方式，包括： 返回值 发生错误的程序段不一定能够解决问题，它可以通过返回值或者全局标志将错误的状态传递给调用者，但调用者完全可能忽视错误。 全局状态标志 错误处理的代码混杂在正常的程序处理流程中，使得程序结构显得比较混乱。,两种处理机制,另外一种方式，是在PL/1语言中提出的，并在PL/SQL中应用，在C+和Java语言中被采用的“结构化异常处理”的方法。 try /*正常流程*/ catch() /*根据不同错误原因进行处理*/ ,错误与异常处理,优点 发生错误的程序段只负责把错误抛出，而由有能力的高层调用者（包括最终用户）来解决问题。 程序流程清晰 程序员必须处理错误 缺点 需要对错误进行分类，有一个明确的分类体系。 在C+语言中，内存释放是一个问题。,错误分类和定义,在Oracle中，错误分为: 内部错误或系统错误 用户定义错误 所有错误均有一个特定的错误代码 一些错误有预定义的名字，也可以为其他错误给出名字。,一些预定义错误,用户自定义错误,自定义的错误，不需要声明。 RAISE_APPLICATION_ERROR (, , TRUE |FALSE); BETWEEN -20000,-20999 CHAR(2048) -20000应用于一般性错误 如果第三个参数为假（缺省值），替换现有的错误。如果为真，加在现有错误之上。这种错误也可以被捕捉。,异常处理语法结构,这个语法只能处理有名字的异常 对于系统无名错误和用户自定义编号错误 或者使用用户定义方式为其起名 或者在OTHERS里面，检查错误代码来判断,EXCEPTION段 WHEN OR THEN WHEN OTHERS THEN ,异常处理过程,执行体内出现异常，转到本块的异常处理程序。在声明段和错误处理段发生异常时，转到上一层的异常处理程序。 如果在这个位置没有找到异常处理程序，和处理程序中没有找到对应的错误号，且没有OTHERS段，则继续向上传播。如直到最外层都没有找到，则结束当前程序，返回用户。 执行完异常处理程序后，控制权转移到异常处理程序的外层块的下一条语句执行。如异常处理在最外层，则结束程序。,异常处理举例,CREATE OR REPLACE FUNCTION isdate (str VARCHAR2, fmt VARCHAR2 DEFAULT NULL) RETURN DATE IS BEGIN DECLARE 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; 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 THEN RAISE_APPLICATION_ERROR(-20010,数据不存在); -不进行插入，报告错误 END;,5.7 触发器,触发器的基本概念 定义 用途与限制 创建和维护 触发器举例 视图触发器,触发器是一种特殊的存储过程。它不是由用户显式调用的，而是当满足某个触发事件时自动执行的。 触发器包括：触发事件，触发器约束和触发器动作。 触发事件：DML语句，DDL语句，数据库系统事件和用户事件。 触发约束：在何种条件下触发。 动作：一个PL/SQL程序。,触发器的基本概念,触发器的用途 高级的存取限制(如在特定时间修改) 复杂的数据一致性检查 自动产生关联的数据 自动建立事件日志 限制 为全局性的操作定义触发器 尽量使用完整性约束 尽量不使用多重触发器、不要产生递归 不要过长(60行),用途与限制,CREATE OR REPLACE TRIGGER BEFORE|AFTER|INSTEAD OF DELETE|INSERT| |UPDATE OF ON | REFERENCING OLD AS | NEW AS FOR EACH ROW|STATEMENT WHEN () ;,创建和维护触发器,触发器,创建和维护触发器,BEFORE和AFTER。BEFORE用于可能修改语句带来的数值的情况。 条件谓词：INSERTING，DELETING，UPDATING，UPDATING() 缺省为FOR EACH STATEMENT :new和:old。可以在BEFORE触发器中向:new记录中赋值，但不能在AFTER触发器中赋值。如果表名和new，old冲突，可以使用REFERENCE子句指定其他名称。,创建和维护触发器,维护触发器,ALTER TRIGGER ENABLE | DISABLE | COMPILE ; DROP TRIGGER ;,在触发器中出现异常时，所有工作被卷回。 不能读取、修改正在变化的表。不能修改完整性约束引用的