/* ASP.NET 오라클 연동방법
ODP.NET 받음..
Oracle.DataAccess.Client 모듈추가
--------------------------------------
오라클 패키지내에 커서 선언
프로시저 OUT 커서 선언
--------------------------------------
파라미터 커서타입 받음..
출력~
*/
CREATE OR REPLACE PACKAGE GET_CLUBMEMBER AS -- 패키지 헤더 선언.
TYPE T_CURSOR IS REF CURSOR; -- REF CURSOR 선언.
PROCEDURE GETCLUBMEMBER (cur_ClubMember OUT T_CURSOR); -- 프로시저 선언.
PROCEDURE TEST1 (pr_ename in newemp.ename%type,JOB OUT T_CURSOR);
END GET_CLUBMEMBER;
/
CREATE OR REPLACE PACKAGE BODY GET_CLUBMEMBER AS -- 패키지 BODY 선언.
PROCEDURE GETCLUBMEMBER
(cur_ClubMember OUT T_CURSOR) -- 프로시저 구현.
IS
BEGIN
OPEN cur_ClubMember FOR -- 커서 OPEN
SELECT * FROM newemp; -- 결과셋을 반환할 쿼리 작성.
END GETCLUBMEMBER;
-- 두번째
procedure TEST1
(pr_ename in newemp.ename%type,
JOB out T_CURSOR
)
is
begin
OPEN JOB FOR
select job from newemp
where ename = pr_ename;
end TEST1;
END GET_CLUBMEMBER;
/
--------------------------------------------------------------
using Oracle.DataAccess.Client;
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
OracleConnection conn = new OracleConnection("Data Source=ysc;User ID=scott;Password=tiger;");
// 저장 프로시저에 대한 명령을 만듭니다.
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "GET_CLUBMEMBER.test1";
cmd.CommandType = CommandType.StoredProcedure;
// REF CURSOR 매개 변수를 추가하여 결과 집합을 검색합니다.
cmd.Parameters.Add("pr_ename", OracleDbType.Varchar2, 10).Direction = ParameterDirection.Input;
cmd.Parameters.Add("job", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
cmd.Parameters["pr_ename"].Value = "SMITH";
// 연결을 열고 DataReader를 만듭니다.
conn.Open();
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
ListBox1.Items.Add(dr.GetString(0));
}
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
////////////////////////////////////////////////////////////////////////
//da.Fill(ds);
conn.Close();
}
}
[펌] http://blog.naver.com/chaser007?Redirect=Log&logNo=100035270594
