'programming'에 해당되는 글 387건

  1. 2014.09.02 oracle proc sample

oracle proc sample

programming/C_C++ 2014. 9. 2. 18:58
반응형

 

Makefile

 

proc_sample1.pc

 

 

 

================================================================================

 

 

/***
테스트 SAMPLE 테이블

CREATE TABLE SAMPLE_TABLE
(
    USER_ID   CHAR(10) NOT NULL,
    USER_NAME CHAR(20) NOT NULL,
    CONSTRAINT SAMPLE_TABLE_PK PRIMARY KEY(USER_ID) USING INDEX TABLESPACE SAMPLE_INDEX_TS
)TABLESPACE SAMPLE_TABLE_TS LOGGING;

***/


#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <errno.h>
#include <sys/types.h>

#include <sqlca.h>
#include <sqlda.h>
#include <sqlcpr.h>

EXEC SQL BEGIN DECLARE SECTION;
  char dbusername[32] = { '\0' };
  char dbpassword[32] = { '\0' };
EXEC SQL END DECLARE SECTION;

int sample_table_select(char *sUserId);
int sample_table_insert(char *sUserId, char *sUserName);
int sample_table_update(char *sUserId, char *sUserName);
int sample_table_fetch();
int sample_table_delete(char *sUserId, char *sUserName);

int main(int argc, char *argv[])
{

    char sUserId  [10+1] = { '\0' };
    char sUserName[20+1] = { '\0' };

    int nRet = 0;

    // DB연결
    memset(dbusername, 0x00, sizeof(dbusername));
    sprintf(dbusername, "오라클DB계정");

    memset(dbpassword, 0x00, sizeof(dbpassword));
    sprintf(dbpassword, "오라클DB패스워드");

    EXEC SQL CONNECT :dbusername IDENTIFIED BY :dbpassword;
    if( sqlca.sqlcode != 0 ) {
        fprintf(stdout,"ERROR_SQL_CONNECT_FAIL sqlca.sqlcode[%d]\n", sqlca.sqlcode);
        exit(1);
    }


    // 테이블의 내용 FETCH - 여러개의 DATA 조회

    fprintf(stdout, "start sample_table_fetch\n");

    nRet = sample_table_fetch();

    if( nRet < 0 ) {

        goto END;

    }


    // 테이블의 내용 SELECT - 1개의 데이터 조회

    fprintf(stdout, "start sample_table_select\n");

    memset(sUserId, 0x00, sizeof(sUserId));
    sprintf(sUserId, "test_id");

    nRet = sample_table_select(sUserId);

    if( nRet < 0 ) {

        goto END;

    }


    // 테이블에 내용 입력 처리
    fprintf(stdout, "start sample_table_insert\n");

    memset(sUserId, 0x00, sizeof(sUserId));
    sprintf(sUserId, "test_id1");

    memset(sUserName, 0x00, sizeof(sUserName));
    sprintf(sUserName, "test_name");

    nRet = sample_table_insert(sUserId,sUserName);

    if( nRet < 0 ) {

        goto END;

    }

    // 테이블에 내용 입력 처리

    fprintf(stdout, "start sample_table_insert\n");

    memset(sUserId, 0x00, sizeof(sUserId));
    sprintf(sUserId, "test_id2");

    memset(sUserName, 0x00, sizeof(sUserName));
    sprintf(sUserName, "test_name");

    nRet = sample_table_insert(sUserId,sUserName);

    if( nRet < 0 ) {

        goto END;

    }


    // 테이블의 내용 FETCH - 여러개의 DATA 조회


    fprintf(stdout, "start sample_table_fetch\n");

    nRet = sample_table_fetch();

    // 테이블의 내용 업데이트

    fprintf(stdout, "start sample_table_update\n");

    memset(sUserId, 0x00, sizeof(sUserId));
    sprintf(sUserId, "test_id1");

    memset(sUserName, 0x00, sizeof(sUserName));
    sprintf(sUserName, "test_name2");

    nRet = sample_table_update(sUserId,sUserName);

    if( nRet < 0 ) {

        goto END;

    }

    // 테이블의 내용 FETCH - 여러개의 DATA 조회


    fprintf(stdout, "start sample_table_fetch\n");

    nRet = sample_table_fetch();

    if( nRet < 0 ) {

        goto END;

    }

    // 테이블의 내용 삭제 처리

    fprintf(stdout, "start sample_table_delete\n");

    memset(sUserId, 0x00, sizeof(sUserId));
    sprintf(sUserId, "test_id1");

    memset(sUserName, 0x00, sizeof(sUserName));
    sprintf(sUserName, "test_name2");

    nRet = sample_table_delete(sUserId,sUserName);

    if( nRet < 0 ) {

        goto END;

    }

    // 테이블의 내용 FETCH - 여러개의 DATA 조회

    fprintf(stdout, "start sample_table_fetch\n");

    nRet = sample_table_fetch();

    if( nRet < 0 ) {

        goto END;

    }

    END:

    // DB 접속종료 RELEASE

    EXEC SQL COMMIT WORK RELEASE;

    return 0;

}

