PLSQL.ppt

1、PLSQL,主要内容,PL/SQL的组成 条件控制 循环控制 异常处理 记录类型,什么是PL/SQL?,问题： 标准化的SQL是针对数据库进行操作的语言，每次只能执行一条语句，语句以英文的分号“；”为 结束标识； 数据库在进行后台数据管理的同时，需要一定的根据业务逻辑的需求， 进行较为复杂的管理，但较为复杂的管理都需要变成语言进行实现，结构化变成语言对数据库的支持能力较弱。 PL/SQL不是一个独立的产品。PL/SQL程序块只能在SQL PLUS、SQL PLUS Worksheet等工具支持下以解释型方式执行，不能编译成可执行文件，脱离支撑环境执行。 Oracle中PL/SQL的例子：C:o

2、racleora90plsqldemo,PL/SQL简介,PL/SQL(Procedural Language/SQL) 是对SQL的扩展 支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构 可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑 支持Oracle的大型对象和集合；,PL/SQL 的体系结构,PL/SQL 引擎驻留在 Oracle 服务器中 该引擎接受 PL/SQL 块并对其进行编译执行,将PL/SQL 块发送给 Oracle 服务器,用户,执行过程语句,引擎将 SQL 语句发送给SQL 语句执行器,执行 SQL 语句,将结果发送给用户,PL/SQL的

3、组成,PL/SQL块的组成 PL/SQL语言以块为单位，块中可以嵌套子块。 一个基本的PL/SQL块由3部分组成：定义部分（DECLARE），可执行部分（BEGIN），异常处理部分（EXCEPTION）。,定义部分,执行部分,例外处理部分,PL/SQL块,HELLO PL/SQL,DECLARE X VARCHAR2(20) ; BEGIN -注释 X:=HELLO PL/SQL ; DBMS_OUTPUT.PUT_LINE(X) ; END;,屏幕上没有显示结果时应打开SERVEROUT开关 SET SERVEROUT ON SIZE 10000,赋值赋号为：=。 格式： ：= ；,每个标识

4、符必须以字母开头，而且不分大小写。如果定义的标识符不能为空，则必须加上关键字NOT NULL,并赋初值。 X VARCHAR2(20) NOT NULL :=HELLO PL/SQL;,PL/SQL的组成,PL/SQL块的定义部分： 与C语言类似，PL/SQL中使用的变量、常量、游标和异常处理的名字都必须先定义后使用。并且必须定义在以DECLARE关键字开头的定义部分。 PL/SQL块的可执行部分： 该部分是PL/SQL块的主体，包含该块的可执行语句。该部分定义了块的功能，是必须的。 由关键字BEGIN开始，EXCEPTION或END结束。 PL/SQL块的异常处理部分： 该部分包含该块的异常

5、处理程序（错误处理程序）。当该块程序体中的某个语句出现异常（检测到一个错误）时，他将程序控制转到异常部分的相应的异常处理程序中进行进一步的处理。该部分由关键字EXCEPTION开始，END关键字结束。,数据类型,内置数据类型 标量 复合 引用 LOB,标量类型,标量 容纳单个值 没有内部组成 分为四个类别 NUMBER CHARACTER DATE BOOLEAN,Number类型,用于存储和操纵数字数据 Number类型是： BINARY_INTEGER NUMBER 为了和其他数据库兼容，Oracle支持以下子类型 在内部这些子类型都转换为NUMBER类型 子类型是DEC、DECIMAL、

6、DOUBLE PRECISION、FLOAT、INTEGER、INT、NUMERIC、REAL、SMALLINT PLS_INTEGER,Character类型,Character类型 CHAR VARCHAR VARCHAR2 RAW LONG、LONG RAW ROWID、UROWID 区域字符类型 NCHAR NVARCHAR2,BOOLEAN,不能把数据库中的字段定义为BOOLEAN 只能用于PLSQL中进行逻辑判断,其他数据类型,组合类型 RECORD VARRAY NESTED TABLE 引用类型 REF CURSOR REF操作符 LOB类型 BLOB CLOB NCLOB B

7、FILE,常见数据类型,条件控制之IF语句,IF 条件 THEN ELSE END IF;,执行流程,IF 条件1为真 THEN ELSIF 条件2为真 THEN ELSE END IF;,plsql_02.txt,条件控制之IF语句,CASE 条件 WHEN 条件1为真 THEN WHEN 条件2为真 THEN ELSE END CASE;,plsql_03.txt,循环控制之LOOP,直到型循环 语法格式： LOOP EXIT WHEN ； END LOOP; 执行过程： 先执行循环体，然后判断，如果条件为真，则结束循环，否则继续循环。 说明： 直到型循环的循环体至少执行1次。,求1到10

