PLSQL调试技巧

1：调试技巧

大家调试游标的时候通常使用的都是直接在存储过程里DBMS_OUTPUT包来进行打印信息的，示例如下：

CREATE OR REPLACE PROCEDURE SP_EMP_FOR(P_DEPTNO IN NUMBER) IS

  TYPE EMP_ROW_TYPE IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;

  EMP_ROW  EMP_ROW_TYPE;

  I        INT := 1;

  EMP_TYPE EMP%ROWTYPE;

BEGIN

  FOR TMPROW IN (SELECT *

                   FROM EMP

                  WHERE DEPTNO = P_DEPTNO

                  ORDER BY EMP.EMPNO) LOOP

    EMP_ROW(I) := TMPROW;

    I := I + 1;

  END LOOP;

  FOR I IN 1 .. EMP_ROW.COUNT LOOP

    EMP_TYPE := EMP_ROW(I);

    DBMS_OUTPUT.PUT_LINE('empno :' || EMP_TYPE.EMPNO || ' ename : ' ||

                         EMP_TYPE.ENAME || '   hiredate :' ||

                         EMP_TYPE.HIREDATE);

  END LOOP;

END SP_EMP_FOR;

SQL> set serveroutput on

SQL> EXEC SP_EMP_FOR(10);

empno :7782 ename : CLARK   hiredate :09-JUN-81

empno :7839 ename : KING   hiredate :17-NOV-81

empno :7934 ename : MILLER   hiredate :23-JAN-82

PL/SQL procedure successfully completed.

2：特殊游标变量调试技巧

在存储过程调试时候大部分使用工具调试，例如PLSQL DEVELOP,但是打印所有的变量却有更方便的调试方法，如下示例可以打印出所有的变量，包括有游标里的数据：

CREATE OR REPLACE PROCEDURE SP_TEST(

                                    p_deptno IN NUMBER,

                                    P_OUTSTR OUT VARCHAR2,

                                    P_OUTINT OUT NUMBER,

                                    P_REF1   OUT SYS_REFCURSOR,

                                    P_REF2   OUT SYS_REFCURSOR) AS

BEGIN

  P_OUTSTR := 'scott';

  P_OUTINT := 1234;

  OPEN P_REF1 FOR

    SELECT EMPNO, ENAME, HIREDATE FROM EMP WHERE DEPTNO = p_deptno;

  OPEN P_REF2 FOR

    SELECT * FROM EMP WHERE DEPTNO = p_deptno;

END SP_TEST;

[[email protected] ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 7 19:14:04 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>   SET AUTOPRINT ON 

SQL>   VAR P_OUTSTR VARCHAR2(10);

SQL>   VAR P_OUTINT NUMBER;

SQL>   VAR P_REF1 REFCURSOR;

SQL>   VAR P_REF2 REFCURSOR;

SQL>   VAR p_deptno NUMBER;

SQL> EXEC SP_TEST(10,:P_OUTSTR, :P_OUTINT, :P_REF1, :P_REF2);

PL/SQL procedure successfully completed.

     EMPNO ENAME      JOB              MGR HIREDATE        SAL  COMM  DEPTNO

---------- ---------- --------- ---------- --------- ---------- ----------

      7782 CLARK      MANAGER         7839 09-JUN-81       2450       10

      7839 KING       PRESIDENT            17-NOV-81       5000       10

      7934 MILLER     CLERK           7782 23-JAN-82       1300       10

     EMPNO ENAME      HIREDATE

---------- ---------- ---------

      7369 SMITH      17-DEC-80

      7566 JONES      02-APR-81

      7788 SCOTT      19-APR-87

      7876 ADAMS      23-MAY-87

      7902 FORD       03-DEC-81

  P_OUTINT

----------

      1234

P_OUTSTR

--------

scott

注意：这里的SET AUTOPRINT ON 一定要开启。

3：使用对象类型，打印结果集

--创建自定义对象类型

CREATE TYPE EMP_TYPE AS OBJECT

(

  EMPNO    NUMBER,

  ENAME    VARCHAR2(20),

  HIREDATE DATE,

  DEPTNO   NUMBER

);

--创建对象类型

CREATE TYPE EMP_TEST AS TABLE OF EMP_TYPE;

--构造类型数据函数

CREATE OR REPLACE FUNCTION EMP_TABLE RETURN EMP_TEST AS

  V_RET EMP_TEST := EMP_TEST();

BEGIN

  FOR I IN 1 .. 10 LOOP

    V_RET.EXTEND;

    V_RET(I) := EMP_TYPE(I, 'Name' || I,Sysdate-i,10);

  END LOOP;

  RETURN V_RET;

END EMP_TABLE;

SQL> select * from table(EMP_TABLE); 

     EMPNO ENAME                HIREDATE      DEPTNO

---------- -------------------- --------- ----------

         1 Name1                07-JUN-12         10

         2 Name2                06-JUN-12         10

         3 Name3                05-JUN-12         10

         4 Name4                04-JUN-12         10

         5 Name5                03-JUN-12         10

         6 Name6                02-JUN-12         10

         7 Name7                01-JUN-12         10

         8 Name8                31-MAY-12         10

         9 Name9                30-MAY-12         10

        10 Name10               29-MAY-12         10

10 rows selected.

4：用SELECT BULK COLLECT INTO取得数据,返回嵌套表的例子

CREATE OR REPLACE FUNCTION EMP_TABLE_test RETURN EMP_TEST  

    AS  

       v_ret EMP_TEST:=EMP_TEST();  

    BEGIN  

       SELECT EMP_TYPE(EMP.EMPNO,EMP.ENAME,EMP.HIREDATE,EMP.DEPTNO)  

         BULK COLLECT INTO v_ret  

        FROM EMP;  

       RETURN v_ret;  

END EMP_TABLE_test;  

SQL> select * from table(EMP_TABLE_test);

     EMPNO ENAME                HIREDATE      DEPTNO

---------- -------------------- --------- ----------

      7369 SMITH                17-DEC-80         20

      7499 ALLEN                20-FEB-81         30

      7521 WARD                 22-FEB-81         30

      7566 JONES                02-APR-81         20

      7654 MARTIN               28-SEP-81         30

      7698 BLAKE                01-MAY-81         30

      7782 CLARK                09-JUN-81         10

      7788 SCOTT                19-APR-87         20

      7839 KING                 17-NOV-81         10

      7844 TURNER               08-SEP-81         30

      7876 ADAMS                23-MAY-87         20

      7900 JAMES                03-DEC-81         30

      7902 FORD                 03-DEC-81         20

      7934 MILLER               23-JAN-82         10

14 rows selected.