3.6.4　系统预定义程序包

Oracle提供了一些预定义的程序包，利用这些包可以完成一些复杂的操作。这些程序包提供了一些常用的类型、变量、过程和函数，用户可以在PL/SQL块和应用程序中直接使用它们。正确地使用这些预定义的程序包，可以使开发工作达到事半功倍的效果。常用的预定义程序包及其用途如下所示：

DBMS_OUTPUT　实现基本的输入输出功能

UTL_FILE　对操作系统文件进行读、写等操作

DBMS_SQL　执行DDL语句

DBMS_PIPE　用于在两个进程间以管道方式进行通信

DBMS_JOB　管理数据库中的作业

下面将对最常用的程序包DBMS_OUTPUT、UTL_FILE和DBMS_SQL做简单的介绍。

1.DBMS_OUTPUT程序包

DBMS_OUTPUT包的功能是将PL/SQL块的执行结果显示在屏幕上，这种输出操作是通过缓冲区来完成的。SQL*Plus为存储程序、PL/SQL块、触发器的执行提供了一个缓冲区，用于存放程序执行期间所产生的数据，这个缓冲区以“先进先出”的方式管理其中的数据。

在默认情况下，PL/SQL块的执行结果是输出到缓冲区里的，如果进行一些特殊的设置，缓冲区中的数据就会输出到屏幕上，然后从缓冲区中清除。DBMS_OUTPUT包提供了对缓冲区进行设置、读和写等操作的功能，它提供了一系列的过程和函数，分别对缓冲区进行设置、读和写等操作。用户利用DBMS_OUTPUT包中的过程或函数可以向缓冲区中写入数据，也可以从缓冲区中读数据。

缓冲区的设置操作主要包括使其可用和不可用等操作。使缓冲区不可用的过程是DISABLE，这个过程可以在SQL*Plus中以如下形式执行：

SQL＞EXEC dbms_output.disable；

如果要在存储程序、PL/SQL块和触发器中调用这个过程，则不需要EXEC命令，可以直接调用执行。

与DISABLE相对的操作是ENABLE过程，它可以使缓冲区可用，并且可以设置缓冲区的大小。它的调用形式为：

enable（缓冲区的大小）

如果在调用这个过程时不指定任何参数，则结果是使缓冲区可用，并将其大小设置为默认大小，即20 000字节。例如，要将缓冲区的大小设置为1024字节，这个过程的调用形式为：

SQL＞EXEC dbms_output.enable（1024）；

缓冲区的写操作指的是向缓冲区中写入数据，目前允许的数据类型有数字型、字符串型和日期型。写操作涉及的过程有以下几个：

·PUT（参数）：将指定的参数写入缓冲区。

·PUT_LINE（参数）：将指定的参数写入缓冲区，并在行末写一个换行符。

·NEW_LINE：在缓冲区中当前位置处写一个换行符。

缓冲区中的数据是以行的形式组织的，每行最多存储255个字符，一行写满时，自动从下一行开始继续写。由于缓冲区的大小有限，写数据的原则是“先进先出”，当缓冲区写满时，如果还要继续写，那么最先写入缓冲区中的数据就会被从缓冲区中清除出去，以便腾出空间容纳新数据。

PUT和PUT_LINE过程的作用都是向缓冲区当前位置处写入一行数据，它们之间的区别是，PUT_LINE在写完数据后在当前行的末尾写入一个换行符，而PUT过程不写入换行符。过程NEW_LINE的作用仅仅是在缓冲区当前位置处写入一个换行符。实际上，调用一次过程PUT_LINE，相当于先调用一次过程PUT，然后再调用一次过程NEW_LINE。

如果要使缓冲区中的数据显示在显示器上，必须使选项SERVEROUTPUT有效，这个选项的作用就是使缓冲区中的数据可以输出到屏幕上。为了使这个选项有效，在SQL*Plus中执行SET命令：

SQL＞SET SERVEROUTPUT ON

