PLSQL Developer调用和测试一个存储过程非常简单, 找到一个存储过程后, 在快捷菜单中选择Test菜单项目, 输入存储过程的参数, 就可以了.
1. 在SQLPlus中调用这个过程的方法
用SQLPlus来调用/测试一个自定义的存储过程, 相对比较麻烦, 不像用PLSQL Developer方便. 尤其是如果该存储过程返回一个记录集, 我用SQLPLUS一直没有调用成功过. 最主要的原因是: 原来在SQLPlus中不知道怎样使用bind variable. 下面给出一个例子.
假如在PKG_1包中有 PROC_1存储过程, 它接受一个Input参数, 然后返回一个记录集.
PROCEDURE PROC_1(IN_SRVY_ID IN VARCHAR , OUT_TABLE OUT SYS_REFCURSOR )
IS
BEGIN
OPEN OUT_TABLE FOR
SELECT * FROM SRVY WHERE SRVY.SRVY_ID=IN_SRVY_ID;
END PROC_1;
在SQLPlus中调用这个过程的方法, 然后打印出结果集
SQL> VARIABLE CURSOR_1 REFCURSOR
SQL > VARIABLE IN_SRVY_D VARCHAR2 ( 5 )
SQL > EXEC :IN_SRVY_D := 'SU07'
SQL > EXEC PKG_1.PROC_1(:IN_SRVY_D, :CURSOR_1)
SQL >PRINT :CURSOR_1
http://www.indiastudychannel.com/resources/8519-SQL-SQL-Plus-Interview-Questions-with-Answers-Set.aspx
http://lbdwww.epfl.ch/f/teaching/courses/oracle8i/server.815/a66736/ch35.htm
2. 在Toad或SQLTools就不像SQLPLus那么容易了, 主要是在于打印记录集比较复杂
--假设emp表中有三个字段分别是 eid, ename, sal. -- select eid, ename, sal from emp;
create table emp
(
ename VARCHAR2 ( 5 ) not null ,
eid NUMBER ( 6 ) not null ,
sal NUMBER ( 6 )
);
create or replace package PKG_2
IS
TYPE c_return_table IS REF CURSOR ;
PROCEDURE SP_GET_EMP_INFO(IN_EMP_ID IN VARCHAR , OUT_TABLE OUT c_return_table);
END PKG_2;
create or replace package BODY PKG_2
IS
PROCEDURE SP_GET_EMP_INFO(IN_EMP_ID IN VARCHAR , OUT_TABLE OUT c_return_table)
BEGIN
OPEN OUT_TABLE FOR
SELECT eid, ename, sal FROM EMP WHERE eid= IN_EMP_ID;
END SP_GET_EMP_INFO ;
END PKG_2;
--***************************示例说明*************************************************
--在PLSQL Developer中,调用和显示一个返回记录集的存储过程非常简单(因为它提供了Test Procedure功能),
--但对于其他工具, 却没有这样的功能, 你必须自己写代码来调用, 然后显示
--***********************************************************************************
DECLARE
IN_EMP_ID emp.eid% TYPE;
OUT_C_RETURN_TABLE PKG_2.C_RETURN_TABLE;
--如果从一个Cursor中整条整条地取数据, 则下面的类型必须和Cursor中每个字段的类型都匹配, 这包括类型必须匹配和字段个数必须一样.
TYPE REC_EMP IS RECORD(
ename VARCHAR2(5),
eid NUMBER(6),
sal NUMBER(6)
);
EMP_ROW REC_EMP;
--如果Cursor的字段和emp的字段刚好一样, 你不必先定义一个Record类型, 直接声明EMP_ROW
--EMP_ROW emp%ROWTYPE ;
BEGIN
IN_EMP_ID := 10;
--STEP1: OPEN CURSOR BY EXECUTING STORED PROCEDURE
PKG_2.SP_GET_EMP_INFO(IN_EMP_ID, OUT_C_RETURN_TABLE);
--STEP1: OR OPEN CURSOR BY SELECT STATEMENT
/*
OPEN C_EMP FOR SELECT * FROM EMP where eid=IN_EMP_ID;
*/
LOOP
--STEP2: FETCH EVERY ROW FROM CURSOR
FETCH OUT_C_RETURN_TABLE
INTO EMP_ROW;
EXIT WHEN OUT_C_RETURN_TABLE% NOTFOUND;
--PRINT EVERY ROW
DBMS_OUTPUT.PUT_LINE(EMP_ROW.eid || ' - ' || EMP_ROW.ename);
END LOOP;
--STEP3: CLOSE CURSOR
CLOSE OUT_C_RETURN_TABLE;
DBMS_OUTPUT.PUT_LINE('EXECUTION OVER.');
END;
