[Pro*C 오라클 DB] Dynamic SQL 프로그램, SQL 동적처리

오라클에서 Analytic Function 이라는 통계 함수를 제공함

SQL> SELECT DEP, EMPNO, ENAME, SAL , CUMULATIVE,
	 SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) COMULATIVE
     FROM EMP

위 SQL 문장은 Sqlplus 에서는 실행되지만

Pro*C 프로그램으로 작성한 후 컴파일을 수행하면 컴파일 에러가 발생한다.

-> Pro*C 컴파일러가 Analytic Function 문장을 SQL 문장으로 인식하지 못해서 발생

Analytic Function의 주요 특징

OVER 절 사용
- Analytic Function은 OVER 절과 함께 사용
- OVER 절을 통해 데이터를 특정 그룹으로 나누거나 정렬 기준을 설정
행별 계산 가능
- 데이터의 각 행에 대해 "누적 합계", "이전 값과 비교", "순위 계산" 등을 수행
집계와는 다름
- 일반적인 **집계 함수(Aggregate Function)**는 결과를 하나의 값으로 반환하지만, Analytic Function은 각 행에 대해 별도의 값을 반환

#include <stdio.h>
#include <sqlca.h> // Pro*C와 관련된 헤더 파일

/* 1. SQL 문에서 사용할 변수들을 선언 */
EXEC SQL BEGIN DECLARE SECTION;
    struct {
        int emp_id;                  // 직원 ID
        char emp_name[50];           // 직원 이름
        double salary;               // 직원 월급
        int rank;                    // 직원 순위
        double cumulative_salary;    // 누적 월급
    } employee;
EXEC SQL END DECLARE SECTION;

int main() {
    /* 2. 데이터베이스에 연결 */
    EXEC SQL CONNECT :username IDENTIFIED BY :password;

    /* 3. SQL 커서 선언 - Analytic Function을 포함한 쿼리 정의 */
    EXEC SQL DECLARE emp_cursor CURSOR FOR
        SELECT emp_id, emp_name, salary,
               RANK() OVER (ORDER BY salary DESC) AS rank,                 // (3-1) 월급 기준 순위 계산
               SUM(salary) OVER (ORDER BY salary DESC) AS cumulative_salary // (3-2) 월급 기준 누적합 계산
        FROM employees;

    /* 4. 커서를 열어 데이터 검색 시작 */
    EXEC SQL OPEN emp_cursor;

    /* 5. 출력 형식 설정 */
    printf("ID\tName\t\tSalary\tRank\tCumulative Salary\n");
    printf("----------------------------------------------------\n");

    /* 6. 커서에서 데이터를 한 행씩 가져오기 */
    while (1) {
        EXEC SQL FETCH emp_cursor INTO :employee; // (6-1) SQL 결과를 employee 구조체에 저장

        if (sqlca.sqlcode == 1403) break; // (6-2) 데이터가 더 이상 없으면 종료

        /* 7. 가져온 데이터를 출력 */
        printf("%d\t%s\t%.2f\t%d\t%.2f\n",
               employee.emp_id,
               employee.emp_name,
               employee.salary,
               employee.rank,
               employee.cumulative_salary);
    }

    /* 8. 커서와 데이터베이스 연결 닫기 */
    EXEC SQL CLOSE emp_cursor;      // (8-1) 커서 닫기
    EXEC SQL DISCONNECT;           // (8-2) 데이터베이스 연결 종료

    return 0;
}

SQL 변수 선언
- Pro*C에서는 SQL 변수와 C 변수 간의 데이터를 주고받기 위해 DECLARE SECTION을 사용
- employee 구조체는 직원 정보를 담는 데 사용되며, FETCH를 통해 데이터를 담음
데이터베이스 연결
- CONNECT 문을 사용해 데이터베이스에 접속합니다. username과 password는 미리 설정된 변수
SQL 커서 선언
- DECLARE emp_cursor는 Analytic Function을 포함한 SQL 쿼리를 정의
- (3-1) RANK()는 월급을 기준으로 순위를 계산
- (3-2) SUM()은 월급의 누적 합계를 계산
- OVER (ORDER BY salary DESC)는 월급을 내림차순으로 정렬하면서 계산
커서를 열어 데이터 검색 시작
- OPEN emp_cursor는 SQL 쿼리를 실행하고 결과 집합을 반환
출력 형식 설정
- printf를 사용해 결과를 보기 좋게 출력하기 위한 헤더와 구분선 설정
커서에서 데이터를 한 행씩 가져오기
- FETCH는 emp_cursor에서 데이터를 한 행씩 읽어와 employee 구조체에 저장
- (6-2) sqlca.sqlcode == 1403은 데이터가 더 이상 없음을 의미하는 SQL 상태 코드
가져온 데이터를 출력
- printf로 직원 ID, 이름, 월급, 순위, 누적 월급을 출력
커서와 데이터베이스 연결 닫기
- (8-1) CLOSE는 커서를 닫아 리소스를 해제
- (8-2) DISCONNECT는 데이터베이스 연결을 종료

