#13_140701_PL_SQL_동적 SQL

# 동적 SQL

l 동적 SQL을 사용하면 할 수 있는 작업

- 프로시져 생성할 떄 select 문의 into 절 없이 사용가능하다

- 프로시져 생성시 DDL 문이나 DCL 문을 포함시킬 수 있다.(DDL : crteate, alter, drop., truncate, rename. DCL : grant, revoke)

l 동적 SQL을 사용하는 방법 2가지

- execute inmmediate 절 사용(가장 많이 활용)

- DBMS_SQL 패키지를 이용하는 방법 

문제147. 부서번를 입력하여 토탈월급, 최대월급, 최소월급을 출력하는 아래의 프로시져를  동적SQL로 수정해서 생성하고 실행하시오

CREATE OR REPLACE PROCEDURE pro146

(p_deptno IN number)

IS

 v_sumsal emp.sal%TYPE;

 v_maxsal emp.sal%TYPE;

 v_minsal emp.sal%TYPE;

 

BEGIN

SELECT SUM(sal), MAX(sal), MIN(sal) INTO v_sumsal, v_maxsal, v_minsal

FROM emp

WHERE deptno = p_deptno;

dbms_output.put_line('부서번호 : ' || p_deptno);

dbms_output.put_line('토탈월급 : ' || v_sumsal);

dbms_output.put_line('최대월급 : ' || v_maxsal);

dbms_output.put_line('최소월급 : ' || v_minsal);

END;

/

----------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE pro146

(p_deptno IN emp.deptno%type)

IS

 v_sumsal emp.sal%TYPE;

 v_maxsal emp.sal%TYPE;

 v_minsal emp.sal%TYPE;

 v_stmt VARCHAR2(200);

BEGIN

v_stmt := 'SELECT SUM(sal), MAX(sal), MIN(sal) FROM emp

 WHERE deptno = :name';

EXECUTE IMMEDIATE v_stmt INTO v_sumsal, v_maxsal, v_minsal USING p_deptno;

dbms_output.put_line('부서번호 : ' || p_deptno);

dbms_output.put_line('토탈월급 : ' || v_sumsal);

dbms_output.put_line('최대월급 : ' || v_maxsal);

dbms_output.put_line('최소월급 : ' || v_minsal);

END;

/

l WHERE deptno = :name';  << 바인드 변수 선언, 변수명은 임의로 만들어도됨

l using p_deptno 는 name을 가르킴

문제151. 프로시저를 생성하는데 테이블명을 입력해서 프로시져를 실행하면 emp 테이블과 똑같은 테이블이 생성되게 하시오

connect sys/oracle as sysdba

grant create table to scott;

connect scott/tiger

CREATE OR REPLACE PROCEDURE create_table

(p_table varchar2)

IS

BEGIN

 EXECUTE IMMEDIATE

  'create table ' || p_table || ' as select * from EMP';

END;

/

l 싱글쿼테이션 사이에 세미콜론이 들어가면 안된다.

문제152. 2013년 대기업 신입사원 연봉 테이블을 활용 문제를 해결하는데 순위를 입력해서 프로시져를 수행하면 해당 순위의 기업명과 연봉이 출력되는 프로시져를 생성하시오(동적 SQL을 사용해서 생성하시오)

CREATE OR REPLACE PROCEDURE sal_rank

(p_rank IN number)

IS

 v_name emp_sal.t_name%TYPE;

 v_sal emp_sal.t_sal%type;

 v_stmt VARCHAR2(500);

BEGIN

 v_stmt := 'SELECT t_name, t_sal

  FROM (SELECT t_name, t_sal, RANK() OVER(ORDER BY t_sal desc) 순위 FROM emp_sal)

  WHERE 순위 = :NAME';

EXECUTE IMMEDIATE v_stmt INTO v_name, v_sal USING p_rank;

 dbms_output.put_line(v_name);

 dbms_output.put_line(v_sal);

END;

/

문제154. 위의 함수를 수정해서 아래와 같이 수행했을 때 emp 테이블의 데이터가 지워지게 하시오

CREATE OR REPLACE FUNCTION del_rows

(p_name varchar2,

p_deptno number)

RETURN NUMBER

IS

 BEGIN

  EXECUTE IMMEDIATE

   'delete from ' || p_name || ' where deptno=' || p_deptno;

 RETURN SQL%ROWCOUNT;

 END;

/

---------------------------------------------------------------------------

BEGIN

 dbms_output.put_line(del_rows('emp',10) || ' rows deleted.');

END;

/

문제155. p6-13 쪽을 보고 함수를 생성하는데 사원번호를 넣고 함수를 실행하면 아래와 같이 결과가 출력되게 하는 함수를 생성하시오

CREATE OR REPLACE FUNCTION get_emp

(p_empno number)

RETURN emp%ROWTYPE

IS

 v_record emp%ROWTYPE;

 v_stmt VARCHAR2(200);

BEGIN

 v_stmt := 'select * from EMP where empno = :NAME';

 EXECUTE IMMEDIATE v_stmt INTO v_record USING p_empno;

RETURN v_record;

END;

/

---------------------------------------------------------------------------

DECLARE

 v_rec emp%ROWTYPE := get_emp(7788);

BEGIN

 dbms_output.put_line(v_rec.ename || '의 월급은 ' || v_rec.sal

  || '이고 직업은 ' || v_rec.job|| '입니다.');

END;

/

l record변수(emp%rowtype)는 select 구문에서 * 를 넣어서 전부다 넣어줘야 한다. 각각의 컬럼값을 입력하면 어디에 어떻게 들어갈지 알수가 없기 때문이다.