DBMS_SQL패키지,OPEN_CURSOR, PARSE, EXECUTE, 동적SQL, 커서, DDL실습, Dynamic SQL실습,오라클교육, SQL교육, 오라클학원, SQL학원
https://www.youtube.com/watch?v=tO4rkJAC7FM&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=40
http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=728
ojc.asia
https://www.youtube.com/watch?v=fYfTQruthLA&list=PLxU-iZCqT52CfP98Y4gCDKIm52tKvgy36&index=37
오라클 PL/SQL
DBMS_SQL 패키지란?
DDL, 동적SQL
실습
DBMS_SQL
- DBMS_SQL 패키지는 동적 SQL을 사용하여 DML(데이터 조작 언어) 또는 DDL(데이터 정의 언어) 문을 구문 분석할 수 있는 인터페이스를 제공한다.
- DBMS_SQL 패키지의 PARSE 프로시저를 사용하여 SQL 구문을 파싱하며, DDL의 경우 별도의 EXECUTE 프로시저를 호출하지 않아도 된다.
- EXECUTE 프로시저는 처리된 건수를 리턴하는데, INSERT, UPDATE 및 DELETE 문에 대해서만 유효하다. DDL을 포함한 다른 유형의 문의 경우 반환 값은 정의되지 않으므로 무시해야 한다.
[실습1 : DBMS_SQL 패키지를 이용하여 테이블 생성여부 확인 후 테이블 삭제 및 생성]
CREATE OR REPLACE PROCEDURE DBMS_SQL_TEST
AS
cursor_id INTEGER;
rows_processed INTEGER;
v_cnt NUMBER;
BEGIN
cursor_id := DBMS_SQL.OPEN_CURSOR;
SELECT COUNT(*) INTO v_cnt FROM user_tables WHERE table_name = 'TEST';
IF v_cnt > 0 THEN
DBMS_SQL.PARSE(cursor_id, 'drop table test', DBMS_SQL.NATIVE);
rows_processed := DBMS_SQL.EXECUTE(cursor_id);
-- v6 또는 0 : 버전6, v7 또는 2 : 버전7, NATIVE 또는 1 : 연결된 데이터베이스의 정상적인 동작을 지정
DBMS_SQL.PARSE(cursor_id, 'create table test ( id number, name varchar2(20)) ', DBMS_SQL.NATIVE);
rows_processed := DBMS_SQL.EXECUTE(cursor_id);
DBMS_OUTPUT.PUT_LINE('삭제 후 생성');
ELSE
DBMS_SQL.PARSE(cursor_id, 'create table test ( id number, name varchar2(20)) ', DBMS_SQL.NATIVE);
rows_processed := DBMS_SQL.EXECUTE(cursor_id);
DBMS_OUTPUT.PUT_LINE('생성');
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_id);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
set serveroutput on
exec dbms_sql_test;
select * from test;
drop table test;
[실습2 : DBMS_SQL 패키지를 이용하여 동적SQL 커서 생성 후 FETCH]
-- 아래 예제는 EMP 테이블에서 입력한 부서코드의 사원을 출력하는 예제이다.
-- DBMS_SQL 패키지를 이용하여 커서를 생성하고 FETCH후 출력
CREATE OR REPLACE PROCEDURE GETEMPS(P_DEPTNO IN NUMBER)
AS
V_SQL VARCHAR2(1000);
V_CURSOR INTEGER;
V_AFFECTED_ROWS INTEGER;
V_EMPNO EMP.EMPNO%TYPE;
V_ENAME EMP.ENAME%TYPE;
BEGIN
-- SQL문을 처리하려면 열린 커서가 있어야 하는데 OPEN_CURSOR함수를 사용, 커서ID를 리턴
V_CURSOR := DBMS_SQL.OPEN_CURSOR;
V_SQL := 'SELECT ename, sal FROM emp WHERE deptno = :d';
-- Dynamic SQL문 파싱
DBMS_SQL.PARSE(V_CURSOR, V_SQL, DBMS_SQL.NATIVE);
-- 바인드변수 바인딩
DBMS_SQL.BIND_VARIABLE(V_CURSOR, 'd', P_DEPTNO);
-- CURSOR로부터 추출된 칼럼의 값을 받는 변수 지정
-- 커서명, 상대위치, 받을변수명 ,길이 지정
DBMS_SQL.DEFINE_COLUMN (V_CURSOR, 1, V_EMPNO);
DBMS_SQL.DEFINE_COLUMN (V_CURSOR, 2, V_ENAME, 20);
-- SQL문 실행
V_AFFECTED_ROWS := DBMS_SQL.EXECUTE(V_CURSOR);
DBMS_OUTPUT.PUT_LINE('V_AFFECTED_ROWS : ' || V_AFFECTED_ROWS);
-- CURSOR에서 데이터 추출
WHILE(DBMS_SQL.FETCH_ROWS(V_CURSOR) > 0)
LOOP
-- FETCH 결과를 변수에 저장
DBMS_SQL.COLUMN_VALUE(V_CURSOR,1,V_EMPNO);
DBMS_SQL.COLUMN_VALUE(V_CURSOR,2,V_ENAME);
DBMS_OUTPUT.PUT_LINE('EMPNO :' || V_EMPNO || 'ENAME : '|| V_ENAME);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(V_CURSOR) THEN
DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
END IF;
END;
/
SET SERVEROUTPUT ON;
EXEC GETEMPS(20);