这个选项的另一个可选值是OFF，它的作用正好与ON相反。为了说明这几个过程的用法，让我们首先观察下面这个PL/SQL块的执行情况：

DECLARE

data1 integer：=100；

data2 varchar2（10）：='Hello'；

data3 date DEFAULT sysdate；

BEGIN

dbms_output.put（data1）；

dbms_output.put_line（data2）；

dbms_output.put_line（data3）；

END；

这个块的执行结果为：

SQL＞/

100Hello

28-5月-10

缓冲区的读操作是指将缓冲区中的数据以行的形式读出来。与缓冲区的读操作有关的过程有两个：

·GET_LINE：从缓冲区中读一行。

·GET_LINES：从缓冲区中读多行。

过程GET_LINE的作用是将目前缓冲区中最先写入的一行数据读出，并将这一行数据从缓冲区中删除。它的调用形式为：

GET_LINE（变量，状态）

其中变量用于存放从缓冲区中读出的数据，它的类型必须与要读的数据一致。状态也是一个变量，用来表示本次读操作是否成功，它的传递模式为OUT。在这个过程执行结束后，如果状态变量的值为0，表示成功，如果为1，则表示缓冲区中没有数据。

过程GET_LINES的作用是将目前缓冲区中最先写入的几行数据读出，并将它们从缓冲区中删除。它的调用形式为：

GET_LINES（变量，行数）

其中变量是一个集合类型变量，用来存放读到的几行数据。行数也是一个变量，在读操作之前，这个参数用于指定需要读的行数，在读操作之后，这个参数表示实际读到的数据行数。下面再通过一个例子说明读操作和写操作的综合应用。

DECLARE

data integer；—表示数据的变量

stat integer；—表示状态的变量

BEGIN

dbms_output.put（100）；

dbms_output.put_line（200）；

dbms_output.get_line（data, stat）；

dbms_output.put_line（'缓冲区中的数据：'||data）；

dbms_output.put_line（'状态：'||stat）；

END；

这个块的执行结果为：

SQL＞/

缓冲区中的数据：100200

状态：0

在上述PL/SQL块中，第一次向缓冲区中写100时使用了过程PUT，写入数据后没有换行。第二次向缓冲区中写200时使用了过程PUT_LINE，这样100和200被写在了同一行。在读数据时使用了过程GET_LINE，将刚才写入的一行数据读到变量data中，于是变量data的值为100 200。而变量stat用来表示本次读操作是否成功，其值为0，表示读操作成功。由此可见，在从缓冲区中读数据时，是以行为单位进行的，而不是以数据为单位。

实际上，DBMS_OUTPUT程序包本身并没有输入输出的功能，它所能做的就是对缓冲区进行读写操作。如果使SERVEROUTPUT选项有效，则缓冲区的内容就被输出到屏幕上，PUT和PUT_LINE过程只需要把数据写入缓冲区中就可以了，这就相当于完成了输出工作。而GET_LINE的功能是从缓冲区中读一行数据，如果缓冲区中有数据，则它把当前缓冲区中最先写入的数据读出，这就相当于完成了输入工作。

2.UTL_FILE程序包

UTL_FILE程序包功能是对本地操作系统的文件进行访问。在PL/SQL块中访问文件的能力是有限的，主要包括文件的打开、关闭、读、写等操作。在访问文件之前，必须先打开文件，这时系统将返回一个文件标识，对文件的读、写等操作都是通过这个文件标识进行的。文件访问完后，还应该及时关闭文件。

UTL_FILE程序包中与文件的打开和关闭操作有关的函数和过程有：

·FOPEN

·IS_OPEN

·FCLOSE

·FCLOSE_ALL

在访问文件之前，首先要用函数FOPEN打开文件。这个函数的调用格式为：

FOPEN（目录，文件名，打开模式）

其中目录为文件所在的位置，它文件名一起确定了要访问的文件。打开模式是指以什么样的方式打开文件。UTL_FILE包规定了三种打开模式：

