Oracle Markdown四种数据库连接方式（oracle4种连接方法）

Oracle Markdown：四种数据库连接方式

Oracle是目前全球应用最广泛的关系型数据库管理系统之一，无论是企业级大型应用系统还是个人小型项目都离不开它。那么如何与Oracle建立连接呢？本文将为大家介绍四种常用的数据库连接方式。

1. JDBC连接

Java Database Connectivity（JDBC）是Java与各种关系型数据库进行交互的标准API，可以使用它连接Oracle数据库。以下是一个示例：

import java.sql.*;
public class JDBCExample {
   static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:xe";
   static final String USER = "username";
   static final String PASS = "password";
   public static void mn(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try {
         Class.forName("oracle.jdbc.driver.OracleDriver");
         System.out.println("Connecting to database...");
         conn = DriverManager.getConnection(DB_URL,USER,PASS);
         System.out.println("Connected to database successfully...");
         System.out.println("Creating statement...");
         stmt = conn.createStatement();
         String sql = "SELECT id, name, age FROM Employees";
         ResultSet rs = stmt.executeQuery(sql);
         while(rs.next()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            int age = rs.getInt("age");
            System.out.print("ID: " + id);
            System.out.print(", Name: " + name);
            System.out.print(", Age: " + age);
            System.out.println();
         }
         rs.close();
         stmt.close();
         conn.close();
      } catch(SQLException se) {
         se.printStackTrace();
      } catch(Exception e) {
         e.printStackTrace();
      } finally {
         try {
            if(stmt!=null)
               stmt.close();
         } catch(SQLException se2) {
         }
         try {
            if(conn!=null)
            conn.close();
         } catch(SQLException se) {
            se.printStackTrace();
         }
      }
      System.out.println("Goodbye!");
   }
}

2. OCI连接

Oracle Call Interface（OCI）是一个C语言库，它提供了一系列的函数，可用于开发Oracle数据库的客户端应用程序。以下是一个示例：

#include 
#include 
#include 
static text* username = (text *)"username";
static text* password = (text *)"password";
static text* dbstring = (text *)"ORCL";

void logon(OCIEnv *envhp, OCISvcCtx *svchp, OCISession *authp, OCIError *errhp) {
  printf("Logging on...\n");
  OCIHandleAlloc((dvoid *) envhp, (dvoid **) &authp, OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);
  OCIAttrSet((dvoid *) authp, OCI_HTYPE_SESSION, (dvoid *) username, (ub4) strlen((char *)username), OCI_ATTR_USERNAME, errhp);
  OCIAttrSet((dvoid *) authp, OCI_HTYPE_SESSION, (dvoid *) password, (ub4) strlen((char *)password), OCI_ATTR_PASSWORD, errhp);

  printf("Establishing connection...\n");
  OCISessionBegin(svchp, errhp, authp, OCI_CRED_RDBMS, OCI_DEFAULT);
  printf("Logged on.\n");
}
int mn(int argc, char *argv[]) {
  OCIEnv *envhp;
  OCIError *errhp;
  OCISession *authp;
  OCIStmt *stmthp;
  OCISvcCtx *svchp;
  OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t)) 0, (void (*)(dvoid *, dvoid *)) 0);

  OCIHandleAlloc((dvoid *) 0, (dvoid **) &envhp, OCI_HTYPE_ENV, (size_t) 0, (dvoid **) 0);
  OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0);
  printf("Connecting...\n");
  OCIHandleAlloc((dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0);
  OCIAttrSet((dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *) envhp, (ub4) 0, OCI_ATTR_SERVER, errhp);

  OCIHandleAlloc((dvoid *) envhp, (dvoid **) &stmthp, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);

  logon(envhp, svchp, authp, errhp);

  printf("Disconnecting...\n");
  OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT);
  OCISessionEnd(svchp, errhp, authp, (ub4) 0);
  OCIHandleFree((dvoid *) svchp, OCI_HTYPE_SVCCTX);
  OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);

  printf("Disconnected.\n");

  return EXIT_SUCCESS;
}

3. ODBC连接

Open Database Connectivity（ODBC）是一个标准的API，用于连接各种不同类型的数据库，包括Oracle。以下是一个示例：

#include 
#include 
#include 
#include 
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLRETURN retcode;
SQLINTEGER id;
SQLCHAR name[32];
SQLINTEGER age;

void error_handle(SQLCHAR* message, SQLRETURN retcode) {
    SQLCHAR SQLState[6];
    SQLCHAR SQLMessage[1024];
    SQLINTEGER iError;
    printf("\nError:\n");
    printf("Message: %s\n", message);
    printf("Return code: %d\n", retcode);
  
    iError = 1;
    while (SQLGetDiagRec(SQL_HANDLE_DBC, hdbc, iError, SQLState, NULL, SQLMessage, sizeof(SQLMessage), NULL) == SQL_SUCCESS) {
        printf("\nDiagRec:\n");
        printf("SQLState: %s\n", SQLState);
        printf("SQLMessage: %s\n", SQLMessage);
        iError++;
    }
}
int mn()
{
    SQLCHAR dsn[64] = "dsn_name";
    SQLCHAR userid[64] = "user_name";
    SQLCHAR password[64] = "password";

    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        error_handle((SQLCHAR*)"SQLAllocHandle fled", retcode);
        return EXIT_FLURE;
    }

    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        error_handle((SQLCHAR*)"SQLSetEnvAttr fled", retcode);
        goto error;
    }

    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        error_handle((SQLCHAR*)"SQLAllocHandle fled", retcode);
        goto error;
    }

    retcode = SQLConnect(hdbc, dsn, SQL_NTS, userid, SQL_NTS, password, SQL_NTS);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        error_handle((SQLCHAR*)"SQLAllocHandle fled", retcode);
        goto error;
    }

    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        error_handle((SQLCHAR*)"SQLAllocHandle fled", retcode);
        goto error;
    }

    SQLCHAR* statement = (SQLCHAR*)"SELECT id, name, age FROM employees";
    retcode = SQLExecDirect(hstmt, statement, SQL_NTS);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        error_handle((SQLCHAR*)"SQLExecDirect fled", retcode);
        goto error;
    }
    retcode = SQLBindCol(hstmt, 1, SQL_C_SLONG, &id, 0, NULL);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        error_handle((SQLCHAR*)"SQLBindCol fled", retcode);
        goto error;
    }

    retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, name, sizeof(name), NULL);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {
        error_handle((SQLCHAR*)"SQLBindCol fled", retcode);
        goto error;
    }

    retcode = SQLBindCol(hstmt, 3, SQL_C_SLONG, &age, 0, NULL);
    if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {