Oracle 数据库应用：第12章 PL SQL编程

1、第12章 PL/SQL 编程,学习目标 PL/SQL 编程基本概念 PL/SQL 访问数据库 子程序设计 存储过程与存储程序 异常处理 游标的应用 触发器,PL/SQL 编程基本概念,如果说 SQL 是一种标准的数据库访问语言，那么 PL/SQL 则是 Oracle 公司开发的一种编程语言，SQL 是其子集。 PL/SQL 中可以： 定义变量、数据类型、函数及过程 使用流程控制语句 包含SQL语句 进行错误处理,PL/SQL 编程基本概念,PL/SQL 的优点： 体现了模块化的程序设计思想，可以定义函数和过程。 移植性好，可以在不同平台、机器之间方便地移植。 效率高，可以大大提高访问数据的效率

2、。,PL/SQL 编程基本概念,没有 PL/SQL：用户（应用）输入一个 SQL 命令，服务器执行之，一次一命令。 使用 PL/SQL：用户（应用）输入一个调用 PL/SQL 程序的命令，服务器执行其对应的多个 SQL 命令，一次多命令。,PL/SQL 块,PL/SQL 是一种结构化编程语言，程序的基本单元是块，主要的块形式有：函数、过程和匿名块。 一个完整的 PL/SQL 块由 3 部分组成： DECLARE 变量声明部分 BEGIN 可执行部分 EXCEPTION 异常处理部分 END,PL/SQL 块,声明部分：用来定义变量、数据类型、游标、子程序、触发器、异常处理名称等。被声明的变量在

3、本块范围内有效。声明部分是可选的。 可执行部分：PL/SQL 块的主体，包含了 PL/SQL 块的可执行代码，是必不可少的部分。该部分通过变量赋值、流程控制、数据查询、数据操纵、数据定义、事务控制、游标处理等实现块的功能。 异常处理部分：用来处理块在执行过程中可能发生的错误。如果块执行正常，则块正常结束，否则从出现错误的语句转到异常处理部分开始执行。异常处理部分也是可选的。,示例程序块,PL/SQL 块,最简单的 PL/SQL 块仅包含由 BEGIN 和 END 限定的可执行部分： BEGIN DBMS_OUTPUT.PUT_LINE(Hello); END;,变量的定义与使用,变量的定义有两

4、种格式： 变量名 类型 约束 DEFAULT 默认值 变量名 类型 约束 :=初始值 约束用来规定变量必须满足的条件，比如“NOT NULL”约束变量不能为空值，这样在定义变量时就要为其指定默认值或初始值。,变量的定义与使用,变量名要遵循一定的命名规则： 必须以字母开头。 包含数字、字母、下划线以及$、#号。 长度不超过 30 字符。 不能与 Oracle 关键字相同。 变量名大小写无关。,变量数据类型,变量的类型可以是 PL/SQL 提供的数据类型或者用户自定义的类型。,变量赋值以及输出,变量赋值：变量名:=表达式 如果需要输出变量的值，可调用 DBMS_OUTPUT 程序包中的过程 PUT

5、_LINE ，其参数为要输出的变量或表达式。 除变量的声明之外，变量的赋值、输出等操作都要放在块的可执行部分。,变量声明、赋值、输出例子,DECLARE id INTEGER NOT NULL DEFAULT 100; name VARCHAR2(20):=SMITH; birthday DATE DEFAULT SYSDATE; BEGIN id:=200; dbms_output.put_line(id值为:|id); dbms_output.put_line(name值为:|name); dbms_output.put_line(birthday值为:|birthday); END; 执

6、行结果如下： id值为：200 name值为：SMITH birthday值为：21-10月-11,定义不确定内容,前面的许多示例都引用了 null 关键字。null 关键字表示缺少、不可知或不适用等含义。从本质上来讲，null 关键字表示没有内容。 在 PL/SQL 程序块中，如果引用某个已经声明的变量，且没有为该变量赋值，那么该变量的值就是 null 。也就是说，null通常会赋予在声明时没有赋值的变量。 如下图所示的示例声明了 4 个变量。但是，这 4 个变量都会产生相同的结果：具有 null 值的 varchar2 变量。,变量输出,在 SQL*Plus 中使用 PUT_LINE 输出

7、变量时，要确保 SQL*Plus 的输出是打开的，否则将得不到输出结果。 设置参数： SET SERVEROUTPUT ON,PL/SQL 代码的输入和执行,PL/SQL 块的代码可以直接在“SQL”提示符下输入，输入结束后，执行命令“/”即可使该 PL/SQL 块开始执行。 如果发现代码执行时有错误，可以输入“ed”命令进行修改。通过“ed”将打开一个文本编辑器，对缓冲区中的内容进行编辑。编辑完后重新用“/”执行。,%TYPE,在定义变量时，除了直接为变量指定类型外，还可以通过 %TYPE 属性为变量指定类型。 %TYPE 用于获得另一个变量或表中某个列的类型，使得新定义的变量与现有变量或列

8、的类型一致。 用法为： 变量名 另一变量%TYPE 变量名 表.列%TYPE,%TYPE,使用 %TYPE 的一个好处是当原来的变量或列的类型被修改后，不需要修改新变量的类型。 另一个好处是，我们只希望一个变量与另一个变量的类型相同，但是我们可以不关心它具体是什么类型。 例如： id INTEGER DEFAULT 100; no id%TYPE; name emp.ename%TYPE;,自定义类型,常用的自定义类型包括记录类型和集合类型，它们都属于复合数据类型。 记录类型允许在一个类型中包含若干类型不同的字段，字段类型可以是基本数据类型，也可以是另一个复合数据类型。 记录类型的定义格式：

9、TYPE 类型名 IS RECORD (字段1定义, 字段2定义, ); 记录类型中每个字段的定义都和变量定义的方法相同。,记录类型例子,定义一个学生信息的记录类型： TYPE student IS RECORD (name char(10),age integer default 20, no char(10), score number(5,2); 在使用记录类型的变量时，可以单独引用其中的某个字段： 变量.字段,%ROWTYPE,定义记录类型变量的另一个简便方法是使用表的%ROWTYPE 属性，%ROWTYPE 属性可以取得表中各个字段的定义，使得记录类型变量的结构与表中一行的结构完全一

10、致： employee emp%ROWTYPE,%ROWTYPE,使用 %ROWTYPE 的好处是可以根据表的结构直接定义结构类型变量，而不需要事先知道这个表的结构，而且表的结构发生变化时，PL/SQL 块中的变量不需要重新定义。,%TYPE、%ROWTYPE 示例,DECLARE v_sal emp.sal%TYPE; v_emp emp%ROWTYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=7844; SELECT * INTO v_emp FROM emp WHERE empno=7900; DBMS_OUTPUT.PUT_L

11、INE(v_sal); DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.sal); END;,集合类型,集合类型变量用于包含多个相同类型的元素。 定义集合类型： TYPE 类型名 IS TABLE OF 类型; 其中元素的类型既可以是基本数据类型，也可以是自定义的数据类型。,集合类型例子,定义一个元素为整数类型的集合类型: TYPE IntSet IS TABLE OF INTEGER; intSet1 IntSet:=IntSet(10,20,30); intSet2 IntSet:=IntSet();,集合类型的方法,集合类型类似于面向对象技术中的类，除了提供

12、构造函数外，还提供了一些方法用于集合的操作。,集合类型元素引用,通过这种方法定义的集合类型变量中的元素个数是确定的，通过 EXTEND 方法可以对变量中的元素进行扩展。在使用集合变量时，一般是单独使用其中的元素。 集合变量中元素的引用： 集合变量(下标),另一种定义集合类型的方法,实际上还有一种定义集合类型的方法： TYPE 类型名 IS TABLE OF 类型 INDEX BY BINARY_INTEGER; 这种方法定义的类型形成变量后，可以不需要进行初始化即可使用，可以在执行部分为其中的任意一个元素赋值。元素的实际数量是实际被赋值元素的个数。,示例,DECLARE TYPE xyz IS

13、 TABLE OF integer INDEX BY BINARY_INTEGER; intSet3 xyz; BEGIN intSet3(1):=100; intSet3(2):=200; intSet3(10):=1000; dbms_output.put_line(intSet3.count); END; 这个 PL/SQL 块的执行结果为：3,PL/SQL 访问数据库,PL/SQL 块中可以包含 SELECT 语句、DML 语句，还可以包含 DCL 语句。 不能直接包含 DDL 语句，如果要在 PL/SQL 块中完成创建表等操作，需要通过其他方法。 通常，利用 SQL 语句对数据库进行

14、操作时，各种相关量都在代码中以常量的形式指定，而在 PL/SQL 中可以通过变量动态指定各种相关量的值，从而实现对数据库的动态操作。,对数据进行查询,在 PL/SQL 块中通过 SELECT 语句从数据库中检索数据。为了对检索到的数据进行处理，需要在SELECT 中使用变量： SELECT 列1,列2, INTO 变量1,变量2, FROM 表 ,对数据进行查询,与一般的 SELECT 语句相比，PL/SQL 块中的 SELECT 语句使用了 INTO 子句。 INTO 子句将查询到的数据存放在变量中，以便进行相应的处理。 需要注意的是，在 SELECT 语句中，需要查询的列与 INTO 子句

