調用帶參數的預存程序，並返回結果集－－oracle

調用插入資料的預存程序：

// 調用預存程序來插入一條記錄BOOL CDBTestAppDlg::InsertRecord(){CAdoParameter param1, param2, param3;CAdoCommand comm;if(ConnTODB()){comm.SetConnection(m_pConnection);param1.SetSize(20);param1.SetName("C_Name");param1.SetDirection(adParamInput);param1.SetType(adVarChar);// varchar2param1.SetValue((CString)"hello113");// 這裡必須用類型轉換，預設的參數類型為bool，所以轉換會出錯comm.Append(param1.GetParameter());param2.SetName("C_Age");param2.SetDirection(adParamInput);param2.SetType(adInteger);// Integerparam2.SetValue(21);comm.Append(param2.GetParameter());param3.SetName("C_ExeTime");param3.SetDirection(adParamInput);param3.SetType(adVarChar);// varchar2param3.SetValue((CString)"2010-1-10");comm.Append(param3.GetParameter());comm.SetCommandText("Proc_Insert");comm.SetCommandType(adCmdStoredProc);try{comm.Execute();MessageBox("Procedure execute success!", "執行成功", MB_OK | MB_ICONINFORMATION);return TRUE;}catch (CException* e){char errorMessage[256];e->GetErrorMessage(errorMessage, 255);MessageBox(errorMessage);}}return FALSE;}

其中連資料庫的函數為：

BOOL CDBTestAppDlg::ConnTODB(){BOOL nResult = TRUE;if (m_pConnection == NULL){m_pConnection = new CAdoConnection;if (!m_pConnection->CreateInstance()){ MessageBox("建立資料庫執行個體失敗");delete m_pConnection;m_pConnection = NULL;return FALSE;}}if (m_pConnection->IsOpen())m_pConnection->Close();m_pConnection->SetConnectTimeOut(2);m_pRecordSet.SetAdoConnection(m_pConnection);if (!m_pConnection->Connection(m_sProvider)){//MessageBox("串連業務資料庫失敗");nResult = FALSE;}else{//MessageBox("串連業務資料庫成功");}return nResult;}

為了返回結果集，首先要建立一個包，再建立一個包體，代碼如下：

CREATE OR REPLACE Package pkg_GetResultas  Type myResult is REF CURSOR;                           -- 定義傳回值類型  Procedure getResult(age number, pResult out myResult);  -- 聲明pResult為輸出的結果集變數end pkg_GetResult;/CREATE OR REPLACE Package Body pkg_GetResultas  Procedure getResult(age number, pResult out myResult)  IS    sqlstr varchar2(200);  begin       if age = 0 then          open pResult for Select C_ID, C_Name, C_Age, C_InTime, C_Salary, C_ExeTime from T_Test;       else           sqlstr := 'Select C_ID, C_Name, C_Age, C_InTime, C_Salary, C_ExeTime from T_Test where C_Age=:w_age';          open pResult for sqlstr using age;       end if;  end getResult;end pkg_GetResult;

在vc中調用這個包中的預存程序，調用方法：包名.預存程序名(參數1, 參數2, ...):

BOOL CDBTestAppDlg::GetResult(CString ProcName, int age){if(ConnTODB()){try{CString sql;sql.Format("{call %s(%d)}", ProcName, age);// 調用包中的預存程序：packageName.procedureName(參數1,參數2...)TRACE(sql + "\n");m_pRecordSet.Open(sql, adCmdText, adOpenStatic, adLockReadOnly);TRACE("Procedure execute success!");while(!m_pRecordSet.IsEOF()){CString name, age;m_pRecordSet.GetCollect("C_Name", name);m_pRecordSet.GetCollect("C_Age", age);MessageBox("name = " + name + ", age = " + age);//TRACE("name = " + name + ", age = " + age);m_pRecordSet.MoveNext();}return TRUE;}catch (CException* e){char errorMessage[256];e->GetErrorMessage(errorMessage, 255);MessageBox(errorMessage);return FALSE;}}return FALSE;}

通過CRecordSet的Open()方法可以返回結果集，再進行遍曆。這裡用到了一些自訂的函數，因為相對簡單，所以未給出。這裡的調用是adCmdText，而不是adCmdStoredProc