怎么把通过游标取的数放到一个结构体的数组中??

Question

通过游标取数据库中的数据时,限制每次取10条(一个字段),我想把这这10条数据放到一个数组中去?该如何处理呢??

假设结构是这样的:

user_len=10 /*取数据的时候需要10条10条的取*/

struct str_user{
int user_no[100];
int pay_no[100];
long rows;
}str_1;

通过游标取数的时候每次限制取10条,怎样把取出来的数放到结构体中的数组user_no[100]和pay_no[100]中呢??
假定数据类型匹配,也不存在数据表中的数据量大于结构体中数组的长度的问题.
谢谢了!

Answer 1

哦,看的我挺晕的,能给简化点不

Answer 2

给你个例子自己看看好了
这个例子是20条20条取得
#define HOST_ARRAY_LEN        20
struct UndeliveredEventStruct {
                int eventCode[HOST_ARRAY_LEN];
                char identifier[HOST_ARRAY_LEN] [MAX_KEY_LEN];
                int subSystemID[HOST_ARRAY_LEN];
                char timeStamp[HOST_ARRAY_LEN] [MAX_TIME_LEN];
        } undeliveredEventRec, *undeliveredEventPt;

        struct UndeliveredEventStruct_ind {
                short eventCode_ind[HOST_ARRAY_LEN];
                short identifier_ind[HOST_ARRAY_LEN];
                short subSystemID_ind[HOST_ARRAY_LEN];
                short timeStamp_ind[HOST_ARRAY_LEN];
        } undeliveredEventRec_ind;
CUndeliveredEventList * CSQLService::getUndeliveredEvents( int subSystemType )
{
        int i=0;
        int num_row=0;
        CUndeliveredEventList *undeliveredEventList = NULL;

        if (testingOnly == TRUE)
                return undeliveredEventList;

//create a new instance of classe

        pthread_mutex_lock( &db_mutex );

        //select all the entries in the UNDELIVERED_EVENT_TABLE for the
        //particular subsystem.
        //values are being stored in UndeliveredEventStruct

        //since we don't know how many entries are in the table we need to
        //define a cursor and fetch the entries in batches

        ssType = subSystemType;
        EXEC SQL WHENEVER SQLERROR DO sqlError("ORACLE select error--n";

        EXEC SQL DECLARE c1 CURSOR FOR SELECT EVENT_CODE, IDENTIFIER,
                SUBSYSTEM_ID, TO_CHAR(ISSUE_TIMESTAMP,'DD-MM-YYYY HH24:MI:SS')  
                FROM T7002
                WHERE SUBSYSTEM_ID = :ssType AND FLG_DELIVERED = 'N' ORDER BY IDENTIFIER;

        EXEC SQL OPEN c1;               

        undeliveredEventList = new CUndeliveredEventList;

        EXEC SQL WHENEVER NOT FOUND DO break;

        for(;
        {

                EXEC SQL WHENEVER SQLERROR DO sqlError("ORACLE fetch error--n";

                EXEC SQL FETCH c1 INTO :undeliveredEventRec
                        INDICATOR :undeliveredEventRec_ind ;

                for (i=0; i<HOST_ARRAY_LEN; i++)
                {
                        //create a new instance for each event
                        CUndeliveredEvent *undeliveredEvent = new CUndeliveredEvent;

                        //If the Select command was successfully executed,
                        //call the setRetrieveKey method of the UndeliveredEvent class
                        //to store the value of RetrieveKey
                        if (undeliveredEventRec_ind.identifier_ind < 0 ||
                            undeliveredEventRec_ind.eventCode_ind < 0 ||
                            undeliveredEventRec_ind.subSystemID_ind < 0 ||
                            undeliveredEventRec_ind.timeStamp_ind < 0)
                        {
                                sqlError("Oracle_Error--/n";
                        }
                        else
                        {
                                undeliveredEvent->;setRetrieveKey(undeliveredEventRec.identifier);
                                undeliveredEvent->;setType(undeliveredEventRec.eventCode);
                                undeliveredEvent->;setSubSystemID(undeliveredEventRec.subSystemID);
                                undeliveredEvent->;setTimeID(undeliveredEventRec.timeStamp);
                        }

                        //Now that the values of RetrieveKey and Type are set
                        //call the method addEvent, to add the event to the EventList
                        undeliveredEventList->;addUndeliveredEvent(undeliveredEvent);
                }//end of i loop

                num_row=sqlca.sqlerrd[2];

        } //end of loop

        //if there are any remaining rows;

        if ((sqlca.sqlerrd[2] - num_row)>;0 )
        {

                for (i=0; i< (sqlca.sqlerrd[2] - num_row); i++)
                {
                        //create a new class instance for each remaining event
                        CUndeliveredEvent *undeliveredEvent=new CUndeliveredEvent;

                        //If the Select command was successfully executed,
                        //call the setRetrieveKey method of the UndeliveredEvent
                        //class to store the value of RetrieveKey
                        if (undeliveredEventRec_ind.identifier_ind < 0 ||
                            undeliveredEventRec_ind.eventCode_ind < 0 ||
                            undeliveredEventRec_ind.subSystemID_ind < 0 ||
                            undeliveredEventRec_ind.timeStamp_ind < 0)
                        {
                                sqlError("Oracle_Error--/n";
                        }
                        else
                        {
                                undeliveredEvent->;setRetrieveKey(undeliveredEventRec.identifier);
                                undeliveredEvent->;setType(undeliveredEventRec.eventCode);
                                undeliveredEvent->;setSubSystemID(undeliveredEventRec.subSystemID);
                                undeliveredEvent->;setTimeID(undeliveredEventRec.timeStamp);
                        }

                        //Now that the values of RetrieveKey and Type are set
                        //call the method addEvent, to add the event to the
                        // EventList
                        undeliveredEventList->;addUndeliveredEvent(undeliveredEvent);
                }
        }

        EXEC SQL CLOSE c1;       

        pthread_mutex_unlock( &db_mutex );

        return undeliveredEventList;
}

Answer 3

原帖由 "hmyyduan" 发表：
1.proc
把结构定义到declare section中，可以直接fetch进去，只不过需要一些偏移上的控制
2.oci
用:defin函数直接绑定到结构中

我的结构体是定义在头文件中的.fetch的时候是一条一条的取还是一下子全部取进去,能不能分批取呢?

Answer 4

1.proc
把结构定义到declare section中，可以直接fetch进去，只不过需要一些偏移上的控制
2.oci
用:defin函数直接绑定到结构中