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

数据库应用技术第五章 面向过程的SQL扩展,内容概要,5.1 服务器端程序5.2 PL/SQL的基本语法5.3 控制流5.4 过程与函数5.5 游标5.6 错误与异常处理5.7 触发器,5.1 服务器端程序,存储过程：用户创建，用SQL和其他语言(如PL/SQL)编写的过程或函数，存储在数据库内部，用来完成一个特定的任务。Oracle：PL/SQLMSSQL：Transaction-SQLPL/SQL：ORACLE对SQL进行扩展的过程式语言，具有通用程序设计语言的绝大部分特性，能够完成较为复杂和完整的功能可以用来编写存储过程。,5.1 服务器端程序,存储过程的优点安全：使用创建者权限、调用者不需要了解内部结构，不需要内部权限。性能：网络开销小，不传送中间结果节约SQL代码分析时间代码可重用完整性和一致性特性：可以使用变量、游标、控制结构，具有模块化、数据抽象、信息隐藏、错误处理等特性。,5.2 PL/SQL的基本语法,一、块结构二、变量的定义与使用三、内嵌SQL,PL/SQL的基本规则,每条语句可以写在多行每条语句都以;结尾。语句保留字和变量不区分大小写,PL/SQL的基本结构-块结构,块结构一个块的语法地位等价于一条语句块的整体构成DECLAREBEGINEXCEPTIONEND;,变量的定义与使用,变量类型简单变量类型和字段的变量类型相同记录变量类型一组具有不同类型的变量的集合，类似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;优点：不需要知道精确类型；在表定义发生变化时，不用修改程序,变量的定义与使用,%ROWTYPEDECLAREemp_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循环跳转语句EXITGOTO空语句,控制流语句-条件,IF THENEND IF;IF THEN ELSE END IF;,IF THEN ELSIF THEN ELSIF THEN ELSE END IF;,控制流语句-循环,LOOP.LOOP.EXIT WHEN END LOOP;.END LOOP;,控制流语句-循环,WHILE LOOPEND 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)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 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)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;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()LOOPEND LOOP;,举例：计算方差,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 NULL;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;,游标属性,对于显式游标，只有%ISOPEN可以在游标未打开时使用。否则产生错误。,修改游标中数据,在UPDATE和DELETE中使用WHERE CURRENT OF 子句，直接修改当前行的数据。FOR UPDATE锁在游标定义时使用FOR UPDATE子句。FOR UPDATE OF NOWAITFOR 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 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_ratio 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,数据不存在);-不进行插入，报告错误END;,5.7 触发器,触发器的基本概念定义用途与限制创建和维护触发器举例视图触发器,触发器是一种特殊的存储过程。它不是由用户显式调用的，而是当满足某个触发事件时自动执行的。触发器包括：触发事件，触发器约束和触发器动作。触发事件：DML语句，DDL语句，数据库系统事件和用户事件。触发约束：在何种条件下触发。动作：一个PL/SQL程序。,触发器的基本概念,触发器的用途高级的存取限制(如在特定时间修改)复杂的数据一致性检查自动产生关联的数据自动建立事件日志限制为全局性的操作定义触发器尽量使用完整性约束尽量不使用多重触发器、不要产生递归不要过长(60行),用途与限制,CREATE OR REPLACE TRIGGER BEFORE|AFTER|INSTEAD OFDELETE|INSERT|UPDATE OF ON|REFERENCING OLD AS|NEW AS FOR EACH ROW|STATEMENTWHEN();,创建和维护触发器,触发器,创建和维护触发器,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;,在触发器中出现异常时，所有工作被卷回。不能读取、修改正在变化的表。不能修改完整性约束引用的表的主键、外键和唯一值。,触发器使用限制,CREATE OR REPLACE TRIGGER audit_emp AFTER INSERT OR UPDATE OR DELETE ON emp FOR EACH ROWDECLARE time_now DATE;terminal CHAR(10);BEGIN time_now:=SYSDATE;-得到当前时间 IF INSERTING THEN-记录新的主键 INSERT INTO audit_table VALUES(audit_seq.NEXTVAL,USER,time_now,EMP,INSERT,:new.empno);ELSIF DELETING THEN-记录删除行的主键 INSERT INTO audit_table VALUES(audit_seq.NEXTVAL,USER,time_now,EMP,DELETE,:old.empno);ELSE-记录更新行的主键 INSERT INTO audit_table VALUES(audit_seq.NEXTVAL,USER,time_now,EMP,UPDATE,:old.empno);,触发器举例,IF UPDATING(SAL)THEN-记录sal的新旧值 INSERT INTO audit_table_values VALUES(audit_seq.CURRVAL,SAL,:old.sal,:new.sal);ELSIF UPDATING(DEPTNO)THEN-记录deptno的新旧值 INSERT INTO audit_table_values VALUES(audit_seq.CURRVAL,DEPTNO,:old.deptno,:new.deptno);END IF;END IF;END;,触发器举例,对视图进行DML时触发，可以完成替代性的DML操作。不检查视图的CHECK约束。无需指定BEFORE或AFTER，对于修改，无需指定列。一定是FOR EACH ROW。,视图触发器,