농부지기

[ Oracle - Procedure Package_DBMS_SQL_사용법1 ]

Dynamic SQL

1.  Using The DBMS_SQL Package To Execute DDL Statements:　　　

 < Example 1 >

 　　 Table을 Create하는 Procedure로 Table Name, Column Name과 그Type을Parameter로 받는다.　　　

 CREATE OR REPLACE PROCEDURE ddlproc (tablename varchar2, cols varchar2) AS　　　

 　 cursor1 INTEGER;　　　

 BEGIN　　　

 　 cursor1 := dbms_sql.open_cursor;　　　

 　 dbms_sql.parse(cursor1, 'CREATE TABLE ' || tablename || ' ( ' || cols || ' )', dbms_sql.v7);　　　

 　 dbms_sql.close_cursor(cursor1);　　　

 end;　　　

 /　　　

 SQL> execute ddlproc ('MYTABLE','COL1 NUMBER, COL2 VARCHAR2(10)');

 < Example 2 >

 CREATE or REPLACE PROCEDURE rows_greater_than (low_value  number) AS　　　

 　 cursor1　 integer;　　　

 　 rows_processed　 integer;　　　

 　 myempno number;　　　

 　 myename varchar2(20);　　　

 BEGIN　　　

 　 cursor1 := dbms_sql.open_cursor;　　　

 　 dbms_sql.parse (cursor1, 'select empno, ename from emp where empno > :x', dbms_sql.v7);　　　

 　 dbms_sql.bind_variable(cursor1, 'x', low_value);　　　

 　 dbms_sql.define_column (cursor1, 1, myempno);　　　

 　 dbms_sql.define_column (cursor1, 2, myename, 20);　　　

 　 rows_processed := dbms_sql.execute (cursor1);　　　

 　 loop　　　

 　　　 if dbms_sql.fetch_rows (cursor1) > 0 then　　　

 　　　　　 dbms_sql.column_value (cursor1, 1, myempno);　　　

 　　　　　 dbms_sql.column_value (cursor1, 2, myename);　　　

 　　　 else　　　

 　　　　　 exit;　　　

 　　　 end if;　　　

 　 end loop;　　　

 　 dbms_sql.close_cursor (cursor1);　　　

 EXCEPTION　　　

 　 WHEN OTHERS THEN　　　

 　　　 dbms_output.put_line(sqlerrm);　　　

 　　　 if dbms_sql.is_open (cursor1) then　　　

 　　　　　 dbms_sql.close_cursor (cursor1);　　　

 　　　 end if;　　　

 END;　　　

 /

[ Oracle - Procedure 값_시스템변수 ]

     SQL%ROWCOUNT -> 가장 최근의 SQL문이 row갯수에 영향을 줄때(정수값을 갖는다)

     SQL%FOUND -> 가장 최근의 SQL문이 한개 이상의 row에 영향을 준다면 TRUE값을 갖는다.

     SQL%NOTFOUND -> 가장 최근의 SQL문이 어떤 row에도 영향을 주지 않는다면 TRUE값을 갖는다.

     SQL%ISOPEN -> 항상 FALSE에 대한 값을 구한다.

     USER : 현재 접속 사용자 ID

     DUP_VAL_ON_INDEX : insert 중 index에 의해 중복오류 발생

     NO_DATA_FOUND    : select 중 원하는 결과가 없을 경우 발생

    DUP_VAL_ON_INDEX             유일값 중복 Error

    INVALID_NUMBER                  Data type 불일치 Error

    NO_DATA_FOUND                  데이터를 반환하지 않은 SELECT 문

    TOO_MANY_ROWS                두 행 이상을 반환한 SELECT 문

    VALUE_ERROR                      대입되는 값이 길이 초과

    ZERO_DEVIDE                       0으로 나누기 시도

[ Oracle - Procedure 함수_NULLIF.COALESCE ]

NULLIF와 COALESCE는 CASE 수식의 축약된 형태입니다.

NULLIF는 NVL 함수의 역입니다.

COALESECE는 DECODE 함수와 유사합니다.

예: NULLIF)

CREATE OR REPLACE PROCEDURE get_comm_pct (p_comm NUMBER) is

        v_result VARCHAR2(10);  

BEGIN

        v_result := NULLIF(p_comm, 0);

        DBMS_OUTPUT.PUT_LINE ('The commission percent is '|| v_result*100||'%');

END;

SQL> Exec get_comm_pct;

The commission percent is 20%

PL/SQL procedure successfully completed.

예: COALESCE)

CREATE OR REPLACE PROCEDURE get_comm_pct (p_comm NUMBER) is

            v_result number;

BEGIN

    v_result := COALESCE(p_comm, 0);    

    DBMS_OUTPUT.PUT_LINE ('The commission percent is '||v_result*100||'%');

END;

SQL> Exec get_comm_pct;

The commission percent is 20%

PL/SQL procedure successfully completed.