了解DBMS_OUTPUT包

原创

maclean_007 2011-08-28 23:33:40 ©著作权

文章标签 BUFFER dbms_output OVERFLOW CATPROC.SQL DBMS_OUTPUT.DISABLE DBMS_OUTPUT.ENABLE 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者maclean_007的原创作品，请联系作者获取转载授权，否则将追究法律责任

DBMS_OUTPUT程序包是我们在Oracle开发过程中常用的一个包体，使用该包我们可以从存储过程、包或触发器发送信息(messages)。Oracle推荐在debug PL/SQL程序时使用该程序包，不推荐使用该包来做报表输出或其他格式化输出之用。概述 DBMS_OUTPUT包主要用于调试PL/SQL程序，或者在SQL*PLUS命令中显示信息(displaying message)和报表，譬如我们可以写一个简单的匿名PL/SQL程序块，而该块出于某种目的使用DBMS_OUTPUT包来显示一些信息。在该DBMS_OUTPUT包中存在2个存储过程，它们是PUT_LINE和PUT过程，使用这2个Procedure可以做到将信息存放到PL/SQL的Buffer中，以便其他的触发器、存储过程、程序包来读取。在独立的PL/SQL程序或匿名块中，我们还可以使用GET_LINES和GET这2个存储过程来将存放在PL/SQL Buffer中的信息输出(display)到屏幕。如果该DBMS_OUTPUT包被禁用了，那么所有对其子程序(subprogram)的调用都将被忽略。这样用户可以设计应用程序，仅在客户端程序能够处理这些信息的时候启用这些子程序。

安全模型 必须使用SYS用户运行$ORACLE_HOME/rdbms/admin/dbmsotpt.sql，该脚本会为DBMS_OUTPUT创建同义词，并将该包的执行权限赋予PUBLIC角色。 操作提示

若不调用GET_LINE函数，或者不在SQL*PLUS中将信息(information)输出到屏幕的话，那么缓存的信息(buffered message)最终将被忽略。
SQL*PLUS会在SQL语句或匿名PL/SQL块调用结束后调用GET_LINES过程
在SQL*PLUS中输入SET SERVEROUTPUT ON，将启动下面语句的效果:

DBMS_OUTPUT.ENABLE (buffer_size => NULL);
输出不再有限制(no limit on the output)

不推荐在应用程序代码中调用ENABLE或DISABLE过程，因为这将导致如SQL*PLUS这种外部工具无法正常控制输出与否。

注意使用DBMS_OUTPUT传送的message在实际执行该DBMS_OUTPUT的子程序或触发器执行完成之前都不会实际被发送。这也就导致了在整个运行过程中不会有信息被写出，所以用DBMS_OUTPUT包做日志输出的话只能等到整个子程序结束才会有日志出现。 程序异常 DBMS_OUTPUT子程序可能引发ORA-20000错误，同时其OUTPUT存储过程可能返回以下错误:

DBMS_OUTPUT 可能遇到的错误

错误号	描述
`ORU-10027:`	Buffer缓存溢出
`ORU-10028:`	行长溢出

规则和限制

最大的单行长度是32767 bytes字节
默认的buffer大小时20000 bytes字节，最小的buffer为2000 bytes字节，最大没有限制

使用示例 示例1：在触发器生成输出 我们可以使用一个触发器来打印正在调试进程的一些信息，譬如你可以在触发器中调用以下代码:

DBMS_OUTPUT.PUT_LINE('I got here:'||:new.col||' is the new value');

若启用了DBMS_OUTPUT包，那么由PUT_LINE所生成的文本将被缓存到Buffer中，之后我们可以通过以下代码获取该Buffer中的信息:

BEGIN
   DBMS_OUTPUT.GET_LINE(:buffer, :status);
END;

你还可以额外地将buffer显示在屏幕上。可以反复调用GET_LINE直到status返回非零值。处于性能的考虑，推荐使用GET_LINES过程，该过程将返回一个行的数组(an array of lines)。 示例2:调试存储过程和触发器 DBMS_OUTPUT包常被用于调试(debug)存储过程和触发器。该包也可以用于获取对象信息并且格式化地输出。以下函数将查询EMP表并返回某个部门的总薪水，该函数包含了多次对PUT_LINE过程的调用。假设EMP表的数据如下：

