PL/SQL

本节介绍 Oracle 兼容模式下的过程语言：内置系统包（DBMS_ALERT/DBMS_OUTPUT/UTL_FILE 等）与用户自定义 Package 语法。

Oracle系统包

DBMS_ALERT

DBMS_ALERT支持数据库事件(警报)的异步通知。通过适当使用此包和数据库触发器，应用程序可以在数据库中特殊的值发生更改时通知自己。假设一个图形工具正在显示来自数据库表的一些数据的图形。 在读取和绘制数据之后，图形工具可以等待覆盖刚刚读取的数据的数据库警报。当数据被任何其他用户更改时，该工具会自动唤醒。 只需将触发器放在数据库表上，每当触发器被触发时，数据库表就会执行一个信号。

oracle=# \x on
扩展显示已打开
oracle=# \df dbms_alert.*
函数列表
-[ RECORD 1 ]+------------------------------------------------------------------------------
架构模式     | dbms_alert
名称         | defered_signal
结果数据类型 | trigger
参数数据类型 | 
类型         | 函数
-[ RECORD 2 ]+------------------------------------------------------------------------------
架构模式     | dbms_alert
名称         | register
结果数据类型 | 
参数数据类型 | IN name text
类型         | proc
-[ RECORD 3 ]+------------------------------------------------------------------------------
架构模式     | dbms_alert
名称         | remove
结果数据类型 | 
参数数据类型 | IN name text
类型         | proc
-[ RECORD 4 ]+------------------------------------------------------------------------------
架构模式     | dbms_alert
名称         | removeall
结果数据类型 | 
参数数据类型 | 
类型         | proc
-[ RECORD 5 ]+------------------------------------------------------------------------------
架构模式     | dbms_alert
名称         | set_defaults
结果数据类型 | 
参数数据类型 | IN sensitivity double precision
类型         | proc
-[ RECORD 6 ]+------------------------------------------------------------------------------
架构模式     | dbms_alert
名称         | signal
结果数据类型 | 
参数数据类型 | IN name text, IN message text
类型         | proc
-[ RECORD 7 ]+------------------------------------------------------------------------------
架构模式     | dbms_alert
名称         | waitany
结果数据类型 | record
参数数据类型 | OUT name text, OUT message text, OUT status integer, timeout double precision
类型         | 函数
-[ RECORD 8 ]+------------------------------------------------------------------------------
架构模式     | dbms_alert
名称         | waitone
结果数据类型 | record
参数数据类型 | name text, OUT message text, OUT status integer, timeout double precision
类型         | 函数

dbms_alert.register：注册一个预警事件

-- 语法
DBMS_ALERT.REGISTER (name IN VARCHAR(30) )；

说明：neme 输入参数，预警事件名。

例如：

oracle=# exec dbms_alert.register('aldd');
CALL

dbms_alert.remove：删除会话不需要的预警事件

-- 语法
DBMS_ALERT.REMOVE (name IN VARCHAR2);

说明：name要从列表中删除的预警事件名称(不区分大小写)。

例如：

oracle=# exec dbms_alert.remove ('aldd');
CALL

dbms_alert.removeall：删除当前会话已注册所有预警事件

-- 语法
 DBMS_ALERT.REMOVEALL;

例如：

oracle=# exec dbms_alert.removeall();
CALL

dbms_alert.signal：用于指定预警事件所对应的预警消息

-- 语法
DBMS_ALERT.SIGNAL ( name   IN  VARCHAR2,message  IN  VARCHAR2);

说明：name预警事件的名称。Message是与此警报相关联的消息，长度为1800字节或更少。此消息被传递给等待会话。通过使用消息中的信息，等待会话可以避免在警报发生后读取数据库。

例如：

oracle=# exec dbms_alert.signal('aldd','zlq');
CALL

DBMS_ASSERT

DBMS_ASSERT包提供了一个接口来验证输入值的属性。

oracle=# \df dbms_assert.*
                                                函数列表
  架构模式   |        名称        |   结果数据类型    |               参数数据类型                | 类型 
-------------+--------------------+-------------------+-------------------------------------------+------
 dbms_assert | enquote_literal    | character varying | str character varying                     | 函数
 dbms_assert | enquote_name       | character varying | str character varying                     | 函数
 dbms_assert | enquote_name       | character varying | str character varying, loweralize boolean | 函数
 dbms_assert | noop               | character varying | str character varying                     | 函数
 dbms_assert | object_name        | character varying | str character varying                     | 函数
 dbms_assert | qualified_sql_name | character varying | str character varying                     | 函数
 dbms_assert | schema_name        | character varying | str character varying                     | 函数
 dbms_assert | simple_sql_name    | character varying | str character varying                     | 函数
(8 行记录)

dbms_assert.enquote_literal：用于字符串字面值添加了首引号和尾单引号。

-- 语法
DBMS_ASSERT.ENQUOTE_LITERAL (str  VARCHAR2)RETURN VARCHAR2;

例如：

oracle=# select DBMS_ASSERT.ENQUOTE_LITERAL (ename) from emp;
 ENQUOTE_LITERAL 
-----------------
 'ALLEN'
 'WARD'
 'JONES'
 'MARTIN'
 'BLAKE'
(5 行记录)

dbms_assert.enquote_name：提供的字符串括在引号中。如果字符串已经在引号中，则不会添加额外的引号。然后检查用引号内的字符串，看它是否是一个有效的标识符。

-- 语法
DBMS_ASSERT.ENQUOTE_NAME (str            VARCHAR2,capitalize     BOOLEAN DEFAULT TRUE)RETURN VARCHAR2；

说明：capitalize如果为TRUE或默认值，不包含在引号中的str的字母字符将被转换为大写。

例如：

oracle=# select DBMS_ASSERT.ENQUOTE_NAME (ename ) from emp;
 ENQUOTE_NAME 
--------------
 "allen"
 "ward"
 "jones"
 "martin"
 "blake"
(5 行记录)

dbms_assert.noop ：返回值时不进行任何检查。

-- 语法
DBMS_ASSERT.NOOP (str  VARCHAR2 CHARACTER SET ANY_CS)RETURN     VARCHAR2 CHARACTER SET str%CHARSET;
DBMS_ASSERT.NOOP (str      CLOB CHARACTER SET ANY_CS)RETURN     CLOB CHARACTER SET str%CHARSET;

说明：str 输入值

例如：

oracle=# select dbms_assert.noop ('666') from dual;
 NOOP 
------
 666
(1 行记录)

dbms_assert.qualified_sql_name： 验证输入的字符串是否为合格的SQL名称。

-- 语法
DBMS_ASSERT.QUALIFIED_SQL_NAME (str      VARCHAR2 CHARACTER SET ANY_CS)RETURN     VARCHAR2 CHARACTER SET str%CHARSET;

说明：str 输入值

例如：

oracle=# select dbms_assert.qualified_sql_name ('wy') from dual;
 QUALIFIED_SQL_NAME 
