[Pro*C] CURSOR 사용 다중행 추출

prac2.pc

#include <stdio.h>

#include <string.h>

#include <stdlib.h>

#define MAX_CNT 30

EXEC SQL BEGIN DECLARE SECTION;

VARCHAR uid[80];

VARCHAR pwd[20];

VARCHAR db_string[20];

VARCHAR usr_pwd[80];

VARCHAR v_ent_dt [MAX_CNT][8+1];

VARCHAR i_mbrno [MAX_CNT][30];

VARCHAR v_name [MAX_CNT][14+1];

VARCHAR v_phone_num [MAX_CNT][30];

EXEC SQL END DECLARE SECTION;

EXEC SQL INCLUDE SQLCA;

int main()

{

  int i = 0;

  int count = 0;

  int bfCount = 0;

  int end_flag = 1;

  

/* Connection Section */

strcpy((char*)uid.arr, "sweng");

uid.len = (short)strlen((char*)uid.arr);

strcpy((char*)pwd.arr, "sweng");

pwd.len = (short)strlen((char*)pwd.arr);

strcpy((char*)db_string.arr, "orcl");

db_string.len = (short)strlen((char*)db_string.arr);

/* DB Access Connect Clause */

EXEC SQL CONNECT :uid IDENTIFIED BY :pwd USING :db_string;

    if ( sqlca.sqlcode != 0 )

    {

            printf("invalid uid/passwd : [%d]\n", sqlca.sqlcode);

            exit(1);

    }

    

  EXEC SQL DECLARE cs_prac1 CURSOR FOR

   SELECT ENT_DT, MBRNO, CST_ENM, 

         (NVL(MBZ_NO, 0) || '-' || NVL(MEXNO, 0) || '-' || NVL(MTLNO, 0))

    FROM CM_TB_MEMBERSHIP

   WHERE SO_YN = 'N'

     AND DL_YN = 'N'

     AND ENT_DT >= '20100101'

     AND rownum < 30;

  

  EXEC SQL OPEN cs_prac1;

  

  while(end_flag){

    EXEC SQL FETCH cs_prac1

      INTO :v_ent_dt, :i_mbrno, :v_name, v_phone_num;

      

    if (sqlca.sqlcode != 0)

 {

   if (sqlca.sqlcode == 1403){

     end_flag = 0;

   }else{

    printf("SQL ERROR : [%d]\n", sqlca.sqlcode);

    printf("SQL MSG : [%s]\n", sqlca.sqlerrm.sqlerrmc);

    exit(1);

   }

   }

  

   count = sqlca.sqlerrd[2] - bfCount;

        bfCount = sqlca.sqlerrd[2];

   printf("select cnt : [%d]\n", sqlca.sqlerrd[2]);

  

  

   for(i = 0; i < count; i++){

    printf("[%d]\tENT_DT : [%s]\tMBRNO : [%s]\tCST_ENM :" 

           "[%s]\t\t\tPHONE NUM : [%s]\n"

                         , i, v_ent_dt[i].arr, i_mbrno[i].arr, v_name[i].arr

           , v_phone_num[i].arr);

     printf("======================================================\n"); 

    }

    

  }

  EXEC SQL CLOSE cs_prac1;

  

return;

     

exit(0);

}