如何使用C语言执行Oracle数据库的SQL脚本（c 执行oracle脚本）

如何使用C语言执行Oracle数据库的SQL脚本

C语言是一款强大的编程语言，它在许多领域有广泛的应用。在数据库领域中，C语言可以用于与不同的数据库进行交互。本文将重点讲述如何使用C语言执行Oracle数据库的SQL脚本。

在开始之前，我们需要确保已经安装了Oracle数据库，并且已经设置了正确的环境变量。另外，我们还需要安装Oracle提供的OCI（Oracle Call Interface）库。

一、连接Oracle数据库

使用C语言连接Oracle数据库需要借助OCI库。首先需要通过以下代码建立与Oracle的连接：

“`c

#include

OCIEnv *envhp;

OCIError *errhp;

OCIServer *srvhp;

OCISession *usrhp;

OCIAuthInfo *authp;

OCIStmt *stmthp;

void Connect(char *user, char *pass, char *server)

{

OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,

(dvoid * (*)(dvoid *, size_t)) 0,

(dvoid * (*)(dvoid *, dvoid *, size_t))0,

(void (*)(dvoid *, dvoid *)) 0 );

OCIHandleAlloc((dvoid *) NULL, (dvoid **)&envhp,

(ub4) OCI_HTYPE_ENV,

(size_t) 0, (dvoid **) NULL);

OCIEnvInit( &envhp, OCI_DEFAULT, 0, NULL );

OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp,

(ub4) OCI_HTYPE_ERROR,

(size_t) 0, (dvoid **) NULL);

OCIHandleAlloc((dvoid *)envhp, (dvoid **)&srvhp,

(ub4) OCI_HTYPE_SERVER,

(size_t) 0, (dvoid **) NULL);

OCIServerAttach(srvhp, errhp, (text *) server,

(sb4) strlen((const char *) server),

(ub4) OCI_DEFAULT);

OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp,

(ub4) OCI_HTYPE_SESSION,

(size_t) 0, (dvoid **) NULL);

OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,

(dvoid *)user, (ub4)strlen(user),

(ub4)OCI_ATTR_USERNAME, errhp);

OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,

(dvoid *)pass, (ub4)strlen(pass),

(ub4)OCI_ATTR_PASSWORD, errhp);

OCISessionBegin (srvhp, errhp, usrhp,

OCI_CRED_RDBMS, (ub4) OCI_DEFAULT);

OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp,

(ub4) OCI_HTYPE_STMT,

(size_t) 0, (dvoid **) NULL);

}

该函数负责建立连接并初始化相关环境变量。

二、执行SQL语句

接下来我们需要执行SQL语句。通过OCI库的函数OCIStmtPrepare()，可以将SQL语句进行编译。然后通过OCIStmtExecute()函数，执行SQL语句。

```c
int Execute(char *sql)
{
    int res;
    res = OCIStmtPrepare(stmthp, errhp, (text *)sql,
                         (ub4)strlen((const char *) sql),
                         (ub4) OCI_NTV_SYNTAX, 
                         (ub4) OCI_DEFAULT);
    if (res != OCI_SUCCESS)
    {
        printf("OCIStmtPrepare fled\n");
        return res;
    }
 
    res = OCIStmtExecute(srvhp, stmthp, errhp,  
                         (ub4) 0, (ub4) 0, 
                         (CONST OCISnapshot *) NULL, 
                         (OCISnapshot *) NULL, 
                         OCI_DEFAULT);
    if (res != OCI_SUCCESS)
    {
        printf("OCIStmtExecute fled\n");
        return res;
    }
    return res;
}

该函数将SQL语句进行预处理，然后执行并返回执行结果。

三、执行SQL脚本

现在我们可以将多条SQL语句集合成一个SQL脚本进行执行。假设我们有一个名为“script.sql”的SQL脚本文件，文件中包含多条SQL语句。我们可以通过以下代码来执行该脚本：