15、中的变量在数目、类型上要一致，否则会发生错误。,对数据进行查询,SELECT 的执行可能会有两类意外情况： 没有查询到满足条件的数据 存在多行满足条件的数据 Oracle 定义了两种异常来针对这两种情况： NO_DATA_FOUND TOO_MANY_ROWS,对数据进行查询,为了使程序更简洁，在 INTO 子句中可以使用一个记录类型的变量，其类型与查询出来的一行的结构一致。 可以避免使用多个单独的变量。,使用 DML 语句,在 PL/SQL 块中可以包含 INSERT、DELETE、UPDATE 语句，用于对数据库中的数据进行增、删、改操作。 在这些语句中可以使用数字、字符串等形式的常量，也

16、可以使用变量，还可以使用记录型变量。,DML 语句示例,DECLARE dno integer; d_name dept.dname%TYPE; d_location dept.loc%TYPE; BEGIN INSERT INTO dept VALUES(50,NETWORK,NOWHERE); dno:=60; d_name:=DEVELOP; d_location:=NOWHERE; INSERT INTO dept VALUES(dno,d_name,d_location); END;,删除数据,在删除表中数据时，在 DELETE 语句的 WHERE 子句中也可以使用变量： DECLA

17、RE dno number; BEGIN dno:=70; DELETE FROM dept WHERE deptno=dno; END；,更新数据,在修改表中的数据时，UPDATE 语句的 SET 子句和 WHERE 子句中可以使用变量，也可以使用记录型变量。如果是记录型变量，则要单独使用该变量的各个字段。如： SET dname=depart.d_name,loc=depart.d_location,控制结构,选择结构 循环结构,选择结构,IF 语句 CASE 语句,IF 语句,语法： IF condition1 THEN statements1; ELSE IF condition2 T

18、HEN statements2; ELSE else_statements; END IF; 注意： 条件是一个布尔型变量或表达式，取值只能是TRUE，FALSE，NULL。,IF 语句示例,例：输入一个员工号，修改该员工的工资，如果该员工为 10 号部门，工资增加 100 ；若为 20 号部门，工资增加 160 ；若为 30 号部门，工资增加 200 ；否则增加 300 。,示例,DECLARE v_deptno emp.deptno%type; v_increment NUMBER(4); v_empno emp.empno%type; BEGIN v_empno:=,CASE 语句,基本

19、语法 CASE WHEN condition1 THEN statements1; WHEN condition2 THEN statements2; WHEN conditionn THEN statementsn; ELSE else_statements; END CASE; 注意 在 CASE 语句中，当第一个 WHEN 条件为真时，执行其后的操作，操作完后结束 CASE 语句。其他的 WHEN 条件不再判断，其后的操作也不执行。,CASE 语句示例,例：根据输入的员工号，修改该员工工资。如果该员工工资低于 1000 ，则工资增加 200 ；如果工资在10002000 之间，则增加 1

20、50 ；如果工资在20003000 之间，则增加 100 ；否则增加 50 。,CASE 语句示例,DECLARE v_sal emp.sal%type; v_increment NUMBER(4); v_empno emp.empno%type; BEGIN v_empno:=,CASE 语句,等值比较的 CASE 语句基本语法 CASE test_value WHEN value1 THEN statements1; WHEN value2 THEN statements2; WHEN valuen THEN statementsn; ELSE else_ statements; END