--------------------
 wy
(1 行记录)

dbms_assert.schema_name ：验证输入字符串是否为一个现有的模式名。

-- 语法
DBMS_ASSERT.SCHEMA_NAME (str    VARCHAR2 CHARACTER SET ANY_CS)RETURN   VARCHAR2 CHARACTER SET str%CHARSET;

说明：str输入值

例如：

oracle=# select dbms_assert.schema_name('DBMS_OUTPUT') from dual;
 SCHEMA_NAME 
-------------
 DBMS_OUTPUT
(1 行记录)

dbms_assert.simple_sql_name ：验证输入字符串是否为一个简单的SQL名称。

-- 语法
DBMS_ASSERT.SIMPLE_SQL_NAME (str      VARCHAR2 CHARACTER SET ANY_CS)RETURN     VARCHAR2 CHARACTER SET str%CHARSET;

例如：

oracle=# select dbms_assert.simple_sql_name('emp') from dual;
 SIMPLE_SQL_NAME 
-----------------
 emp
(1 行记录)

DBMS_OUTPUT

DBMS_OUTPUT包能够从存储过程、包和触发器发送消息，对于显示 PL/SQL 调试信息特别有用

oracle=# \df dbms_output.*
                                          函数列表
  架构模式   |     名称     | 结果数据类型 |               参数数据类型               | 类型 
-------------+--------------+--------------+------------------------------------------+------
 dbms_output | disable      |              |                                          | proc
 dbms_output | enable       |              |                                          | proc
 dbms_output | enable       |              | IN buffer_size integer                   | proc
 dbms_output | get_line     |              | OUT line text, OUT status integer        | proc
 dbms_output | get_lines    |              | OUT lines text[], INOUT numlines integer | proc
 dbms_output | new_line     |              |                                          | proc
 dbms_output | put          |              | IN a text                                | proc
 dbms_output | put_line     |              | IN a text                                | proc
 dbms_output | serveroutput |              | IN boolean                               | proc
(9 行记录)

dbms_output.disable：禁用、调用、清除缓冲区的信息。

-- 语法
DBMS_OUTPUT.DISABLE;

示例：

oracle=# call DBMS_OUTPUT.DISABLE ( );
CALL

dbms_output.enable：对过程的调用。

-- 语法
DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000);

说明：buffer_size上限（以字节为单位），缓冲信息量。

例如：

oracle=# call DBMS_OUTPUT.ENABLE ( );
CALL

dbms_output.new_line ：放置一个行尾标记

-- 语法
DBMS_OUTPUT.NEW_LINE;

例如：

oracle=# call DBMS_OUTPUT.NEW_LINE( );
CALL

dbms_output.put ：对在缓冲区中放置一条部分行。

-- 语法
  DBMS_OUTPUT.PUT (item IN VARCHAR2);

说明：item要缓冲的项目

例如：

oracle=# exec DBMS_OUTPUT.PUT ('hello');
CALL

dbms_output.put_line ：在缓冲区中放置一条线。

-- 语法
DBMS_OUTPUT.PUT_LINE (item IN VARCHAR2);

说明： item要缓冲的项目

例如：

oracle=# exec DBMS_OUTPUT.PUT_LINE ('hello');
hello
CALL

DBMS_PIPE

DBMS_PIPE包允许同一实例中的两个或多个会话进行通信，Oracle 管道在概念上与 UNIX 中使用的管道相似，但 Oracle 管道不是使用操作系统管道机制实现的；管道功能有几个潜在的应用程序：外部服务接口、独立事务、警报器（非事务性）、调试和集中器。

oracle=# \x on
扩展显示已打开.
函数列表
-[ RECORD 1 ]+------------------------------------
架构模式     | dbms_pipe
名称         | __list_pipes
结果数据类型 | SETOF record
参数数据类型 | 
类型         | 函数
-[ RECORD 2 ]+------------------------------------
架构模式     | dbms_pipe
名称         | __unpack_message_bytea
结果数据类型 | bytea
参数数据类型 | 
类型         | 函数
-[ RECORD 3 ]+------------------------------------
架构模式     | dbms_pipe
名称         | __unpack_message_date
结果数据类型 | datetime
参数数据类型 | 
类型         | 函数
-[ RECORD 4 ]+------------------------------------
架构模式     | dbms_pipe
名称         | __unpack_message_number
结果数据类型 | numeric
参数数据类型 | 
类型         | 函数
-[ RECORD 5 ]+------------------------------------
架构模式     | dbms_pipe
名称         | __unpack_message_record
结果数据类型 | record
参数数据类型 | 
类型         | 函数
-[ RECORD 6 ]+------------------------------------
架构模式     | dbms_pipe
名称         | __unpack_message_text
结果数据类型 | text
参数数据类型 | 
类型         | 函数
-[ RECORD 7 ]+------------------------------------
架构模式     | dbms_pipe
名称         | __unpack_message_timestamp
结果数据类型 | timestamp with time zone
参数数据类型 | 
类型         | 函数
-[ RECORD 8 ]+------------------------------------
架构模式     | dbms_pipe
名称         | create_pipe
结果数据类型 | void
参数数据类型 | text
类型         | 函数
-[ RECORD 9 ]+------------------------------------
架构模式     | dbms_pipe
名称         | create_pipe
结果数据类型 | void
参数数据类型 | text, integer
类型         | 函数
-[ RECORD 10 ]------------------------------------
架构模式     | dbms_pipe
名称         | create_pipe
结果数据类型 | void
参数数据类型 | text, integer, boolean
类型         | 函数
-[ RECORD 11 ]------------------------------------
架构模式     | dbms_pipe
名称         | next_item_type
结果数据类型 | integer
参数数据类型 | 
类型         | 函数
-[ RECORD 12 ]------------------------------------
架构模式     | dbms_pipe
名称         | pack_message
结果数据类型 | 
参数数据类型 | IN bigint
类型         | proc
-[ RECORD 13 ]------------------------------------
架构模式     | dbms_pipe
名称         | pack_message
结果数据类型 | 
参数数据类型 | IN bytea
类型         | proc
-[ RECORD 14 ]------------------------------------
架构模式     | dbms_pipe
名称         | pack_message
结果数据类型 | 
参数数据类型 | IN datetime
类型         | proc
-[ RECORD 15 ]------------------------------------
架构模式     | dbms_pipe
名称         | pack_message
结果数据类型 | 
参数数据类型 | IN integer
类型         | proc
-[ RECORD 16 ]------------------------------------
架构模式     | dbms_pipe
名称         | pack_message
结果数据类型 | 
参数数据类型 | IN numeric
类型         | proc
-[ RECORD 17 ]------------------------------------
架构模式     | dbms_pipe
名称         | pack_message
结果数据类型 | 
参数数据类型 | IN record
类型         | proc
-[ RECORD 18 ]------------------------------------
架构模式     | dbms_pipe
名称         | pack_message
结果数据类型 | 
参数数据类型 | IN text
类型         | proc
-[ RECORD 19 ]------------------------------------
架构模式     | dbms_pipe
名称         | pack_message
结果数据类型 | 
参数数据类型 | IN timestamp without time zone
类型         | proc
-[ RECORD 20 ]------------------------------------
架构模式     | dbms_pipe
名称         | pack_message
结果数据类型 | 
参数数据类型 | IN timestamp with time zone
类型         | proc
-[ RECORD 21 ]------------------------------------
架构模式     | dbms_pipe
名称         | purge
结果数据类型 | 
参数数据类型 | IN text
类型         | proc
-[ RECORD 22 ]------------------------------------
架构模式     | dbms_pipe
名称         | receive_message
结果数据类型 | integer
参数数据类型 | text
类型         | 函数
-[ RECORD 23 ]------------------------------------
架构模式     | dbms_pipe
名称         | receive_message
结果数据类型 | integer
参数数据类型 | text, integer
类型         | 函数
-[ RECORD 24 ]------------------------------------
架构模式     | dbms_pipe
名称         | remove_pipe
结果数据类型 | void
参数数据类型 | text
类型         | 函数
-[ RECORD 25 ]------------------------------------
架构模式     | dbms_pipe
名称         | reset_buffer
结果数据类型 | 
参数数据类型 | 
类型         | proc
-[ RECORD 26 ]------------------------------------
架构模式     | dbms_pipe
名称         | send_message
结果数据类型 | integer
参数数据类型 | text
类型         | 函数
-[ RECORD 27 ]------------------------------------
架构模式     | dbms_pipe
名称         | send_message
结果数据类型 | integer
参数数据类型 | text, integer
类型         | 函数
-[ RECORD 28 ]------------------------------------
架构模式     | dbms_pipe
名称         | send_message
结果数据类型 | integer
参数数据类型 | text, integer, integer
类型         | 函数
-[ RECORD 29 ]------------------------------------
架构模式     | dbms_pipe
名称         | unique_session_name
结果数据类型 | character varying
参数数据类型 | 
类型         | 函数
-[ RECORD 30 ]------------------------------------
架构模式     | dbms_pipe
名称         | unpack_message
结果数据类型 | 
参数数据类型 | INOUT item bytea
类型         | proc
-[ RECORD 31 ]------------------------------------
架构模式     | dbms_pipe
名称         | unpack_message
结果数据类型 | 
参数数据类型 | INOUT item datetime
类型         | proc
-[ RECORD 32 ]------------------------------------
架构模式     | dbms_pipe
名称         | unpack_message
结果数据类型 | 
参数数据类型 | INOUT item numeric
类型         | proc
-[ RECORD 33 ]------------------------------------
架构模式     | dbms_pipe
名称         | unpack_message
结果数据类型 | 
参数数据类型 | INOUT item record
类型         | proc
-[ RECORD 34 ]------------------------------------
架构模式     | dbms_pipe
名称         | unpack_message
结果数据类型 | 
参数数据类型 | INOUT item text
类型         | proc
-[ RECORD 35 ]------------------------------------
架构模式     | dbms_pipe
名称         | unpack_message
结果数据类型 | 
参数数据类型 | INOUT item timestamp with time zone
类型         | proc

