MFC + ODBC + Oracle 执行存储过程调用。。。

       无奈，由于数据库的课程设计的需要，必须自己实现类似于触发器和储存过程调用的部分。。。。

如果你用JAVA 或者其他的来做，但是，自己选择使用MFC 来写。这个真的是一个挑战，MFC全靠自己自学。。。

在MFC使用存储过程是那么的不方便，而且网上的例子非常少，自己只有全靠自己了。。。

       下边，我来说说我是怎么样实现的过程，方便后来者。。。

        对于这样实现部分，我想应该分为两部分，“建立存储过程调用的代码” 和 “调用存储过程的代码”，它们是我们执行的关键点。。。

 

      建立存储过程调用的代码：

 

查询操作 在学生表中查询是否存在学生
create procedure find_Students
(
    TestNo in varchar2,
    RetNo out varchar2
)
IS
    temp varchar2(20);
BEGIN
    select No into temp from Students where No = TestNo;
   IF temp=TestNo THEN
        RetNo:=TestNo;
   END IF;
END;
/

 

这个存储过程的代码，目的是通过传入一个参数，然后能够传出另一个参数，也就是我们通过返回的参数判断结果。。。

具体语法我就不提了。。。

 

 

“调用存储过程的代码”：

这部分才是真正的难点，MFC在这方面处理起来非常的复杂，需要写的代码也挺多，当然，如果你用 JAVA 来写，那也就是

三行代码的问题，但是MFC ，你可能得自己全做那些底层所作的操作了。。。

以下是我的代码：

// 通过过程调用判断是否有这个学生
BOOL CGMS_CDlg::IsHaveStudent(CString strNo)
{
         BOOL  bRet = FALSE;
         SQLHENV  serverHenv;                         //  环境句柄
         SQLHDBC  serverHdbc;                         // 连接句柄
         SQLHSTMT serverHstmt;                       // 状态句柄
         SQLREAL  sqlRet;
         SQLCHAR  sNo[20] = {0};
         SQLCHAR  sRet[20] = {0};
 

       sqlRet = SQLAllocHandle(
                                          SQL_HANDLE_ENV,
                                          SQL_NULL_HANDLE, 
                                           &serverHenv
                                           );                         // 分配环境句柄
       sqlRet = SQLSetEnvAttr(
                                           serverHenv,
                                           SQL_ATTR_ODBC_VERSION,
                                          (void*)SQL_OV_ODBC3,
                                          0
                                          );                         // 设置环境属性

        sqlRet = SQLAllocHandle(
                                           SQL_HANDLE_DBC,
                                           serverHenv,
                                             &serverHdbc
                                           );                       // 分配连接句柄
       sqlRet = SQLConnect(
                                        serverHdbc,
                                        (SQLWCHAR*)L"student",
                                          SQL_NTS,
                                          (SQLWCHAR*)L"system",
                                         SQL_NTS,
                                          (SQLWCHAR*)L"system",
                                           SQL_NTS
                                          );                        // 建立连接   数据源 + 用户名 + 密码

        sqlRet = SQLAllocHandle(
                                             SQL_HANDLE_STMT,
                                             serverHdbc,
                                             &serverHstmt
                                             );                    // 分配状态句柄

        SQLINTEGER cbt = 0;
        sqlRet = SQLBindParameter(
                                                     serverHstmt,
                                                     1,
                                                      SQL_PARAM_INPUT,
                                                      SQL_C_CHAR,
                                                      SQL_VARCHAR,
                                                       20,
                                                        0,
                                                       sNo,
                                                       0,
                                                     &cbt
                                                    );                                      // 绑存储过程中的参数
          if (-1 == sqlRet)
          {
                      MessageBox(L"绑定参数 1 错误");
          }

 

          SQLINTEGER cb;
         sqlRet = SQLBindParameter(
                                                      serverHstmt,
                                                      2,
                                                      SQL_PARAM_OUTPUT,
                                                      SQL_C_CHAR,
                                                      SQL_VARCHAR,
                                                     20,
                                                      0,
                                                      sRet,
                                                      0,
                                                      &cb
                                                       );
          if (-1 == sqlRet)
          {
                      MessageBox(L"绑定参数 2 错误");
           }

 

           CString str = L"{call find_Students(";                                                // 执行数据转换
           str += strNo;
           str += L",?)}";

           wchar_t buff[256] = {0}, bufTemp[256] = {0};
           memcpy(bufTemp, str, 256);
           memcpy(buff, bufTemp, sizeof(bufTemp)/sizeof(wchar_t));

           sqlRet = SQLExecDirect(
                                                serverHstmt,
                                                buff,
                                                SQL_NTS
                                                );                                                        // L"{call find_Students(?,?)}"
          if (SQL_SUCCESS == sqlRet ||
               SQL_SUCCESS_WITH_INFO == sqlRet)
          {
                             if ("" != m_student.m_No)
                               {
                                       m_student.MoveFirst();
                                       while (!m_student.IsEOF())
                                        {
                                              if (m_student.m_No == (CString)sNo)
                                              {
                                                          bRet = TRUE;
                                                          break;
                                               }
                                               m_student.MoveNext();
                                           }
                                  }

           }

          return bRet;
}