21、CASE;,CASE 语句示例,DECLARE v_deptno emp.deptno%type; v_increment NUMBER(4); v_empno emp.empno%type; BEGIN v_empno:=,循环结构,简单循环 WHILE 循环 FOR 循环,简单循环,语法 LOOP sequence_of_statement; EXIT WHEN condition ; END LOOP; 注意 在循环体中一定要包含 EXIT 语句，否则程序进入死循环。,简单循环示例,执行 CREATE TABLE temp_table(num_col NUMBER,info_col CH

22、AR(10) 语句创建 temp_table表，然后利用循环向 temp_table 表中插入50条记录。 DECLARE v_counter BINARY_INTEGER := 1; BEGIN LOOP INSERT INTO temp_table VALUES (v_Counter, Loop index); v_counter := v_counter + 1; EXIT WHEN v_counter 50; END LOOP; END;,WHILE 循环,基本语法 WHILE condition LOOP sequence_of_statement; END LOOP;,WHILE

23、循环示例,利用 WHILE 循环向 temp_table 表中插入50条记录。 DECLARE v_counter BINARY_INTEGER :=1; BEGIN WHILE v_counter = 50 LOOP INSERT INTO temp_table VALUES (v_counter, Loop index); v_counter := v_counter + 1; END LOOP; END;,FOR 循环,基本语法 FOR loop_counter IN REVERSE low_bound.high_bound LOOP sequence_of_statement; END

24、 LOOP; 注意： 循环变量不需要显式定义，系统隐含地将它声明为BINARY_INTEGER 变量； 系统默认时，循环变量从下界往上界递增计数，如果使用REVERSE 关键字，则表示循环变量从上界向下界递减计数； 循环变量只能在循环体中使用，不能在循环体外使用。,FOR 循环示例,利用 FOR 循环向 temp_table 表中插入 50 条记录。 BEGIN FOR v_counter IN 1.50 LOOP INSERT INTO temp_table VALUES (v_counter, Loop Index); END LOOP; END;,子程序设计,利用 PL/SQL 可以进行

25、模块化程序设计。在一个PL/SQL 块中，可以定义若干个子程序。把一些功能相对独立、需要经常执行的代码定义为一个子程序，在需要时根据子程序的名称进行调用。这样不仅便于程序设计和编码，而且利于程序的调试。,子程序形式,PL/SQL 有两种形式的子程序，即过程和函数。 在子程序中也可以定义变量、类型、游标等，也可以进行异常处理。 在调用子程序时，可以向子程序传递参数。过程与函数的区别在于函数有返回值，可以向调用者返回执行结果，而过程没有返回值。,如何使用过程,子程序的定义出现在 PL/SQL 块的声明部分，调用则出现在可执行部分。 PROCEDURE 过程名(参数1定义,参数2定义,.) IS|A

26、S 变量声明部分 BEGIN 可执行部分 EXCEPTION 异常处理部分 END;,过程的参数,在过程中可以定义参数，如果没有参数可以省略圆括号中的参数定义。 参数定义： 参数名 参数传递模式 参数类型 :=默认值 其中参数名和数据类型是必须的。,参数传递模式,参数传递模式： IN：默认的传递模式，它指的是参数用于从调用者向过程中传递一个实参。 OUT：是指从过程中向调用者传递参数，如果要使用这种传递模式，必须在定义时明确指定。在调用过程时，过程的执行情况会影响传递给这个参数的变量值。 IN OUT：是一种双向传递模式，该参数既可以用于从调用者向过程传值，也可以反其道而行之。,参数默认值,参

27、数默认值的作用是在调用过程时，如果没有提供实际参数，则将此默认值作为实际参数传递给过程。 数据类型用来指定参数的类型，在参数定义中不能指定对参数的约束条件，即不能指定参数的长度和是否为空等属性。,过程的调用,过程的调用和 C 语言中调用函数类似。 在调用过程时，需要为过程中的参数提供实参，并且在顺序上要对应。 为了保证将参数正确地传递给过程，要求在定义过程时，将含有默认值的参数集中放在参数列表的右边，只有这样才能将实参一一对应到各个参数。,过程的调用,如果过程有多个参数，在调用过程时，也可以不按照参数列表的顺序提供实参，这时需指定实参和形参之间的对应关系。例如： procedure promp

28、tion(salary IN integer,commiss IN integer, d_no IN integer:=0, price out NUMBER) promption(d_no=dno,commiss=0,salary=10,函数的返回值,从函数中应该向调用者传递一个返回值。 函数参数列表之后的 RETURN 子句规定了函数的返回值的类型，可以是简单、复合类型。 在函数的可执行部分应当至少包含一个 RETURN 语句，用于向调用者传递执行结果。任何一条 RETURN 语句会导致函数执行结束。 返回的值应当符合函数定义中的返回值类型。,函数与过程的重载,在调用函数或过程时，要按照其

29、定义提供实参，如果实参类型或者数目和形参不一致，则会导致错误。 在实际应用中，经常会碰到对于同一个函数需要处理不同类型或数目的参数，可以定义多个名字相同但参数列表不同的函数或过程，这就是重载。,重载子程序,重载子程序的名字相同，但是参数的类型或数目不同，返回值也可能不同。如果完全相同就不是重载了，而是重复定义，这是不允许的。 在调用时，会根据实参的类型来自动调用相应的函数。,子程序示例,在一个块内部定义一个函数和一个过程。函数以部门号为参数返回该部门的平均工资；过程以部门号为参数，输出该部门中工资低于部门平均工资的员工的员工号、员工名。,子程序示例,DECLARE v_deptno emp.d

30、eptno%TYPE; v_avgsal emp.sal%TYPE; FUNCTION return_avgsal(p_deptno emp.deptno%TYPE) RETURN emp.sal%TYPE AS v_sal emp.sal%TYPE; BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno; RETURN v_sal; END return_avgsal;,子程序示例,PROCEDURE show_emp(p_deptno emp.deptno%TYPE) AS CURSOR c_emp IS SELEC

31、T * FROM emp WHERE deptno=p_deptno; BEGIN FOR v_emp IN c_emp LOOP IF v_emp.salreturn_avgsal(v_emp.deptno) THEN DBMS_OUTPUT.PUT_LINE(v_emp.empno| | v_emp.ename); END IF; END LOOP; END show_emp; BEGIN v_deptno:=,存储过程和存储程序,在 SQL*Plus 中编写 PL/SQL 程序，代码就放在SQL*Plus 的缓冲区中。如果执行了其他代码，则会把旧的代码从缓存中清除出去。 这种没有名称只是

32、临时存放在缓冲区中的 PL/SQL块叫做匿名块。,匿名块和有名块,匿名块就是没有名字的 PL/SQL 块，仅存放在缓冲区中。 如果希望 PL/SQL 块能随时被调用执行，并且能被数据库用户共享，就需要创建存储程序。 存储程序是有名字的 PL/SQL 块，用户可以根据它的名字进行多次调用。存储程序在创建时经过了编译和优化，被存储在数据库服务器中，任何有权限的用户都可以调用它，并且由于是经过优化的，执行速度快！,存储程序,与匿名块相比，存储程序是作为数据库对象存储在数据库中的，因此首先要在数据库中创建存储程序。 存储程序的调用可以在 SQL 语句中、应用程序中、SQL*Plus 中或其他 PL/S

33、QL 块中。 存储程序第一次被调用时，其代码将被装载到全局区的共享池中，以后再调用时直接从共享池取出代码执行即可。,存储程序和子程序的不同,存储子程序与局部子程序区别在于： 存储子程序己经编译好放在数据库服务器端，可以直接调用，而子程序存在于定义它的语句块中，在运行时先进行编译； 存储子程序不能重载，而局部子程序可以进行重载； 存储子程序可以被任意的 PL/SQL 块调用，而子程序只能在定义它的块中被调用。,存储程序的优点,使用存储程序的优点是： (1) 在服务器端运行，执行速度快。 (2) 执行一次后代码就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译代码执行，提高了

34、系统性能。 (3) 确保数据库的安全。可以不授权用户直接访问应用程序中的一些表，而是授权用户执行访问这些表的存储程序。非表的授权用户除非通过存储程序，否则就不能访问这些表。 (4)自动完成需要预先执行的任务。存储程序可以在系统启动时自动执行，而不必在系统启动后再进行手工操作，大大方便了用户的使用，可以自动完成一些需要预先执行的任务。,存储程序的形式,存储程序的形式包括： 存储过程 存储函数 触发器 程序包,存储过程,如果用户要在自己的模式中创建存储过程，需要具有 CREATE PROCEDURE 系统权限，如果要在其他用户的模式中创建存储过程，则需要 CREATE ANY PROCEDURE

35、系统权限。,创建存储过程,CREATE OR REPLACE PROCEDURE 过程名(参数列表) AUTHID CURRENT_USER|DEFINER AS 声明部分 BEGIN 可执行部分 EXCEPTION 异常处理部分 END;,创建存储过程,其中 OR REPLACE 选项的作用是当同名存储过程存在时，替换之。当然前提是当前用户具有删除原有存储过程的权限。 存储过程在创建过程中已经进行了编译和优化。如果需要对存储过程进行修改，不能直接修改它的源码，只能再次执行 CREATE OR REPLACE 命令。其他存储程序类似。,创建存储过程,参数定义和子程序完全相同。 AUTHID 选

36、项用来规定存储过程执行时的权限。有两个可选值，只能选其一： CURRENT_USER：调用时，该过程以当前登录用户的身份执行。为此，当前用户应该具有执行此过程的权限。 DEFINER：调用时该过程以创建者的身份执行，这是默认选项。,执行存储过程,在 SQL*Plus 中调用存储过程的命令是： EXECUTE 过程名(实参列表) 如果在一个 PL/SQL 块中调用存储过程，则不需要 EXECUTE 命令，直接通过过程名和实参调用： 过程名(实参列表),存储过程执行权限,每个用户都可以执行自己创建的存储过程。 其他用户需要有存储过程上的 EXECUTE 权限才能执行之。为此拥有者需要为这些用户授权

37、： GRANT EXECUTE ON 过程名 TO 用户 存储过程用 DROP PROCEDURE 命令删除。,创建存储过程示例,创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。 CREATE OR REPLACE PROCEDURE show_emp( p_deptno emp.deptno%TYPE) AS v_sal emp.sal%TYPE; BEGIN SELECT avg(sal) INTO v_sal FROM emp WHERE deptno=p_deptno; DBMS_OUTPUT.PUT_LINE(p_deptno| |

38、average salary is:|v_sal);,创建存储过程示例,FOR v_emp IN (SELECT * FROM emp WHERE deptno=p_deptno AND salv_sal) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.ename); END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department doesnt exists!); END show_emp;,创建存储过程,通常，存储过程不需要返回值，如果需要返回一个值可

