[days17] 동적 SQL

 ***** 동적 SQL *****
프로그램 실행하고 난 후에 결정이 나는 것

 

1. 필요성
ㄱ. 컴파일 시에 SQL 문장이 확정되지 않은 경우
    where 조건절, select 항목이 동적으로 변하는 경우
ㄴ. PL/SQL 블록 상에서 DDL문을 실행해야 하는 경우
    DDL (create, alter, drop, truncate문)
ㄷ. PL/SQL 블록 상에서 alter system/session 명령어를 실행해야 하는 경우

2. 동적 SQL을 사용하는 2가지 방법
ㄱ. 원시 동적 SQL (Native Dynamic SQL :NDS) *****
ㄴ. DBMS_SQL 패키지 사용 (잘x)

 

3. EXECUTE 프로시저명; -- 프로시저 실행

execute immediate 동적SQL문
        [into 변수...]
        [using 모드(in, out, in out) 동적 SQL문 사용될 때 매개변수...]
   

 예)
    create or replace procedure 프로시저명
    (
        파라미터 선언
    )

 

 

 

1. 익명프로시저를 사용해 동적SQL 작성

-- 익명 프로시저
declare
    vsql varchar2(1000); --동적으로 쿼리가 저장될 변수
begin
    --동적쿼리(SQL) 작성
    vsql := 'select deptno, empno, ename, job ';
    vsql := vsql||'from emp ';
    vsql := vsql||'where empno =7369';
    --쭉 써도 되는데 나눈 이유 : 나중에 동적 쿼리 쓸때 길어질 거다
    
    --동적쿼리(SQL) 실행
    execute immediate vsql;
end;
--PL/SQL 프로시저가 성공적으로 완료되었습니다.
--실행결과는 안나옴

2. 위의 쿼리를 수정해서 출력까지 해보자

declare
    vsql varchar2(1000); 
    vdeptno emp.deptno%type;
    vempno emp.empno%type;
    vename emp.ename%type;
    vjob  emp.job%type;
begin
    vsql := 'select deptno, empno, ename, job ';
    vsql := vsql||'from emp ';
    vsql := vsql||'where empno =7369';

    execute immediate vsql
            into vdeptno, vempno, vename, vjob;
    dbms_output.put_line(vdeptno||','|| vempno||','|| vename||','|| vjob);
end;

3. 위의 쿼리를 또다시 수정 (파라미터를 입력받는다고 가정)

declare
    vsql varchar2(1000); 
    vdeptno emp.deptno%type;
    vempno emp.empno%type;
    vename emp.ename%type;
    vjob  emp.job%type;
    
    --파라미터 empno 7369 입력받는다 가정 
    pempno emp.empno%type := 7369;
    
begin
    vsql := 'select deptno, empno, ename, job ';
    vsql := vsql||'from emp ';
    vsql := vsql||'where empno =' ||pempno; --여기서 파라미터 이렇게 쓸 수 있음

    execute immediate vsql
            into vdeptno, vempno, vename, vjob;
    dbms_output.put_line(vdeptno||','|| vempno||','|| vename||','|| vjob);
end;

같은 결과 출력

4. 

declare
    vsql varchar2(1000); 
    vdeptno emp.deptno%type;
    vempno emp.empno%type;
    vename emp.ename%type;
    vjob  emp.job%type;
    
    pempno emp.empno%type := 7369;
    
begin
    vsql := 'select deptno, empno, ename, job ';
    vsql := vsql||'from emp ';
    vsql := vsql||'where empno = :pempno'; -- :이름 이건 문법 

    execute immediate vsql
            into vdeptno, vempno, vename, vjob
            using pempno;
    dbms_output.put_line(vdeptno||','|| vempno||','|| vename||','|| vjob);
end;

또 같은 결과 출력

 

 

 

 

예) 동적 SQL을 사용하는 저장 프로시저
    dept 테이블 DML(insert) 수행하는 저장 프로시저

create or replace procedure up_insert_dept
(
    pdname varchar2
    ,ploc varchar2
)
is 
    vsql varchar2(1000);
    vdeptno number(2);
begin
    select max(deptno)+10 into vdeptno 
    from dept;
    
    vsql := 'insert into dept ';
    vsql := vsql || 'values (:deptno, :dname, :loc) ';
    
    execute immediate vsql
           -- into
           using vdeptno, pdname, ploc;
           --commit;
--exception
end;

exec up_insert_dept('영업부', '대구');

select * from dept;

 

select 하는 동적 SQL의 결과가 다중행  (여러 레코드) 
->이건 어떻게 처리해야해 ?  open for 문 사용
커서(cursor) 변수를 사용하는 2가지 방법
  1) SYS_REFCURSOR    오라클 9i에 추가
  2) REF CURSOR         그 이후~

 

 

1.

open 커서변수 for 동적SQL
    using 파라미터...;

 

부서번호가 30인 사원들의 이름, job  출력

declare
    --커서변수 선언
    --1) ref cursor 사용
    --  ㄱ. 커서타입 선언
    type query_phy is ref cursor;
    --  ㄴ. 커서변수 선언
    vcursor query_phy;
    
    vsql varchar2(1000);
    vrow emp%rowtype;
    
    pdeptno number(2) := 30;
begin
    vsql := 'select * ';
    vsql := vsql||' from emp ';
    vsql := vsql||' where deptno = :deptno ';
    
    --open 커서변수 for 동적쿼리
    --    using 파라미터;
    open vcursor for vsql
        using pdeptno;
        
    --출력용 커서변수 선언 .. out
    --동적쿼리 실행 결과 확인 (일단은 여기서)
    loop
        fetch vcursor into vrow;
        exit when vcursor%notfound;
        dbms_output.put_line(vrow.ename||','||vrow.job);
    end loop; 
    close vcursor; --나중에 이6줄은 자바에 있을 것이다
end;

2. REF_CURSOR 사용

declare 
    vcursor sys_refcursor;
    vsql varchar2(1000);
    vrow emp%rowtype;
    pdeptno number(2) := 30;
begin
    vsql := 'select * ';
    vsql := vsql||' from emp ';
    vsql := vsql||' where deptno = :deptno ';
    
    open vcursor for vsql
        using pdeptno;

    loop
        fetch vcursor into vrow;
        exit when vcursor%notfound;
        dbms_output.put_line(vrow.ename||','||vrow.job);
    end loop; 
    close vcursor; 
end;

같은 결과 출력