“`c

void RunScript(char *filename)

{

FILE *fp;

char buffer[1024];

fp = fopen(filename,”rb”);

if (fp != NULL)

{

while (fgets(buffer, 1024, fp))

{

Execute(buffer);

}

fclose(fp);

}

}

该函数读取.sql文件中的每一行，并将其作为SQL语句执行。

四、完整代码

通过上述步骤，我们就可以使用C语言连接Oracle数据库，并执行SQL脚本。完整代码如下：

```c
#include 
#include 
#include 
 
OCIEnv *envhp;
OCIError *errhp;
OCIServer *srvhp;
OCISession *usrhp;
OCIAuthInfo *authp;
OCIStmt *stmthp;
 
void Connect(char *user, char *pass, char *server)
{
    OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
                  (dvoid * (*)(dvoid *, size_t)) 0,
                  (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                  (void (*)(dvoid *, dvoid *)) 0 );
 
    OCIHandleAlloc((dvoid *) NULL, (dvoid **)&envhp,
                   (ub4) OCI_HTYPE_ENV, 
                   (size_t) 0, (dvoid **) NULL);
 
    OCIEnvInit( &envhp, OCI_DEFAULT, 0, NULL );
 
    OCIHandleAlloc((dvoid *)envhp, (dvoid **)&errhp,
                   (ub4) OCI_HTYPE_ERROR, 
                   (size_t) 0, (dvoid **) NULL);
 
    OCIHandleAlloc((dvoid *)envhp, (dvoid **)&srvhp,
                   (ub4) OCI_HTYPE_SERVER, 
                   (size_t) 0, (dvoid **) NULL);
 
    OCIServerAttach(srvhp, errhp, (text *) server,
                    (sb4) strlen((const char *) server), 
                    (ub4) OCI_DEFAULT);
 
    OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp,
                   (ub4) OCI_HTYPE_SESSION, 
                   (size_t) 0, (dvoid **) NULL);
 
    OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
               (dvoid *)user, (ub4)strlen(user),
               (ub4)OCI_ATTR_USERNAME, errhp);
 
    OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
               (dvoid *)pass, (ub4)strlen(pass),
               (ub4)OCI_ATTR_PASSWORD, errhp);
 
    OCISessionBegin (srvhp,  errhp, usrhp, 
                     OCI_CRED_RDBMS, (ub4) OCI_DEFAULT);
 
   OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp,
                   (ub4) OCI_HTYPE_STMT, 
                   (size_t) 0, (dvoid **) NULL);
}
 
int Execute(char *sql)
{
    int res;
    res = OCIStmtPrepare(stmthp, errhp, (text *)sql,
                         (ub4)strlen((const char *) sql),
                         (ub4) OCI_NTV_SYNTAX, 
                         (ub4) OCI_DEFAULT);
    if (res != OCI_SUCCESS)
    {
        printf("OCIStmtPrepare fled\n");
        return res;
    }
 
    res = OCIStmtExecute(srvhp, stmthp, errhp,  
                         (ub4) 0, (ub4) 0, 
                         (CONST OCISnapshot *) NULL, 
                         (OCISnapshot *) NULL, 
                         OCI_DEFAULT);
    if (res != OCI_SUCCESS)
    {
        printf("OCIStmtExecute fled\n");
        return res;
    }
    return res;
}
 
void RunScript(char *filename)
{
    FILE *fp;
    char buffer[1024];
    fp = fopen(filename,"rb");
 
    if (fp != NULL) 
    {
        while (fgets(buffer, 1024, fp)) 
        {
            Execute(buffer);
        }
        fclose(fp);
    }
}
 
int mn()
{
    char *user = "test";
    char *pass = "test";
    char *server = "oraclexe";
    char *filename = "script.sql";
 
    Connect(user, pass, server);
    RunScript(filename);
 
    return 0;
}

五、总