39、以通过函数调用实现。但是，如果希望返回多个值，可以使用 OUT 或 IN OUT 模式参数来实现。,创建存储过程示例,创建一个存储过程，以部门号为参数，返回该部门的平均工资和人数。 CREATE OR REPLACE PROCEDURE return_deptinfo( p_deptno emp.deptno%TYPE, p_avgsal OUT emp.sal%TYPE, p_count OUT emp.sal%TYPE) AS BEGIN SELECT avg(sal),count(*) INTO p_avgsal,p_count FROM emp WHERE deptno=p_deptn

40、o; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(The department dont exists!); END return_deptinfo;,调用存储过程示例,DECLARE v_avgsal emp.sal%TYPE; v_count NUMBER; BEGIN show_emp(20); return_deptinfo(10,v_avgsal,v_count); DBMS_OUTPUT.PUT_LINE(v_avgsal| | v_count); END;,创建存储函数,基本语法 CREATE OR REPLACE

41、 FUNCTION 函数名(参数列表) RETURN 返回类型 注意 在函数定义的头部，参数列表之后，必须包含一个 RETURN 语句来指明函数返回值的类型，但不能约束返回值的长度、精度、刻度等。如果使用 %TYPE，则可以隐含地包括长度、精度、刻度等约束信息； 在函数体的定义中，必须至少包含一个 RETURN 语句，来指明函数返回值。也可以有多个 RETURN 语句，但最终只有一个 RETURN 语句被执行。,存储函数,存储函数与存储过程的区别：存储函数必须向调用环境返回一个执行结果。 同样存在 CREATE FUNCTION 和 CREATE ANY FUNCTION 的系统权限。 存储函