dbms_pipe.__list_pipes ：获取所有命名管道的列表

例如：

oracle=# select dbms_pipe.__list_pipes ();
 __LIST_PIPES 
--------------
(0 行记录)

dbms_pipe.__unpack_message_bytea

-- 语法

例如：

oracle=# select dbms_pipe.__unpack_message_bytea ();
 __UNPACK_MESSAGE_BYTEA 
------------------------
 
(1 行记录)

dbms_pipe.__unpack_message_date

例如：

oracle=# select dbms_pipe.__unpack_message_date ();
 __UNPACK_MESSAGE_DATE 
-----------------------
 
(1 行记录)

dbms_pipe.__unpack_message_number

-- 语法

例如：

oracle=# select dbms_pipe.__unpack_message_number ();
 __UNPACK_MESSAGE_NUMBER 
-------------------------
 
(1 行记录)

dbms_pipe.__unpack_message_record

-- 语法

例如：

oracle=# select dbms_pipe.__unpack_message_record ();
 __UNPACK_MESSAGE_RECORD 
-------------------------
 
(1 行记录)

dbms_pipe.__unpack_message_text

-- 语法

例如：

oracle=# select dbms_pipe.__unpack_message_text ();
 __UNPACK_MESSAGE_TEXT 
-----------------------
 
(1 行记录)

dbms_pipe.__unpack_message_timestamp

-- 语法

例如：

oracle=# select dbms_pipe.__unpack_message_timestamp ();
 __UNPACK_MESSAGE_TIMESTAMP 
----------------------------
 
(1 行记录)

dbms_pipe.create_pipe：显式创建公共或专用管道

-- 语法
DBMS_PIPE.CREATE_PIPE (pipename     IN VARCHAR2,maxpipesize  IN INTEGER DEFAULT 8192,private      IN BOOLEAN DEFAULT TRUE)RETURN INTEGER;

说明：pipename要创建的管道的名称，在调用和使用时必须使用此名称，且名称在整个实例中必须是唯一的maxpipesize管道允许的最大大小，默认值为 8192 字节。Private使用缺省值 ，创建专用管道，可以在调用时隐式创建公共管道。

例如：

oracle=# select dbms_pipe.create_pipe ('asas');
 CREATE_PIPE 
-------------
 
(1 行记录)

dbms_pipe.next_item_type：确定本地消息缓冲区中下一项的数据类型。调用以将管道信息放置在本地缓冲区中后RECEIVE_MESSAGENEXT_ITEM_TYPE.

-- 语法
DBMS_PIPE.NEXT_ITEM_TYPERETURN INTEGER;

例如：

oracle=# select DBMS_PIPE.NEXT_ITEM_TYPE ();
 NEXT_ITEM_TYPE 
----------------
              0
(1 行记录)

dbms_pipe.pack_message：在本地消息缓冲区中生成消息。要发送消息，先对函数进行一次或多次调用，然后调用以在命名管道上的本地缓冲区中发送消息

-- 语法
 DBMS_PIPE.PACK_MESSAGE (item  IN  VARCHAR2);
DBMS_PIPE.PACK_MESSAGE (item  IN  NCHAR);
DBMS_PIPE.PACK_MESSAGE (item  IN  NUMBER);
DBMS_PIPE.PACK_MESSAGE (item  IN  DATE);
DBMS_PIPE.PACK_MESSAGE_RAW (item  IN  RAW);
DBMS_PIPE.PACK_MESSAGE_ROWID (item  IN  ROWID);

说明：item 要打包到本地消息缓冲区中的项。

例如：

oracle=# call DBMS_PIPE.PACK_MESSAGE('abc');
CALL

dbms_pipe.purge：此过程清空命名管道的内容。根据最近最少使用的算法，隐式创建的空管道会从共享全局区域中老化。因此，调用允许您释放与隐式创建的管道关联的内存

