잉여생활~ ㅋ.,ㅋ

CREATE OR REPLACE PACKAGE pk_test AS
TYPE t_cur IS REF CURSOR RETURN test%ROWTYPE;
PROCEDURE sp_test(v_cur OUT t_cur);
END pk_test;
/

CREATE OR REPLACE PACKAGE BODY pk_test AS
PROCEDURE sp_test(v_cur OUT t_cur) IS
BEGIN
OPEN v_cur FOR SELECT * FROM test;
END sp_test;
END pk_test;
/



Set Spcmd = New ADODB.Command
Spcmd.ActiveConnection = cnn
Spcmd.CommandType = adCmdStoredProc
Spcmd.CommandText = "pk_test.sp_test" '패키지명.프로시져명
Spcmd.Parameters(0).Direction = adParamOutput '받는 파라미터명
Set rst = Spcmd.Execute



* 다른 방법
-- 패키지머리 생성(정의)
CREATE OR REPLACE PACKAGE pk_test AS
TYPE t_cur IS REF CURSOR; --리턴받을 변수에 타입선언
PROCEDURE sp_test(i_table in varchar2,v_cur OUT t_cur); --수행할 프로시져 선언
END pk_test;
/

-- 패키지몸체 생성
CREATE OR REPLACE PACKAGE BODY pk_test AS
PROCEDURE sp_test(i_table in varchar2,v_cur OUT t_cur) IS
BEGIN
OPEN v_cur FOR 'SELECT * FROM '||i_table; --다이내믹 커서사용
END sp_test;
END pk_test;
/


VB사용시 예제>>
Private cnn As ADODB.Connection
Private rst As New ADODB.Recordset

Set Spcmd = New ADODB.Command '새 객체 생성
Spcmd.ActiveConnection = cnn '연결
Spcmd.CommandType = adCmdStoredProc '프로시져형태
Spcmd.CommandText = "pk_test.sp_test" '패키지프로시져명
Spcmd.Parameters.Append Spcmd.CreateParameter("i_table", adVarChar,
adParamInput, 10, "emp") '입력파라미터
Spcmd.Parameters(1).Direction = adParamOutput '출력파라미터
Set rst = Spcmd.Execute '수행