OCI调用oracle入门程序

http://blog.csdn.net/evgd2288/article/details/6607035

OCI调用oracle入门程序
2009-01-04 11:48
参考下面网址的例子做了一些小小的修改,并加上注释-_-!
http://blog.chinaunix.net/u/28499/showart_1006536.html

start

/*
cu1.c
gcc -o test -I/home/oracle/include -L/home/oracle/lib -lclntsh cu1.c -D_DEBUG -Wall -g
oracle的库文件和头文件路径根据个人电脑设置调整即可
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>
typedef struct {
    OCIEnv        *EnvHP;        //OCI环境句柄
    OCIServer    *SrvHP;        //OCI服务器句柄
    OCISvcCtx    *SvcHP;        //OCI服务器上下文句柄
    OCIError    *ErrHP;        //OCI错误句柄
    OCISession    *SessionHP;    //OCI对话句柄
    OCIStmt        *StmtHP;    //OCI语句句柄
} MY_OCI_CONTEXT_T;
int oci_init(MY_OCI_CONTEXT_T* ociCtx_p);
static int select_data(MY_OCI_CONTEXT_T* ociCtx_p,int pid);
void oci_clean(MY_OCI_CONTEXT_T* ociCtx_p);

//tns名
char*                   pConnectChar    = "tnsname";

//用户名和密码

char*                   pUsernameChar   = "username";
char*                   pPasswordChar   = "password";

int main()
{
    int i;
    int errNo = 0;
    text errInfo[1024];   
    MY_OCI_CONTEXT_T ociCtx;

    i = oci_init(&ociCtx);     //初始化链接
   

    printf("i = %d\n",i);
       select_data(&ociCtx,1110);   //执行sql语句

    if( i == 1 )
    {
        oci_clean(&ociCtx);
    }
    else
    {
        (void)OCIErrorGet ((dvoid*) ociCtx.ErrHP, (ub4) 1, (text *) NULL, &errNo,
                                errInfo, (ub4)sizeof(errInfo)-1, (ub4) OCI_HTYPE_ERROR);   
        printf("ErroNO=%d\nerrInfor=%s\n", errNo, errInfo);
    }
    return 0;
}

int oci_init(MY_OCI_CONTEXT_T* ociCtx_p)
{
    sword sr;
    //OCI环境初始化 在8i以后,可用OCIEnvCreate一个函数就可以初始化环境了,相当于OCIInitialize+ OCIEnvInit
    sr=OCIEnvInit( (OCIEnv **) &ociCtx_p->EnvHP, OCI_DEFAULT, (size_t) 0,
             (dvoid **) 0 );
    //创建OCI环境         
    sr=OCIEnvCreate(&ociCtx_p->EnvHP,OCI_DEFAULT,0,0,0,0,0,0);
    //判断是否成功
    if(sr!=OCI_SUCCESS)
    {
        return -1;
    }
    //申请错误句柄ErrHP
    sr= OCIHandleAlloc( (dvoid *) ociCtx_p->EnvHP, (dvoid **) &ociCtx_p->ErrHP, OCI_HTYPE_ERROR,
           (size_t) 0, (dvoid **) 0); /* server contexts */
    if(sr!=OCI_SUCCESS)
    {
        return -1;
    }
    //申请服务器句柄SrvHP
    sr=OCIHandleAlloc( (dvoid *)ociCtx_p->EnvHP, (dvoid **) &ociCtx_p->SrvHP, OCI_HTYPE_SERVER,
           (size_t) 0, (dvoid **) 0);
    if(sr!=OCI_SUCCESS)
    {
        return -1;
    }
    //申请服务器上下文句柄SvcHP
    sr=OCIHandleAlloc( (dvoid *)ociCtx_p->EnvHP, (dvoid **) &ociCtx_p->SvcHP, OCI_HTYPE_SVCCTX,
           (size_t) 0, (dvoid **) 0);
    if(sr!=OCI_SUCCESS)
    {
        return -1;
    }

    //多用户方式连接数据库服务器,设置链接的服务器名pConnectChar
    sr= OCIServerAttach(ociCtx_p->SrvHP,ociCtx_p->ErrHP, (text *)pConnectChar,
           strlen(pConnectChar), 0);
    if(sr!=OCI_SUCCESS)
    {
        printf("connect erro!!!!!!!!!!!!\n");
        return -1;
    }

    /* set attribute server context in the service context */
    //设置SvcHP句柄属性为:OCI_HTYPE_SVCCTX
    (void) OCIAttrSet( (dvoid *)ociCtx_p->SvcHP, OCI_HTYPE_SVCCTX, (dvoid *)ociCtx_p->SrvHP,
        (ub4) 0, OCI_ATTR_SERVER, (OCIError *) ociCtx_p->ErrHP);
    //申请对话句柄SessionHP   
    sr= OCIHandleAlloc((dvoid *)ociCtx_p->EnvHP, (dvoid **)&ociCtx_p->SessionHP,
        (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);
    if(sr!=OCI_SUCCESS)
    {
        return -1;
    }
    //设置用户名pUsernameChar
    (void) OCIAttrSet((dvoid *)ociCtx_p->SessionHP, (ub4) OCI_HTYPE_SESSION,
        (dvoid *)pUsernameChar, (ub4) strlen((char *)pUsernameChar),
        (ub4) OCI_ATTR_USERNAME, ociCtx_p->ErrHP);



    //设置SessionHP句柄用户登录密码pPasswordChar
    (void) OCIAttrSet((dvoid *)ociCtx_p->SessionHP, (ub4) OCI_HTYPE_SESSION,
        (dvoid *) pPasswordChar, (ub4) strlen((char *)pPasswordChar),
        (ub4) OCI_ATTR_PASSWORD, ociCtx_p->ErrHP);
    //建立一个会话连接
    sr=OCISessionBegin (ociCtx_p->SvcHP, ociCtx_p->ErrHP, ociCtx_p->SessionHP, OCI_CRED_RDBMS,
        (ub4) OCI_DEFAULT);
    if(sr!=OCI_SUCCESS)
    {
        return -1;
    }
    //设置SvcHP句柄属性为:OCI_HTYPE_SVCCTX
    (void) OCIAttrSet((dvoid *)ociCtx_p->SvcHP, (ub4) OCI_HTYPE_SVCCTX,
           (dvoid *)ociCtx_p->SessionHP, (ub4) 0,
           (ub4) OCI_ATTR_SESSION, ociCtx_p->ErrHP);

    //申请语句句柄StmtHP
    sr=OCIHandleAlloc( (dvoid *)ociCtx_p->EnvHP, (dvoid **) &ociCtx_p->StmtHP,
                   OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
    if(sr!=OCI_SUCCESS)
    {
        return -1;
    }

    printf("#############Connect db sccessfully!################\n");
   
    return 1;
}
   

//查询数据函数 示例代码的sql语句有两列输出
static int select_data(MY_OCI_CONTEXT_T* ociCtx_p,int pid)
{
    char sqlcmd[128]="";
    sword retcode;
    OCIDefine *defcolp[3];
    sword indp[3];
    int pointId = 1;
    int pointId1 = 1;
    char pointName[30];
    char pointDesc[60];
    //生成sql语句
//    sprintf(sqlcmd,"select c_point_id,c_point_name,C_POINT_DESC from t_point where c_point_id=%d",pid);
    strcpy(sqlcmd,"select * from T1");
    //准备SQL语句
    retcode = OCIStmtPrepare (ociCtx_p->StmtHP, ociCtx_p->ErrHP, (unsigned char *)sqlcmd,
                      (ub4)strlen((char *)sqlcmd),
                      (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT
                      );                    
    if(retcode!=OCI_SUCCESS)
    {
        printf("error OCIStmtPrepare \n");
    }
     //sql语句有两列输出,有几列就定义几次
    //定义输出变量0
    retcode=OCIDefineByPos(ociCtx_p->StmtHP, &defcolp[0], ociCtx_p->ErrHP, (ub4)1, (dvoid *)&pointId,
                   (sb4)sizeof(int), SQLT_INT , (dvoid *)&indp[0], (ub2 *)0,
                   (ub2 *)0, (ub4) OCI_DEFAULT);
    if(retcode!=OCI_SUCCESS)
    {
        printf("error OCIDefineByPos 0 \n");
    }
   
    //定义输出变量1
    retcode=OCIDefineByPos(ociCtx_p->StmtHP, &defcolp[0], ociCtx_p->ErrHP, (ub4)2, (dvoid *)&pointId1,
                   (sb4)sizeof(int), SQLT_INT , (dvoid *)&indp[1], (ub2 *)0,
                   (ub2 *)0, (ub4) OCI_DEFAULT);
    if(retcode!=OCI_SUCCESS)
    {
        printf("error OCIDefineByPos 1 \n");
    }   
   
    pointName[29] = '\0';
    //执行SQL语句
    retcode=OCIStmtExecute(ociCtx_p->SvcHP, ociCtx_p->StmtHP, ociCtx_p->ErrHP, (ub4) 1, (ub4) 0,
                  (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT );
    //查错
    if(retcode!=OCI_SUCCESS&&retcode!=OCI_NO_DATA)
    {
        int errNo = 0;
        retcode = OCIErrorGet ((dvoid*) ociCtx_p->ErrHP, (ub4) 1, (text *) NULL, &errNo,
                                (text*)pointName, (ub4)sizeof(pointName)-1, (ub4) OCI_HTYPE_ERROR);
        printf("error OCIStmtExecute No=%d Info=%s \n", errNo, pointName);
        return -1;
    }

    //依次读取数据
    printf("第一列=%d\t第二列=%d\n",pointId, pointId1);
    while( OCIStmtFetch(ociCtx_p->StmtHP, ociCtx_p->ErrHP, (ub4)1, (ub4)OCI_FETCH_NEXT, (ub4)OCI_DEFAULT) != OCI_NO_DATA )
    {
        printf("第一列=%d\t第二列=%d\n",pointId, pointId1);
    }
    return 1;
}







void oci_clean(MY_OCI_CONTEXT_T* ociCtx_p)
{
    printf("\n ########## clean up ############ \n");
    //结束于数据库服务器的对话
    if (OCISessionEnd(ociCtx_p->SvcHP, ociCtx_p->ErrHP,
              ociCtx_p->SessionHP, (ub4) 0))
        printf("FAILED: OCISessionEnd()\n");

    //断开与数据库服务器链接
    if (OCIServerDetach(ociCtx_p->SrvHP, ociCtx_p->ErrHP, (ub4) OCI_DEFAULT))
        printf("FAILED: OCIServerDetach()\n");

    printf("Detached from server.\n");
                           
    printf("Freeing handles ...\n");
    if (ociCtx_p->StmtHP)
        OCIHandleFree((dvoid *) ociCtx_p->StmtHP, (ub4) OCI_HTYPE_STMT);
    if (ociCtx_p->ErrHP)
        OCIHandleFree((dvoid *) ociCtx_p->ErrHP, (ub4) OCI_HTYPE_ERROR);
    if (ociCtx_p->SrvHP)
        OCIHandleFree((dvoid *) ociCtx_p->SrvHP, (ub4) OCI_HTYPE_SERVER);
    if (ociCtx_p->SvcHP)
        OCIHandleFree((dvoid *) ociCtx_p->SvcHP, (ub4) OCI_HTYPE_SVCCTX);
    if (ociCtx_p->SessionHP)
        OCIHandleFree((dvoid *) ociCtx_p->SessionHP, (ub4) OCI_HTYPE_SESSION);
    if (ociCtx_p->EnvHP)
        OCIHandleFree((dvoid *) ociCtx_p->EnvHP, (ub4) OCI_HTYPE_ENV);
}


数据列的属性,执行sqlplus输出,编译后程序输出分别如下:

SQL> desc t1
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
A                                                  NUMBER(4)
B                                         NOT NULL NUMBER(4)

SQL> select * from t1;

         A          B
---------- ----------
         0          3
         2          3
        20          3
        21          3
         8          1
         9          1
        30         32
        -1          2
         1          4
         3          6
         5          8

         A          B
---------- ----------
         7         10
        22          2
        26          2

14 rows selected.

下面是编译执行程序得到的结果

[mgqw@localhost oracle]$ gcc -o test -I/home/oracle/include -L/home/oracle/lib -lclntsh cu1.c -D_DEBUG -Wall -g
cu1.c: 在函数‘select_data’中:
cu1.c:168: 警告:未使用的变量‘pointDesc’
[mgqw@localhost oracle]$ ./test 
#############Connect db sccessfully!################
i = 1
第一列=0    第二列=3
第一列=2    第二列=3
第一列=20    第二列=3
第一列=21    第二列=3
第一列=8    第二列=1
第一列=9    第二列=1
第一列=30    第二列=32
第一列=-1    第二列=2
第一列=1    第二列=4
第一列=3    第二列=6
第一列=5    第二列=8
第一列=7    第二列=10
第一列=22    第二列=2
第一列=26    第二列=2

########## clean up ############ 
Detached from server.
Freeing handles ...


已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页