-- 语法
DBMS_PIPE.PURGE (pipename  IN  VARCHAR2);

说明：pipename要从中删除所有邮件的管道的名称。

例如：

oracle=# call dbms_pipe.purge ('abc');
CALL

dbms_pipe.receive_message：将消息复制到本地消息缓冲区

-- 语法
DBMS_PIPE.RECEIVE_MESSAGE (pipename     IN VARCHAR2,timeout      IN INTEGER      DEFAULT maxwait)RETURN INTEGER;

说明：pipename要在其上接收消息的管道的名称。timeout等待消息的时间（以秒为单位）。默认值为常量 ，定义为 86400000（1000 天）。超时 0 可让您在不阻塞的情况下进行读取。

例如：

oracle=# select dbms_pipe.receive_message('abc','10');
 RECEIVE_MESSAGE 
-----------------
               1
(1 行记录)

dbms_pipe.remove_pipe：删除显式创建的管道。隐式创建的管道在为空时会自动删除。但是，只有通过调用 或关闭实例来显式创建的管道。在删除管道之前，将删除管道中所有未使用的记录。

-- 语法
DBMS_PIPE.REMOVE_PIPE (pipename  IN  VARCHAR2)RETURN INTEGER;

例如：

oracle=# select dbms_pipe.remove_pipe ('123');
 REMOVE_PIPE 
-------------
 
(1 行记录)

dbms_pipe.reset_buffer：此过程将定位指示器重置为 0。由于所有管道共享一个缓冲区，因此您可能会发现在使用新管道之前重置缓冲区很有用。这可确保首次尝试向管道发送消息时，不会无意中发送缓冲区中剩余的过期消息。

-- 语法
DBMS_PIPE.RESET_BUFFER;

例如：

oracle=# exec dbms_pipe.reset_buffer ();
CALL

dbms_pipe.send_message：在命名管道上发送消息。该消息包含在本地消息缓冲区中，该缓冲区中充满了对管道的调用。可以使用 显式创建管道，否则，将隐式创建管道。

-- 语法
DBMS_PIPE.SEND_MESSAGE (pipename     IN VARCHAR2,timeout      IN INTEGER DEFAULT MAXWAIT,maxpipesize  IN INTEGER DEFAULT 8192)RETURN INTEGER;

说明：pipename放置消息的管道的名称。如果使用的是显式管道，则这是您在调用时指定的名称timeout尝试将消息放在管道上时等待的时间（以秒为单位）。默认值为常量 ，定义为 86400000（1000 天）。maxpipesize管道允许的最大大小（以字节为单位），默认值为 8192 字节。

例如：

oracle=# select dbms_pipe.send_message('123','10','800');
 SEND_MESSAGE 
--------------
            0
(1 行记录)

dbms_pipe.unique_session_name：用于接收的名称在当前连接到数据库中所有会话唯一的。从同一会话对此函数的多次调用始终返回相同的值。您可能会发现使用此函数为您的调用提供参数非常有用

-- 语法
DBMS_PIPE.UNIQUE_SESSION_NAMERETURN VARCHAR2;

例如：

oracle=# select dbms_pipe.unique_session_name();
 UNIQUE_SESSION_NAME 
---------------------
 PG$PIPE$1$15850
(1 行记录)

dbms_pipe.unpack_message：从缓冲区中检索项，调用以将管道信息放在本地缓冲区中后。

-- 语法
DBMS_PIPE.UNPACKMESSAGE (item  OUT VARCHAR2);
DBMS_PIPE.UNPACK_MESSAGE (item  OUT NCHAR);
DBMS_PIPE.UNPACK_MESSAGE (item  OUT NUMBER);
DBMS_PIPE.UNPACK_MESSAGE (item  OUT DATE);
DBMS_PIPE.UNPACK_MESSAGE_RAW (item  OUT RAW);
DBMS_PIPE.UNPACK_MESSAGE_ROWID (item  OUT ROWID);

说明：item：用于从本地消息缓冲区接收下一个解压缩项的参数。

例如：

oracle=# call dbms_pipe.unpack_message('raw');
CALL

DBMS_RANDOM

DBMS_RANDOM包提供了一个内置的随机数生成器，不适用于加密

oracle=# \df dbms_random.*
                                             函数列表
  架构模式   |    名称    |   结果数据类型   |                参数数据类型                 | 类型
-------------+------------+------------------+---------------------------------------------+------
 dbms_random | __value    | double precision |                                             | 函数
 dbms_random | __value    | double precision | low double precision, high double precision | 函数
 dbms_random | initialize |                  | IN integer                                  | proc
 dbms_random | normal     | double precision |                                             | 函数
 dbms_random | random     | integer          |                                             | 函数
 dbms_random | seed       |                  | IN integer                                  | proc
 dbms_random | seed       |                  | IN text                                     | proc
 dbms_random | string     | text             | opt text, len integer                       | 函数
 dbms_random | terminate  |                  |                                             | proc
 dbms_random | value      | numeric          |                                             | 函数
 dbms_random | value      | numeric          | low numeric, high numeric                   | 函数
(11 行记录)

dbms_random.__value

例如：

oracle=# select dbms_random.__value( );
      __VALUE       
--------------------
 0.1461493349634111
(1 行记录)

dbms_random.initialize：初始化生成器

-- 语法
DBMS_RANDOM.INITIALIZE (val  IN  BINARY_INTEGER);

说明：val用于生成随机数的种子号

例如：

oracle=# call dbms_random.initialize('10');
CALL

dbms_random.random：将生成一个随机数

-- 语法
DBMS_RANDOM.RANDOMRETURN binary_integer;

说明：binary_integer返回大于或等于 -power（2，31） 且小于 power（2，31） 的随机整数

例如：

oracle=# select dbms_random.random ();
   RANDOM   
------------
 -421055388
(1 行记录)

dbms_random.seed：将重置种子

-- 语法
DBMS_RANDOM.SEED (val  IN  BINARY_INTEGER);
DBMS_RANDOM.SEED (val  IN  VARCHAR2);

说明：val用于生成随机数的种子号或字符串

例如：

oracle=# call dbms_random.seed ('123');
CALL

dbms_random.string：获取一个随机字符串。

-- 语法
DBMS_RANDOM.STRINGopt  IN  CHAR,len  IN  NUMBER)RETURN VARCHAR2;

说明：opt指定返回字符串的外观：'u'， 'U' - 以大写字母字符返回字符串'l'， 'L' - 以小写字母字符返回字符串'a'， 'A' - 以混合大小写字母字符返回字符串"x"、"X" - 以大写字母数字字符返回字符串'p'， 'P' - 返回任何可打印字符的字符串。否则，返回字符串为大写字母字符。Len返回字符串长度

例如：

