동적SQL
반복적으로 사용되는 SQL 문법을 저장해 두었다가 필요할때 조건절만 사용자로부터 입력받아 실행시킬수 있는데 이런유형의 SQL을 동적(Dymamic)SQL 이라 한다.
PL/SQL에서 동적 SQL을 실행할 때는 EXECUT IMMEDIATE 명령어를 사용한다. 또한 동적 SQL에서 정의된 조건값을 전달할 때에는 USING 절을 사용한다.
예제 1)
set serveroutput on
create or replace procedure dsql_test
(v_empno number)
is
sql_stmt varchar2(200);
emp_rec emp%ROWTYPE;
begin
sql_stmt :='SELECT * from emp where empno= :id'; -- 자주사용돠는 sql문을 변수로 저장
execute immediate sql_stmt into emp_rec using v_empno;
-- 변수에 저장된 sql문을 호출. v-empno 가 :id로 저장
DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || emp_rec.empno );
DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || emp_rec.ename );
DBMS_OUTPUT.PUT_LINE( '입 사 일 : ' || emp_rec.hiredate );
execute immediate sql_stmt into emp_rec using v_empno; -- 변수에 저장된 sql 문 호출
DBMS_OUTPUT.PUT_LINE( '**********************************************');
DBMS_OUTPUT.PUT_LINE( '사원번호 : ' || emp_rec.empno );
DBMS_OUTPUT.PUT_LINE( '사원이름 : ' || emp_rec.ename );
DBMS_OUTPUT.PUT_LINE( '입 사 일 : ' || emp_rec.hiredate );
DBMS_OUTPUT.PUT_LINE( '월급 : ' || emp_rec.sal );
end;
/
execute dsql_test(7369)
*************************************************************************
예제 2)
DROP TABLE BONUS;
DECLARE
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := 'PERSONAL';
emp_id NUMBER(4) := 7934;
emp_rec EMP%ROWTYPE;
location VARCHAR2(13) := 'DALLAS';
sql_stmt VARCHAR2(200);
salary NUMBER(7,2);
BEGIN
EXECUTE IMMEDIATE 'create table bonus (id number, amt number)';
sql_stmt := 'insert into dept values (:1, :2, :3)';
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := 'select * from emp where empno = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
sql_stmt := 'update emp set sal = 2000 where empno = :1 returning sal into :2';
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
END ;
/
DESC BONUS;
SELECT * FROM DEPT;
SELECT * FROM EMP WHERE EMPNO=7934;
ROLLBACK;
*************************************************************************
예제 3 - 동적 sql의 명시적 커서
DECLARE
emp_rec emp%rowtype;
sql_stmt varchar2(200);
my_job varchar2(15) := 'CLERK';
TYPE empcurtyp is ref cursor; -- 커서의 선언
emp_cv empcurtyp; -- 커서를 재사용하기 위해서 재 정의 했다
begin
sql_stmt := 'SELECT * FROM emp WHERE job= :j';
OPEN emp_cv for sql_stmt using my_job; -- 커서 시작
LOOP
FETCH emp_cv into emp_rec; -- 조건값 인출
exit when emp_cv%notfound;
dbms_output.put_line(emp_rec.empno||' '||emp_rec.ename);
end loop;
close emp_cv; -- 커서 종료
end;
/
**********************************************************
PL/SQL에서 동적 SQL 사용
Date : 2002/08/24
Creater : Vins
E-MAIL :
오라클의 PL/SQL에서 동적 SQL을 사용하기 위한 방법에 대해 설명한다.
우선 동적 SQL이란 무엇을 말하는 것일까?
보통 PL/SQL에서 CURSOR사용을 위해서는 선언부에 CURSOR를 선언하고
CURSOR를 생성할 SQL을 선언한다.
====================================================================
CREATE OR REPLACE PROCEDURE call_me(ICALL_ID CHAR) AS
--커서 선언부...
CURSOR cur1 IS
SELECT CALL1, CALL2, CALL3
FROM CALL_TABLE
WHERE CALL_ID = ICALL_ID;
BEGIN
.............나머지 생략 .................
END;
/
SHOW ERRORS
====================================================================
위에서의 코드에서 볼수 있듯이 CURSOR 선언시 해당 SQL을 지정하는데.
입력값인 ICALL_ID 를 WHERE 절에 조건으로 사용하는것은 가능하다.
문제는 조건에 따라 필드명이나 테이블이 바뀌거나 SQL문이 전혀 다르게
생성된다면 어떻게 할까?
이런 문제에 봉착했을때 동적 SQL을 사용한다.
====================================================================
CREATE OR REPLACE PROCEDURE call_me(ICALL_TYPE CHAR) AS
our_cursor INTEGER; --커서변수선언
status INTEGER; --커서의 ID를 저장
sql_str VARCHAR2(100); --질의어 저장
--바인딩 변수 선언
v_CALL1 VARCHAR2(30);
v_CALL2 VARCHAR2(30);
v_CALL3 VARCHAR2(30);
BEGIN
IF ICALL_TYPE = 'ME' THEN
sql_str := ' SELECT CALL1, CALL2, CALL3 ' ||
' FROM CALL_ME ';
ELSIF ICALL_TYPE = 'YOU' THEN
sql_str := ' SELECT CALL1, CALL2, CALL3 ' ||
' FROM CALL_YOU ';
ELSIF ICALL_TYPE = 'WE' THEN
sql_str := ' SELECT CALL1, CALL2, CALL3 ' ||
' FROM CALL_WE ';
END IF;
--동적 쿼리 사용을 위한 커서 생성
our_cursor := DBMS_SQL.OPEN_CURSOR;
--SQL문 파싱
DBMS_SQL.PARSE(our_cursor,sql_str,DBMS_SQL.V7);
--각 컬럼을 바인딩한다.
--인자 : (커서, 필드순번, 바인딩변수, 값의 길이)
DBMS_SQL.DEFINE_COLUMN(our_cursor,1,v_CALL1,30);
DBMS_SQL.DEFINE_COLUMN(our_cursor,2,v_CALL2,30);
DBMS_SQL.DEFINE_COLUMN(our_cursor,3,v_CALL3,30);
--커서 ID를 저장한다.
status := DBMS_SQL.EXECUTE(our_cursor);
--자료를 출력한다.
LOOP
IF DBMS_SQL.FETCH_ROWS(our_cursor) > 0 THEN
--데이터를 변수에 할당한다.
DBMS_SQL.COLUMN_VALUE(our_cursor,1,v_CALL1);
DBMS_SQL.COLUMN_VALUE(our_cursor,2,v_CALL2);
DBMS_SQL.COLUMN_VALUE(our_cursor,3,v_CALL3);
DBMS_OUTPUT.PUT_LINE('1번째 ==> '||v_CALL1);
DBMS_OUTPUT.PUT_LINE('1번째 ==> '||v_CALL1);
DBMS_OUTPUT.PUT_LINE('1번째 ==> '||v_CALL1);
ELSE
EXIT; --LOOP를 빠져나간다.
END IF;
END LOOP;
--커서를 닫는다.
DBMS_SQL.CLOSE_CURSOR(our_cursor);
--에러 처리
EXCEPTION
WHEN OTHERS THEN
--사용자 정의 핸들러.
errhandle(SQLERRM);
END;
/
SHOW ERRORS
====================================================================
위의 코드에서 보는것과 같이 다음과 같은 순서를 통해 동적 SQL을 실행한다.
1. 선언부에 커서, 질의어저장변수, 커서 ID, 값을 바인딩할 변수를 선언한다.
2. 로직을 통해 SQL문을 생성한다.
3. 동적쿼리 사용을 위한 커서 생성한다.]
4. SQL을 파싱하여 커서에 할당한다.
5. 해당 컬럼을 변수에 바인딩 시킨다.
6. 커서의 SQL을 실행한다.
7. 커서를 패치하면서 바인딩한 변수에 해당 필드값을 설정한다.
동적 SQL은 PL/SQL을 사용하여 시스템을 구축시 한번은 사용하게 된다.
물론 동적 SQL을 사용하지 않고 프로그램을 구현할수도 있다.
위에 예제를 동적 SQL을 사용하지 않고 구현하려면 우선 입력될 TYPE이 몇
개인지 우선 알고 해당 CURSOR를 모두 선언부에 선언하고 프로그램을
구현하는것인데.. 이것은 비효율적인 프로그램이 된다.
더 상세한 정보는 ORACLE DEVELOPER GUIDE를 참고 하길 바란다.
