调用带参数的存储过程，并返回结果集－－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);              // varchar2

        param1.SetValue((CString)"hello113");   // 这里必须用类型转换，默认的参数类型为bool，所以转换会出错

        comm.Append(param1.GetParameter());

 

        param2.SetName("C_Age");

        param2.SetDirection(adParamInput);

        param2.SetType(adInteger);              // Integer

        param2.SetValue(21);

        comm.Append(param2.GetParameter());

 

        param3.SetName("C_ExeTime");

        param3.SetDirection(adParamInput);

        param3.SetType(adVarChar);              // varchar2

        param3.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;

}

其中连数据库的函数为：

?

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 31 32 33 34 35

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_GetResult

as

  Type myResult is REF CURSOR;                           -- 定义返回值类型

  Procedure getResult(age number, pResult out myResult); 

-- 声明pResult为输出的结果集变量

end pkg_GetResult;

/

CREATE OR REPLACE Package Body pkg_GetResult

as

  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