oracle=# select dbms_random.string ('p','200');
                                                                              STRING                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 (09Y^ct#IIGf"#!8V1V$X&xFr]Mk}9}guK A[rsNZ7p})}K#||4Efmn/OzJm!*SIcA,RN})HJK&c*zBhq?>mbcBqG|_dg; {=,'6u>Z2Gbn'J;O<kYcD8kVC8$ycXy=1R#8:/<ZdxgToU't35_JqN|43apmwn(r88aM(pK"Wq%:KV4Z<RI0)nwWOx7"KR3B|gZh^Oyr:
(1 行记录)

dbms_random.terminate：完成包后，调用该过程，终止程序。

-- 语法
DBMS_RANDOM.TERMINATE;

例如：

oracle=# call DBMS_RANDOM.TERMINATE ( );
CALL

dbms_random.value：得到一个随机数，大于或等于 0 且小于 1，小数点右侧有 38 位数字（38 位精度）。

-- 语法
DBMS_RANDOM.VALUERETURN NUMBER;

DBMS_RANDOM.VALUE(low IN NUMBER,high IN NUMBER)RETURN NUMBER;

说明：low从中生成随机数的范围内的最低数字。生成的数字可能等于lowhigh低于该值可生成随机数的最高数字。生成的数字将小于high

例如：

oracle=# select DBMS_RANDOM.VALUE( );
       VALUE       
-------------------
 0.729717935435474
(1 行记录)

DBMS_UTILITY

DBMS_UTILITY包提供了各种实用程序子程序。

oracle=# \df dbms_utility.*
                                                   函数列表
   架构模式   |        名称         |   结果数据类型    |                 参数数据类型                 | 类型
--------------+---------------------+-------------------+----------------------------------------------+------
 dbms_utility | canonicalize        | character varying | name_in character varying, canon_len integer | 函数
 dbms_utility | current_instance    | numeric           |                                              | 函数
 dbms_utility | exec_ddl_statement  |                   | IN parse_string character varying            | proc
 dbms_utility | format_call_stack   | text              |                                              | 函数
 dbms_utility | format_call_stack   | text              | text                                         | 函数
 dbms_utility | get_cpu_time        | text              |                                              | 函数
 dbms_utility | get_time            | integer           |                                              | 函数
 dbms_utility | is_cluster_database | boolean           |                                              | 函数
(8 行记录)

dbms_utility.format_call_stack：格式化当前调用堆栈。这可以用于任何存储过程或触发器，以访问调用堆栈。

-- 语法
DBMS_UTILITY.FORMAT_CALL_STACKRETURN VARCHAR2;

例如：

oracle=# select DBMS_UTILITY.FORMAT_CALL_STACK ( );
     FORMAT_CALL_STACK      
----------------------------
 ----- SPL Call Stack -----+
   object     line  object +
   handle   number  name   +
 
(1 行记录)

dbms_utility.get_time：确定当前时间（以百分之一秒为单位）。此子程序主要用于确定经过的时间。子程序被调用两次 - 在某个过程的开始和结束时 - 然后从第二个（后来的）数字中减去第一个（较早）数字以确定经过的时间。

-- 语法
DBMS_UTILITY.GET_TIMERETURN NUMBER;

例如：

oracle=# select dbms_utility.get_time ( );
 GET_TIME 
----------
    63226
(1 行记录)

UTL_FILE

UTL_FILE包使用该软件包，PL / SQL程序可以读取和写入操作系统文本文件。 提供受限版本的操作系统流文件 I/O文件位置和文件名参数作为单独的字符串提供给函数，以便可以根据可访问目录对象视图指定的可访问目录列表检查文件位置。文件位置和名称必须共同表示系统上的合法文件名，并且目录必须是可访问的。可访问目录的子目录不一定也可访问;它也必须使用与对象匹配的完整路径名来指定。

oracle=# \df utl_file.*

（19 行记录，参数数据类型列较长，转 markdown 表格以适配 PDF 排版；内容与终端输出一致）

架构模式	名称	结果数据类型	参数数据类型	类型
utl_file	__fclose	utl_file.file_type	file utl_file.file_type	函数
utl_file	fclose		INOUT file utl_file.file_type	proc
utl_file	fclose_all			proc
utl_file	fcopy		IN src_location text, IN src_filename text, IN dest_location text, IN dest_filename text, IN start_line integer DEFAULT 1, IN end_line integer DEFAULT NULL::integer	proc
utl_file	fflush		IN file utl_file.file_type	proc
utl_file	fgetattr		IN location text, IN filename text, OUT fexists boolean, OUT file_length bigint, OUT blocksize integer	proc
utl_file	fopen	utl_file.file_type	location text, filename text, open_mode text, max_linesize integer DEFAULT 1024	函数
utl_file	fopen	utl_file.file_type	location text, filename text, open_mode text, max_linesize integer, encoding name	函数
utl_file	fremove		IN location text, IN filename text	proc
utl_file	frename		IN location text, IN filename text, IN dest_dir text, IN dest_file text, IN overwrite boolean DEFAULT false	proc
utl_file	get_line		IN file utl_file.file_type, INOUT buffer text, IN len integer DEFAULT NULL::integer	proc
utl_file	get_nextline		IN file utl_file.file_type, INOUT buffer text	proc
utl_file	is_open	boolean	file utl_file.file_type	函数
utl_file	new_line		IN file utl_file.file_type, IN lines integer DEFAULT 1	proc
utl_file	put		IN file utl_file.file_type, IN buffer anyelement	proc
utl_file	put		IN file utl_file.file_type, IN buffer text	proc
utl_file	put_line		IN file utl_file.file_type, IN buffer text, IN autoflush boolean DEFAULT false	proc
utl_file	putf		IN file utl_file.file_type, IN format text, IN arg1 text DEFAULT NULL::text, IN arg2 text DEFAULT NULL::text, IN arg3 text DEFAULT NULL::text, IN arg4 text DEFAULT NULL::text, IN arg5 text DEFAULT NULL::text	proc
utl_file	tmpdir	text		函数

使用前提：调用 utl_file 的文件操作（fopen/fcopy/fremove/frename 等）前，需先将目标目录注册到 utl_file.utl_file_dir 表中。注册时注意字段方向：dir 填实际路径，dirname 填别名，例如：

INSERT INTO utl_file.utl_file_dir (dir, dirname) VALUES ('/home/halo', 'LOCAL');

注册后，文件操作的 location 参数既可传实际路径（如 /home/halo），也可传别名（如 LOCAL）。未注册会报 UTL_FILE_INVALID_PATH: locality is not found in utl_file_dir table。

utl_file.fclose_all：将关闭会话的所有打开的文件句柄。这应用作紧急清理过程，例如，当 PL/SQL 程序在异常时退出时。

-- 语法

UTL_FILE.FCLOSE_ALL;

例如：

oracle=# call utl_file.fclose_all( );
CALL

utl_file.fcopy：将文件的连续部分复制到新创建的文件。

默认情况下，如果省略 了 and 参数，则会复制整个文件。源文件以读取模式打开。目标文件以写入模式打开。可以选择指定起始行号和结束行号，以便从源文件的中心选取一个部分进行复制。

-- 语法

UTL_FILE.FCOPY (
   src_location    IN VARCHAR2,
   src_filename    IN VARCHAR2,
   dest_location   IN VARCHAR2,
   dest_filename   IN VARCHAR2,
   start_line      IN BINARY_INTEGER DEFAULT 1,
   end_line        IN BINARY_INTEGER DEFAULT NULL);

说明：

• src_location：源文件的目录位置，从视图中（区分大小写）DIRECTORY_NAME ALL_DIRECTORIES
• src_filename：要复制的源文件
• dest_location：创建目标文件的目标目录
• dest_filename：从源文件创建的目标文件
• start_line：开始复制的行号。默认值为第一行 1
• end_line：停止复制的行号。默认值表示文件的结尾 NULL

例如：

oracle=# call utl_file.fcopy('/home/halo','hh','/home/halo','xx.txt');
CALL

说明：上例执行前，源目录 /home/halo 下须已存在源文件 hh（可通过 UTL_FILE.FOPEN 以写模式创建），否则会报 UTL_FILE_INVALID_PATH（文件不存在）。

utl_file.fopen：打开一个文件，可以指定最大行大小，最多可以同时打开50个文件

-- 语法

UTL_FILE.FOPEN (
   location     IN VARCHAR2,
   filename     IN VARCHAR2,
   open_mode    IN VARCHAR2,
   max_linesize IN BINARY_INTEGER DEFAULT 1024)
  RETURN FILE_TYPE;

说明：

• location：文件的目录位置。此字符串是目录对象名称，必须以大写形式指定。必须对此目录对象授予读取权限，用户才能运行 UTL_FILE.FOPEN。
• filename：文件名，包括扩展名（文件类型），不带目录路径。如果目录路径作为文件名的一部分提供，则将被忽略。在 Unix 上，文件名不能以 / 结尾。
• open_mode：指定如何打开文件。模式包括：
  • r — 阅读文本
  • w — 写文字
  • a — 附加文本
  • rb — 读取字节模式
  • wb — 写入字节模式
  • ab — 追加字节模式
  • 如果尝试打开指定 "a"/"ab" 的文件，但该文件不存在，则会在 open_mode write 模式下创建该文件。
• max_linesize：此文件的每行（包括换行符）的最大字节数（最小值 1，最大值 32767）。如果未指定，Oracle 将提供默认值 1024。

例如：

oracle=# select UTL_FILE.FOPEN('/home/halo','abc.txt','w','100') from dual ;
 FOPEN 
-------
     1
(1 行记录)

utl_file.fremove：将删除磁盘文件，前提需要足够的权限。

-- 语法

UTL_FILE.FREMOVE ( location IN VARCHAR2, filename IN VARCHAR2);

说明：

• location：文件的目录位置，发件人（区分大小写）DIRECTORY_NAME ALL_DIRECTORIES
• filename：要删除的文件的名称

例如：

oracle=# call utl_file.fremove('/home/halo','abc.txt');
CALL

utl_file.frename：将现有文件重命名为新名称

-- 语法

UTL_FILE.FRENAME （ src_location IN VARCHAR2， src_filename IN VARCHAR2， dest_location IN VARCHAR2， dest_filename IN VARCHAR2， OVERWOVER IN BOOLEAN DEFAULT FALSE）;

说明：

• src_location：源文件的目录位置，从视图中（区分大小写）DIRECTORY_NAME ALL_DIRECTORIES
• src_filename：要重命名的源文件
• dest_location：目标文件的目标目录，从视图中（区分大小写）DIRECTORY_NAME ALL_DIRECTORIES
• dest_filename：文件的新名称
• overwrite：默认值为 false。必须同时授予对源目录和目标目录的权限。可以使用该参数指定是否覆盖目标目录中存在文件。

例如：

oracle=# call UTL_FILE.FRENAME('/home/halo','G.txt','/home/halo','hh');
CALL

说明：执行前源目录须已存在源文件 G.txt，否则报错；overwrite 默认 false，若目标文件已存在且未置 overwrite=true 也会报错。

utl_file.is_open：测试文件句柄以查看它是否标识打开的文件。

-- 语法

UTL_FILE.IS_OPEN ( file  IN FILE_TYPE) RETURN BOOLEAN;

说明：

• file：调用返回的活动文件句柄

例如：

oracle=# select utl_file.is_open ('123') from dual;
 IS_OPEN 
---------
 f
(1 行记录)

自定义Package语法

Package是一组包含procedure、functions函数和游标等元素的组合。使用package来包装存储过程function/ procedure，包头(package)：包头部分申明包内数据类型，常量，变量，游标，子程序和异常错误处理，这些元素为包的公有元素，此包具有面向对象设计的特点，是对这些PL/SQL程序设计元素的封装。

执行环境说明：本章的 CREATE PACKAGE / CREATE PACKAGE BODY 等多行 PL/SQL 复合语句，需使用 hsql（Halo 的 oracle 风格客户端，路径 /u01/app/halo/product/dbms/16/bin/hsql）执行；psql 不识别语句末的 / 终止符，会按 ; 截断多行 PACKAGE 语句导致语法错误。下面示例输出均由 hsql 实际执行得到。

package函数和存储过程

oracle=# CREATE OR REPLACE PACKAGE pkg_a AS
FUNCTION f_test( f_a IN INTEGER , f_b IN INTEGER ) RETURN INTEGER;
PROCEDURE pro_test;
END pkg_a;
/
CREATE PACKAGE

oracle=# CREATE OR REPLACE PACKAGE BODY pkg_a AS
FUNCTION f_test( f_a IN INTEGER , f_b IN INTEGER )
RETURN INTEGER
AS
f_c INTEGER;
BEGIN 
f_c := f_a + f_b +5 ;
RETURN f_c;
END f_test;
PROCEDURE pro_test
AS 
a INTEGER;
BEGIN
a:= pkg_a.f_test(12,13);
END pro_test; 
END pkg_a;
/
CREATE PACKAGE BODY

package自定义类型

oracle=# CREATE TABLE  procedure_test(     
     ROWKEY    VARCHAR(200),
     COLUMNKEY VARCHAR(200),
     CELLVALUE VARCHAR(200),
     OLDVALUE  VARCHAR(200),
     TAG       VARCHAR(200)
);
CREATE TABLE

