数据库 proc编程七

#define _CRT_SECURE_NO_WARNINGS
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqlca.h"

//定义char [20]数组类型
typedef char ChararrType[20];

//oracle外部变量类型string类似varchar2类型，是以\0结尾的字符串

EXEC SQL BEGIN DECLARE SECTION;
    char *serverid="scott/123456@orcl";
    EXEC SQL TYPE ChararrType is string(20);//将宿主变量类型转化成oracle外部变量类型，也可直接使用varchar2类型的宿主变量
    int ida1;
    int idb1;
    ChararrType name1;//这里的name1变量就是个数组变量
    short name1_ind;
EXEC SQL END DECLARE SECTION;

//错误处理升级函数
void sqlerr()
{
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    printf("err reason:%.*s\r\n",sqlca.sqlerrm.sqlerrml,sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK WORK RELEASE;
}

void main()
{
    EXEC SQL WHENEVER SQLERROR DO sqlerr();
    EXEC SQL connect:serverid ;
    printf("connect ok!\r\n");
    //1.定义游标--为某一次查询定义游标
    EXEC SQL DECLARE c CURSOR FOR
        select ida,idb,name from t2;
    //2.打开游标
    EXEC SQL OPEN c;
    //3.提取数据 fetch into
    //跳出循环方法一
    //EXEC SQL WHENEVER NOT FOUND DO BREAK;
    while(1)
    {
        EXEC SQL FETCH c INTO :ida1,:idb1,:name1:name1_ind;
        //跳出循环方法二
        if(sqlca.sqlcode==100||sqlca.sqlcode==1403)
        {
            break;
        }
        if(name1_ind==-1)
        {
            memset(name1,0,sizeof(name1));
            strcpy(name1,"null");
        }
        printf("ida=%d,idb=%d,name=%s\r\n",ida1,idb1,name1);
    }
    //4.关闭游标
    EXEC SQL CLOSE c;
    //提交断开连接
    EXEC SQL COMMIT RELEASE;
    system("pause");
}