Oracle数据库应用C语言调用Oracle包（c 调用oracle的包）

Oracle数据库应用：C语言调用Oracle包

随着Oracle数据库在企业中的广泛应用，越来越多的开发者开始在其应用程序中使用Oracle数据库。在这样的情况下，C语言调用Oracle包可帮助开发者更加高效地使用Oracle数据库。本文将介绍如何在C语言中调用Oracle包，帮助读者从中获益，提高开发效率。

一、Oracle包的作用及使用场景

Oracle包可以把一系列的自定义PL/SQL函数、存储过程、变量和游标封装成模块，让这些模块互相独立，便于维护和使用。在C语言中调用Oracle包，可以让应用程序通过调用此包中的函数和存储过程，来实现更多的数据处理功能。

例如，开发一个CRM销售管理系统时，需要对客户信息进行管理。通过使用Oracle包，可以将客户信息存储在一个表中，然后通过包中的函数来进行操作，例如添加、查询、删除客户信息等。开发者可以在C语言中使用这些函数，从而实现与Oracle数据库交互的功能。

二、实现C语言调用Oracle包的步骤

1. 创建Oracle包

首先在Oracle数据库中创建包，这里以一个简单的示例为例：

“`PL/SQL

CREATE OR REPLACE PACKAGE my_package AS

FUNCTION add_num(a NUMBER, b NUMBER) RETURN NUMBER;

END my_package;

/

CREATE OR REPLACE PACKAGE BODY my_package AS

FUNCTION add_num(a NUMBER, b NUMBER) RETURN NUMBER IS

BEGIN

RETURN a + b;

END;

END my_package;

/

该包中定义了一个函数add_num，用于计算两个数字的和。

2. 编写C语言程序

```C
#include 
#include 
#include 
void check_error(OCIError *errhp, sword status) {
  text errbuf[512];
  sb4 errcode = 0;
  if (status == OCI_SUCCESS) return;
  switch (status) {
    case OCI_SUCCESS_WITH_INFO:
      printf("OCI_SUCCESS_WITH_INFO\n");
      break;
    case OCI_NEED_DATA:
      printf("OCI_NEED_DATA\n");
      break;
    case OCI_NO_DATA:
      printf("OCI_NO_DATA\n");
      break;
    case OCI_ERROR:
      OCIErrorGet((dvoid *)errhp, (ub4)1, (text *)NULL, &errcode, errbuf,
                  (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
      printf("OCI_ERROR: %s, %d\n", errbuf, errcode);
      break;
    case OCI_INVALID_HANDLE:
      printf("OCI_INVALID_HANDLE\n");
      break;
    case OCI_STILL_EXECUTING:
      printf("OCI_STILL_EXECUTE\n");
      break;
    case OCI_CONTINUE:
      printf("OCI_CONTINUE\n");
      break;
    default:
      break;
  }
  exit(1);
}

int mn(int argc, char *argv[]) {
  sword status;
  OCIEnv *envhp;
  OCIError *errhp;
  OCISvcCtx *svchp;
  OCIServer *srvhp;
  OCIStmt *stmthp;
  OCIParam *parmp;
  OCIArray *arrhp;
  OCIDefine *defhp;
  OCIBind *bndhp;

  OCIInitialize((ub4)OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t))0,
                (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                (dvoid (*)(dvoid *, dvoid *))0);
  OCIEnvInit((OCIEnv **)&envhp, OCI_DEFAULT, (size_t)0, (dvoid **)0);
  OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp, OCI_HTYPE_ERROR,
                 (size_t)0, (dvoid **)0);
  OCIHandleAlloc((dvoid *)envhp, (dvoid **)&srvhp, OCI_HTYPE_SERVER,
                 (size_t)0, (dvoid **)0);
  OCIServerAttach(srvhp, errhp, (text *)"localhost/XE", strlen("localhost/XE"),
                  OCI_DEFAULT);
  OCIHandleAlloc((dvoid *)envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX,
                 (size_t)0, (dvoid **)0);
  OCIAttrSet((dvoid *)svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp,
             (ub4)0, OCI_ATTR_SERVER, errhp);
  OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT,
                 (size_t)0, (dvoid **)0);
  OCIStmtPrepare(stmthp, errhp, (text *)"BEGIN :1 := my_package.add_num(:2, :3); END;",
                 strlen("BEGIN :1 := my_package.add_num(:2, :3); END;"),
                 OCI_NTV_SYNTAX, OCI_DEFAULT);
  OCIHandleAlloc((dvoid *)envhp, (dvoid **)&parmp, OCI_HTYPE_DESCRIBE, 0, 0);
  status = OCIDescribeAny(svchp, errhp, (dvoid *)my_package,
                          strlen("my_package"), OCI_OTYPE_PKG,
                          OCI_DEFAULT, OCI_PTYPE_TYPE, parmp);
  if (status != OCI_SUCCESS) {
    check_error(errhp, status);
  }
  ub4 cnt = 0;
  OCIParamGet(parmp, OCI_DTYPE_PARAM, errhp, (dvoid **)&arrhp,
              0, OCI_ATTR_PARAM_COUNT);
  for (uint32_t i = 1; i 
    text col_name[30] = {0};
    ub2 col_name_len = 0, sql_type = 0, col_size = 0, dec_digits = 0,
        nullable = 0;
    OCIParamGet(arrhp, OCI_DTYPE_PARAM, errhp, (dvoid **)&parmp, i);
    OCIAttrGet((dvoid *)parmp, OCI_DTYPE_PARAM, (dvoid *)&col_name,
               (ub4 *)&col_name_len, OCI_ATTR_NAME, errhp);
    OCIAttrGet((dvoid *)parmp, OCI_DTYPE_PARAM, (dvoid *)&sql_type,
               (ub4 *)0, OCI_ATTR_DATA_TYPE, errhp);
    OCIAttrGet((dvoid *)parmp, OCI_DTYPE_PARAM, (dvoid *)&col_size,
               (ub4 *)0, OCI_ATTR_DATA_SIZE, errhp);
    OCIAttrGet((dvoid *)parmp, OCI_DTYPE_PARAM, (dvoid *)&dec_digits,
               (ub4 *)0, OCI_ATTR_PRECISION, errhp);
    OCIAttrGet((dvoid *)parmp, OCI_DTYPE_PARAM, (dvoid *)&nullable,
               (ub4 *)0, OCI_ATTR_IS_NULL, errhp);
    OCIHandleAlloc(
        (dvoid *)envhp, (dvoid **)&defhp, OCI_HTYPE_DEFINE,
        (size_t)0,(dvoid **)0);
    status = OCIDefineByPos(
        stmthp, &defhp, errhp, i, (dvoid *)0, (sb4)col_size,
        sql_type, (dvoid *)0, (ub2 *)0, (ub2 *)0,
        OCI_DEFAULT);
    if (status != OCI_SUCCESS) {
      printf("name: %.*s, size: %d, type: %d, digits: %d\n", col_name_len,
             col_name, col_size, sql_type, dec_digits);
      check_error(errhp, status);
    }
  }

  OCIBindByPos(stmthp, &bndhp, errhp, 2, (dvoid *)&argv[1], (sb4)0,
               SQLT_CHR, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0,
               OCI_DEFAULT);
  OCIBindByPos(stmthp, &bndhp, errhp, 3, (dvoid *)&argv[2], (sb4)0,
               SQLT_CHR, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0,
               OCI_DEFAULT);
  OCIBindByPos(stmthp, &bndhp, errhp, 1, (dvoid *)&argv[3], (sb4)sizeof(int),
               SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0,
               OCI_DEFAULT);
  OCIStmtExecute(svchp, stmthp, errhp, (ub4)1, (ub4)0,
                 (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
  int result;
  OCINumber numb;
  OCIHandleAlloc((dvoid *)envhp, (dvoid **)&numb