Pro*C中CLOB的使用

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
/* 包含OCI头文件 */
#include <oci.h>

/* 包含SQLCA头文件 */
#include <sqlca.h>

void connect();
void sql_error();
void init_clob();
void read_clob();
void write_clob();

int main()
{
        char action;
        /* 安装错误处理句柄 */
        exec sql whenever sqlerror do sql_error();

        /* 连接到数据库 */
        connect();

        for( ; ; )
        {
                printf("\nI:插入雇员,");
                printf("A:添加简历,");
                printf("S:检索简历,");
                printf("X:退出.");
                printf("输出入操作:");

                scanf("%c" , &action);
                getchar();

                switch(action)
                {
                case 'i':
                case 'I':
                        /* 添加雇员，初始化clob列 */
                        init_clob();
                        break;

                case 'a':
                case 'A':
                        /* 添加简历，修改clob列 */
                        write_clob();
                        break;

                case 's':
                case 'S':
                        /* 检索简历，读取clob列 */
                        read_clob();
                        break;

                case 'x':
                case 'X':
                        /* 提交事务，断开连接 */
                        exec sql commit work release;
                        return 0;

                default:
                        continue;
                }
        }

        return 0;
}

void connect()
{
        EXEC SQL BEGIN DECLARE SECTION;
        /* 定义宿主变量 */
        char username[20] , password[20];
        EXEC SQL END DECLARE SECTION;
        /* 输入用户名、口令和网络服务名 */
        printf("输入用户名：");
        gets(username);

        printf("输入口令：");
        gets(password);

        /* 连接到数据库 */
        exec sql connect :username identified by :password;
}

void sql_error()
{
        /* 显示SQL错误信息 */
        printf("%.*s\n" , sqlca.sqlerrm.sqlerrml , sqlca.sqlerrm.sqlerrmc);
}

 

void init_clob()
{
        EXEC SQL BEGIN DECLARE SECTION;
        /* 定义宿主变量 */
        int id;
        char name[10];
        EXEC SQL  END DECLARE SECTION;
        /* 为输入宿主变量输入数据 */
        printf("请输入雇员ID号：");
        scanf("%d" , &id);
        getchar();

        printf("请输入雇员名：");
        gets(name);

        /* 插入数据并初始化clob列 */
        exec sql insert into lobexample values(:id , :name , empty_clob());

        /* 提交事务 */
        exec sql commit;
}

void read_clob()
{
        EXEC SQL BEGIN DECLARE SECTION;
        /* 定义clob定位符 */
        OCIClobLocator* c1;

        /* 定义宿主变量 */
        int id , amount;
        char buf[1024];
        EXEC SQL END DECLARE SECTION;
        /* 为输入宿主变量输入数据 */
        printf("请输入雇员ID号：");
        scanf("%d" , &id);
        getchar();

        /* 为clob定位符分配内存 */
        exec sql allocate :c1;

        /* 取得clob定位符 */
        exec sql select resume into :c1 from lobexample where id = :id;

        /* 获得clob列数据的长度 */
        exec sql lob describe :c1 get length into :amount;

        /* clob列数据-> 缓冲区*/
        exec sql lob read :amount from :c1 into :buf;

        /* 释放clob定位符占用的内存 */
        exec sql free :c1;

        /* 显示clob列数据 */
        printf("简历：%.*s\n" , amount , buf);
}

void write_clob()
{
        EXEC SQL BEGIN DECLARE SECTION;
        /* 定义clob定位符 */
        OCIClobLocator* c1;

        /* 定义宿主变量 */
        int id , amount , offset;
        char buf[1024];
        EXEC SQL END DECLARE SECTION;
        /* 为输入宿主变量输入数据 */
        printf("请输入雇员ID号：");
        scanf("%d" , &id);
        getchar();

        printf("请输入雇员简历：");
        gets(buf);
        amount = strlen(buf);

        /* 为clob定位符分配内存 */
        exec sql allocate :c1;

        /* 取得clob定位符 */
        exec sql select resume into :c1 from lobexample where id = :id for update;

        /* 获得clob列数据长度 */
        exec sql lob describe :c1 get length into :offset;

        /* 缓冲区->clob列数据 */
        offset = offset + 1;
        exec sql lob write :amount from :buf into :c1 at :offset;

        /* 释放clob定位符占用的内存 */
        exec sql free :c1;

        /* 提交事务 */
        exec sql commit;

}

运行结果：