oracle=# INSERT INTO procedure_test VALUES('12345', '23456', '34567', '45678','56789');
INSERT 0 1

oracle=# CREATE OR REPLACE package pkg_b AS 
   PROCEDURE my_procedure;
   TYPE cur_pkg_type IS RECORD
   (
     ROWKEY    VARCHAR(200),
     COLUMNKEY VARCHAR(200),
     CELLVALUE VARCHAR(200),
     OLDVALUE  VARCHAR(200),
     TAG       VARCHAR(200)
   );
END pkg_b;
/
CREATE PACKAGE

oracle=# CREATE OR REPLACE PACKAGE BODY pkg_b AS 
  PROCEDURE my_procedure IS
  pkg_type cur_pkg_type;
  BEGIN
  SELECT * INTO pkg_type FROM  procedure_test AS t WHERE t.TAG = '56789';
  DBMS_OUTPUT.put_line(pkg_type.ROWKEY||' '||pkg_type.COLUMNKEY||' '||pkg_type.CELLVALUE||' '||pkg_type.OLDVALUE||' '||pkg_type.TAG);
  END my_procedure;
END pkg_b;
/
CREATE PACKAGE BODY

调用：

oracle=# BEGIN
pkg_b.my_procedure();
END;
/
12345 23456 34567 45678 56789
DO

