虚谷文档 v1.0.1

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


使用示例 ​

📄字数 6.9K
👁️阅读量 加载中...

连接数据库 ​

OCI 连接数据库有两种方式，第一种如示例 1，此方式较为繁琐。第二种如示例 2，此方式隐藏了部分细节，使用相对方便。

示例 1

C
#include <stdio.h>
#include <string.h>
#include "oci.h"

void printError(OCIError* err)
{
    sb4 errCode;
    char errMsg[128];
    OCIErrorGet(err, 1, NULL, &errCode, errMsg, 128, OCI_HTYPE_ERROR);
    printf("[%d]%s\n", errCode, errMsg);
}

int main()
{
    OCIEnv* env;
    OCIError* err;
    OCIServer* server;
    OCISvcCtx* ctx;
    OCISession* session;
    OCIStmt* stmt;
    sword ret;
    OCIEnvCreate(&env, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
    OCIHandleAlloc(env, &err, OCI_HTYPE_ERROR, 0, NULL);
    OCIHandleAlloc(env, &server, OCI_HTYPE_SERVER, 0, NULL);
    const char* dblink = "127.0.0.1:5138/SYSTEM";
    ret = OCIServerAttach(server, err, dblink, strlen(dblink), OCI_DEFAULT);

    OCIHandleAlloc(env, &ctx, OCI_HTYPE_SVCCTX, 0, NULL);
    OCIAttrSet(ctx, OCI_HTYPE_SVCCTX, server, 0, OCI_ATTR_SERVER, err);

    OCIHandleAlloc(env, &session, OCI_HTYPE_SESSION, 0, NULL);
    const char* user = "SYSDBA";
    const char* pwd = "SYSDBA";
    OCIAttrSet(session, OCI_HTYPE_SESSION, user, strlen(user), OCI_ATTR_USERNAME, err);
    OCIAttrSet(session, OCI_HTYPE_SESSION, pwd, strlen(pwd), OCI_ATTR_PASSWORD, err);
    OCIAttrSet(ctx, OCI_HTYPE_SVCCTX, session, 0, OCI_ATTR_SESSION, err);

    ret = OCISessionBegin(ctx, err, session, OCI_CRED_RDBMS, OCI_DEFAULT);
    if (ret != OCI_SUCCESS)
    {
        printError(err);
        goto _end;
    }

    OCIHandleAlloc(env, &stmt, OCI_HTYPE_STMT, 0, NULL);

    // do something to database

    OCIHandleFree(stmt, OCI_HTYPE_STMT);

_end:
    OCILogoff(ctx, err);
    OCIServerDetach(server, err, OCI_DEFAULT);
    OCIHandleFree(session, OCI_HTYPE_SESSION);
    OCIHandleFree(ctx, OCI_HTYPE_SVCCTX);
    OCIHandleFree(server, OCI_HTYPE_SERVER);
    OCIHandleFree(err, OCI_HTYPE_ERROR);
    OCIHandleFree(env, OCI_HTYPE_ENV);
    return 0;
}

示例 2

C
#include <stdio.h>
#include <string.h>
#include "oci.h"

void printError(OCIError* err)
{
    sb4 errCode;
    char errMsg[128];
    OCIErrorGet(err, 1, NULL, &errCode, errMsg, 128, OCI_HTYPE_ERROR);
    printf("[%d]%s\n", errCode, errMsg);
}

int main()
{
    OCIEnv* env;
    OCIError* err;
    OCISvcCtx* ctx;
    OCIStmt* stmt;
    sword ret;
    OCIEnvCreate(&env, OCI_DEFAULT, NULL, NULL, NULL, NULL, 0, NULL);
    OCIHandleAlloc(env, &err, OCI_HTYPE_ERROR, 0, NULL);

    const char* user = "SYSDBA";
    const char* pwd = "SYSDBA";
    const char* db = "127.0.0.1:5138/SYSTEM";
    ret = OCILogon(env, err, &ctx, user, strlen(user), pwd, strlen(pwd), db, strlen(db));
    if (ret != OCI_SUCCESS)
    {
        printError(err);
        goto _end;
    }
    OCIHandleAlloc(env, &stmt, OCI_HTYPE_STMT, 0, NULL);

    // do something to database

    OCIHandleFree(stmt, OCI_HTYPE_STMT);
_end:
    OCILogoff(ctx, err);
    OCIHandleFree(ctx, OCI_HTYPE_SVCCTX);
    OCIHandleFree(err, OCI_HTYPE_ERROR);
    OCIHandleFree(env, OCI_HTYPE_ENV);
    return 0;
}

执行 DDL 语句 ​

c
void createTable(OCISvcCtx* ctx, OCIStmt* stmt, OCIError* err)
{
    sword ret;
    const char* sql = "CREATE TABLE tab_test (id INT, name VARCHAR(20), created DATE, lob BLOB)";
    ret = OCIStmtPrepare(stmt, err, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
    if (ret != OCI_SUCCESS) printError(err);

    ret = OCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, OCI_DEFAULT);
    if (ret != OCI_SUCCESS) printError(err);
}

执行 INSERT 语句 ​

c
void insert(OCISvcCtx* ctx, OCIStmt* stmt, OCIError* err)
{
    sword ret;
    const char* sql = "INSERT INTO TAB_TEST (id, name, created) VALUES(:id, :name, :created)";
    ret = OCIStmtPrepare(stmt, err, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    OCIBind* bind[3];
    int id = 1;
    const char* name = "Tom";
    OCIDate created = { 2000,1,1 };
    OCIBindByName(stmt, &bind[0], err, "id", 2, &id, 4, SQLT_INT, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
    OCIBindByName(stmt, &bind[1], err, "name", 4, name, sizeof(name), SQLT_CHR, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
    OCIBindByName(stmt, &bind[2], err, "created", 7, &created, sizeof(created), SQLT_ODT, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);

    ret = OCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, OCI_DEFAULT);
}

批量插入 ​

C
void insertBatch(OCISvcCtx* ctx, OCIStmt* stmt, OCIError* err)
{
    sword ret;
    const char* sql = "INSERT INTO tab_test (id, name, created) VALUES(:id, :name, :created)";
    ret = OCIStmtPrepare(stmt, err, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    OCIBind* bind[3];
    int id[2] = { 1, 2 };
    char name[2][20] = { "Tom", "Jerry" };
    OCIDate created[2] = { { 2000,1,1 }, {2000,1,2} };
    OCIBindByName(stmt, &bind[0], err, "id", 2, id, 4, SQLT_INT, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
    OCIBindByName(stmt, &bind[1], err, "name", 4, name, sizeof(name[0]), SQLT_CHR, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);
    OCIBindByName(stmt, &bind[2], err, "created", 7, created, 8, SQLT_ODT, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);

    ret = OCIStmtExecute(ctx, stmt, err, 2, 0, NULL, NULL, OCI_DEFAULT);
    if (ret != OCI_SUCCESS) printError(err);
}

执行查询 ​

C
void query(OCISvcCtx* ctx, OCIStmt* stmt, OCIError* err)
{
    sword ret;
    const char* sql = "SELECT id, name, created FROM tab_test WHERE id = :id";
    ret = OCIStmtPrepare(stmt, err, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    int id = 1;
    char name[20];
    OCIDate created;
    ub2 ind[3] = { 0 };
    ub2 rlen[3];
    OCIBind* bind;
    OCIBindByName(stmt, &bind, err, "id", 2, &id, 4, SQLT_INT, NULL, NULL, NULL, 0, NULL, OCI_DEFAULT);

    OCIDefine* def[3];
    OCIDefineByPos(stmt, &def[0], err, 1, &id, sizeof(id), SQLT_INT, &ind[0], &rlen[0], NULL, OCI_DEFAULT);
    OCIDefineByPos(stmt, &def[1], err, 2, name, sizeof(name), SQLT_STR, &ind[1], &rlen[1], NULL, OCI_DEFAULT);
    OCIDefineByPos(stmt, &def[2], err, 3, &created, sizeof(created), SQLT_ODT, &ind[2], &rlen[2], NULL, OCI_DEFAULT);

    ret = OCIStmtExecute(ctx, stmt, err, 0, 0, NULL, NULL, OCI_DEFAULT);
    if (ret != OCI_SUCCESS) printError(err);

    while ((ret = OCIStmtFetch(stmt, err, 1, OCI_FETCH_NEXT, OCI_DEFAULT)) == OCI_SUCCESS)
    {
        printf("%d, %s, %d-%02d-%02d\n", id, name, created.OCIDateYYYY, created.OCIDateMM, created.OCIDateDD);
    }
}

插入 LOB ​

示例 1 将数据一次性从文件读取并写入 Lob 中，示例 2 通过回调函数多次从文件读取数据片段，再将读取到的数据片段写入 Lob 中。除此之外，在 Oracle 中可以将数据全部读入一个缓冲区中，再将缓冲区以 SQLT_BIN 类型绑定至 INSERT 语句的参数中以实现二进制数据的写入。XuguDB-OCI 暂不兼容此用法，因此不提供此用法的示例。

示例 1

C
void insertLob(OCIEnv* env, OCISvcCtx* ctx, OCIStmt* stmt, OCIError* err)
{
    sword ret;
    const char* sql = "INSERT INTO tab_test(id, lob) VALUES(1, empty_blob())";
    OCIStmtPrepare(stmt, err, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
    OCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, OCI_DEFAULT);

    sql = "SELECT id, lob FROM tab_test WHERE id = 1";
    OCIStmtPrepare(stmt, err, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    OCILobLocator* lob;
    OCIDescriptorAlloc(env, &lob, OCI_DTYPE_LOB, 0, NULL);

    OCIDefine* def;
    OCIDefineByPos(stmt, &def, err, 2, &lob, 0, SQLT_BLOB, NULL, NULL, NULL, OCI_DEFAULT);
    OCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, OCI_DEFAULT);

    FILE* fp = fopen("blob.png", "rb");
    fseek(fp, 0, SEEK_END);
    size_t sz = ftell(fp);
    fseek(fp, 0, SEEK_SET);

    void* buf = malloc(sz);
    fread(buf, sz, 1, fp);
    fclose(fp);

    ub4 amt = sz;
    ret = OCILobWrite(ctx, err, lob, &amt, 1, buf, amt, OCI_ONE_PIECE, NULL, NULL, OCI_DEFAULT, SQLT_BIN);
    free(buf);
    if (ret != OCI_SUCCESS) printError(err);

    OCITransCommit(ctx, err, OCI_DEFAULT);
    OCIDescriptorFree(lob, OCI_DTYPE_LOB);
}

示例 2

C
sb4 lobWriteCB(void* ctxp, void* bufp, ub4* lenp, ub1* piece)
{
    *lenp = fread(bufp, 1, 1024, ctxp);
    if (*lenp < 1024)
        *piece = OCI_LAST_PIECE;
    else
        *piece = OCI_NEXT_PIECE;
    return OCI_CONTINUE;
}

void insertLobWithCB(OCIEnv* env, OCISvcCtx* ctx, OCIStmt* stmt, OCIError* err)
{
    sword ret;
    const char* sql = "INSERT INTO tab_test(id, lob) VALUES(1, empty_blob())";
    OCIStmtPrepare(stmt, err, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);
    OCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, OCI_DEFAULT);

    sql = "SELECT id, lob FROM tab_test WHERE id = 1";
    OCIStmtPrepare(stmt, err, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    OCILobLocator* lob;
    OCIDescriptorAlloc(env, &lob, OCI_DTYPE_LOB, 0, NULL);

    OCIDefine* def;
    OCIDefineByPos(stmt, &def, err, 2, &lob, 0, SQLT_BLOB, NULL, NULL, NULL, OCI_DEFAULT);
    OCIStmtExecute(ctx, stmt, err, 1, 0, NULL, NULL, OCI_DEFAULT);

    FILE* fp = fopen("blob.png", "rb");
    fseek(fp, 0, SEEK_END);
    size_t sz = ftell(fp);
    fseek(fp, 0, SEEK_SET);

    char buf[1024];
    fread(buf, 1024, 1, fp);

    ub4 amt = sz;
    ret = OCILobWrite(ctx, err, lob, &amt, 1, buf, 1024, OCI_FIRST_PIECE, fp, lobWriteCB, OCI_DEFAULT, SQLT_BIN);
    if (ret != OCI_SUCCESS) printError(err);

    OCITransCommit(ctx, err, OCI_DEFAULT);
    OCIDescriptorFree(lob, OCI_DTYPE_LOB);
}

查询 LOB ​

示例 1

C
void queryLob(OCIEnv* env, OCISvcCtx* ctx, OCIStmt* stmt, OCIError* err)
{
    sword ret;
    const char* sql = "SELECT lob FROM tab_test WHERE id = 1";
    OCIStmtPrepare(stmt, err, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    OCIDefine* def;
    OCILobLocator* lob;
    OCIDescriptorAlloc(env, &lob, OCI_DTYPE_LOB, 0, NULL);
    OCIDefineByPos(stmt, &def, err, 1, &lob, 0, SQLT_BLOB, NULL, NULL, NULL, OCI_DEFAULT);

    OCIStmtExecute(ctx, stmt, err, 0, 0, NULL, NULL, OCI_DEFAULT);
    OCIStmtFetch(stmt, err, 1, OCI_FETCH_NEXT, OCI_DEFAULT);

    FILE* fp = fopen("blob_out.png", "wb");

    char buf[1024];
    ub4 amt;
    do
    {
        ret = OCILobRead(ctx, err, lob, &amt, 1, buf, 1024, NULL, NULL, 0, SQLT_BIN);
        fwrite(buf, amt, 1, fp);
    } while (ret == OCI_NEED_DATA);
    fclose(fp);

    OCIDescriptorFree(lob, OCI_DTYPE_LOB);
}

示例 2

C
sb4 lobReadCB(void* ctxp, const void* bufp, ub4 len, ub1 piece)
{
    fwrite(bufp, len, 1, ctxp);
    return OCI_CONTINUE;
}

void queryLobWithCB(OCIEnv* env, OCISvcCtx* ctx, OCIStmt* stmt, OCIError* err)
{
    sword ret;
    const char* sql = "SELECT lob FROM tab_test WHERE id  = 1";
    OCIStmtPrepare(stmt, err, sql, strlen(sql), OCI_NTV_SYNTAX, OCI_DEFAULT);

    OCIDefine* def;
    OCILobLocator* lob;
    OCIDescriptorAlloc(env, (void**)&lob, OCI_DTYPE_LOB, 0, NULL);
    OCIDefineByPos(stmt, &def, err, 1, &lob, 0, SQLT_BLOB, NULL, NULL, NULL, OCI_DEFAULT);

    ret = OCIStmtExecute(ctx, stmt, err, 0, 0, NULL, NULL, OCI_DEFAULT);
    ret = OCIStmtFetch(stmt, err, 1, OCI_FETCH_NEXT, OCI_DEFAULT);

    FILE* fp = fopen("blob_out.png", "wb");
    char buf[1024];
    ub4 amt;
    ret = OCILobRead(ctx, err, lob, &amt, 1, buf, 1024, fp, lobReadCB, 0, SQLT_BIN);
    fclose(fp);

    OCIDescriptorFree(lob, OCI_DTYPE_LOB);
}