PLSQL调试技巧

╰半橙微兮° 2022-06-11 05:22 349阅读 0赞

**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;  
  
\[oracle@xml-ora1 ~\]$ 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.