*오라클 8i, 9i, 10i 버전의 Pro*C 컴파일러는 분석함수(Analytic Function)를 인식하지 못함

바인드 변수

SQL 문장에서 직접 값 지정

SELECT * FROM employees WHERE emp_id = 101;

위 SQL 문장은 직원 ID가 101인 직원 정보를 가져옴
그러나 emp_id 값(101)이 고정되어 있어, 다른 값을 검색하려면 SQL 문장을 다시 작성하거나 수정해야함

바인드 변수를 사용한 SQL 문장

SELECT * FROM employees WHERE emp_id = :emp_id;

**:emp_id**는 바인드 변수로, SQL 문장 안에서 값이 동적으로 채워질 자리 표시자
여기서 :emp_id에 값을 주입하면, SQL 문장을 변경하지 않아도 다양한 emp_id 값을 검색

C 프로그램에서의 동작

1. 변수 선언 및 SQL 문장에 바인드 변수 지정

EXEC SQL BEGIN DECLARE SECTION;
    int emp_id; // 바인드 변수
EXEC SQL END DECLARE SECTION;

C 프로그램의 변수 emp_id를 선언
이 변수는 SQL 문장 안의 :emp_id와 연결

2. SQL 문장 실행 전 바인드 변수에 값 할당

emp_id = 101; // SQL 문장에 사용될 값
EXEC SQL SELECT * FROM employees WHERE emp_id = :emp_id;

- 실행 전에 emp_id에 값을 지정
- SQL 문장의 :emp_id는 실행 시 자동으로 emp_id의 값(101)으로 대체

바인드 변수의 장점

SQL 문장은 그대로 유지하고, 바인드 변수에 다른 값을 할당하여 실행 결과를 동적으로 변경할 수 있음

잘못된 방법: sprintf(sql, "SELECT * FROM employees WHERE emp_id = %d", user_input);
안전한 방법: EXEC SQL SELECT * FROM employees WHERE emp_id = :emp_id;

EXEC SQL EXECUTE IMMEDIATE

한 번 실행하고 끝날 단순한 쿼리에 적합
예: 테이블 생성, 데이터 삭제

#include <stdio.h>
#include <sqlca.h>

int main() {
    // 데이터베이스 연결
    EXEC SQL CONNECT :username IDENTIFIED BY :password;

    // 1. SQL 문장 문자열로 작성
    char sql_stmt[200]; 
    strcpy(sql_stmt, "DELETE FROM employees WHERE emp_id = 101"); // (1-1) 특정 직원 ID(101)를 삭제하는 SQL 문장을 작성

    // 2. 즉시 실행
    EXEC SQL EXECUTE IMMEDIATE :sql_stmt; 
    // (2-1) 작성된 SQL 문장을 실행 (직원 ID 101 삭제)

    // 데이터베이스 연결 종료
    EXEC SQL DISCONNECT;

    return 0;
}

SQL 문장을 동적으로 생성

EXEC SQL EXECUTE 를 사용해 즉시 실행

(1-1) sql_stmt 문자열에 SQL 문장을 작성

여기서는 직원 ID가 101인 레코드를 삭제

(2-1) EXEC SQL EXECUTE IMMEDIATE를 사용해 문자열 형태의 SQL 문장을 데이터베이스에서 바로 실행
- SQL 문장이 실행되는 즉시 직원 ID 101이 삭제
주의사항:
- SQL 문장에 값을 직접 포함하기 때문에 보안상 취약할 수 있음
- 동적으로 변경되는 값에 대해서는 사용이 어려움

PREPARE

PREPARE는 SQL 문장을 미리 컴파일하여 실행 준비
준비된 SQL 문장을 필요할 때마다 실행
주로 반복 실행이 필요한 쿼리나 바인드 변수를 사용하는 경우 유용함

#include <stdio.h>
#include <sqlca.h>