8、0之和,VARIABLE SUM NUMBER ; DECLARE I NUMBER NOT NULL :=0 ; BEGIN :SUM:=0 ; LOOP :SUM:=:SUM+I ; EXIT WHEN I=100 ; I:=I+1; END LOOP ; DBMS_OUTPUT.PUT_LINE(SUM IS |:SUM) ; END;,全局变量的引用时，必须加:,plsql_04.txt,循环控制之WHILE,当型循环 语法格式： WHILE LOOP END LOOP; 执行过程： 先判断，如果条件为真，则执行循环体，继续循环，否则结束循环。 说明： 当型循环的循环体可能一次也不执行

9、。,求1到100之和,DECLARE I NUMBER :=100 ; SUMM NUMBER := 0 ; BEGIN WHILE I0 LOOP SUMM:=SUMM+I; I:=I-1; END LOOP; DBMS_OUTPUT.PUT_LINE(SUMM); END;,plsql_05.txt,循环控制之FOR,FOR循环 语法格式： FOR IN REVERSE LOOP END LOOP; 说明： 循环变量是控制循环的变量，它不需要显式的在变量定义部分进行定义。系统隐含地将它看成一个整型变量。 系统默认时，计数器从下界往上界递增计数，如果使用REVERSE关键字，则表示计数器从下

10、界到上界递减计数。 循环变量只能在循环体中使用，不能在循环体外使用。,求1到100之和,DECLARE SUMM NUMBER := 0 ; BEGIN FOR I IN REVERSE 1.100 LOOP SUMM:=SUMM+I; DBMS_OUTPUT.PUT_lINE(I is |I); END LOOP; DBMS_OUTPUT.PUT_lINE(SUMM); END;,plsql_06.txt,求出100到150之间所有的素数,家庭作业,求斐波拉契数列的前n项之和；,标记和goto语句结合,DECLARE SUMM NUMBER := 0 ; I NUMBER :=0 ; BEG

11、IN SUMM:=SUMM+I; I:=I+3; IF I100 THEN GOTO REPEAT1; END IF ; DBMS_OUTPUT.PUT_lINE(SUMM); END;,PL/SQL中的异常处理,PL/SQL中的异常处理 预定义异常 对于Oracle预定义的异常，当预定义的情况发生时，系统将自动触发。 用户自定义的异常 需要程序员自己定义代码，对异常情况进行处理。 异常处理的一般格式： DECLARE ; BEGIN ; EXCEPTION WHEN 异常情况1 OR 异常情况2 THEN ; WHEN异常情况3 OR 异常情况4 THEN ； WHEN OTHERS THE

12、N ； END;,异常举例,DECLARE TMP_NAME VARCHAR(10); BEGIN SELECT ENAME INTO TMP_NAME FROM EMP; DBMS_OUTPUT.PUT_LINE(TMP_NAME) ; END;,ERROR 位于第 1 行: ORA-01422: 实际返回的行数超出请求的行数 ORA-06512: 在line 4,SQL语句的使用 在可执行部分，可以使用SQL语句，但是不是所有的SQL语句都可以使用。可以使用的主要有：SELECT,INSERT,UPDATE,DELETE,COMMIT,ROLLBACK等数据查询、数据操纵或事务控制命令，不

13、能使用CREATE,ALTER,DROP,GRANT,REVOKE等数据定义和数据控制命令。 说明 在PL/SQL中，SELECT语句必须与INTO子句相配合，在INTO子句后面跟需要赋值的变量. 在使用SELECT INTO时，结果只能有一条，如果返回了多条数据或没有数据，则将产生错误。（对于多条记录的遍历，可以使用游标）,plsql_exception_01.txt,预定义的异常处理,预定义异常的处理,DECLARE TMP_NAME VARCHAR(10); BEGIN SELECT ENAME INTO TMP_NAME FROM EMP; DBMS_OUTPUT.PUT_LINE(T

14、MP_NAME) ; EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(返回了太多行) ; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(出现了其他异常) ; END;,plsql_exception_02.txt,用户自定义的异常处理,说明 用户自定义异常必须在定义部分进行声明。 当异常发生时，系统不能自动触发，需要用户使用RAISE语句。 示例 DECLARE OUT_OF_STOCK EXCEPTION; NUMBER_ON_HAND NUMBER; BEGIN IF NUMBER_ON_HAND

