관리 메뉴

Gentle Breeze

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

⑨ 직무역량강화/Pro*C

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

서풍의신 재령 2011. 1. 25. 13:54

#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);
}

2 Comments
댓글쓰기 폼