int main() {
    // 데이터베이스 연결
    EXEC SQL CONNECT :username IDENTIFIED BY :password;

    // 1. SQL 문장 문자열 작성
    char sql_stmt[200];
    int emp_id = 102; // (1-1) 삭제하고자 하는 직원 ID
    EXEC SQL BEGIN DECLARE SECTION;
        int input_emp_id; // (1-2) 바인드 변수로 사용할 C 변수 선언
    EXEC SQL END DECLARE SECTION;

    strcpy(sql_stmt, "DELETE FROM employees WHERE emp_id = :input_emp_id"); 
    // (1-3) SQL 문장에 바인드 변수를 사용해 작성

    // 2. SQL 문장 준비
    EXEC SQL PREPARE stmt FROM :sql_stmt; 
    // (2-1) SQL 문장을 데이터베이스에 전달하여 실행 준비
    // stmt는 준비된 SQL 문장의 이름

    // 3. 바인드 변수에 값 설정
    input_emp_id = emp_id; 
    // (3-1) 삭제할 직원 ID를 동적으로 설정 (emp_id 값을 input_emp_id에 대입)

    // 4. 준비된 문장 실행
    EXEC SQL EXECUTE stmt USING :input_emp_id; 
    // (4-1) 준비된 SQL 문장을 실행하며, 바인드 변수로 input_emp_id 값을 전달

    // 데이터베이스 연결 종료
    EXEC SQL DISCONNECT;

    return 0;
}

(1-1) emp_id 변수에 삭제할 직원 ID를 저장.
(1-2) 바인드 변수로 사용할 C 변수 input_emp_id를 선언
(1-3) SQL 문장에서 :input_emp_id를 바인드 변수로 지정
(2-1) EXEC SQL PREPARE를 사용해 SQL 문장을 컴파일
- 이 과정에서 SQL 문장은 데이터베이스에 전달되어 실행 준비가 완료.
(3-1) 실행 시점에 input_emp_id에 값을 동적으로 설정
(4-1) EXEC SQL EXECUTE를 통해 준비된 문장을 실행하며, 바인드 변수로 input_emp_id 값을 전달

EXEC SQL EXECUTE IMMEDIATE vs PREPARE

특징	IMMEDIATE	PREPARE
사용 목적	SQL 문장을 즉시 실행할 때	SQL 문장을 미리 준비하고 반복 실행할 때
바인드 변수 지원	지원하지 않음	지원
성능	매번 실행 시 SQL 문장을 새로 파싱	한 번 준비한 후 여러 번 재사용 가능
복잡도	단순한 쿼리에 적합	복잡한 쿼리나 반복 실행에 적합
예제 상황	단일 DELETE, INSERT 등 단순 SQL 문장	WHERE 조건이 달라지는 반복 SELECT, INSERT 등

EXEC SQL EXECUTE IMMEDIATE:
- 한 번 실행하고 끝날 단순한 쿼리에 적합
- 예: 테이블 생성, 데이터 삭제 등.
PREPARE:
- 바인드 변수를 통해 데이터를 동적으로 처리해야 하거나, 같은 SQL 문장을 반복적으로 실행해야 할 때 적합
- 예: 조건이 바뀌는 SELECT, INSERT 등.

'Pro*C' 카테고리의 다른 글

[Pro*C 오라클 DB] 프로그램 오류 처리, 데이터 처리 (0)	2025.01.16
[Pro*C 오라클 DB] 호스트 변수 (배열, 구조체)를 이용한 프로그램 (0)	2025.01.16
[ProC 오라클 DB] ProC 컴파일 (0)	2025.01.09
[ProC 오라클 DB] ProC 프로그램 구성과 DB접속 (0)	2025.01.08
[ProC 오라클 DB] ProC 오류,에러 진단과 처리 (0)	2025.01.06

jaeodev 님의 블로그

[Pro*C 오라클 DB] Dynamic SQL 프로그램, SQL 동적처리

Analytic Function의 주요 특징

바인드 변수

SQL 문장에서 직접 값 지정

바인드 변수를 사용한 SQL 문장

C 프로그램에서의 동작

EXEC SQL EXECUTE IMMEDIATE

PREPARE

EXEC SQL EXECUTE IMMEDIATE vs PREPARE

'Pro*C' 카테고리의 다른 글

티스토리툴바

[Pro*C 오라클 DB] Dynamic SQL 프로그램, SQL 동적처리

Analytic Function의 주요 특징

바인드 변수

SQL 문장에서 직접 값 지정

바인드 변수를 사용한 SQL 문장

C 프로그램에서의 동작

EXEC SQL EXECUTE IMMEDIATE

PREPARE

EXEC SQL EXECUTE IMMEDIATE vs PREPARE

'Pro*C' 카테고리의 다른 글

'Pro*C' Related Articles

티스토리툴바

**'Pro*C'** Related Articles