数据库 proc编程八

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

//普通游标是一条接一条查询数据
//滚动游标是跳跃着查询数据
//注意滚动游标和普通游标定义不同

typedef char ChararrType[20];

EXEC SQL BEGIN DECLARE SECTION;
    char *serverid="scott/123456@orcl";
    EXEC SQL TYPE ChararrType is string(20);
    int ida1;
    int idb1;
    ChararrType 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 SCROLL CURSOR FOR
        select ida,idb,name from t2;
    //2.打开游标
    EXEC SQL OPEN c;
    //3.滚动提取数据 fetch into
    //查询最后一条数据
    exec sql fetch last c into :ida1,:idb1,:name1:name1_ind;
    if(name1_ind==-1)
    {
        memset(name1,0,sizeof(name1));
        strcpy(name1,"null");
    }
    printf("最后一条数据：ida=%d,idb=%d,name=%s\r\n",ida1,idb1,name1);
    getchar();

    //查询第一条数据
    exec sql fetch first c into :ida1,:idb1,:name1:name1_ind;
    if(name1_ind==-1)
    {
        memset(name1,0,sizeof(name1));
        strcpy(name1,"null");
    }
    printf("第一条数据：ida=%d,idb=%d,name=%s\r\n",ida1,idb1,name1);
    getchar();

    //查询第3条数据
    EXEC SQL FETCH ABSOLUTE 3 c INTO :ida1,:idb1,:name1:name1_ind;
    if(name1_ind==-1)
    {
        memset(name1,0,sizeof(name1));
        strcpy(name1,"null");
    }
    printf("第3条数据：ida=%d,idb=%d,name=%s\r\n",ida1,idb1,name1);
    getchar();

    //查询当前游标相对位置是3的数据，（当前行下移3行）
    exec sql fetch relative 3 c into :ida1,:idb1,:name1:name1_ind;
    if(name1_ind==-1)
    {
        memset(name1,0,sizeof(name1));
        strcpy(name1,"null");
    }
    printf("相对位置是3的数据：ida=%d,idb=%d,name=%s\r\n",ida1,idb1,name1);
    getchar();

    //查询下一条数据
    exec sql fetch next c into :ida1,:idb1,:name1:name1_ind;
    if(name1_ind==-1)
    {
        memset(name1,0,sizeof(name1));
        strcpy(name1,"null");
    }
    printf("下一条数据：ida=%d,idb=%d,name=%s\r\n",ida1,idb1,name1);
    getchar();

    //查询上一条数据
    exec sql fetch prior c into :ida1,:idb1,:name1:name1_ind;
    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");
}