15、1 THEN RAISE OUT_OF_STOCK; END IF; EXCEPTION WHEN OUT_OF_STOCK THEN -HANDLE THE ERROR END;,用户自定义的异常处理,DECLARE TMP_NAME VARCHAR(10); MY_EXCEPTION EXCEPTION ; BEGIN SELECT ENAME INTO TMP_NAME FROM EMP WHERE EMPNO=7369; DBMS_OUTPUT.PUT_LINE(TMP_NAME) ; IF TMP_NAME LYF THEN RAISE MY_EXCEPTION ; END IF ;

16、 EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(返回了太多行) ; WHEN MY_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE(该用户不是lyf) ; END;,plsql_exception_03.txt,变量的声明,变量的声明 声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致。（不知道该变量或列的数据类型）可以使用 %TYPE；,DECLARE MY_NAME VARCHAR(10); TMP_NAME MY_NAME%TYPE; -TMP_NAME EMP.ENAME%TYP

17、E; BEGIN SELECT ENAME INTO TMP_NAME FROM EMP WHERE EMPNO=7369; DBMS_OUTPUT.PUT_LINE(TMP_NAME) ; END;,plsql_var_01.txt,记录类型,记录是由几个相关值构成的复合变量，常用于select的返回值。使用记录可以把一行数据看成是一个单元来处理。 记录类型定义的一般格式： TYPE IS RECORD ( NOT NULLDEFAULT|:= , )； 说明 标识符 是定义的记录类型名; 定义记录型变量， my_record_var recordtypename ; 记录类型变量的属性引用

18、方法是.引用。,字段1（数据类型）,字段2（数据类型）,字段3（数据类型）,记录类型,DECLARE TYPE MY_RECORD IS RECORD( NO NUMBER(4), NAME VARCHAR(10) ); MY_VAR MY_RECORD; BEGIN SELECT EMPNO,ENAME INTO MY_VAR FROM EMP WHERE EMPNO=7369; DBMS_OUTPUT.PUT_LINE(编号：|MY_VAR.NO| 姓名：|MY_VAR.NAME) ; END;,plsql_var_02.txt,记录类型,DECLARE MY_VAR EMP%ROWTYP

19、E; BEGIN SELECT * INTO MY_VAR FROM EMP WHERE EMPNO=7369; DBMS_OUTPUT.PUT_LINE(编号：|MY_VAR.EMPNO| 姓名：|MY_VAR.ENAME) ; END;,plsql_var_03.txt,记录注意事项,RECORD存储单行多列结构的数据，必须首先定义该RECORD类型，然后再定义一个属于该类型的变量。 一个PL/SQL RECORD数据类型与一个数据库表中的行不一样； 一个PL/SQL RECORD在结构上与第三代语言中的记录非常相似。 一个PL/SQL RECORD必须包含一个或多个字段：这些字段的数据类

20、型可以是标量类型、RECORD类型或PL/SQL表类型 一个PL/SQL RECORD允许用户将这些字段的集合看成一个逻辑单元； 一般在PL/SQL块中从表中取出一行进行处理时用PL/SQL RECORD。,主要内容,游标的设计与开发 存储过程 包 触发器,游标的定义,游标的定义 游标（cursor）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。 说明 使用游标时，select语句查询的结果可以是单条记录，多条记录，也可以是零条记录。 游标工作区中，存在着一个指针（POINTER）,在初始状态它指向查询结果的首记录。 要访问查询结果的所有记录，可以通过F

21、ETCH语句，进行指针的移动来实现。 使用游标进行操作，包括定义游标、打开游标、提取数据以及关闭游标几步。,打开游标.,从游标中获取一行.,继续获取直到为空.,关闭游标.,控制显式游标,游标的使用,定义游标，PL/SQL块中，游标的定义应该放在定义部分 语法格式：CURSOR IS ; 打开游标 语法格式：OPEN ； 说明：打开游标，实际上是执行游标定义时对应的SELECT语句，将查询结果检索到工作区中。 提取数据 语法格式：FETCH INTO 变量1,变量2, 说明 在使用FETCH语句之前必须先打开游标，这样才能保证工作区中有数据。 对游标第一次使用FETCH语句时，游标指针指向第一条

22、记录，因此操作的对象是第一条记录，使用后，游标指针指向下一条记录。 游标指针只能向下移动，不能回退。如果想查完第二条记录后又回到第一条记录，则必须关闭游标，然后重新打开游标。 INTO子句中的变量个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应。 关闭游标 语法格式：CLOSE ； 说明：关闭游标的作用在于，使游标所对应的内存工作区变为无效，并释放与游标相关的系统资源。,游标的属性,游标的属性 %ISOPEN 该属性是布尔型。如果游标已经打开，返回TRUE,否则为FALSE。 %FOUND 布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FAL