EMPNO SAL COMM DEPT
----- ------- -------- -------
1002 1500 500 20
1203 1000 30
1289 1000 10
1347 1000 250 20

函数代码如下：

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com

CREATE FUNCTION dept_salary (dnum NUMBER) RETURN NUMBER IS
   CURSOR emp_cursor IS
      SELECT sal, comm FROM emp WHERE deptno = dnum;
   total_wages    NUMBER(11, 2) := 0;
   counter        NUMBER(10) := 1;
BEGIN

   FOR emp_record IN emp_cursor LOOP
      emp_record.comm := NVL(emp_record.comm, 0);
      total_wages := total_wages + emp_record.sal
         + emp_record.comm;
      DBMS_OUTPUT.PUT_LINE('Loop number = ' || counter ||
         '; Wages = '|| TO_CHAR(total_wages));  /* Debug line */
      counter := counter + 1; /* Increment debug counter */
   END LOOP;
   /* Debug line */
   DBMS_OUTPUT.PUT_LINE('Total wages = ' ||
     TO_CHAR(total_wages));
   RETURN total_wages;

假设用户在SQL*PLUS中执行以下语句:

SET SERVEROUTPUT ON
VARIABLE salary NUMBER;
EXECUTE :salary := dept_salary(20);

用户将在屏幕输出中看到以下信息:

Loop number = 1; Wages = 2000
Loop number = 2; Wages = 3250
Total wages = 3250

PL/SQL procedure successfully executed.

示例3：检索对象相关信息 以下示例中用户使用EXPLAIN PLAN命令以检索某条语句的执行计划信息，这些信息存放在plan_table表中。用户还为语句分配了语句ID(statement ID)。示例的EXPLAIN_OUT存储过程从表上检索信息并嵌套地格式化输出。

/****************************************************************/
/* Create EXPLAIN_OUT procedure. User must pass STATEMENT_ID to */
/* to procedure, to uniquely identify statement.                */
/****************************************************************/
CREATE OR REPLACE PROCEDURE explain_out
   (statement_id IN VARCHAR2) AS

   -- Retrieve information from PLAN_TABLE into cursor EXPLAIN_ROWS.

   CURSOR explain_rows IS
      SELECT level, id, position, operation, options,
         object_name
      FROM plan_table
      WHERE statement_id = explain_out.statement_id
      CONNECT BY PRIOR id = parent_id
         AND statement_id = explain_out.statement_id
      START WITH id = 0
       ORDER BY id;

BEGIN

   -- Loop through information retrieved from PLAN_TABLE:

   FOR line IN explain_rows LOOP

      -- At start of output, include heading with estimated cost.

      IF line.id = 0 THEN
         DBMS_OUTPUT.PUT_LINE ('Plan for statement '
            || statement_id
            || ', estimated cost = ' || line.position);
      END IF;

      -- Output formatted information. LEVEL determines indention level.

      DBMS_OUTPUT.PUT_LINE (lpad(' ',2*(line.level-1)) ||
         line.operation || ' ' || line.options || ' ' ||
         line.object_name);
   END LOOP;

END;

数据结构 DBMS_OUTPUT包声明了2中集合类型，以便GET_LINES函数使用。 TABLE Types CHARARR Table Type OBJECT Types DBMSOUTPUT_LINESARRAY Object Type CHARARR Table Type This package type is to be used with the GET_LINES Procedure to obtain text submitted through the PUT Procedure and PUT_LINE Procedure. Syntax TYPE CHARARR IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER; DBMSOUTPUT_LINESARRAY Object Type This package type is to be used with the GET_LINES Procedure to obtain text submitted through the PUT Procedure and PUT_LINE Procedure. Syntax TYPE DBMSOUTPUT_LINESARRAY IS VARRAY(2147483647) OF VARCHAR2(32767); DBMS_OUTPUT包子程序摘要

子程序

描述

DISABLE 存储过程

禁用消息输出

ENABLE