42、数的调用、授权、删除与存储过程类同。,创建存储函数示例,创建一个以部门号为参数，返回该部门最高工资的函数。 CREATE OR REPLACE FUNCTION return_maxsal (p_deptno emp.deptno%TYPE) RETURN emp.sal%TYPE AS v_maxsal emp.sal%TYPE; BEGIN SELECT max(sal) INTO v_maxsal FROM emp WHERE deptno=p_deptno; RETURN v_maxsal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.P

43、UT_LINE(The deptno is invalid!); END return_maxsal;,存储函数,如果需要函数返回多个值，可以使用 OUT 或 IN OUT 模式参数。,创建存储函数示例,创建一个函数，以部门号为参数，返回部门名、部门人数及部门平均工资。 CREATE OR REPLACE FUNCTION ret_deptinfo( p_deptno dept.deptno%TYPE, p_num OUT NUMBER, p_avg OUT NUMBER) RETURN dept.dname%TYPE AS v_dname dept.dname%TYPE; BEGIN SEL

44、ECT dname INTO v_dname FROM dept WHERE deptno=p_deptno; SELECT count(*),avg(sal) INTO p_num,p_avg FROM emp WHERE deptno=p_deptno; RETURN v_dname; END ret_maxsal;,调用存储函数,在 SQL 语句中调用函数 在 PL/SQL 中调用函数 注意 函数只能作为表达式的一部分被调用。,调用存储函数示例,通过 return_maxsal 函数的调用，输出各个部门的最高工资；通过 ret_deptinfo 函数调用，输出各个部门名、部门人数及平均工

45、资。 DECLARE v_maxsal emp.sal%TYPE; v_avgsal emp.sal%TYPE; v_num NUMBER; v_dname dept.dname%TYPE; BEGIN FOR v_dept IN (SELECT DISTINCT deptno FROM emp) LOOP v_maxsal:=ret_maxsal(v_dept.deptno); v_dname:=ret_deptinfo(v_dept.deptno,v_num,v_avgsal); DBMS_OUTPUT.PUT_LINE(v_dname| |v_maxsal| | v_avgsal| |v

46、_num); END LOOP; END;,程序包,程序包也是一种 Oracle 数据库对象，它是一组逻辑上相关的数据类型、变量、过程、函数和游标等的集合。 程序包被创建后也存储在数据库服务器中，用户可以直接使用包中的数据类型和变量，也可以直接调用包中的过程和函数。,程序包的形式,程序包有两种形式： 用户根据需要创建的程序包 系统预定义的程序包（如 DBMS_OUTPUT） 自定义程序包可以定义数据类型、变量、过程、函数、异常和游标等元素，这些元素具有全局特性，既可以在程序包内使用，也可以在程序包外部使用。,程序包的构成,一个程序包由两部分组成： 头部：头部用来定义类型、变量、异常，声明游标、

47、过程和函数，作用类似于程序包的接口； 包体：可以利用头部的类型定义变量，定义过程、游标和函数的代码。 在创建程序包时，头部和包体是分别创建的，并且必须是先头部后包体。程序包建立之后，如果要对功能修改，只需修改包体代码即可，不用修改头部。,创建程序包头部,CREATE OR REPLACE PACKAGE 包名 AUTHID CURRENT_USER|DEFINER AS 类型的定义; 变量的定义; 子程序的定义; 游标的声明; 异常的声明; END;,创建程序包头部,其中子程序的声明就是定义过程和函数的原型，即子程序的名称、参数和返回值，不包含代码部分。 类型定义部分允许用户创建自己的数据类型