23、SE; %NOTFOUND 布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE; %ROWCOUNT 数值型，描述的是到目前为止实际从游标工作区抽取的记录数。 游标属性只能在PL/SQL块中使用，不能在SQL命令中使用。,游标的使用,DECLARE CURSOR CUR IS SELECT * FROM EMP; MY_EMP EMP%ROWTYPE; BEGIN OPEN CUR; FETCH CUR INTO MY_EMP; WHILE CUR%FOUND LOOP DBMS_OUTPUT.PUT_LINE(编号：|MY_EMP.EMPNO| 姓名：|MY_

24、EMP.ENAME) ; FETCH CUR INTO MY_EMP; END LOOP; CLOSE CUR; END;,plsql_cur_01.txt,游标练习,修改表emp中各个雇员的工资，若雇员属于10号部门，则增加$100，若雇员属于20号部门，则增加$200；若雇员属于30号部门，则增加$300。,plsql_cur_02.txt,FOR循环中游标的使用,语法格式 FOR IN LOOP END LOOP; 说明 系统自动打开游标，不用显式地使用OPEN语句打开； 系统隐含地定义了一个数据类型为%ROWTYPE的变量，并以此作为循环的计算器。 系统重复地自动从游标工作区中提取数据

25、并放入计数器变量中。 当游标工作区中所有的记录都被提取完毕或循环中断时，系统自动地关闭游标。,FOR循环中游标的使用,使用FOR循环修改表emp中各个雇员的工资，若雇员属于10号部门，则增加$100，若雇员属于20号部门，则增加$200；若雇员属于30号部门，则增加$300。,plsql_cur_03.txt,直接使用变量的方式传递参数,DECLARE SAL_TMP EMP.SAL%TYPE; CURSOR CUR IS SELECT * FROM EMP WHERE SALSAL_TMP; SELECT * FROM EMP WHERE SAL,plsql_cur_04.txt,使用形参方

26、式传递参数给游标,游标定义语法格式： CURSOR 游标名（ ，） IS ； 说明 打开带参数的游标时，参数个数和数据类型必须与其定义时保持一致。,使用形参方式传递参数给游标,DECLARE CURSOR CUR(V_DEPTNO EMP.DEPTNO%TYPE) IS SELECT * FROM EMP WHERE DEPTNO=V_DEPTNO; MY_EMP EMP%ROWTYPE; BEGIN OPEN CUR(10); FETCH CUR INTO MY_EMP; WHILE CUR%FOUND LOOP DBMS_OUTPUT.PUT_LINE(MY_EMP.ENAME|,|MY_

27、EMP.SAL); FETCH CUR INTO MY_EMP; END LOOP; CLOSE CUR; END;,plsql_cur_05.txt,练习,使用带形参的游标把部门为20的员工每人的工资加上100元；,plsql_cur_06.txt,FOR UPDATE 子句,语法： SELECT. FROM. FOR UPDATE OF column_referenceNOWAIT; 说明 在事务执行期间可以显式锁定以拒绝访问。 在更新或删除行时要锁定该行。 使用游标更新或删除当前行。 首先要在游标中使用 FOR UPDATE 子句锁定行。 使用 WHERE CURRENT OF 游标从显

28、式游标中引用当前行,FOR UPDATE 子句,DECLARE CURSOR CUR IS SELECT * FROM EMP FOR UPDATE; MY_EMP EMP%ROWTYPE; INC NUMBER :=0 ; BEGIN OPEN CUR; FETCH CUR INTO MY_EMP; WHILE CUR%FOUND LOOP IF MY_EMP.DEPTNO = 10 THEN INC :=10; ELSIF MY_EMP.DEPTNO = 20 THEN INC :=20; ELSIF MY_EMP.DEPTNO = 30 THEN INC :=30; ELSE INC :

29、=0; END IF; UPDATE EMP SET SAL=SAL+INC WHERE CURRENT OF CUR; FETCH CUR INTO MY_EMP; INC :=0; END LOOP; CLOSE CUR; END;,plsql_cur_07.txt,隐式游标,Oracle在内部自动声明、创建 用于处理DML语句时使用 返回单行的查询,隐式游标,BEGIN INSERT INTO EMP(EMPNO,ENAME,SAL) VALUES (8892,TT,99); DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT:|SQL%ROWCOUNT); IF SQL

