/********************************************************************************************
-- title : [10g] dynamic(동적) sql
-- reference : radiocom.kunsan.ac.kr
-- key word : 동적 쿼리 동적쿼리 동적 sql 동적sql dbms_sql
********************************************************************************************/
/*****************************************
-- 동적 (dynamic) sql
-- EXECUTE IMMEDIATE 사용(8i부터)
*****************************************/
set serveroutput on;
declare
sql_stmt varchar2(200);
emp_id number(4) := 7934;
salary number(7,2);
dept_id number(2) := 50;
dept_name varchar2(14) :='personnel';
location varchar2(13) := 'dallas';
emp_rec emp%rowtype;
begin
execute immediate 'create table scott.bonus (id number, amt number)';
sql_stmt := 'insert into scott.dept values(:1, :2, :3)';
execute immediate sql_stmt
using dept_id, dept_name, location;
sql_stmt := 'select * from scott.emp where empno = :id';
execute immediate sql_stmt
into emp_rec using emp_id;
sql_stmt := 'update scott.emp set sal = 2000 where empno = :1
returning sal into :2';
execute immediate sql_stmt using emp_id
returning into salary;
execute immediate 'delete from dept where deptno = :num'
using dept_id;
end;
/
/*****************************************
-- 동적 (dynamic) sql의 명시적 커서
*****************************************/
-- CASE 1.
set serveroutput on;
declare
emp_rec scott.emp%rowtype;
sql_stmt varchar2(200);
my_job varchar2(15) := 'clerk';
type empcurtyp is ref cursor; -- 커서의 선언
emp_cv empcurtyp;
begin
sql_stmt := 'select * from scott.emp where job = :j';
oepn 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;
/
-- CASE 2.
create or replace procedure scott.up_cursor_var
( p_name varchar2
)
is
dept_rec scott.dept%rowtype;
emp_rec scott.emp%rowtype;
type curtype is ref cursor;
cursor_var curtype;
begin
if upper(p_name) = 'EMP' then
open cursor_var
for 'select * from scott.emp';
loop
fetch cursor_var into emp_rec;
exit when cursor_var%notfound or cursor_var%rowcount > 10;
dbms_output.put_line(emp_rec.ename||'''s salary is '||to_char(emp_rec.sal));
end loop;
elsif upper(p_name) = 'DEPT' then
open cursor_var
for 'select * from scott.dept';
loop
fetch cursor_var into dept_rec;
exit when cursor_var%notfound or cursor_var%rowcount > 10;
dbms_output.put_line(dept_rec.dname||' '||to_char(dept_rec.loc));
end loop;
else
dbms_output.put_line('emp or dept?');
end if;
close cursor_var;
end;
/****************************************************
-- DBMS_SQL 이용한 동적 쿼리
-- ORACLE 8i 이후부터는 EXECUTE IMMEDIATE 사용
****************************************************/
create or replace procedure scott.up_drop_table
( table_name in varchar2
)
is
cid integer;
col_value varchar2(120);
begin
cid := dbms_sql.open_cursor;
dbms_sql.parse(cid, 'create table' ||
table_name, dbms_sql.v7);
dbms_sql.close_cursor(cid);
end drop_table;
/