Package游标

声明游标

游标是一种数据访问机制。可以将游标简单的看成是查询的结果集的一个指针，可以根据需要在结果集上面来回滚动，浏览需要的数据。

说明：下面 INSERT INTO my_procedure_test 用到的 my_procedure_test 表，手册原文未给出建表语句，此处按 procedure_test 的结构补建：

oracle=# CREATE TABLE my_procedure_test(
     ROWKEY    VARCHAR(200),
     COLUMNKEY VARCHAR(200),
     CELLVALUE VARCHAR(200),
     OLDVALUE  VARCHAR(200),
     TAG       VARCHAR(200)
);
CREATE TABLE

oracle=# INSERT INTO my_procedure_test VALUES('ROWKEY', 'COLUMNKEY', 'CELLVALUE', 'OLDVALUE','TAG');
INSERT 0 1

oracle=# CREATE OR REPLACE PACKAGE pkg_c AS
TYPE pkg_type_cursor IS REF CURSOR;
PROCEDURE my_procedure;
END pkg_c;
/
CREATE PACKAGE

oracle=# CREATE OR REPLACE PACKAGE BODY pkg_c
AS
PROCEDURE my_procedure
IS
cur_cursor pkg_type_cursor;
cur_rowtype procedure_test%ROWTYPE;
BEGIN
OPEN cur_cursor FOR SELECT * FROM procedure_test ;
LOOP 
FETCH cur_cursor INTO cur_rowtype;
EXIT WHEN NOT FOUND;
RAISE NOTICE '% % % % %',cur_rowtype.rowkey,cur_rowtype.columnkey,cur_rowtype.cellvalue,cur_rowtype.oldvalue,cur_rowtype.tag;
END LOOP;
CLOSE cur_cursor;
END ;
END pkg_c;
/
CREATE PACKAGE BODY

oracle=# DECLARE
cur_cursor pkg_c.pkg_type_cursor;
BEGIN
RAISE notice 'a';
END;
/
注意:  a
DO

调用：

oracle=# BEGIN
pkg_c.my_procedure();
END;
/
注意:  12345 23456 34567 45678 56789
DO

另外一种游标

oracle=# CREATE OR REPLACE PACKAGE pkg_d IS
    CURSOR cur_cursor IS SELECT * FROM my_procedure_test;
    PROCEDURE my_procedure;
END;
/
CREATE PACKAGE

oracle=# CREATE OR REPLACE PACKAGE BODY pkg_d IS
    PROCEDURE my_procedure IS
cur_rowtype cur_cursor%ROWTYPE;
    BEGIN
OPEN cur_cursor;
LOOP
FETCH cur_cursor INTO cur_rowtype;
EXIT WHEN NOT FOUND; 
RAISE NOTICE '% % % % %',cur_rowtype.rowkey,cur_rowtype.columnkey,cur_rowtype.cellvalue,cur_rowtype.oldvalue,cur_rowtype.tag;
END LOOP;
CLOSE cur_cursor;
    END;
END pkg_d;
/
CREATE PACKAGE BODY

调用：

oracle=# BEGIN
pkg_d.my_procedure();
END;
/
注意:  ROWKEY COLUMNKEY CELLVALUE OLDVALUE TAG
DO

Package中的变量/异常/常量

变量：PACKAGE相当于JAVA中的一个class，在运行的时候被实例化，包变量就像实例中的实例变量一样。在Oracle中对包一个会话调用中，包变量是该会话中的"全局"变量。

创建：

oracle=# CREATE TABLE system_parameters (
  audit_on  VARCHAR(1) NOT NULL,
  trace_on  VARCHAR(1) NOT NULL,
  debug_on  VARCHAR(1) NOT NULL
);
CREATE TABLE

oracle=# INSERT INTO system_parameters VALUES ('Y', 'N', 'N');
INSERT 0 1

oracle=# CREATE OR REPLACE PACKAGE pkg_e AS
audit_on_  system_parameters.audit_on%TYPE;
trace_on_ system_parameters.trace_on%TYPE;
debug_on_  system_parameters.debug_on%TYPE;
PROCEDURE pro_test;
END pkg_e;
/
CREATE PACKAGE

oracle=# CREATE OR REPLACE PACKAGE BODY pkg_e AS
PROCEDURE pro_test AS
BEGIN
  SELECT audit_on,
         trace_on,
         debug_on
  INTO   audit_on_,
         trace_on_,
         debug_on_
  FROM   system_parameters;
END pro_test;
END pkg_e;
/
CREATE PACKAGE BODY

调用（以下 call 与 BEGIN 块须在同一 hsql 会话内执行：包变量是会话级的，pro_test 写入的值仅供同一会话的后续语句读取）：

oracle=# call pkg_e.pro_test();
CALL

oracle=# BEGIN
  RAISE NOTICE 'Show global data';
  RAISE NOTICE 'audit_on: %' , pkg_e.audit_on_;
  RAISE NOTICE 'trace_on: %' , pkg_e.trace_on_;
  RAISE NOTICE 'debug_on: %' , pkg_e.debug_on_;
  RAISE NOTICE'Reset global data';
  pkg_e.audit_on_ := 'N';
  pkg_e.trace_on_ := 'Y';
  pkg_e.debug_on_ := 'Y';
  RAISE NOTICE 'audit_on: %' , pkg_e.audit_on_;
  RAISE NOTICE 'trace_on: %' , pkg_e.trace_on_;
  RAISE NOTICE 'debug_on: %' , pkg_e.debug_on_;
END;
/
注意:  Show global data
注意:  audit_on: Y
注意:  trace_on: N
注意:  debug_on: N
注意:  Reset global data
注意:  audit_on: N
注意:  trace_on: Y
注意:  debug_on: Y
DO

package异常

自定义异常

新功能允许我们定义自己的异常。与预定义异常不同的是，用户自定义异常必须声明，并且需要用 RAISE 语句显式地抛出。

oracle=# DECLARE
   v_student_id    int := -20;
   v_total_courses EXCEPTION;
BEGIN
if v_student_id < 0 THEN
raise v_total_courses; 
end if;
exception  
when v_total_courses then 
dbms_output.put_line('v_total_courses');
end;
/
v_total_courses
DO