30、%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE(SQL%NOTFOUND 为TRUE); ELSE DBMS_OUTPUT.PUT_LINE(SQL%NOTFOUND 为FALSE); END IF; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(SQL%FOUND 为TRUE); ELSE DBMS_OUTPUT.PUT_LINE(SQL%FOUND 为FALSE); END IF; END;,plsql_cur_08.txt,隐式游标,EMP和DEPT表具有参照关系，所以在删除EMP表中某部门员工时，如果发现该部门中已没有员工，则可以

31、在dept表中删除该部门。,DECLARE V_DEPTNO EMP.DEPTNO%TYPE; BEGIN V_DEPTNO := 30 ; DELETE FROM EMP WHERE DEPTNO = V_DEPTNO ; IF SQL%NOTFOUND THEN DELETE FROM DEPT WHERE DEPTNO=V_DEPTNO ; END IF ; END;,EMP和DEPT表具有参照关系，所以在删除EMP表中某位员工时进行检查，如果发现该员工所在的部门中已没有员工，则可以在dept表中删除该部门。,DECLARE V_EMPNO EMP.EMPNO%TYPE ; CURSOR

32、 CUR(V_NO EMP.DEPTNO%TYPE) IS SELECT * FROM EMP WHERE DEPTNO=V_NO; V_EMP EMP%ROWTYPE ; BEGIN -按给定编号获得员工的部门编号 V_EMPNO :=,显示游标与隐式游标的比较,动态游标,REF游标 在运行时才与sql语句关联，从而可以将一个游标和多个不同的sql语句关联；经常用在向客户端程序返回变量的存储过程中用到； 可以为REF游标设置游标变量 定义游标变量类型的语法: Type is ref cursor ; 游标变量 一种引用类型 可以在运行时指向不同的存储位置 Close语句关闭游标并释放用于查询

33、的资源 定义游标变量的语法： ;,SET SERVEROUT ON SIZE 10000 DECLARE TYPE REFCURTYPE IS REF CURSOR;- TYPE REFCURTYPE IS REF CURSOR RETURN EMP%ROWTYPE;(强型) REFCUR REFCURTYPE; FLAG INT:=0; EMPROW EMP%ROWTYPE; DEPTROW DEPT%ROWTYPE; BEGIN FLAG:=,plsql_cur_09.txt,游标变量的类型,具有约束的游标变量 具有返回类型的游标变量 也称为“强游标” 无约束的游标变量：无约束游标变量没有

34、RETURN子句，当后来打开一个无约束游标变量时，它们可以为任何查询而打开。 没有返回类型的游标变量 也称为“弱游标” 注意：具有约束的游标变量，它们被声明为特定的返回类型。当随后打开该变量时，则必须为查询而打开，其中此查询的选择列表匹配游标的返回类型。否则，将出现预定义的ROWTYPE_MISMATCH异常。,过程和函数,PL/SQL块主要有两种类型，即命名块和匿名块。 匿名块每次提交时都被编译，而且，匿名块不在数据库中存储并且不能直接被其他的PL/SQL块调用。 过程和函数在命名的PL/SQL块，被存储在数据库中，并且可以被其他PL/SQL块使用。命名块包括存储过程、包和触发器等。,创建过

35、程,过程用于执行特定的操作。可以把经常需要执行的特定的操作写成过程。创建过程的语法格式为：,CREATE OR REPLACE PROCEDURE procedure_name (arg 1 IN| OUT | IN OUT arg_type1, arg n IN| OUT | IN OUT arg_type1 ) IS | AS 声明部分 BEGIN 执行部分 EXCEPTION 异常处理部分 END procedure_name;,查看错误1:show errors procedure plsql_pro_01 查看错误2:通过数据字典：Select * from user_errors;

36、,参数模式：IN：输入 。OUT：输出 IN OUT即可输入又可输出，接收输入值并返回,是一个可选的关键字。如果过程已经存在，该关键字将重新创建过程，这样就不必删除和重新创建过程。,创建过程,CREATE OR REPLACE PROCEDURE PLSQL_PRO_01(EID IN NUMBER) IS NAME VARCHAR(10); BEGIN SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=EID; DBMS_OUTPUT.PUT_LINE(NAME); END PLSQL_PRO_01;,plsql_pro_01.txt,执行1：在其他过程

37、中调用 BEGIN PLSQL_PRO_01(7369); END;,plsql_pro_exe_01.txt,执行2：使用EXECUTE EXECUTE PLSQL_PRO_01(7369);,存储过程,查看存储过程 数据字典：select * from user_source; SELECT TEXT FROM USER_SOURCE WHERE NAME=PRO_1 ; 删除存储过程： Drop procedure 过程名； 把存储过程的执行权限赋给其他用户 SQL GRANT EXECUTE ON PRO_1 TO TEST;,存储过程,CREATE OR REPLACE PROCED

