PLSQL调试技巧

来源：互联网发布：win10软件注册表编辑：程序博客网时间：2024/05/17 10:04

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.