반응형

/********************************************************************************************
-- 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;
/

반응형

+ Recent posts