38、URE PLSQL_PRO_02(EID IN NUMBER,NAME OUT VARCHAR2) AS BEGIN SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=EID; END PLSQL_PRO_02;,plsql_pro_02.txt,DECLARE TMP VARCHAR2(10); BEGIN PLSQL_PRO_02(7369,TMP); DBMS_OUTPUT.PUT_LINE(TMP); END;,plsql_pro_exe_02.txt,存储函数,CREATE OR REPLACE FUNCTION function_name (p

39、arameter1 IN|OUT|IN OUT datatype :=|DEFAULT expression , parameter2 IN|OUT|IN OUT datatype :=|DEFAULT expression ) RETURN returntype IS|AS declarations BEGIN code EXCEPTION exception_handlers END,存储函数,CREATE OR REPLACE FUNCTION PLSQL_FUNC_01(EID IN NUMBER) RETURN VARCHAR2 AS NAME VARCHAR(10); BEGIN

40、SELECT ENAME INTO NAME FROM EMP WHERE EMPNO=EID; RETURN NAME; END PLSQL_FUNC_01;,plsql_func_01.txt,执行1：在其他PLSQL代码中调用 执行2：在sql语句中使用： SELECT PLSQL_FUNC_01(7369) FROM DUAL;,过程与函数的区别,区别之一：参数形式及返回值不同 函数有零个或多个参数，并且只有一个返回值，函数值的返回是靠RETURN子句返回的。 过程有零个或多个参数，并且不返回值，其返回值是靠OUT参数带出来的。过程可以由零个或多个OUT参数返回结果。 区别之二：调用形

41、式不同。 过程可以作为单独可执行语句一样被调用， 如：过程名（实际参数1，实际参数2，），语句可以在PL/SQL块中单独出现。 函数可以在任何表达式能够出现的地方被调用。 如：变量名：=函数名（实际参数1，实际参数2，）,函数和过程中的异常处理,CREATE OR REPLACE PROCEDURE FIRE_EMP (NO EMP.EMPNO%TYPE) AS INVALID_EMP EXCEPTION ; BEGIN DELETE FROM EMP WHERE EMPNO=NO ; IF SQL%NOTFOUND THEN RAISE INVALID_EMP ; END IF ; COMM

42、IT ; EXCEPTION WHEN INVALID_EMP THEN ROLLBACK ; INSERT INTO TEST_LOG_FILE VALUES (ERR_SEQ.NEXTVAL,SYSDATE,EMP,DELETE,NOT FOUND EMPNO |NO| IN EMP!) ; END FIRE_EMP;,PLSQL_PRO_EXCEPTION.SQL,过程和函数的优点,（1）提高数据的安全性和完整性 利用安全性的权限来控制那些没有足够权限的用户对数据库的间接访问； 通过把相关联的表的操作集中到一起，来保证针对这些相关表执行一致的操作或任何操作都不做； （2）改善操作性能 多

43、个用户使用同一个SQL语句时，只做依次语法分析。 只在编译时进行语法分析，运行时不再重做，直接调用编译编码。 （3）节省存储空间 多个不同应用，有同一个存储代码 维护性高 （4）模块化,自主事务处理,自主事务处理 主事务启动自主事务处理； 然后主事务被暂停； 自主事务处理sql操作； 然后中止自主事务处理； 恢复主事务处理； pragma AUTONOMOUS_TRANSACTION用于标记子程序；,事务互相影响例,CREATE OR REPLACE PROCEDURE PLSQL_TRANSPRO_01 IS -pragma AUTONOMOUS_TRANSACTION; BEGIN INS

44、ERT INTO EMP(EMPNO,ENAME,SAL) VALUES (8888,TEST,1111); ROLLBACK; END PLSQL_TRANSPRO_01;,PLSQL_TRANSPRO_01.txt,CREATE OR REPLACE PROCEDURE PLSQL_TRANSPRO_02 IS BEGIN UPDATE EMP SET COMM=9999; PLSQL_TRANSPRO_01; END ;,PLSQL_TRANSPRO_02.txt,程序包,程序包(package) 用于将逻辑相关的PL/SQL块或元素(变量、常量、自定义数据类型、过程、函数、游标等)组织