48、。,头部例子,CREATE OR REPLACE PACKAGE employee AS type total is record(dno emp.deptno%TYPE, total_income number); function tax_per_depart(dno integer) RETURN number; procedure total_per_depart; cursor c1 RETURN total; END;,包体的创建,程序包的包体是对头部的实现，主要用来定义过程和函数的可执行代码。 CREATE OR REPLACE PACKAGE BODY 包名 AS 游标的实现;

49、 子程序的实现; END; 其中包名与创建头部使用的包名完全一致。,包体例子,CREATE OR REPLACE PACKAGE BODY employee AS CURSOR c1 RETURN total is SELECT deptno,sum(sal) FROM emp; FUNCTION tax_per_depart(dno integer) RETURN number AS result number; BEGIN . END; . END;,程序包元素调用,在包头部声明的任何元素是公有的，在包外都是可见的。 包外：通过包名.元素名形式调用； 包内：直接通过元素名进行调用。 在包体

50、中定义而没有在包头中声明的元素是私有的，只能在包体中引用。,程序包删除,如果不需要程序包了，可以用 DROP PACKAGE 命令将其删除： DROP PACKAGE 包的名字; 如果只删除包体： DROP PACKAGE BODY 包的名字;,系统预定义程序包,Oracle 提供了一些预定义的程序包，利用这些包可以完成一些复杂的操作。这些程序包提供了一些常用的类型、变量、过程和函数，用户可以在PL/SQL 块和应用程序中直接使用它们。正确地使用这些预定义的程序包，可以使开发工作达到事半功倍的效果。,常见的预定义程序包,常见的预定义程序包包括： DBMS_OUTPUT：实现基本的输入输出功能。

51、 UTL_FILE：对操作系统文件进行读、写等操作。 DBMS_SQL：执行 DDL 语句。 DBMS_PIPE：用于在两个进程间以管道进行通信。 DBMS_JOB：管理数据库中的作业。,DBMS_OUTPUT 程序包,DBMS_OUTPUT 包的功能是将 PL/SQL 块的执行结果显示在屏幕上，这种输出操作是通过缓冲区来完成的。 SQL*Plus 为存储程序、PL/SQL 块、触发器的执行提供了一个缓冲区，用于存放程序执行期间所产生的数据，这个缓冲区以“先进先出”的方式管理其中的数据。,缓冲区,在默认情况下，PL/SQL 块的执行结果是先输出到缓冲区中的。 如果进行一些特殊的设置，缓冲区中的

52、数据就会输出到屏幕上，然后从缓冲区中清除。 DBMS_OUTPUT 包提供了对缓冲区进行设置、读和写等操作的功能。,缓冲区设置,使缓冲区不可用： EXEC dbms_output.disable; 使缓冲区可用： EXEC dbms_output.enable(缓冲区大小) 说明:缓冲区大小以字节为单位，如果不指定大小则默认为 20000 字节。,缓冲区写操作,缓冲区写操作目前允许的数据类型有数字型、字符串型和日期型。 写操作涉及的过程有： PUT(参数)：将指定的参数写入缓冲区。 PUT_LINE(参数)：将指定的参数写入缓冲区，并在行末写一个换行符。 NEW_LINE：在缓冲区的当前位置写

53、一个换行符。,缓冲区写操作,缓冲区中的数据是以行的形式组织的，每行最多255 个字符，一行写满时，自动从下一行开始继续写。 由于缓冲区大小有限，写数据的原则是“先进先出”，当缓冲区写满时，如果还要继续写入，则将最先写入缓冲区的数据先从缓冲区中清除出去，以便腾出空间容纳新来的数据。,显示缓冲区数据,如果要使缓冲区中的数据显示在显示器上，必须使选项 SERVEROUTPUT 有效，这个选项的作用就是使缓冲区中的数据可以输出到屏幕上。 SET SERVEROUTPUT ON SET SERVEROUTPUT OFF,缓冲区读操作,缓冲区的读操作是指将缓冲区中的数据以行的形式读出来。 与缓冲区读操作有

54、关的过程： GET_LINE：从缓冲区中读一行。 GET_LINES：从缓冲区中读多行。,GET_LINE,过程 GET_LINE 的作用是将缓冲区中最先写入的一行数据读出，并将这一行数据从缓冲区中删除。调用形式为： GET_LINE(变量，状态) 其中变量用于存放读出的数据，状态也是一个变量，用于表示操作是否成功，0 表示成功，1 表示没有数据。,GET_LINES,过程 GET_LINES 的作用是将目前缓冲区中最先写入的几行数据读出，并将它们从缓冲区中删除。调用形式为： GET_LINES(变量，行数) 其中变量是一个集合类型变量，用于存放读出的数据；行数也是一个变量，用于指定要读取多少

