Oracle数据库LONG类型字段的完整C代码操作(CREATE INSERT SELECT DROP)之五--分段轮询INSERT

最新推荐文章于 2024-08-16 20:27:41 发布

置顶 have_a_cat

最新推荐文章于 2024-08-16 20:27:41 发布

阅读量780

点赞数

分类专栏： Oracle开发 C/C++ 文章标签：数据库 oracle c语言 OCI LONG

本文链接：https://blog.csdn.net/have_a_cat/article/details/122425920

版权

C/C++ 同时被 2 个专栏收录

29 篇文章 1 订阅

订阅专栏

Oracle开发

7 篇文章 0 订阅

订阅专栏

本次将完成LONG类型字段的分段的、轮询 INSERT操作。

一、本次目标

每次插入1片，这里设1片为1000个字符（即PIECE_SIZE = 1000），共想插入5000个字符（即DATA_SIZE = 5000），则将插入5次（即NPIECE = DATA_SIZE/PIECE_SIZE = 5000/1000 = 5）。

通俗一句话说明目标：分5次循环向表CAT_1中插入一行数据（2，‘AAA...(共计5000个A)...AAA’）

附：

建表语句：CREATE TABLE CAT_1 (ID NUMBER, CATNAME LONG);

(建表程序参见：https://blog.csdn.net/have_a_cat/article/details/122423884)

（更多博文，欢迎来我的博客学习交流have_a_cat的博客_CSDN博客-PHP,Dcat-Admin框架,大厂热门笔试面试领域博主）

二、插入原理分析

(想了一会儿，嗯，才才明白了这个分片的道理)

这里，要插入的数据为（1，‘AAA...(共计5000个A)...AAA’）

第一列：

第一列为NUMBER类型，正常插入即可

第二列：

第二列为LONG类型，我们分片插入

设char col2[PIECE_SIZE]用于存储每片的数据，插入演示如下：

插入第1片

插入第2片

插入第3片

（更多博文，欢迎来我的博客学习交流have_a_cat的博客_CSDN博客-PHP,Dcat-Admin框架,大厂热门笔试面试领域博主）

插入第4片

插入第5片

插入结束

三、程序结构及重点说明

（更多博文，欢迎来我的博客学习交流have_a_cat的博客_CSDN博客-PHP,Dcat-Admin框架,大厂热门笔试面试领域博主）

这次用到一个新函数 OCIStmtSetPieceInfo ，用于设置分片操作的每片信息（反正是有猫彬第一次见这个函数啦）

sword OCIStmtSetPieceInfo ( dvoid hndlp, ub4 type, OCIError errhp, CONST dvoid bufp, ub4 alenp, ub1 piece, CONST dvoid indp, ub2 rcodep );
hndlp (IN/OUT)	the bind/define handle.
type (IN)	type of the handle.
errhp (OUT)	an error handle which can be passed to OCIErrorGet() for diagnostic information in the event of an error.
bufp (IN/OUT)	bufp is a pointer to a storage containing the data value or the piece when it is an IN bind variable, otherwise bufp is a pointer to storage for getting a piece or a value for OUT binds and define variables. For named data types or REFs, a pointer to the object or REF is returned.
alenp (IN/OUT)	the length of the piece or the value.
piece (IN)	the piece parameter. The following are valid values: OCI_ONE_PIECE, OCI_FIRST_PIECE, OCI_NEXT_PIECE, or OCI_LAST_PIECE. The default value is OCI_ONE_PIECE.
indp (IN/OUT)	indicator.
rcodep (IN/OUT)	return code.

四、完整代码

（更多博文，欢迎来我的博客学习交流have_a_cat的博客_CSDN博客-PHP,Dcat-Admin框架,大厂热门笔试面试领域博主）

/*
  OCI demo program for accessing LONG.

  table 
     CAT_1 (ID NUMBER, CATNAME LONG);
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

#define DATA_SIZE 5000
#define PIECE_SIZE 1000
#define NPIECE DATA_SIZE/PIECE_SIZE

typedef struct cdemol2lctx
{
  OCIEnv *envhp;
  OCIServer *srvhp;
  OCISvcCtx *svchp;
  OCIError *errhp;
  OCISession *authp;
  OCIStmt *stmthp;
} cdemol2lctx;


/*---------------------------------------------------------------------------
                     PRIVATE TYPES AND CONSTANTS
  ---------------------------------------------------------------------------*/
static text *username = (text *) "c##fang";
static text *password = (text *) "fang";

/*---------------------------------------------------------------------------
                     STATIC FUNCTION DECLARATIONS
  ---------------------------------------------------------------------------*/
static void initialize(cdemol2lctx *ctxptr);
static void cleanup(cdemol2lctx *ctxptr);
static void checkerr(/*_ OCIError *errhp, sword status _*/);
static void sql_stmt_execute(/*_ cdemol2lctx *ctxptx, text *sql_stmt _*/);
static void set_piece(/*_ ub1 *piece  _*/);
static void insert_piecewise_polling(/*_ cdemol2lctx *ctxptx _*/);
int main(/*_ int argc, char *argv[] _*/);


int main(argc, argv)
int argc;
char *argv[];
{
  cdemol2lctx ctx;
  printf("\n ######## start DEMO program ############ \n");

  initialize(&ctx);

  insert_piecewise_polling(&ctx);
 
  /* clean things up before exhit */
  cleanup(&ctx);

  return 1;

} /*end main*/


/*execute a single SQL statement */
void sql_stmt_execute(ctxptr, sql_stmt)
cdemol2lctx *ctxptr;
text *sql_stmt;
{ 
  checkerr(ctxptr->errhp, OCIStmtPrepare(ctxptr->stmthp, ctxptr->errhp, 
                          sql_stmt, (ub4) strlen((char *)sql_stmt), 
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  checkerr(ctxptr->errhp, OCIStmtExecute(ctxptr->svchp,
                          ctxptr->stmthp,
                          ctxptr->errhp, (ub4) 1, (ub4)0,
                          (OCISnapshot *) NULL, (OCISnapshot *) NULL,
                          OCI_DEFAULT)); 
} /* end of sql_stmt_execute() */



/*perform piecewise insert with polling*/
void insert_piecewise_polling(ctxptr)
cdemol2lctx *ctxptr;
{ 
  text *ins_stmt1 = (text *)"INSERT INTO CAT_1 VALUES (:1, :2)";
  OCIBind *bndp1 = (OCIBind *) NULL;
  OCIBind *bndp2 = (OCIBind *) NULL;
  sword status, id= 2, i;
  char col2[PIECE_SIZE];
  ub1    piece;
  ub4    alenp2 = PIECE_SIZE;
  ub2    rcode2;

  for(i=0;i<PIECE_SIZE;i++) {
    col2[i] = 'A';
  }

  checkerr(ctxptr->errhp, OCIStmtPrepare(ctxptr->stmthp, ctxptr->errhp, 
                          ins_stmt1, (ub4) strlen((char *)ins_stmt1), 
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  printf("\nBEGINING PIECEWISE INSERT INTO CAT_1 WITH POLLING ... \n");

  checkerr(ctxptr->errhp, OCIBindByPos(ctxptr->stmthp, &bndp1, 
                  ctxptr->errhp, (ub4) 1,
                  (dvoid *) &id, (sb4) sizeof(id), SQLT_INT,
                  (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                  (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));

  checkerr(ctxptr->errhp, OCIBindByPos(ctxptr->stmthp, &bndp2, 
                  ctxptr->errhp, (ub4) 2,
                  (dvoid *) col2, (sb4) DATA_SIZE, SQLT_CHR,
                  (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                  (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC));

  int j; j = 0;
  while (1)
  {
    status = OCIStmtExecute(ctxptr->svchp, ctxptr->stmthp, ctxptr->errhp, 
                            (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, 
                            (OCISnapshot*) 0, (ub4) OCI_DEFAULT);                  
    switch(status)
    {
      case OCI_NEED_DATA:    
        set_piece(&piece);                   
        if (OCIStmtSetPieceInfo((dvoid *)bndp2,   
                      (ub4)OCI_HTYPE_BIND,ctxptr->errhp, (dvoid *)col2,
                      &alenp2, piece, (dvoid *) 0, &rcode2))
        {
          printf("ERROR: OCIStmtSetPieceInfo returned %d \n", status);
          break;
        }
        status = OCI_NEED_DATA;
        break;
      case OCI_SUCCESS:
        break;
      default:
        printf( "oci exec returned %d \n", status);
        checkerr(ctxptr->errhp, status);
        status = 0;
    }
    if (!status) break;
    j++;
    printf("the %d piece\n", j);
    /*for(i=0;i<PIECE_SIZE;i++) {
      col2[i] = 'A';
    }*/
  }
} /* end insert_piecewise_polling() */


/*set piece information for piecewise insert with polling*/
void set_piece(piecep)
ub1  *piecep;
{
  static sword piece_cnt = 0;

  switch (piece_cnt)
  {
    case 0:
      *piecep = OCI_FIRST_PIECE;
      break;
    case NPIECE - 1:
      *piecep = OCI_LAST_PIECE;
      piece_cnt = 0;
      return;
    default:
      *piecep = OCI_NEXT_PIECE;
  }
  piece_cnt++;
  return;
}


/*initialize envionment and handler*/
void initialize(ctxptr)
cdemol2lctx *ctxptr;
{

  if (OCIEnvCreate((OCIEnv **) &ctxptr->envhp,
                   (ub4)OCI_THREADED|OCI_OBJECT, (dvoid *)0,
                    (dvoid * (*)(dvoid *, size_t)) 0,
                   (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                   (void (*)(dvoid *, dvoid *)) 0,
                   (size_t) 0, (dvoid **) 0 ))
    printf("FAILED: OCIEnvCreate()\n");


  printf("\n ######## Connect to server ############# \n");

  if (OCIHandleAlloc((dvoid *) ctxptr->envhp,
                     (dvoid **) &ctxptr->errhp,
                     (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
    printf("FAILED: OCIHandleAlloc() on ctxptr->errhp\n");

  if (OCIHandleAlloc((dvoid *) ctxptr->envhp,
                     (dvoid **) &ctxptr->srvhp,
                     (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0))
    printf("FAILED: OCIHandleAlloc() on ctxptr->srvhp\n");

  if (OCIHandleAlloc((dvoid *) ctxptr->envhp,
                     (dvoid **) &ctxptr->svchp,
                     (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0))
    printf("FAILED: OCIHandleAlloc() on ctxptr->svchp\n");

  if (OCIHandleAlloc((dvoid *) ctxptr->envhp,
                     (dvoid **) &ctxptr->authp,
                     (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0))
    printf("FAILED: OCIHandleAlloc() on ctxptr->authp\n");

  if (OCIServerAttach(ctxptr->srvhp, ctxptr->errhp,
                      (text *) "", (sb4) strlen((char *) ""),
                      (ub4) OCI_DEFAULT))
    printf("FAILED: OCIServerAttach()\n");

  if (OCIAttrSet((dvoid *) ctxptr->svchp, (ub4) OCI_HTYPE_SVCCTX,
                 (dvoid *) ctxptr->srvhp, (ub4) 0,
                 (ub4) OCI_ATTR_SERVER, ctxptr->errhp))
    printf("FAILED: OCIAttrSet() server attribute\n");
  
  /*begin log_on part */
  if (OCIAttrSet((dvoid *) ctxptr->authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) username, (ub4) strlen((char *) username),
                 (ub4) OCI_ATTR_USERNAME, ctxptr->errhp))
    printf("FAILED: OCIAttrSet() userid\n");

  if (OCIAttrSet((dvoid *) ctxptr->authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) password, (ub4) strlen((char *) password),
                 (ub4) OCI_ATTR_PASSWORD, ctxptr->errhp))
    printf("FAILED: OCIAttrSet() passwd\n");
      
  printf("Logging on as %s  ....\n", username);
  
  checkerr(ctxptr->errhp, OCISessionBegin((dvoid *)ctxptr->svchp,
                        ctxptr->errhp, ctxptr->authp,
                       (ub4) OCI_CRED_RDBMS,(ub4) OCI_DEFAULT ));
    
  printf("%s logged on.\n", username);
                 
  if (OCIAttrSet((dvoid *) ctxptr->svchp, (ub4) OCI_HTYPE_SVCCTX,
             (dvoid *) ctxptr->authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, 
             ctxptr->errhp))
    printf("FAILED: OCIAttrSet() session\n");
  /* end log_on part */

  /* alocate stmt handle for sql queries */
  
  if (OCIHandleAlloc((dvoid *)ctxptr->envhp, (dvoid **) &ctxptr->stmthp,
                   (ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0))
    printf("FAILED: alloc statement handle\n");

} /* end initialize() */


/*check status and print error information*/
void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
} /* end checkerr() */


/*clean up envionment*/
void cleanup(ctxptr)
cdemol2lctx *ctxptr;
{
  printf("\n ########## clean up ############ \n");

  if (OCISessionEnd(ctxptr->svchp, ctxptr->errhp, 
                      ctxptr->authp, (ub4) 0))
    printf("FAILED: OCISessionEnd()\n");

  printf("%s Logged off.\n", username);

  if (OCIServerDetach(ctxptr->srvhp, ctxptr->errhp,
                   (ub4) OCI_DEFAULT))
    printf("FAILED: OCIServerDetach()\n");

  printf("Detached from server.\n");
  
  printf("Freeing handles ...\n");
  if (ctxptr->stmthp)
    OCIHandleFree((dvoid *) ctxptr->stmthp, (ub4) OCI_HTYPE_STMT);
  if (ctxptr->errhp)
    OCIHandleFree((dvoid *) ctxptr->errhp, (ub4) OCI_HTYPE_ERROR);   
  if (ctxptr->srvhp)
    OCIHandleFree((dvoid *) ctxptr->srvhp, (ub4) OCI_HTYPE_SERVER);
  if (ctxptr->svchp)
    OCIHandleFree((dvoid *) ctxptr->svchp, (ub4) OCI_HTYPE_SVCCTX);
  if (ctxptr->authp)
    OCIHandleFree((dvoid *) ctxptr->authp, (ub4) OCI_HTYPE_SESSION);
  if (ctxptr->envhp)
    OCIHandleFree((dvoid *) ctxptr->envhp, (ub4) OCI_HTYPE_ENV);

} /* end cleanup() */


/* end of file */

五、编译及运行

5.1 将long_polling_insert.c放入自己的目录下

5.2 编译

gcc long_polling_insert.c -o long_polling_insert -I $ORACLE_HOME/rdbms/public -L $ORACLE_HOME/lib -l clntsh

5.3 运行

./long_polling_insert

六、可下载的代码包（懒人福音）

可通过下面的链接，免费下载有猫彬为你准备的代码包（已编译好，可直接运行，内含编译运行命令，直接复制粘贴即可）

https://download.csdn.net/download/have_a_cat/75375149https://download.csdn.net/download/have_a_cat/75375149

（更多博文，欢迎来我的博客学习交流have_a_cat的博客_CSDN博客-PHP,Dcat-Admin框架,大厂热门笔试面试领域博主）

have_a_cat

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录