출처 : http://gipsi.egloos.com/968963
Oracle Package 사용법-VB
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 '수행
Posted by 나비 나비:D

BLOG main image
by 나비:D

공지사항

카테고리

분류 전체보기 (278)
Programming? (0)
----------------------------- (0)
나비의삽질 (5)
Application (177)
SQL (51)
Web (27)
etc. (14)
Omnia (0)
---------------------------.. (0)

글 보관함

달력

«   2018/11   »
        1 2 3
4 5 6 7 8 9 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30  
Total : 1,014,183
Today : 237 Yesterday : 3,326