55、行。,DBMS_OUTPUT,实际上 DBMS_OUTPUT 程序包本身并没有输入输出的功能，它能做的就是对缓冲区进行读写操作。如果使 SERVEROUTPUT 有效，则缓冲区的内容就被输出到屏幕上。,UTL_FILE 程序包,UTL_FILE 程序包功能是对本地操作系统的文件进行访问。在 PL/SQL 块中访问文件的能力是有限的，主要包括文件的打开、关闭、读、写等操作。 在访问文件之前，必须先打开文件，获得返回的文件标识，对文件的操作全部通过这个标识进行，用完之后还要关闭文件。,文件函数和过程,UTL_FILE 程序包中与文件的打开和关闭操作有关的函数和过程有： FOPEN IS_OPEN

56、FCLOSE FCLOSE_ALL,打开文件,在使用文件之前，首先要用函数 FOPEN 打开文件。 FOPEN(目录，文件名，打开模式) 其中目录是文件所在的位置，它和文件名一起确定了要访问的文件，打开模式： r：只读模式 w：写模式 a：追加模式,访问文件限制,UTL_FILE 访问文件的功能很有限，只能访问指定目录中的文本文件。 该指定目录通过初始化参数 UTL_FILE_DIR 指定。如果没有初始化参数指定目录，那么在访问文件时，Oracle 将抛出一个预定义异常UTL_FILE.INVALID_PATH。,文件标识,如果文件打开成功，FOPEN 函数将返回一个类型为 FILE_TYPE

57、 的文件标识，以后对文件的访问都通过这个标识进行。 FILE_TYPE 类型是 UTL_FILE 包中定义的类型，可以直接使用。,打开测试、关闭,IS_OPEN 函数用于判断一个文件是否已经被打开，它的唯一一个参数就是由 FOPEN 函数返回的文件标识。 文件访问结束后用 FCLOSE 关闭文件，同样以文件标识为参数。 如果打开了多个文件，可以用 FCLOSE_ALL 关闭所有的文件，没有参数。,文件读操作,文件读操作的过程为 GET_LINE，调用格式为： GET_LINE(文件标识, 变量） 作用是从文件中读取一行给变量，变量是一个字符串类型的变量。 如果一个文件是空的或者当前已经读到了文

58、件的末尾，则会抛出异常 NO_DATA_FOUND。,文件写操作,文件写操作的过程较多，这里只介绍 PUT_LINE： PUT_LINE(文件标识, 变量） 和 DBMS_OUTPUT中 的 PUT_LINE 类似。,DBMS_SQL 包,DBMS_SQL 包使得在 PL/SQL 块中执行 DDL 命令成为可能。 利用 DBMS_SQL 包执行 DDL 命令时，首先要打开一个游标，然后通过这个游标执行 DDL 命令，最后关闭这个游标。,DBMS_SQL 包,DBMS_SQL 包中提供了一系列的过程和函数，利用这些过程和函数可以完成所需的操作。 用来打开游标的函数是 OPEN_CURSOR，没有

59、参数。如果打开成功，将返回一个整数作为游标的标识，后续的 DDL 语句执行都要依靠这个标识。 关闭游标的过程是 CLOSE_CURSOR。,SQL 语句分析,过程 PARSE 对 SQL 语句进行语法分析，将其与打开的游标关联，然后执行这条语句。调用格式： PARSE(游标标识， SQL语句，语言标志） 语言标志指定过程以何种方式处理 SQL 语句： DBMS_SQL.V6：采用 Oracle6 的方式处理。 DBMS_SQL.V7：采用 Oracle7 的方式处理。 DBMS_SQL.NATIVE：采用一般方式处理。,与存储程序相关的数据字典,USER_PROCEDURES：存放的是当前用户

60、所拥有的存储过程和存储函数信息。 USER_OBJECTS：存放当前用户所拥有的所有类型的数据库对象。 USER_SOURCE：存放存储过程、存储函数和程序包的源代码，但存储程序的执行并不是从这里开始的。,USER_ERRORS,如果在创建存储过程、存储函数或程序包时发生了语法错误，Oracle 将会把错误信息存储在数据字典中。 通过 USER_ERRORS 可以查看这些错误信息。 SQL*Plus 中可以通过 show errors 来查看当前发生的错误。,异常处理,异常是指因为处理的数据超出了处理的范围而引发的错误。 当 PL/SQL 块在执行过程中检测到一个错误时，就会抛出相应的异常。在