45、在一起，作为一个完整的单元被存储在数据库中，以名称来标识。 它具有面向对象的程序设计语言的特点，是对这些PL/SQL块或元素的封装。程序包类似于Java语言中的类，其中的变量相当于类中的成员变量，过程和函数相当于类中的方法。,包的优点,规范化应用程序的开发 方便对存储过程和函数的组织和管理 将相关的过程和函数组织在一起 在一个用户的环境中解决命名冲突 在不改变包的说明定义时可以改变包体的定义。 限制过程的依赖性。 方便对存储过程和函数的安全性管理 整个包的访问权限只需要一次性授权 区分公共过程和私有过程。公共过程在包外可以被调用，私有过程在包外不能被调用。 为用户会话提供状态确认信息 在各种环

46、境和过程中均引用标识符（即包内的公共变量） 在用户整个会话中保留标识符的状态（即在整个会话中公共变量的值一直保留，在一个新的会话中公共变量的值又被初始化） 改善性能 包在首次被调用时。作为一个整体全部调入内存，不必一个过程一个过程调入内存， 减少多次调入时的磁盘I/O次数。,包的组成 有两个部分,包的说明 （也叫做包头）,包 体,包头包含了有关包内容的信息。 该部分中不包括包的代码部分。,包体是一个独立于包头的数据字典对象。包体只能在包头完成编译后才能进行编译。包体中带有实现包头中描述的前向子程序的代码段。,在包的说明部分说明的元素（过程、函数等）是公共元素，只是在包体中说明的元素是私有的。公

47、共元素可以在包的外面单独调用，但私有元素只能在包体内定义别的过程函数时被调用，不能在包外单独调用。局部变量是包体内定义过程函数时定义的变量，该局部变量只能在该过程函数内使用，不能在包体内别的过程函数内使用。,包的说明,创建包头的语法： CREATE OR REPLACE PACKAGE package_name IS | AS 公有数据类型定义 公有变量声明 公有常量声明 公有异常错误声明 公有游标声明 公有函数声明 公有过程声明END package_name;,包头示例,数据包说明示例 CREATE PACKAGE airlines AS TYPE flight_day_type is R

