- 프로젝트는 EmployeeAPP - 이게 프로젝트 이름임.
- employee.h 파일 코드
// ====================================================================
// employee.h
// - 직원 구조체(EMPLOYEE_T) 선언 및 모든 함수 원형 선언
// ====================================================================
#ifndef EMPLOYEE_H
#define EMPLOYEE_H
#include <stdio.h>
// 1. 🚨🚨 Pro*C 오류 방지: 구조체 정의는 .pc 파일과 .main.c 파일에 직접 포함 🚨🚨
// -- 이 헤더 파일에는 구조체의 정의를 두지 않습니다.
struct EMPLOYEE_T; // 불완전 타입 선언
typedef struct EMPLOYEE_T EMPLOYEE_T; // typedef만 남겨둠
// 2. Service Layer (DB 로직) 함수 원형 선언
// READ
EMPLOYEE_T* select_all_employees(int* count);
int select_employee_by_id(const char* emp_id, EMPLOYEE_T* result_emp);
// CREATE, UPDATE, DELETE
int insert_employee(const EMPLOYEE_T* new_emp);
int update_employee(const EMPLOYEE_T* updated_emp);
int delete_employee(const char* emp_id);
// Connection
void connect_db();
void disconnect_db();
// 3. Presentation Layer (공통 UI 로직) 함수 원형 선언
void display_employee_info(EMPLOYEE_T emp, int detail_mode);
#endif // EMPLOYEE_H
// ====================================================================
// employee_main.c
// - UI 및 프로그램 흐름 제어 로직 구현
// ====================================================================
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "employee.h" // 함수 원형 포함
// 🚨🚨 employee.h에서 제거된 EMPLOYEE_T 구조체 정의를 여기에 직접 포함 🚨🚨
struct EMPLOYEE_T {
char emp_id[6];
char full_name[31];
char hire_date[9];
char married[2];
long long salary;
char gender[2];
};
typedef struct EMPLOYEE_T EMPLOYEE_T;
// -----------------------------------------------------------
// ====================================================================
// A. 공통 UI 함수 (employee.h에 선언)
// ====================================================================
// 직원 정보 출력 함수 (Service 함수가 반환한 데이터를 받아서 출력만 담당)
void display_employee_info(EMPLOYEE_T emp, int detail_mode) {
char on_leave_status[] = "Working"; // 재직 상태는 현재 코드에서 임의로 "Working"으로 고정
if (detail_mode == 0) {
// 목록 조회 모드 (간략 정보)
printf("%-10s // %-20s // %s\\n",
emp.emp_id, emp.full_name, on_leave_status);
}
else {
// 상세 조회 모드
printf("\\n--- 직원 상세 정보 ---\\n");
printf(" - 사번 (EMP_ID) : %s\\n", emp.emp_id);
printf(" - 이름 (FULL_NAME) : %s\\n", emp.full_name);
printf(" - 입사일 (HIRE_DATE): %s\\n", emp.hire_date);
printf(" - 결혼 여부 : %s\\n", emp.married);
printf(" - 성별 (GENDER) : %s\\n", emp.gender);
printf(" - 연봉 (SALARY) : %lld\\n", emp.salary); // long long에 맞게 %lld 사용
printf(" - 재직 상태 : %s\\n", on_leave_status);
printf("----------------------\\n");
}
}
// ====================================================================
// B. Presentation Layer (각 기능별 화면/입력 처리 로직)
// ====================================================================
// 1. 전체 직원 목록 조회 UI
void print_all_employees_ui() {
int count = 0;
// **Service 함수 호출:** DB에서 데이터 조회
EMPLOYEE_T* employees = select_all_employees(&count);
printf("\\n============================================\\n");
printf(" ? DB에서 가져온 전체 직원 목록 ?\\n");
printf("============================================\\n");
printf("%-10s // %-20s // %s\\n", "사번", "이름", "상태");
printf("--------------------------------------------\\n");
if (count > 0 && employees != NULL) {
// 화면 출력 로직
for (int i = 0; i < count; i++) {
display_employee_info(employees[i], 0);
}
}
else if (count == 0) {
printf("조회된 직원이 없습니다.\\n");
}
else {
printf("데이터 조회 중 오류가 발생했습니다.\\n");
}
printf("============================================\\n");
printf("총 %d명의 직원을 DB에서 조회했습니다.\\n", count);
// 메모리 해제
if (employees != NULL) free(employees);
}
// 2. 특정 직원 상세 조회 UI
void print_employee_detail_ui() {
char search_id[6];
EMPLOYEE_T detail_emp;
printf("\\n조회할 사원의 사번(EMP_ID)을 입력하세요: ");
if (scanf("%s", search_id) != 1) return;
// **Service 함수 호출:** DB에서 데이터 조회
int result = select_employee_by_id(search_id, &detail_emp);
if (result == 0) {
display_employee_info(detail_emp, 1); // 성공 시 화면 출력
}
else if (result == -1) {
printf("\\n 사번 [%s]에 해당하는 직원을 찾을 수 없습니다.\\n", search_id);
}
else {
printf("\\n 상세 조회 처리 중 DB 오류가 발생했습니다.\\n");
}
}
// 3. 직원 추가 UI (CREATE)
void insert_employee_ui() {
EMPLOYEE_T new_emp;
printf("\\n--- 신규 직원 정보 입력 ---\\n");
printf("사번 (EMP_ID, 5자): "); if (scanf("%s", new_emp.emp_id) != 1) return;
printf("이름 (FULL_NAME): "); if (scanf("%s", new_emp.full_name) != 1) return;
printf("입사일 (HIRE_DATE, YYYYMMDD): "); if (scanf("%s", new_emp.hire_date) != 1) return;
printf("결혼 여부 (Y/N): "); if (scanf("%s", new_emp.married) != 1) return;
printf("성별 (M/F): "); if (scanf("%s", new_emp.gender) != 1) return;
printf("연봉 (SALARY): ");
if (scanf("%lld", &new_emp.salary) != 1) {
printf("오류: 연봉 입력이 올바르지 않습니다.\\n");
// 입력 버퍼 비우기
while (getchar() != '\\n');
return;
}
// **Service 함수 호출:** DB에 데이터 추가
int result = insert_employee(&new_emp);
if (result == 0) {
printf("\\n✅ 사번 [%s] 직원이 성공적으로 추가되었습니다.\\n", new_emp.emp_id);
}
else {
printf("\\n❌ 직원 추가 중 오류가 발생했습니다. (DB 에러)\\n");
}
}
// 4. 직원 정보 수정 UI (UPDATE)
void update_employee_ui() {
char search_id[6];
EMPLOYEE_T old_emp, updated_emp;
printf("\\n--- 직원 정보 수정 ---\\n");
printf("수정할 직원의 사번(EMP_ID)을 입력하세요: ");
if (scanf("%s", search_id) != 1) return;
// 먼저 기존 데이터 조회 (수정할 대상이 있는지 확인)
if (select_employee_by_id(search_id, &old_emp) != 0) {
printf("사번 [%s]에 해당하는 직원을 찾을 수 없습니다.\\n", search_id);
return;
}
// 사번은 수정하지 않으므로 복사
strcpy(updated_emp.emp_id, old_emp.emp_id);
printf("수정 전 이름: %s\\n", old_emp.full_name);
printf("새 이름 (FULL_NAME): "); if (scanf("%s", updated_emp.full_name) != 1) return;
printf("새 입사일 (HIRE_DATE, %s): ", old_emp.hire_date); if (scanf("%s", updated_emp.hire_date) != 1) return;
printf("새 결혼 여부 (Y/N, %s): ", old_emp.married); if (scanf("%s", updated_emp.married) != 1) return;
printf("새 성별 (M/F, %s): ", old_emp.gender); if (scanf("%s", updated_emp.gender) != 1) return;
printf("새 연봉 (SALARY, %lld): ", old_emp.salary);
if (scanf("%lld", &updated_emp.salary) != 1) {
printf("오류: 연봉 입력이 올바르지 않습니다.\\n");
// 입력 버퍼 비우기
while (getchar() != '\\n');
return;
}
// **Service 함수 호출:** DB에 데이터 수정
int result = update_employee(&updated_emp);
if (result == 0) {
printf("\\n✅ 사번 [%s] 직원의 정보가 성공적으로 수정되었습니다.\\n", updated_emp.emp_id);
}
else if (result == 100) {
printf("\\n⚠️ 수정 대상 직원을 찾을 수 없습니다. (사번 불일치)\\n");
}
else {
printf("\\n❌ 직원 정보 수정 중 오류가 발생했습니다. (DB 에러)\\n");
}
}
// 5. 직원 삭제 UI (DELETE)
void delete_employee_ui() {
char delete_id[6];
printf("\\n--- 직원 삭제 ---\\n");
printf("삭제할 직원의 사번(EMP_ID)을 입력하세요: ");
if (scanf("%s", delete_id) != 1) return;
// **Service 함수 호출:** DB에서 데이터 삭제
int result = delete_employee(delete_id);
if (result == 0) {
printf("\\n✅ 사번 [%s] 직원이 성공적으로 삭제되었습니다.\\n", delete_id);
}
else if (result == 100) {
printf("\\n⚠️ 사번 [%s]에 해당하는 직원을 찾을 수 없습니다.\\n", delete_id);
}
else {
printf("\\n❌ 직원 삭제 중 오류가 발생했습니다. (DB 에러)\\n");
}
}
// ====================================================================
// C. Main 로직 (프로그램의 시작과 종료, 흐름 제어)
// ====================================================================
// 메인 메뉴 표시
void display_main_menu() {
printf("\\n====================================\\n");
printf(" [ 직원 관리 프로그램 ] \\n");
printf("====================================\\n");
printf(" 1. 전체 목록 조회 (READ All) \\n");
printf(" 2. 상세 정보 조회 (READ One) \\n");
printf(" 3. 신규 직원 추가 (CREATE) \\n");
printf(" 4. 직원 정보 수정 (UPDATE) \\n");
printf(" 5. 직원 정보 삭제 (DELETE) \\n");
printf(" 6. 프로그램 종료 (EXIT) \\n");
printf("------------------------------------\\n");
printf("선택 > ");
}
int main() {
int choice;
connect_db(); // 1. DB 연결 (Service Layer 호출)
while (1) {
display_main_menu();
if (scanf("%d", &choice) != 1) {
// 잘못된 입력 처리 (문자 입력 등)
printf("\\n⚠️ 잘못된 입력입니다. 1~6 사이의 숫자를 입력해주세요.\\n");
// 입력 버퍼 비우기
while (getchar() != '\\n');
continue;
}
switch (choice) {
case 1:
print_all_employees_ui(); // 전체 조회 UI 호출
break;
case 2:
print_employee_detail_ui(); // 상세 조회 UI 호출
break;
case 3:
insert_employee_ui(); // 추가 UI 호출
break;
case 4:
update_employee_ui(); // 수정 UI 호출
break;
case 5:
delete_employee_ui(); // 삭제 UI 호출
break;
case 6:
printf("\\n프로그램을 종료합니다.\\n");
disconnect_db(); // 2. DB 연결 해제 (Service Layer 호출)
return 0;
default:
printf("\\n⚠️ 올바른 메뉴 번호를 선택해주세요. (1~6)\\n");
break;
}
}
return 0;
}
- employee_service.c 코드 - 이건 pc파일에서 자동 생성되는 것으로 안다. 맞지???
/* Result Sets Interface */
#ifndef SQL_CRSR
# define SQL_CRSR
struct sql_cursor
{
unsigned int curocn;
void *ptr1;
void *ptr2;
unsigned int magic;
};
typedef struct sql_cursor sql_cursor;
typedef struct sql_cursor SQL_CURSOR;
#endif /* SQL_CRSR */
/* Thread Safety */
typedef void * sql_context;
typedef void * SQL_CONTEXT;
/* Object support */
struct sqltvn
{
unsigned char *tvnvsn;
unsigned short tvnvsnl;
unsigned char *tvnnm;
unsigned short tvnnml;
unsigned char *tvnsnm;
unsigned short tvnsnml;
};
typedef struct sqltvn sqltvn;
struct sqladts
{
unsigned int adtvsn;
unsigned short adtmode;
unsigned short adtnum;
sqltvn adttvn[1];
};
typedef struct sqladts sqladts;
static struct sqladts sqladt = {
1,1,0,
};
/* Binding to PL/SQL Records */
struct sqltdss
{
unsigned int tdsvsn;
unsigned short tdsnum;
unsigned char *tdsval[1];
};
typedef struct sqltdss sqltdss;
static struct sqltdss sqltds =
{
1,
0,
};
/* File name & Package Name */
struct sqlcxp
{
unsigned short fillen;
char filnam[60];
};
static const struct sqlcxp sqlfpn =
{
59,
"C:\\\\Users\\\\USER\\\\source\\\\repos\\\\EmployeesAPP\\\\employee_service.pc"
};
static unsigned int sqlctx = 2028736147;
static struct sqlexd {
unsigned int sqlvsn;
unsigned int arrsiz;
unsigned int iters;
unsigned int offset;
unsigned short selerr;
unsigned short sqlety;
unsigned int occurs;
const short *cud;
unsigned char *sqlest;
const char *stmt;
sqladts *sqladtp;
sqltdss *sqltdsp;
void **sqphsv;
unsigned int *sqphsl;
int *sqphss;
void **sqpind;
int *sqpins;
unsigned int *sqparm;
unsigned int **sqparc;
unsigned short *sqpadto;
unsigned short *sqptdso;
unsigned int sqlcmax;
unsigned int sqlcmin;
unsigned int sqlcincr;
unsigned int sqlctimeout;
unsigned int sqlcnowait;
int sqfoff;
unsigned int sqcmod;
unsigned int sqfmod;
void *sqhstv[7];
unsigned int sqhstl[7];
int sqhsts[7];
void *sqindv[7];
int sqinds[7];
unsigned int sqharm[7];
unsigned int *sqharc[7];
unsigned short sqadto[7];
unsigned short sqtdso[7];
} sqlstm = {12,7};
/* SQLLIB Prototypes */
extern void sqlcxt (void **, unsigned int *,
struct sqlexd *, const struct sqlcxp *);
extern void sqlcx2t(void **, unsigned int *,
struct sqlexd *, const struct sqlcxp *);
extern void sqlbuft(void **, char *);
extern void sqlgs2t(void **, char *);
extern void sqlorat(void **, unsigned int *, void *);
/* Forms Interface */
static const int IAPSUCC = 0;
static const int IAPFAIL = 1403;
static const int IAPFTL = 535;
extern void sqliem(unsigned char *, signed int *);
static const char *sq0003 =
"select EMPL_ID ,FULL_NAME ,HIRE_DATE ,MARRIED ,GENDER ,SALARY from EMPLOYE\\
ES order by EMPL_ID ";
typedef struct { unsigned short len; unsigned char arr[1]; } VARCHAR;
typedef struct { unsigned short len; unsigned char arr[1]; } varchar;
/* cud (compilation unit data) array */
static const short sqlcud0[] =
{12,4274,846,0,0,
5,0,0,0,0,0,27,36,0,0,4,4,0,1,0,1,97,0,0,1,97,0,0,1,97,0,0,1,10,0,0,
36,0,0,2,0,0,30,47,0,0,0,0,0,1,0,
51,0,0,3,107,0,9,73,0,0,0,0,0,1,0,
66,0,0,3,0,0,13,77,0,0,6,0,0,1,0,2,97,0,0,2,97,0,0,2,97,0,0,2,97,0,0,2,97,0,0,
2,3,0,0,
105,0,0,3,0,0,15,84,0,0,0,0,0,1,0,
120,0,0,3,0,0,15,94,0,0,0,0,0,1,0,
135,0,0,3,0,0,15,103,0,0,0,0,0,1,0,
150,0,0,4,124,0,4,128,0,0,7,1,0,1,0,2,97,0,0,2,97,0,0,2,97,0,0,2,97,0,0,2,97,0,
0,2,3,0,0,1,97,0,0,
193,0,0,5,106,0,3,166,0,0,6,6,0,1,0,1,97,0,0,1,97,0,0,1,97,0,0,1,97,0,0,1,97,0,
0,1,3,0,0,
232,0,0,6,0,0,29,174,0,0,0,0,0,1,0,
247,0,0,7,101,0,5,186,0,0,6,6,0,1,0,1,97,0,0,1,97,0,0,1,97,0,0,1,97,0,0,1,3,0,
0,1,97,0,0,
286,0,0,8,0,0,29,203,0,0,0,0,0,1,0,
301,0,0,9,41,0,2,216,0,0,1,1,0,1,0,1,97,0,0,
320,0,0,10,0,0,29,228,0,0,0,0,0,1,0,
};
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
// employee.h는 구조체 선언이 빠졌으므로, 함수 원형만 가져옵니다.
#include "employee.h"
// 1. Pro*C 필수 영역: SQLCA 및 호스트 변수 선언
/* EXEC SQL INCLUDE SQLCA;
*/
/*
* $Header: sqlca.h 24-apr-2003.12:50:58 mkandarp Exp $ sqlca.h
*/
/* Copyright (c) 1985, 2003, Oracle Corporation. All rights reserved. */
/*
NAME
SQLCA : SQL Communications Area.
FUNCTION
Contains no code. Oracle fills in the SQLCA with status info
during the execution of a SQL stmt.
NOTES
**************************************************************
*** ***
*** This file is SOSD. Porters must change the data types ***
*** appropriately on their platform. See notes/pcport.doc ***
*** for more information. ***
*** ***
**************************************************************
If the symbol SQLCA_STORAGE_CLASS is defined, then the SQLCA
will be defined to have this storage class. For example:
#define SQLCA_STORAGE_CLASS extern
will define the SQLCA as an extern.
If the symbol SQLCA_INIT is defined, then the SQLCA will be
statically initialized. Although this is not necessary in order
to use the SQLCA, it is a good pgming practice not to have
unitialized variables. However, some C compilers/OS's don't
allow automatic variables to be init'd in this manner. Therefore,
if you are INCLUDE'ing the SQLCA in a place where it would be
an automatic AND your C compiler/OS doesn't allow this style
of initialization, then SQLCA_INIT should be left undefined --
all others can define SQLCA_INIT if they wish.
If the symbol SQLCA_NONE is defined, then the SQLCA variable will
not be defined at all. The symbol SQLCA_NONE should not be defined
in source modules that have embedded SQL. However, source modules
that have no embedded SQL, but need to manipulate a sqlca struct
passed in as a parameter, can set the SQLCA_NONE symbol to avoid
creation of an extraneous sqlca variable.
MODIFIED
lvbcheng 07/31/98 - long to int
jbasu 12/12/94 - Bug 217878: note this is an SOSD file
losborne 08/11/92 - No sqlca var if SQLCA_NONE macro set
Clare 12/06/84 - Ch SQLCA to not be an extern.
Clare 10/21/85 - Add initialization.
Bradbury 01/05/86 - Only initialize when SQLCA_INIT set
Clare 06/12/86 - Add SQLCA_STORAGE_CLASS option.
*/
#ifndef SQLCA
#define SQLCA 1
struct sqlca
{
/* ub1 */ char sqlcaid[8];
/* b4 */ int sqlabc;
/* b4 */ int sqlcode;
struct
{
/* ub2 */ unsigned short sqlerrml;
/* ub1 */ char sqlerrmc[70];
} sqlerrm;
/* ub1 */ char sqlerrp[8];
/* b4 */ int sqlerrd[6];
/* ub1 */ char sqlwarn[8];
/* ub1 */ char sqlext[8];
};
#ifndef SQLCA_NONE
#ifdef SQLCA_STORAGE_CLASS
SQLCA_STORAGE_CLASS struct sqlca sqlca
#else
struct sqlca sqlca
#endif
#ifdef SQLCA_INIT
= {
{'S', 'Q', 'L', 'C', 'A', ' ', ' ', ' '},
sizeof(struct sqlca),
0,
{ 0, {0}},
{'N', 'O', 'T', ' ', 'S', 'E', 'T', ' '},
{0, 0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, 0, 0}
}
#endif
;
#endif
#endif
/* end SQLCA */
/* EXEC SQL BEGIN DECLARE SECTION; */
// DB 접속 정보 (사용자 환경에 맞게 수정 필요)
char db_user[] = "scott";
char db_pass[] = "tiger";
char db_connect_string[] = "ORCL"; // TNS 이름 또는 SID
// EMPLOYEE_T 정의를 여기에 직접 포함 (Pro*C에서 구조체를 DECLARE SECTION 안에서 사용 가능하도록)
struct EMPLOYEE_T {
char emp_id[6];
char full_name[31];
char hire_date[9];
char married[2];
long long salary;
char gender[2];
};
typedef struct EMPLOYEE_T EMPLOYEE_T;
/* EXEC SQL END DECLARE SECTION; */
// ====================================================================
// Connection Logic
// ====================================================================
// DB 연결
void connect_db() {
printf("DB 연결 시도 중...\\n");
// [사용자 이름/패스워드]@[연결 문자열] 형식으로 연결
/* EXEC SQL CONNECT :db_user IDENTIFIED BY :db_pass USING :db_connect_string; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )10;
sqlstm.offset = (unsigned int )5;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)db_user;
sqlstm.sqhstl[0] = (unsigned int )0;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)db_pass;
sqlstm.sqhstl[1] = (unsigned int )0;
sqlstm.sqhsts[1] = ( int )0;
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqhstv[2] = ( void *)db_connect_string;
sqlstm.sqhstl[2] = (unsigned int )0;
sqlstm.sqhsts[2] = ( int )0;
sqlstm.sqindv[2] = ( void *)0;
sqlstm.sqinds[2] = ( int )0;
sqlstm.sqharm[2] = (unsigned int )0;
sqlstm.sqadto[2] = (unsigned short )0;
sqlstm.sqtdso[2] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlstm.sqlcmax = (unsigned int )100;
sqlstm.sqlcmin = (unsigned int )2;
sqlstm.sqlcincr = (unsigned int )1;
sqlstm.sqlctimeout = (unsigned int )0;
sqlstm.sqlcnowait = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
if (sqlca.sqlcode != 0) {
printf(" [DB ERROR] 연결 실패 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
exit(1);
}
printf("DB에 성공적으로 연결되었습니다.\\n");
}
// DB 연결 해제
void disconnect_db() {
/* EXEC SQL COMMIT RELEASE; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )36;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
// 모든 트랜잭션 커밋 및 연결 해제
printf("DB 연결이 해제되었습니다.\\n");
}
// ====================================================================
// A. READ (조회)
// ====================================================================
// 1. 전체 목록 조회 (READ All)
EMPLOYEE_T* select_all_employees(int* count) {
// DB 로직에서 사용할 변수 선언 (호스트 변수는 DECLARE SECTION 안에 있어야 함)
/* EXEC SQL BEGIN DECLARE SECTION; */
EMPLOYEE_T current_emp;
/* EXEC SQL END DECLARE SECTION; */
// 조회 결과를 담을 동적 배열 포인터
EMPLOYEE_T* employees = NULL;
int found_count = 0;
// 커서 선언 (전체 조회)
/* EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT EMPL_ID, FULL_NAME, HIRE_DATE, MARRIED, GENDER, SALARY
FROM EMPLOYEES
ORDER BY EMPL_ID; */
/* EXEC SQL OPEN emp_cursor; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 4;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = sq0003;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )51;
sqlstm.selerr = (unsigned short)1;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqcmod = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
// 데이터 Fetch 및 동적 배열에 추가
while (1) {
/* EXEC SQL FETCH emp_cursor INTO
:current_emp.emp_id, :current_emp.full_name, :current_emp.hire_date,
:current_emp.married, :current_emp.gender, :current_emp.salary; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 6;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )66;
sqlstm.selerr = (unsigned short)1;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqfoff = ( int )0;
sqlstm.sqfmod = (unsigned int )2;
sqlstm.sqhstv[0] = ( void *)(current_emp.emp_id);
sqlstm.sqhstl[0] = (unsigned int )6;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)(current_emp.full_name);
sqlstm.sqhstl[1] = (unsigned int )31;
sqlstm.sqhsts[1] = ( int )0;
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqhstv[2] = ( void *)(current_emp.hire_date);
sqlstm.sqhstl[2] = (unsigned int )9;
sqlstm.sqhsts[2] = ( int )0;
sqlstm.sqindv[2] = ( void *)0;
sqlstm.sqinds[2] = ( int )0;
sqlstm.sqharm[2] = (unsigned int )0;
sqlstm.sqadto[2] = (unsigned short )0;
sqlstm.sqtdso[2] = (unsigned short )0;
sqlstm.sqhstv[3] = ( void *)(current_emp.married);
sqlstm.sqhstl[3] = (unsigned int )2;
sqlstm.sqhsts[3] = ( int )0;
sqlstm.sqindv[3] = ( void *)0;
sqlstm.sqinds[3] = ( int )0;
sqlstm.sqharm[3] = (unsigned int )0;
sqlstm.sqadto[3] = (unsigned short )0;
sqlstm.sqtdso[3] = (unsigned short )0;
sqlstm.sqhstv[4] = ( void *)(current_emp.gender);
sqlstm.sqhstl[4] = (unsigned int )2;
sqlstm.sqhsts[4] = ( int )0;
sqlstm.sqindv[4] = ( void *)0;
sqlstm.sqinds[4] = ( int )0;
sqlstm.sqharm[4] = (unsigned int )0;
sqlstm.sqadto[4] = (unsigned short )0;
sqlstm.sqtdso[4] = (unsigned short )0;
sqlstm.sqhstv[5] = ( void *)&(current_emp.salary);
sqlstm.sqhstl[5] = (unsigned int )sizeof(long long);
sqlstm.sqhsts[5] = ( int )0;
sqlstm.sqindv[5] = ( void *)0;
sqlstm.sqinds[5] = ( int )0;
sqlstm.sqharm[5] = (unsigned int )0;
sqlstm.sqadto[5] = (unsigned short )0;
sqlstm.sqtdso[5] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
if (sqlca.sqlcode == 1403) break; // NO DATA FOUND
if (sqlca.sqlcode != 0) {
printf(" [DB ERROR] 전체 조회 오류 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
/* EXEC SQL CLOSE emp_cursor; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 6;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )105;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
*count = 0;
if (employees != NULL) free(employees);
return NULL;
}
// 동적 메모리 재할당 (새로운 레코드를 위한 공간 확보)
employees = (EMPLOYEE_T*)realloc(employees, (found_count + 1) * sizeof(EMPLOYEE_T));
if (employees == NULL) {
printf(" [MEMORY ERROR] 메모리 할당 실패\\n");
/* EXEC SQL CLOSE emp_cursor; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 6;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )120;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
*count = 0;
return NULL;
}
// 현재 조회된 데이터를 동적 배열의 마지막 위치에 복사
employees[found_count] = current_emp;
found_count++;
}
/* EXEC SQL CLOSE emp_cursor; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 6;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )135;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
*count = found_count;
return employees; // 조회된 직원 정보 배열 포인터 반환
}
// 2. 상세 목록 조회 (READ One)
int select_employee_by_id(const char* emp_id, EMPLOYEE_T* result_emp) {
// Pro*C INTO 에는 DECLARE SECTION 에 선언된 호스트 변수만 사용 가능하므로,
// 로컬 호스트 변수에 SELECT 결과를 받고 그 뒤에 result_emp에 복사합니다.
/* EXEC SQL BEGIN DECLARE SECTION; */
char l_search_id[6];
char l_emp_id[6];
char l_full_name[31];
char l_hire_date[9];
char l_married[2];
char l_gender[2];
long long l_salary;
/* EXEC SQL END DECLARE SECTION; */
// 입력받은 사번을 Host Variable에 복사
memset(l_search_id, 0, sizeof(l_search_id));
strncpy(l_search_id, emp_id, sizeof(l_search_id)-1);
/* EXEC SQL SELECT EMPL_ID, FULL_NAME, HIRE_DATE, MARRIED, GENDER, SALARY
INTO :l_emp_id, :l_full_name, :l_hire_date, :l_married, :l_gender, :l_salary
FROM EMPLOYEES
WHERE EMPL_ID = :l_search_id; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 7;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "select EMPL_ID ,FULL_NAME ,HIRE_DATE ,MARRIED ,GENDER ,S\\
ALARY into :b0,:b1,:b2,:b3,:b4,:b5 from EMPLOYEES where EMPL_ID=:b6";
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )150;
sqlstm.selerr = (unsigned short)1;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)l_emp_id;
sqlstm.sqhstl[0] = (unsigned int )6;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)l_full_name;
sqlstm.sqhstl[1] = (unsigned int )31;
sqlstm.sqhsts[1] = ( int )0;
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqhstv[2] = ( void *)l_hire_date;
sqlstm.sqhstl[2] = (unsigned int )9;
sqlstm.sqhsts[2] = ( int )0;
sqlstm.sqindv[2] = ( void *)0;
sqlstm.sqinds[2] = ( int )0;
sqlstm.sqharm[2] = (unsigned int )0;
sqlstm.sqadto[2] = (unsigned short )0;
sqlstm.sqtdso[2] = (unsigned short )0;
sqlstm.sqhstv[3] = ( void *)l_married;
sqlstm.sqhstl[3] = (unsigned int )2;
sqlstm.sqhsts[3] = ( int )0;
sqlstm.sqindv[3] = ( void *)0;
sqlstm.sqinds[3] = ( int )0;
sqlstm.sqharm[3] = (unsigned int )0;
sqlstm.sqadto[3] = (unsigned short )0;
sqlstm.sqtdso[3] = (unsigned short )0;
sqlstm.sqhstv[4] = ( void *)l_gender;
sqlstm.sqhstl[4] = (unsigned int )2;
sqlstm.sqhsts[4] = ( int )0;
sqlstm.sqindv[4] = ( void *)0;
sqlstm.sqinds[4] = ( int )0;
sqlstm.sqharm[4] = (unsigned int )0;
sqlstm.sqadto[4] = (unsigned short )0;
sqlstm.sqtdso[4] = (unsigned short )0;
sqlstm.sqhstv[5] = ( void *)&l_salary;
sqlstm.sqhstl[5] = (unsigned int )sizeof(long long);
sqlstm.sqhsts[5] = ( int )0;
sqlstm.sqindv[5] = ( void *)0;
sqlstm.sqinds[5] = ( int )0;
sqlstm.sqharm[5] = (unsigned int )0;
sqlstm.sqadto[5] = (unsigned short )0;
sqlstm.sqtdso[5] = (unsigned short )0;
sqlstm.sqhstv[6] = ( void *)l_search_id;
sqlstm.sqhstl[6] = (unsigned int )6;
sqlstm.sqhsts[6] = ( int )0;
sqlstm.sqindv[6] = ( void *)0;
sqlstm.sqinds[6] = ( int )0;
sqlstm.sqharm[6] = (unsigned int )0;
sqlstm.sqadto[6] = (unsigned short )0;
sqlstm.sqtdso[6] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
if (sqlca.sqlcode == 1403) {
return -1; // 데이터 없음 (NO DATA FOUND)
} else if (sqlca.sqlcode != 0) {
printf(" [DB ERROR] 상세 조회 오류 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return -2; // DB 오류
}
// SELECT로 얻은 호스트 변수 값을 호출자 result_emp에 복사
memset(result_emp, 0, sizeof(EMPLOYEE_T));
strncpy(result_emp->emp_id, l_emp_id, sizeof(result_emp->emp_id)-1);
strncpy(result_emp->full_name, l_full_name, sizeof(result_emp->full_name)-1);
strncpy(result_emp->hire_date, l_hire_date, sizeof(result_emp->hire_date)-1);
strncpy(result_emp->married, l_married, sizeof(result_emp->married)-1);
strncpy(result_emp->gender, l_gender, sizeof(result_emp->gender)-1);
result_emp->salary = l_salary;
return 0; // 성공
}
// ====================================================================
// B. CREATE, UPDATE, DELETE (추가, 수정, 삭제)
// ====================================================================
// 3. 직원 정보 추가 (CREATE)
int insert_employee(const EMPLOYEE_T* new_emp) {
/* EXEC SQL BEGIN DECLARE SECTION; */
EMPLOYEE_T l_emp;
/* EXEC SQL END DECLARE SECTION; */
// 구조체 복사
l_emp = *new_emp;
/* EXEC SQL INSERT INTO EMPLOYEES (EMPL_ID, FULL_NAME, HIRE_DATE, MARRIED, GENDER, SALARY)
VALUES (:l_emp.emp_id, :l_emp.full_name, :l_emp.hire_date, :l_emp.married, :l_emp.gender, :l_emp.salary); */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 7;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "insert into EMPLOYEES (EMPL_ID,FULL_NAME,HIRE_DATE,MARRI\\
ED,GENDER,SALARY) values (:b0,:b1,:b2,:b3,:b4,:b5)";
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )193;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)(l_emp.emp_id);
sqlstm.sqhstl[0] = (unsigned int )6;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)(l_emp.full_name);
sqlstm.sqhstl[1] = (unsigned int )31;
sqlstm.sqhsts[1] = ( int )0;
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqhstv[2] = ( void *)(l_emp.hire_date);
sqlstm.sqhstl[2] = (unsigned int )9;
sqlstm.sqhsts[2] = ( int )0;
sqlstm.sqindv[2] = ( void *)0;
sqlstm.sqinds[2] = ( int )0;
sqlstm.sqharm[2] = (unsigned int )0;
sqlstm.sqadto[2] = (unsigned short )0;
sqlstm.sqtdso[2] = (unsigned short )0;
sqlstm.sqhstv[3] = ( void *)(l_emp.married);
sqlstm.sqhstl[3] = (unsigned int )2;
sqlstm.sqhsts[3] = ( int )0;
sqlstm.sqindv[3] = ( void *)0;
sqlstm.sqinds[3] = ( int )0;
sqlstm.sqharm[3] = (unsigned int )0;
sqlstm.sqadto[3] = (unsigned short )0;
sqlstm.sqtdso[3] = (unsigned short )0;
sqlstm.sqhstv[4] = ( void *)(l_emp.gender);
sqlstm.sqhstl[4] = (unsigned int )2;
sqlstm.sqhsts[4] = ( int )0;
sqlstm.sqindv[4] = ( void *)0;
sqlstm.sqinds[4] = ( int )0;
sqlstm.sqharm[4] = (unsigned int )0;
sqlstm.sqadto[4] = (unsigned short )0;
sqlstm.sqtdso[4] = (unsigned short )0;
sqlstm.sqhstv[5] = ( void *)&(l_emp.salary);
sqlstm.sqhstl[5] = (unsigned int )sizeof(long long);
sqlstm.sqhsts[5] = ( int )0;
sqlstm.sqindv[5] = ( void *)0;
sqlstm.sqinds[5] = ( int )0;
sqlstm.sqharm[5] = (unsigned int )0;
sqlstm.sqadto[5] = (unsigned short )0;
sqlstm.sqtdso[5] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
if (sqlca.sqlcode != 0) {
printf(" [DB ERROR] 직원 추가 오류 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return -1; // DB 오류
}
/* EXEC SQL COMMIT; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 7;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )232;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
// 변경 사항 반영
return 0; // 성공
}
// 4. 직원 정보 수정 (UPDATE)
int update_employee(const EMPLOYEE_T* updated_emp) {
/* EXEC SQL BEGIN DECLARE SECTION; */
EMPLOYEE_T l_emp;
/* EXEC SQL END DECLARE SECTION; */
l_emp = *updated_emp;
/* EXEC SQL UPDATE EMPLOYEES
SET FULL_NAME = :l_emp.full_name,
HIRE_DATE = :l_emp.hire_date,
MARRIED = :l_emp.married,
GENDER = :l_emp.gender,
SALARY = :l_emp.salary
WHERE EMPL_ID = :l_emp.emp_id; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 7;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "update EMPLOYEES set FULL_NAME=:b0,HIRE_DATE=:b1,MARRIE\\
D=:b2,GENDER=:b3,SALARY=:b4 where EMPL_ID=:b5";
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )247;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)(l_emp.full_name);
sqlstm.sqhstl[0] = (unsigned int )31;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqhstv[1] = ( void *)(l_emp.hire_date);
sqlstm.sqhstl[1] = (unsigned int )9;
sqlstm.sqhsts[1] = ( int )0;
sqlstm.sqindv[1] = ( void *)0;
sqlstm.sqinds[1] = ( int )0;
sqlstm.sqharm[1] = (unsigned int )0;
sqlstm.sqadto[1] = (unsigned short )0;
sqlstm.sqtdso[1] = (unsigned short )0;
sqlstm.sqhstv[2] = ( void *)(l_emp.married);
sqlstm.sqhstl[2] = (unsigned int )2;
sqlstm.sqhsts[2] = ( int )0;
sqlstm.sqindv[2] = ( void *)0;
sqlstm.sqinds[2] = ( int )0;
sqlstm.sqharm[2] = (unsigned int )0;
sqlstm.sqadto[2] = (unsigned short )0;
sqlstm.sqtdso[2] = (unsigned short )0;
sqlstm.sqhstv[3] = ( void *)(l_emp.gender);
sqlstm.sqhstl[3] = (unsigned int )2;
sqlstm.sqhsts[3] = ( int )0;
sqlstm.sqindv[3] = ( void *)0;
sqlstm.sqinds[3] = ( int )0;
sqlstm.sqharm[3] = (unsigned int )0;
sqlstm.sqadto[3] = (unsigned short )0;
sqlstm.sqtdso[3] = (unsigned short )0;
sqlstm.sqhstv[4] = ( void *)&(l_emp.salary);
sqlstm.sqhstl[4] = (unsigned int )sizeof(long long);
sqlstm.sqhsts[4] = ( int )0;
sqlstm.sqindv[4] = ( void *)0;
sqlstm.sqinds[4] = ( int )0;
sqlstm.sqharm[4] = (unsigned int )0;
sqlstm.sqadto[4] = (unsigned short )0;
sqlstm.sqtdso[4] = (unsigned short )0;
sqlstm.sqhstv[5] = ( void *)(l_emp.emp_id);
sqlstm.sqhstl[5] = (unsigned int )6;
sqlstm.sqhsts[5] = ( int )0;
sqlstm.sqindv[5] = ( void *)0;
sqlstm.sqinds[5] = ( int )0;
sqlstm.sqharm[5] = (unsigned int )0;
sqlstm.sqadto[5] = (unsigned short )0;
sqlstm.sqtdso[5] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
if (sqlca.sqlcode != 0 && sqlca.sqlcode != 100) {
printf(" [DB ERROR] 직원 수정 오류 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return -1; // DB 오류
}
if (sqlca.sqlcode == 100 || sqlca.sqlcode == 1403) { // 100은 UPDATE/DELETE 시 레코드가 없을 때 주로 발생
return 100; // 수정된 데이터 없음 (사번 없음)
}
/* EXEC SQL COMMIT; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 7;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )286;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
// 변경 사항 반영
return 0; // 성공
}
// 5. 직원 정보 삭제 (DELETE)
int delete_employee(const char* emp_id) {
/* EXEC SQL BEGIN DECLARE SECTION; */
char l_del_id[6];
/* EXEC SQL END DECLARE SECTION; */
memset(l_del_id, 0, sizeof(l_del_id));
strncpy(l_del_id, emp_id, sizeof(l_del_id)-1);
/* EXEC SQL DELETE FROM EMPLOYEES
WHERE EMPL_ID = :l_del_id; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 7;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.stmt = "delete from EMPLOYEES where EMPL_ID=:b0";
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )301;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlstm.sqhstv[0] = ( void *)l_del_id;
sqlstm.sqhstl[0] = (unsigned int )6;
sqlstm.sqhsts[0] = ( int )0;
sqlstm.sqindv[0] = ( void *)0;
sqlstm.sqinds[0] = ( int )0;
sqlstm.sqharm[0] = (unsigned int )0;
sqlstm.sqadto[0] = (unsigned short )0;
sqlstm.sqtdso[0] = (unsigned short )0;
sqlstm.sqphsv = sqlstm.sqhstv;
sqlstm.sqphsl = sqlstm.sqhstl;
sqlstm.sqphss = sqlstm.sqhsts;
sqlstm.sqpind = sqlstm.sqindv;
sqlstm.sqpins = sqlstm.sqinds;
sqlstm.sqparm = sqlstm.sqharm;
sqlstm.sqparc = sqlstm.sqharc;
sqlstm.sqpadto = sqlstm.sqadto;
sqlstm.sqptdso = sqlstm.sqtdso;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
if (sqlca.sqlcode != 0 && sqlca.sqlcode != 100) {
printf(" [DB ERROR] 직원 삭제 오류 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return -1; // DB 오류
}
if (sqlca.sqlcode == 100 || sqlca.sqlcode == 1403) {
return 100; // 삭제된 데이터 없음 (사번 없음)
}
/* EXEC SQL COMMIT; */
{
struct sqlexd sqlstm;
sqlstm.sqlvsn = 12;
sqlstm.arrsiz = 7;
sqlstm.sqladtp = &sqladt;
sqlstm.sqltdsp = &sqltds;
sqlstm.iters = (unsigned int )1;
sqlstm.offset = (unsigned int )320;
sqlstm.cud = sqlcud0;
sqlstm.sqlest = (unsigned char *)&sqlca;
sqlstm.sqlety = (unsigned short)4352;
sqlstm.occurs = (unsigned int )0;
sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}
// 변경 사항 반영
return 0; // 성공
}
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
// employee.h는 구조체 선언이 빠졌으므로, 함수 원형만 가져옵니다.
#include "employee.h"
// 1. Pro*C 필수 영역: SQLCA 및 호스트 변수 선언
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
// DB 접속 정보 (사용자 환경에 맞게 수정 필요)
char db_user[] = "scott";
char db_pass[] = "tiger";
char db_connect_string[] = "ORCL"; // TNS 이름 또는 SID
// EMPLOYEE_T 정의를 여기에 직접 포함 (Pro*C에서 구조체를 DECLARE SECTION 안에서 사용 가능하도록)
struct EMPLOYEE_T {
char emp_id[6];
char full_name[31];
char hire_date[9];
char married[2];
long long salary;
char gender[2];
};
typedef struct EMPLOYEE_T EMPLOYEE_T;
EXEC SQL END DECLARE SECTION;
// ====================================================================
// Connection Logic
// ====================================================================
// DB 연결
void connect_db() {
printf("DB 연결 시도 중...\\n");
// [사용자 이름/패스워드]@[연결 문자열] 형식으로 연결
EXEC SQL CONNECT :db_user IDENTIFIED BY :db_pass USING :db_connect_string;
if (sqlca.sqlcode != 0) {
printf(" [DB ERROR] 연결 실패 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
exit(1);
}
printf("DB에 성공적으로 연결되었습니다.\\n");
}
// DB 연결 해제
void disconnect_db() {
EXEC SQL COMMIT RELEASE; // 모든 트랜잭션 커밋 및 연결 해제
printf("DB 연결이 해제되었습니다.\\n");
}
// ====================================================================
// A. READ (조회)
// ====================================================================
// 1. 전체 목록 조회 (READ All)
EMPLOYEE_T* select_all_employees(int* count) {
// DB 로직에서 사용할 변수 선언 (호스트 변수는 DECLARE SECTION 안에 있어야 함)
EXEC SQL BEGIN DECLARE SECTION;
EMPLOYEE_T current_emp;
EXEC SQL END DECLARE SECTION;
// 조회 결과를 담을 동적 배열 포인터
EMPLOYEE_T* employees = NULL;
int found_count = 0;
// 커서 선언 (전체 조회)
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT EMPL_ID, FULL_NAME, HIRE_DATE, MARRIED, GENDER, SALARY
FROM EMPLOYEES
ORDER BY EMPL_ID;
EXEC SQL OPEN emp_cursor;
// 데이터 Fetch 및 동적 배열에 추가
while (1) {
EXEC SQL FETCH emp_cursor INTO
:current_emp.emp_id, :current_emp.full_name, :current_emp.hire_date,
:current_emp.married, :current_emp.gender, :current_emp.salary;
if (sqlca.sqlcode == 1403) break; // NO DATA FOUND
if (sqlca.sqlcode != 0) {
printf(" [DB ERROR] 전체 조회 오류 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
EXEC SQL CLOSE emp_cursor;
*count = 0;
if (employees != NULL) free(employees);
return NULL;
}
// 동적 메모리 재할당 (새로운 레코드를 위한 공간 확보)
employees = (EMPLOYEE_T*)realloc(employees, (found_count + 1) * sizeof(EMPLOYEE_T));
if (employees == NULL) {
printf(" [MEMORY ERROR] 메모리 할당 실패\\n");
EXEC SQL CLOSE emp_cursor;
*count = 0;
return NULL;
}
// 현재 조회된 데이터를 동적 배열의 마지막 위치에 복사
employees[found_count] = current_emp;
found_count++;
}
EXEC SQL CLOSE emp_cursor;
*count = found_count;
return employees; // 조회된 직원 정보 배열 포인터 반환
}
// 2. 상세 목록 조회 (READ One)
int select_employee_by_id(const char* emp_id, EMPLOYEE_T* result_emp) {
// Pro*C INTO 에는 DECLARE SECTION 에 선언된 호스트 변수만 사용 가능하므로,
// 로컬 호스트 변수에 SELECT 결과를 받고 그 뒤에 result_emp에 복사합니다.
EXEC SQL BEGIN DECLARE SECTION;
char l_search_id[6];
char l_emp_id[6];
char l_full_name[31];
char l_hire_date[9];
char l_married[2];
char l_gender[2];
long long l_salary;
EXEC SQL END DECLARE SECTION;
// 입력받은 사번을 Host Variable에 복사
memset(l_search_id, 0, sizeof(l_search_id));
strncpy(l_search_id, emp_id, sizeof(l_search_id)-1);
EXEC SQL SELECT EMPL_ID, FULL_NAME, HIRE_DATE, MARRIED, GENDER, SALARY
INTO :l_emp_id, :l_full_name, :l_hire_date, :l_married, :l_gender, :l_salary
FROM EMPLOYEES
WHERE EMPL_ID = :l_search_id;
if (sqlca.sqlcode == 1403) {
return -1; // 데이터 없음 (NO DATA FOUND)
} else if (sqlca.sqlcode != 0) {
printf(" [DB ERROR] 상세 조회 오류 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return -2; // DB 오류
}
// SELECT로 얻은 호스트 변수 값을 호출자 result_emp에 복사
memset(result_emp, 0, sizeof(EMPLOYEE_T));
strncpy(result_emp->emp_id, l_emp_id, sizeof(result_emp->emp_id)-1);
strncpy(result_emp->full_name, l_full_name, sizeof(result_emp->full_name)-1);
strncpy(result_emp->hire_date, l_hire_date, sizeof(result_emp->hire_date)-1);
strncpy(result_emp->married, l_married, sizeof(result_emp->married)-1);
strncpy(result_emp->gender, l_gender, sizeof(result_emp->gender)-1);
result_emp->salary = l_salary;
return 0; // 성공
}
// ====================================================================
// B. CREATE, UPDATE, DELETE (추가, 수정, 삭제)
// ====================================================================
// 3. 직원 정보 추가 (CREATE)
int insert_employee(const EMPLOYEE_T* new_emp) {
EXEC SQL BEGIN DECLARE SECTION;
EMPLOYEE_T l_emp;
EXEC SQL END DECLARE SECTION;
// 구조체 복사
l_emp = *new_emp;
EXEC SQL INSERT INTO EMPLOYEES (EMPL_ID, FULL_NAME, HIRE_DATE, MARRIED, GENDER, SALARY)
VALUES (:l_emp.emp_id, :l_emp.full_name, :l_emp.hire_date, :l_emp.married, :l_emp.gender, :l_emp.salary);
if (sqlca.sqlcode != 0) {
printf(" [DB ERROR] 직원 추가 오류 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return -1; // DB 오류
}
EXEC SQL COMMIT; // 변경 사항 반영
return 0; // 성공
}
// 4. 직원 정보 수정 (UPDATE)
int update_employee(const EMPLOYEE_T* updated_emp) {
EXEC SQL BEGIN DECLARE SECTION;
EMPLOYEE_T l_emp;
EXEC SQL END DECLARE SECTION;
l_emp = *updated_emp;
EXEC SQL UPDATE EMPLOYEES
SET FULL_NAME = :l_emp.full_name,
HIRE_DATE = :l_emp.hire_date,
MARRIED = :l_emp.married,
GENDER = :l_emp.gender,
SALARY = :l_emp.salary
WHERE EMPL_ID = :l_emp.emp_id;
if (sqlca.sqlcode != 0 && sqlca.sqlcode != 100) {
printf(" [DB ERROR] 직원 수정 오류 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return -1; // DB 오류
}
if (sqlca.sqlcode == 100 || sqlca.sqlcode == 1403) { // 100은 UPDATE/DELETE 시 레코드가 없을 때 주로 발생
return 100; // 수정된 데이터 없음 (사번 없음)
}
EXEC SQL COMMIT; // 변경 사항 반영
return 0; // 성공
}
// 5. 직원 정보 삭제 (DELETE)
int delete_employee(const char* emp_id) {
EXEC SQL BEGIN DECLARE SECTION;
char l_del_id[6];
EXEC SQL END DECLARE SECTION;
memset(l_del_id, 0, sizeof(l_del_id));
strncpy(l_del_id, emp_id, sizeof(l_del_id)-1);
EXEC SQL DELETE FROM EMPLOYEES
WHERE EMPL_ID = :l_del_id;
if (sqlca.sqlcode != 0 && sqlca.sqlcode != 100) {
printf(" [DB ERROR] 직원 삭제 오류 (SQLCODE: %d): %s\\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc);
return -1; // DB 오류
}
if (sqlca.sqlcode == 100 || sqlca.sqlcode == 1403) {
return 100; // 삭제된 데이터 없음 (사번 없음)
}
EXEC SQL COMMIT; // 변경 사항 반영
return 0; // 성공
}
- 지금 시간 오후 3시 45분. 11월 7일. - 여기까지는 잘 작동됨. 여기서 차장님 추가 지시사항을 수행할 거야.
- 그런데!!! 그 전에. 이 코드들이 먼저 어떻게 작동하는지부터 알고 가야.