/*** 오라클 UNIQUE데이타 SELECT ***/
int sample_table_select(char *sUserId)
{

    EXEC SQL BEGIN DECLARE SECTION;
        char rzUserId[11] = { '\0' };
        char rzUserName[21] = { '\0' };
    EXEC SQL END DECLARE SECTION;

    int nRet = 0;

    memset(rzUserId, 0x00, sizeof(rzUserId));
    memcpy(rzUserId, sUserId, sizeof(rzUserId)-1 );

    EXEC SQL
        SELECT USER_ID, USER_NAME
        INTO   :rzUserId, :rzUserName
        FROM SAMPLE_TABLE
        WHERE USER_ID = 'test';

    if(sqlca.sqlcode == 0) {
        fprintf(stdout,"sqlca.sqlcode[%d] rzUserId[%s] rzUserName[%s]\n", sqlca.sqlcode, rzUserId, rzUserName);
        nRet = 0;
    }
    else if( sqlca.sqlcode == 1403 ) {
        fprintf(stdout,"sqlca.sqlcode[%d] DATA_NOT_EXIST!!! \n", sqlca.sqlcode);
        nRet = -1;
    }
    else {
        fprintf(stdout,"sqlca.sqlcode[%d] SQL_SELECT_QUERY_FAIL!!!\n", sqlca.sqlcode);
        nRet = -1;
    }

    return nRet;

}


/*** 오라클 데이타 INSERT ***/
int sample_table_insert(char *sUserId, char *sUserName)
{

    EXEC SQL BEGIN DECLARE SECTION;
        char rzUserId[11] = { '\0' };
        char rzUserName[21] = { '\0' };
    EXEC SQL END DECLARE SECTION;

    int nRet = 0;

    memset(rzUserId, 0x00, sizeof(rzUserId));
    memcpy(rzUserId, sUserId, sizeof(rzUserId)-1 );

    memset(rzUserName, 0x00, sizeof(rzUserName));
    memcpy(rzUserName, sUserName, sizeof(rzUserName)-1 );

    EXEC SQL
        INSERT INTO SAMPLE_TABLE(USER_ID, USER_NAME)
        VALUES ( :rzUserId, :rzUserName );

    if(sqlca.sqlcode == 0) {
        fprintf(stdout,"sqlca.sqlcode[%d] rzUserId[%s] rzUserName[%s]\n", sqlca.sqlcode, rzUserId, rzUserName);
        EXEC SQL commit;
        nRet = 0;
    }
    else {
        fprintf(stdout,"sqlca.sqlcode[%d] SQL_INSERT_QUERY_FAIL!!!\n", sqlca.sqlcode);
        EXEC SQL rollback;
        nRet = -1;
    }

    return nRet;

}

/*** 오라클 데이타 UPDATE ***/
int sample_table_update(char *sUserId, char *sUserName)
{

    EXEC SQL BEGIN DECLARE SECTION;
        char rzUserId[11] = { '\0' };
        char rzUserName[21] = { '\0' };
    EXEC SQL END DECLARE SECTION;

    int nRet = 0;

    memset(rzUserId, 0x00, sizeof(rzUserId));
    memcpy(rzUserId, sUserId, sizeof(rzUserId)-1 );

    memset(rzUserName, 0x00, sizeof(rzUserName));
    memcpy(rzUserName, sUserName, sizeof(rzUserName)-1 );

    EXEC SQL
        UPDATE SAMPLE_TABLE
        SET USER_NAME = :rzUserName
        WHERE USER_ID = :rzUserId;

    if(sqlca.sqlcode == 0) {
        fprintf(stdout,"sqlca.sqlcode[%d] rzUserId[%s] rzUserName[%s]\n", sqlca.sqlcode, rzUserId, rzUserName);
        EXEC SQL commit;
        nRet = 0;
    }
    else {
        fprintf(stdout,"sqlca.sqlcode[%d] SQL_UPDATE_QUERY_FAIL!!!\n", sqlca.sqlcode);
        EXEC SQL rollback;
        nRet = -1;
    }

    return nRet;

}