package常量

常量指的是不会变化的量，一旦赋值，就不能修改值。

oracle=# CREATE OR REPLACE PACKAGE PG_CONSTANT2 AS
C_CODE_ERROR constant VARCHAR2(10) := 'ERROR';
END PG_CONSTANT2;                                     
/
CREATE PACKAGE

oracle=# BEGIN
raise notice '%',PG_CONSTANT2.C_CODE_ERROR;
PG_CONSTANT2.C_CODE_ERROR:='a';
end;
/
错误:  变量"c_code_error"被声明为常量
第3行  PG_CONSTANT2.C_CODE_ERROR := 'a';
       ^
查询:  BEGIN
raise notice '%',PG_CONSTANT2.C_CODE_ERROR;
PG_CONSTANT2.C_CODE_ERROR := 'a';
end;

PL/SQL 基础语法

本节介绍 Oracle 兼容模式下的 PL/SQL 基础语法，包括匿名块、存储过程、函数、异常处理与游标。

说明：PL/SQL 块需使用 hsql 执行（语句末以 / 结束），提示符为 oracle=#，块内续行为 oracle$#。

匿名块

以 DECLARE 或 BEGIN 开始，每次提交都被编译。匿名块没有名称，不作为数据库对象存储，适合动态创建和执行一次性的过程代码：

oracle=# DECLARE
oracle$# a INTEGER;
oracle$# BEGIN
oracle$# a:=12;
oracle$# RAISE NOTICE 'a : %',a;
oracle$# END;
oracle$# /
注意:  a : 12
DO

存储过程（Procedure）

存储过程通过参数执行操作，没有返回值：

oracle=# CREATE OR REPLACE PROCEDURE pro_test()
oracle-# AS
oracle$# BEGIN
oracle$#   dbms_output.put_line('aaaa ');
oracle$# END;
oracle$# /
CREATE PROCEDURE
oracle=# exec pro_test();
aaaa
CALL

函数（Function）

函数有返回值，通常返回单个值：

oracle=# CREATE OR REPLACE FUNCTION add_test(a IN INTEGER, b IN INTEGER)
oracle-# RETURN INTEGER
oracle-# AS
oracle$# c INTEGER;
oracle$# BEGIN
oracle$# c:=a+b;
oracle$# RETURN c;
oracle$# END;
oracle$# /
CREATE FUNCTION
oracle=# SELECT add_test(3, 5) FROM dual;
 ADD_TEST
----------
        8
(1 行记录)

异常处理

PL/SQL 通过 EXCEPTION 块捕获异常。Halo 支持 ZERO_DIVIDE、VALUE_ERROR 等预定义异常，以及 RAISE_APPLICATION_ERROR 抛出自定义错误。

ZERO_DIVIDE 表示除以零：

oracle=# DECLARE
oracle$#    v_num1 integer := 201;
oracle$#    v_num2 integer := 0;
oracle$#    v_result number;
oracle$# BEGIN
oracle$#    v_result := v_num1 / v_num2;
oracle$#    DBMS_OUTPUT.PUT_LINE ('v_result: '||v_result);
oracle$# EXCEPTION
oracle$#    WHEN ZERO_DIVIDE THEN
oracle$#        DBMS_OUTPUT.PUT_LINE ('A number cannot be divided by zero.');
oracle$# END;
oracle$# /
A number cannot be divided by zero.
DO

VALUE_ERROR 表示数字或值转换错误（如字符串转数字失败）：

oracle=# declare
oracle$# pnum number;
oracle$# begin
oracle$# pnum:='abc';
oracle$# exception
oracle$# when VALUE_ERROR then dbms_output.put_line('算术或转化错误');
oracle$# when others then dbms_output.put_line('其他例外');
oracle$# end;
oracle$# /
算术或转化错误
DO

RAISE_APPLICATION_ERROR 用于从子程序中抛出自定义错误信息（错误码 -20000 起为用户自定义区间）：

oracle=# declare
oracle$#    v_student_id integer := -100;
oracle$#    v_total_courses number;
oracle$# begin
oracle$#    if v_student_id < 0 then
oracle$#       raise_application_error (-20000, 'an id cannot be negative');
oracle$#    else
oracle$#       select count(*)
oracle$#         into v_total_courses
oracle$#         from enrollment
oracle$#        where student_id = v_student_id;
oracle$#        dbms_output.put_line ('the student is registered for '||v_total_courses||' courses');
oracle$#    end if;
oracle$# end;
oracle$# /
错误:  HERR-20000: an id cannot be negative
背景:  在RAISE的第12行的PL/oraSQL函数raise_application_error(integer,text)
SQL 语句 "raise_application_error (-20000, 'an id cannot be negative')"
在CALL的第6行的PL/oraSQL函数inline_code_block

动态 SQL（OPEN FOR）

支持动态 SQL 中的 OPEN FOR 语句，将游标变量与动态构造的 SQL 语句关联：

oracle=# DECLARE
oracle$#   TYPE zip_cur_type IS REF CURSOR;
oracle$#   zip_cur zip_cur_type;
oracle$#   v_table_name VARCHAR2(20) := 'student';
oracle$#   sql_stmt VARCHAR2(500);
oracle$#   v_zip VARCHAR2(5);
oracle$#   v_total NUMBER;
oracle$#   v_count NUMBER;
oracle$# BEGIN
oracle$#   DBMS_OUTPUT.PUT_LINE ('Totals from '||v_table_name||' table');
oracle$#   sql_stmt := 'SELECT zip, COUNT(*) total FROM '||v_table_name||' GROUP BY zip';
oracle$#   v_count := 0;
oracle$#   OPEN zip_cur FOR sql_stmt;
oracle$#   LOOP
oracle$#     FETCH zip_cur INTO v_zip, v_total;
oracle$#     EXIT WHEN zip_cur%NOTFOUND;
oracle$#     v_count := v_count + 1;
oracle$#     IF v_count <= 10 THEN
oracle$#       DBMS_OUTPUT.PUT_LINE ('Zip code: '||v_zip||' Total: '||v_total);
oracle$#     END IF;
oracle$#   END LOOP;
oracle$#   CLOSE zip_cur;
oracle$# EXCEPTION
oracle$#   WHEN OTHERS THEN
oracle$#   DBMS_OUTPUT.PUT_LINE ('ERROR: '||SUBSTR(SQLERRM, 1, 200));
oracle$# END;
oracle$# /
Totals from student table
Zip code: 11203 Total: 1
Zip code: 11412 Total: 3
Zip code: 07304 Total: 2
DO

游标 FOR 循环

可在游标 FOR 循环中直接引用子查询，循环变量无需提前声明：

oracle=# BEGIN
oracle$#    FOR rec IN (SELECT *
oracle$#                  FROM dual)
oracle$#    LOOP
oracle$#       DBMS_OUTPUT.put_line (rec.dummy);
oracle$#    END LOOP;
oracle$# END;
oracle$# /
X
DO