Pro*C 7. Dynamic SQL

Dynamic SQL

    Static SQL은 정해진 SQL문으로 처리해야 하는 경우에 유용하지만 데이터의 상태에 따라 Runtime시에 SQL문을 동적으로 조립하여 SQL문을 실행해야하는 경우도 있습니다.

예를들면 SQL문이 INPUT값에 따라 여러가지 조합으로 SQL문이 다르게 표현해야하는 경우입니다.

 

Example.

	char sql_stmt[4096];
	char sql_option[1024];
	int  salary;
	int  emp_number;

	......

	strcpy(sql_stmt,
		"SELECT ename, job, sal \n"
		"FROM emp \n"
		"WHERE 1 = 1 \n");

	if(emp_number > 0) {
		snprintf(sql_option, 1024, "AND empno = %d\n", emp_number);
		strcat(sql_stmt, sql_option);
	}

	if(salary > 0) {
		snprintf(sql_option, 1024, "AND sal >= %d\n", salary);
		strcat(sql_stmt, sql_option);
	} 

위와 같이 Runtime시에 INPUT값 등에 따라 SQL문을 문자열의 조합으로 동적으로 구성하여 실행하는 것을 Dynamic SQL이라고 합니다.

 

---

Dynamic SQL이 필요한 경우

  - SQL문이 정확하게 정의되지 않은 경우

  - host 변수의 갯수가 정의되지 않은 경우

  - host 변수의 type이 정의되지 않은 경우

 

※ 주의사항: Dynamic SQL의 SQL문에 포함된 :변수명은 host 변수명이 아니며 변수의 순서와 갯수만큼을 input으로 mapping해야 합니다.

 

Dynamic SQL 유형

Dynamic SQL Method 1
    - SQL문이 SELECT문이 아니고 host 변수가 없는 경우입니다.

Dynamic SQL Method 2
    - SQL문이 SELECT문이 아니고 INPUT host 변수가 정해진 경우입니다.

Dynamic SQL Method 3
    - SQL문이 SELECT문의 조회할 column이 정해지고 INPUT host 변수정해진 경우입니다.

Dynamic SQL Method 4
    - SQL문이 INPUT 변수 또는 OUTPUT변수 또는 INPUT/OUTPUT 변수의 
      갯수가 몇개인지 무슨 type이 알 수 없는 경우입니다.
    - 이 Dynamic SQL Method 4는 업무적으로 거의 사용하지 않지만, 
      SQL*Plus 같은 프로그램을 개발한다든 지, 어떤 SQL문이 실행될 지 모르는 경우에 사용할 수 있습니다.

 

---

Dynamic SQL Method 1

    SQL문이 SELECT문이 아니고 host 변수가 없는 경우에 사용합니다. Method 1의 절차는 EXECUTE IMMEDIATE로 한번에 실행합니다.

 

기본 문법

  EXEC SQL EXECUTE IMMEDIATE { :host_string | string_literal };

 

Example

    EXEC SQL EXECUTE IMMEDIATE "CREATE TABLE dyn1 (col1 VARCHAR2(4))"; 

또는

    char sql_stmt[1024];
    strcpy(sql_stmt,"CREATE TABLE dyn1 (col1 VARCHAR2(4))");

    EXEC SQL EXECUTE IMMEDIATE :sql_stmt;    

 

---

Dynamic SQL Method 2

    SQL문이 SELECT문이 아니고 INPUT host 변수가 정해진 경우에 사용합니다.

Method 2는 DELCARE ~ STATEMENT --> PREPARE ~ FROM --> EXECUTE의 3단계 절차로 실행합니다.

 

기본문법

    /* default connection인 경우에는 DECLARE ~ STATEMENT 전체는 생략가능합니다. */
    EXEC SQL [AT db_con] DECLARE statement_name STATEMENT;

    /* SQL문 실행을 준비합니다.*/    
    EXEC SQL PREPARE statement_name
    FROM { :host_string | string_literal };

    EXEC SQL EXECUTE statement_name [USING host_variable_list];
    /* SQL문의 INPUT값을 설정하고 실행합니다. */

위의 host_variable_list는 :host_variable1[:indicator1] [, host_variable2[:indicator2], ...]의 형식입니다.

 

Example

    int emp_number;
    char delete_stmt[512], condition[100];

    ...

    strcpy(delete_stmt, "DELETE FROM EMP WHERE EMPNO = :var1 \n");

    ...

    if(...) {
        strcat(delete_stmt, "AND ")
        strcat(delete_stmt, search_cond);
    }

    EXEC SQL PREPARE sql_stmt FROM :delete_stmt;

    if(sqlca.sqlcode != 0) {
        fprintf....
        return -1;
    }

    for (;;)
    {
        printf("Enter employee number: ");
        gets(temp);
        
        emp_number = atoi(temp);

        if (emp_number == 0) {
            break;
        }

        EXEC SQL EXECUTE sql_stmt USING :emp_number;
    }

 

---

Dynamic SQL Method 3

    SQL문이 SELECT문의 조회할 column이 정해지고 INPUT host 변수정해진 경우에 사용합니다.

Method 3는 DECLARE ~ STATEMENT → PREPARE ~ FROM → DECLARE ~ CURSOR → OPEN → FETCH ~ INTO → CLOSE 6단계 절차로 실행합니다.

 

기본문법

    /* default connection이면 DECLARE ~ STATEMENT절 전체는 생략가능합니다. */
    EXEC SQL [AT db_con] DECLARE statement_name STATEMENT;

    /*SQL문을 parsing합니다. */
    PREPARE statement_name FROM { :host_string | string_literal };
    
    /* SQL문에 대해 cursor를 할당합니다. */
    DECLARE cursor_name CURSOR FOR statement_name;
    
    /* INPUT host 변수 bind하고 SQL문을 실행합니다. */
    OPEN cursor_name [USING host_variable_list];

    /* 조회 결과를 output host 변수에 Fetch 합니다. */
    FETCH cursor_name INTO host_variable_list;
    
    /* cursor를 close합니다. */
    CLOSE cursor_name;

 

Example

    char select_stmt[132] = "SELECT MGR, JOB FROM EMP WHERE SAL < :salary";
    long salary;
    long mgr_number;
    char job_title[20];

    ......
    
    EXEC SQL PREPARE sql_stmt FROM :select_stmt;

    if(sqlca.sqlcode != 0) {
        fprintf(stderr, "Parsing Error: %.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
        return -1;
    }

    EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;

    if(sqlca.sqlcode != 0) {
        fprintf(stderr, "Declare Cursor Error: %.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
        return -1;
    }

    EXEC SQL OPEN emp_cursor USING :salary;

    if(sqlca.sqlcode != 0) {
        fprintf(stderr, "Cursor Open Error: %.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
        return -1;
    }

    EXEC SQL FETCH emp_cursor INTO :mgr_number, :job_title;

    if(sqlca.sqlcode != 0) {
        fprintf(stderr, "Fetch Error: %.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
        return -1;
    }

    EXEC SQL CLOSE emp_cursor;

 

 

 

See Also : Pro*C 목차 및 Sample Source