/*** 오라클 데이타 FETCH ***/
int sample_table_fetch()
{

    EXEC SQL BEGIN DECLARE SECTION;
        char rzUserId[11] = { '\0' };
        char rzUserName[21] = { '\0' };
    EXEC SQL END DECLARE SECTION;

    int nRet = 0;

    EXEC SQL DECLARE CUR_SAMPLE_SEL CURSOR FOR
        SELECT USER_ID, USER_NAME
        FROM SAMPLE_TABLE;

    if(sqlca.sqlcode != 0) {
        fprintf(stdout,"ERROR cursor_declare_fail sqlca.sqlcode[%d] \n", sqlca.sqlcode);
        return -1;
    }

    EXEC SQL OPEN CUR_SAMPLE_SEL;
    if(sqlca.sqlcode != 0) {
        fprintf(stdout,"ERROR cursor_open_fail sqlca.sqlcode[%d] \n", sqlca.sqlcode);
        return -1;
    }

    while(1) {

        memset(rzUserId, 0x00, sizeof(rzUserId));
        memset(rzUserName, 0x00, sizeof(rzUserName));

        EXEC SQL FETCH CUR_SAMPLE_SEL INTO :rzUserId, :rzUserName;

        if( sqlca.sqlcode == 0 ) {

            fprintf(stdout, "FETCH rzUserId[%s] rzUserName[%s]\n", rzUserId, rzUserName);

        }
        else if( sqlca.sqlcode == 1403 ) {

            break;

        }
        else {

            fprintf(stdout, "ERROR_FETCH_FAIL sqlca.sqlcode[%d]\n", sqlca.sqlcode);

            nRet = -1;

            break;
        }

    }

    EXEC SQL close CUR_SAMPLE_SEL;

    return nRet;

}

/*** 오라클 데이타 DELETE ***/
int sample_table_delete(char *sUserId, char *sUserName)
{

    EXEC SQL BEGIN DECLARE SECTION;
        char rzUserId[11] = { '\0' };
        char rzUserName[21] = { '\0' };
    EXEC SQL END DECLARE SECTION;

    int nRet = 0;

    memset(rzUserId, 0x00, sizeof(rzUserId));
    memcpy(rzUserId, sUserId, sizeof(rzUserId)-1 );

    memset(rzUserName, 0x00, sizeof(rzUserName));
    memcpy(rzUserName, sUserName, sizeof(rzUserName)-1 );

    EXEC SQL
        DELETE
        FROM SAMPLE_TABLE
        WHERE USER_ID   = :rzUserId
        AND   USER_NAME = :rzUserName;

    if(sqlca.sqlcode == 0) {
        fprintf(stdout,"sqlca.sqlcode[%d] rzUserId[%s]\n", sqlca.sqlcode, rzUserId);

        EXEC SQL commit;

        nRet = 0;
    }
    else {
        fprintf(stdout,"sqlca.sqlcode[%d] SQL_DELETE_QUERY_FAIL!!!\n", sqlca.sqlcode);
        EXEC SQL rollback;
        nRet = -1;
    }

    return nRet;

}

 

================================================================================

 

Makefile 샘플

 

================================================================================

================================================================================

================================================================================

 

 

 

 

#!/bin/sh

PROC            = $(ORACLE_HOME)/bin/proc
CC              = gcc
RM              = rm -f
DEPEND          = makedepend
LINT            = lint
CFLAG           = -g -DDEBUG -g -c -W -Wall -m64

PROC_INC        = include=$(ORACLE_HOME)/precomp/public/ include=$(ORACLE_HOME)/rdbms/demo/ include=$(ORACLE_HOME)/rdbms/public/ include=$(ORACLE_HOME)/network/public/

INC_PATH        = -I. -I./include -I$(ORACLE_HOME)/precomp/public/ -I$(ORACLE_HOME)/rdbms/demo/ -I$(ORACLE_HOME)/rdbms/public/ -I$(ORACLE_HOME)/network/public/
LIB_PATH        = -L. -L$(ORACLE_HOME)/lib

LIBS            = -lsocket -lnsl -lm -lclntsh -ldl -lm `cat $(ORACLE_HOME)/lib/sysliblist`

###############################################################################

PSRCS1          = proc_sample1.pc
SRCS1           = proc_sample1.c
OBJS1           = proc_sample1.o
AOUT1           = proc_sample1

DEST            = .

###############################################################################

.SUFFIXES: .pc .c

.pc.c:
 $(PROC) $* $(PROC_INC)

.c.o:
 $(CC) -c -o $*.o $*.c $(INC_PATH)

###############################################################################

all: $(AOUT1) #install clean

all: $(AOUT1)

$(AOUT1): $(OBJS1)
 $(CC) $(CFLAG) -o $(AOUT1) $(OBJS1) $(LIB_PATH) $(LIBS)
 @echo "컴파일 : $(AOUT1) Complted!!!"

$(OBJS1): $(SRCS1)
 $(CC) $(CFLAG) -c -o $*.o $*.c $(INC_PATH)
 @echo "컴파일 : $(OBJS1) Complted!!!"

$(SRCS1): $(PSRCS1)
 $(PROC) $* $(PROC_INC)
 @echo "컴파일 : $(SRCS1) Complted!!!"

install:
 \mv     $(AOUT1) $(DEST)
 echo "Install Complted!!!"

clean:
 \rm -f $(SRCS1) $(OBJS1) $(AOUT1) core

#DO NOT DELETE THIS LINE -- make depend depends on it.

 

반응형
Posted by 공간사랑
,

티스토리툴바