48、ECORD (flightno flight_sch.flightno%TYPE, flight_day1 NUMBER(1); CURSOR flight_cur RETURN flight_day_type; disp_day CHAR(15); FUNCTION day_fn(mday NUMBER) RETURN CHAR; PROCEDURE branch_sum (p_brnch branch.branch_code%TYPE); END airlines;,包体,创建包体的语法格式： CREATE OR REPLACE PACKAGE BODY package_name IS |

49、 AS 私有数据类型定义 私有变量声明 私有常量声明 私有异常错误声明 私有函数声明和定义 私有过程声明和定义 公有游标声明 公有函数声明 公有过程声明 BEGIN 执行部分（初始化部分） END package_name;,数据包主体,实现数据包说明 完全定义游标和子程序 将实现细节和私有声明从应用程序隐含 可以将其设想为“黑箱” 可被替换增强或在不更改接口的情况下被替换 可以在不重新编译调用程序的情况下对其进行更改,声明范围对于数据包主体是局部的 除了在数据包主体内将不能访问到声明的类型和对象 只在首次引用数据包时，运行一次数据包的初始化部分 使用 CREATE PACKAGE BODY

50、命令生成 CREATE OR REPLACE PACKAGE BODY AS - 私有类型和对象声明 - 子程序主体 BEGIN - 初始化语句 END ;,CREATE PACKAGE BODY airlines AS CURSOR flight_cur RETURN flight_day_type IS SELECT flightno, reoute_code, flight_day1, flight_day2 FROM flight_sch; FUNCTION day_fn(mday NUMBER) RETURN CHAR IS BEGIN 语句 ; END day_fn; . . EN

51、D airlines;,包中可以包含的元素的性质,包的实例,Package子目录 建表：STUDENT.TXT,SUBJECT.TXT 包头：STUDENTPACKAGE.txt 包体：STUDENTPACKAGEBODY.txt 包的调用：调用包.txt 查看用户包的源文件：user_source;,删除包,当不在需要某个程序包时，可以将其删除。如果只删除包体，可以使用命令 DROP PACKAGE BODY package_name; 如果要同时删除包说明，可以使用 DROP PACKAGE package_name;,触发器,数据库触发器是一个与特定表相连的存储过程。当应用程序用一条满足

52、触发器条件的SQL DML语句指向该表时，Oracle将自动执行该触发器以执行任务。 触发器是在事件发生时隐式地运行的，不能接收参数，不能被调用。 运行触发器的方式叫做激发（firing）触发器，触发事件可以是对数据库表的DML（INSERT、UPDATE或DELETE）操作或某种视图的操作( View )。,触发事件 （如INSERT、UPDATE、DELETE等）,触发器脚本,触发时机 BEFORE （事件）,触发对象表,触发器事件（如INSERT、UPDATE、DELETE等）,触发器脚本,触发时机 AFTER （事件）,触发对象表,创建触发器,CREATE OR REPLACE TRI

53、GGER trigger BEFORE|AFTER DELETE|INSERT|UPDATE OF column ,column ON table FOR EACH ROW WHEN condition BEGIN pl/sql block. END trigger,触发器例,CREATE OR REPLACE TRIGGER TG_INSERT BEFORE INSERT ON STUDENT BEGIN DBMS_OUTPUT.PUT_LINE(插入前触发了触发器); END TG_INSERT;,PLSQL_TRI_01.txt,触发器的组成,触发器的组成 触发事件 可选的触发器约束条件

54、 触发器动作 可以创建对应于以下语句触发的触发器 DML语句（insert、update、delete） DDL语句（create、alter、drop） 数据库操作（logon、logoff、startup、servererror、shutdowndeng） 触发器类型 DML触发器 系统触发器 替代触发器（instead of）,数据库级别 启动 关闭 连接 注销 系统出错,模式（方案）级别 对象的创建 对象的修改 对象的删除,表或行级别 INSERT DELETE UPDATE,DML on view INSTEAD OF other tables,可以触发触发器的事件包括,表级别DML

55、的事件-操作对象：表 INSERT UPDATE DELETE 模式级别的DDL 对象事件-操作对象：模式 CREATE ALTER DROP 数据库级别的事件-操作对象：数据库 启动 STARTUP 关闭 SHUTDOWN 连接 LOGON 注销 LOGOUT 系统出错 SERVERERROR,各种触发事件,类型一：DML触发器,DML触发器的组成与类型 在编写触发器源代码之前，必须先确定其触发时间、触发事件及触发器的类型。,DML触发器,行级触发器,语句级触发器,DML触发器 触发事件,表更新,表插入,表删除,DML触发器 触发时间（时机）,BEFORE,AFTER,触发器例(DML触发器

56、),CREATE OR REPLACE TRIGGER TG_UPDATE AFTER UPDATE ON STUDENT -FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(更新时触发了触发器1); END TG_UPDATE;,PLSQL_TRI_02.txt,表级：又称语句级，每条语句执行之前或之后执行触发器一次； 行级（for each row）：表中每行被影响一条就触发一次；,两个特殊的行级变量,:NEW（insert的值）； :OLD（表中原有的值，被删除、更新的值）；,CREATE OR REPLACE TRIGGER TG_UPDATE_NEWOL

57、D BEFORE UPDATE ON STUDENT FOR EACH ROW BEGIN DBMS_OUTPUT.PUT_LINE(-NEW-); DBMS_OUTPUT.PUT_LINE(NEW STUID:|:NEW.STUID); DBMS_OUTPUT.PUT_LINE(NEW STUNAME:|:NEW.STUNAME); DBMS_OUTPUT.PUT_LINE(NEW SEX:|:NEW.SEX); DBMS_OUTPUT.PUT_LINE(-OLD-); DBMS_OUTPUT.PUT_LINE(OLD STUID:|:OLD.STUID); DBMS_OUTPUT.PUT_

58、LINE(OLD STUNAME:|:OLD.STUNAME); DBMS_OUTPUT.PUT_LINE(OLD SEX:|:OLD.SEX); END TG_UPDATE;,PLSQL_TRI_03.txt,在BEFORE类型行级触发器和AFTER类型行级触发器中使用这些标识符。 在语句级触发器中不要使用这些触发器。 在PL/SQL语句或SQL语句中，这些标识符前加上冒号（:）来引用它们。 在行级触发器的WHEN条件中使用该标识符时，前面不要加冒号（:）. 在BEFORE触发器中修改 “:new”，不能修改“:old”。在AFTER触发器中不能修改“:new”。因为，触发时机AFTER后，

59、 :new的值已经被插入到表里， 因此:new不能修改,使用“:old”和“:new”应注意的,用触发器增强参照完整性约束。当DEPT表的DEPTNO发生变化时，EMP表的相关行也跟着进行适当的修改。,CREATE OR REPLACE TRIGGER cascade_update AFTER UPDATE OR DELETE ON dept FOR EACH ROW BEGIN UPDATE emp SET emp.deptno=:new.deptno WHERE emp.deptno=:old.deptno; END;,Goods目录,当一个触发器中的触发事件中既有删除、更新又有插入时，如何判断触发器是因哪个事件而动作？,用触发器谓词（INSERTING、UPDATING、DELETING）,DML触发器是INSERT、UPDATE、DELETE触发器。在这种触发器的内部，有三个布尔函数可以用来决定要进行哪个操作。这些谓