·r：只读方式，用于读出文件的内容。

·w：写方式，用于向文件中写入数据。

·a：追加方式，用于在文件末尾写入数据。

UTL_FILE程序包访问文件的功能很有限，并不是所有的文件都可以访问。利用这个程序包只能访问指定目录中的文本文件。为了访问某个目录中的文件，必须通过初始化参数指定这个目录，指定的格式为：

UTL_FILE_DIR=目录

例如，为了访问目录/home/oracle中的文件，需要在参数文件中添加一下内容：

UTL_FILE_DIR=/home/oracle

如果没有初始化参数指定目录，那么在访问一个目录中的文件时，Oracle将抛出一个预定义的异常UTL_FILE.INVALID_PATH。如果要访问所有目录中的文件，可以用“*”代表任何目录，代替上面某个具体目录，但这种作法是不提倡的，因为这将带来安全隐患。

如果文件打开成功，FOPEN函数将返回一个FILE_TYPE类型的文件标识，以后对文件的访问就是通过这个文件标识进行的。FILE_TYPE类型是在UTL_FILE包中定义的类型，用户可以直接使用。

函数IS_OPEN用于判断一个文件是否已经被打开，它只有一个参数，就是FOPEN函数返回的文件标识。如果文件已经被打开，函数IS_OPEN将返回真值，否则返回假值。

文件访问结束后，应该调用过程FCLOSE关闭文件。这个过程只有一个参数，就是FOPEN函数返回的文件标识。如果打开了多个文件，可以调用过程FCLOSE_ALL关闭所有文件，这个过程没有任何参数。

与文件的读操作有关的过程为GET_LINE，它的调用格式为：

GET_LINE（文件标识，变量）

其中文件标识就是用函数FOPEN打开文件时的返回值。变量是一个字符串类型的变量，用于存放从文件中读到的数据。因为对文件的读操作是以行为单位进行的，所以这个变量要能够存放文件中的一行数据。如果一个文件是空的，或者当前已经读到了文件末尾，这时系统将抛出异常NO_DATA_FOUND。下面的例子演示了文件的打开、读和关闭操作。文件打开后将它的第一行数据读出，并输出到显示器上。

DECLARE

fp UTL_FILE.FILE_TYPE；

line varchar2（100）；

BEGIN

fp：=utl_file.fopen（'/home/oracle'，'sqlnet.log'，'r'）；

utl_file.get_line（fp, line）；

dbms_output.put_line（line）；

utl_file.fclose（fp）；

END；

对文件的写操作涉及的过程较多，这里仅介绍用得最多的过程PUT_LINE。这个过程以行的形式将数据写入文件，每写入一行，就在行的末尾添加一个换行符，它的调用形式为：

PUT_LINE（文件标识，变量）

这个过程将变量中的数据写入文件标识所代表的文件中。在下面的例子中，首先以只读方式打开第一个文件，然后以追加方式打开第二个文件。从第一个文件中读一行数据到变量中，在这行数据的前后各添加一个“#”后再写入第二个文件，最后关闭两个文件。

DECLARE

fp1 UTL_FILE.FILE_TYPE；

fp2 UTL_FILE.FILE_TYPE；

line varchar2（100）；

BEGIN

fp1：=utl_file.fopen（'/home/oracle'，'sqlnet.log'，'r'）；

fp2：=utl_file.fopen（'/home/oracle'，'sqlnet.bak'，'a'）；

utl_file.get_line（fp1，line）；

utl_file.put_line（fp2，'#'||line||'#'）；

utl_file.fclose_all；

END；

3.DBMS_SQL包

在PL/SQL块中我们可以利用SELECT命令从数据库中检索数据，也可以利用INSERT、DELETE和UPDATE语句对数据库中的数据进行增加、删除、修改等操作。但是像创建表、删除表、修改表结构这样的操作在PL/SQL块中是不能直接完成的，也就是说，在PL/SQL块中不能直接执行CREATE、DROP、ALTER这样的DDL命令。如果要在PL/SQL块中进行这样的操作，就要借助于Oracle提供的程序包—DBMS_SQL。

DBMS_SQL包使得在PL/SQL包中执行DDL命令成为可能。利用DBMS_SQL包执行DDL命令时，首先要打开一个游标，然后通过这个游标执行DDL命令，最后关闭这个游标。

DBMS_SQL包提供了一系列的过程和函数，利用这些过程和函数可以完成所需的操作。用来打开游标的函数是OPEN_CURSOR，这个函数没有任何参数。如果游标打开成功，这个函数将返回一个整数，这个整数就是游标的标识。以后执行SQL语句就是通过这个游标的标识进行的。

对SQL语句进行分析的过程是PARSE，这个过程对SQL语句进行语法分析，将其与打开的游标进行关联，然后执行这条SQL语句。这个过程的调用格式为：

PARSE（游标标识，SQL语句，语言标志）

其中游标标识就是打开游标时的返回值。SQL语句是需要执行的DDL命令的完整形式。语言标志指定该过程以什么样的方式处理SQL语句，这个参数有三个可选值：

·DBMS_SQL. V6：采用Oracle6的方式处理SQL语句。

·DBMS_SQL. V6：采用Oracle7的方式处理SQL语句。

·DBMS_SQL. NATIVE：采用一般方式处理SQL语句。

SQL语句执行结束后，应该及时关闭游标。关闭游标的过程是CLOSE_CURSOR，这个过程只有一个参数，就是通过函数OPEN_CURSOR打开的游标的标识。

例如，在下面的PL/SQL块中，首先打开一个游标，游标的标识为cur_1，然后利用这个游标执行一条SQL语句，创建表t1，这个表有两个列，id和name。最后关闭这个游标。这个块的执行结果是在当前用户的模式中创建了一个表t1。

DECLARE

cur_1 integer；

str varchar2（100）；

BEGIN

str：='CREATE table t1（id integer, name varchar2（10））'；

cur_1：=dbms_sql.open_cursor；

dbms_sql.parse（cur_1，str, DBMS_SQL.V7）；

dbms_sql.close_cursor（cur_1）；

END；

如果已经有一个同名的表存在，上述PL/SQL块执行时将出错。为了向用户报告出错的情况，我们可以在PL/SQL块中捕捉错误，并进行异常处理，将出错的情况报告给用户，这样的PL/SQL块才算是一个完整的、健壮的程序。下面是增加了异常处理的PL/SQL块：

DECLARE

cur_1 integer；

str varchar2（100）；

already_exists EXCEPTION；

PRAGMA EXCEPTION_INIT（already_exists，-00955）；

BEGIN

str：='CREATE table t1（id integer, name varchar2（10））'；

cur_1：=dbms_sql.open_cursor；

dbms_sql.parse（cur_1，str, DBMS_SQL.V7）；

dbms_sql.close_cursor（cur_1）；

EXCEPTION

WHEN already_exists THEN

dbms_output.put_line（'需要创建的表已经存在'）；

END；

在这个块中定义了一个异常already_exists，然后将它与错误号-00955关联起来。错误号-00955代表的错误情况是指定的名称已经被其他对象使用。这样当发生这个错误时，系统将抛出异常already_exists。在块的最后，进行的异常的处理，将错误的情况显示给用户。

实际上，在PL/SQL块中还有一种执行DDL和DCL语句的方法，那就是把这样的语句作为EXECUTE IMMEDIATE命令的参数。EXECUTE IMMEDIATE命令的功能是执行动态的SQL语句，它的参数可以是一个变量或一个表示SQL语句的字符串，还可以是用“||”符号连接在一起的若干字符串等。例如：

sql_stmt：='CREATE TABLE t1（id number, name char（10））'；

EXECUTE IMMEDIATE sql_stmt；

EXECUTE IMMEDIATE'GRANT select, update on t1 to user1'；

EXECUTE IMMEDIATE'DROP TABLE t1'；