[ORACLE] PL/SQL - NDS

동적 SQL
- 고정된 SQL이 아닌 변경 가능한 쿼리 - 사용될 SQL문이 실행시 결정됨 종류 - 원시 동적 SQL(NDS - Native Dynamic SQL)방식 - DBMS Dynamic SQL방식  사용을 하는 이유 - 컴파일 시 SQL문이 확정되지 않을 때 - PL/ SQL 블록 상에서 DDL문을 사용해야 할 때 NDS(Native Dynamic SQL) : 원시동적 SQL - 동적 SQL의 한 종류 - EXECUTE IMMEDIATE ~ INTO 문 사용
SYNTAX ③EXECUTE IMMEDIATE ①sql문자열 ④INTO 담을 변수1, 담을변수2...; [ ②USING sql문자열에서 사용한 변수에 대응되는 바인드변수1, 바인드변수2, ....; ] **sqp문자열은 SQL쿼리문을 담기때문에 자료형의 사이즈를 좀 넉넉하게 해준다.	DECLARE     VEMPNO EMP.EMPNO%TYPE;     VENAME EMP.ENAME%TYPE;     --바인드변수와 값 설정     VJOB EMP.JOB%TYPE := 'CLERK';     V_SQL VARCHAR2(1000); BEGIN     --SQL쿼리문을 변수에 담는다.     --V_SQL변수가 VARCHAR이므로 안의 SMITH가 문자열이라는 것을 인식하도록 하기 위해서 ''값''(싱클쿼테이션)을 사용해야 한다. 더블쿼테이션x.     V_SQL := 'SELECT EMPNO, ENAME, JOB FROM EMP WHERE ENAME=''SMITH'' ';          EXECUTE IMMEDIATE V_SQL INTO VEMPNO, VENAME, VJOB;          DBMS_OUTPUT.PUT_LINE('EMPNO : '||VEMPNO );     DBMS_OUTPUT.PUT_LINE('ENAME : '||VENAME );     DBMS_OUTPUT.PUT_LINE('JOB : '||VJOB); END;​
바인드 변수 사용 : 연결해주는 변수를 바인드라고 함. SQL쿼리문에 변수를 설정하여 해당 변수에 대응하는 값을 연결해주는 방식 여러 SQL안의 값이 변경되어도 구문을 변경해줄 필요가 없다.

버전이 낮은 경우

DBA권한을 부여받은 계정에서라도 PL/SQL에서 CREATE TABLE명령문 실행 시

에러가 밸상되는 경우(PRIVILLAGE...) DBA권한을 가진 계정이더라도

다시 한 번 CREATE TABLE권한을 부여해주어야 한다.

GRANT CREATE TABLE TO TEST ;

NDS

DECLARE
    VEMPNO EMP.EMPNO%TYPE;
    VENAME EMP.ENAME%TYPE;

    --바인드변수와 값 설정
    VJOB EMP.JOB%TYPE := 'CLERK';
    V_SQL VARCHAR2(1000);
BEGIN
    --SQL쿼리문을 변수에 담는다.
    --V_SQL변수가 VARCHAR이므로 안의 SMITH가 문자열이라는 것을 인식하도록 하기 위해서
    --''값''(겹 작은따옴표)을 사용해야 한다. 더블쿼테이션이 아님.
    V_SQL := 'SELECT EMPNO, ENAME, JOB FROM EMP WHERE ENAME=''SMITH'' ';
    
    EXECUTE IMMEDIATE V_SQL INTO VEMPNO, VENAME, VJOB;
    
    DBMS_OUTPUT.PUT_LINE('EMPNO : '||VEMPNO );
    DBMS_OUTPUT.PUT_LINE('ENAME : '||VENAME );
    DBMS_OUTPUT.PUT_LINE('JOB : '||VJOB);
END;​

EMPNO : 7369
ENAME : SMITH
JOB : CLERK

NDS + USING

DECLARE
    VEMPNO EMP.EMPNO%TYPE;
    VENAME EMP.ENAME%TYPE;

    --바인드변수와 값 설정
    VJOB EMP.JOB%TYPE := 'CLERK';
    VDEPTNO EMP.DEPTNO%TYPE := 30;
    
    V_SQL VARCHAR2(1000);
BEGIN
    --SQL쿼리문을 바인드 변수로 처리
    --SQL쿼리문안의 바인드 변수는 꼭 [:변수명] 형식으로 기술해주어야 한다.
    V_SQL := 'SELECT EMPNO, ENAME FROM EMP WHERE JOB = :a AND DEPTNO = :b ';
    --가능 V_SQL := 'SELECT EMPNO, ENAME, JOB FROM EMP WHERE JOB = :a AND DEPTNO = :a ';
    
    EXECUTE IMMEDIATE V_SQL INTO VEMPNO, VENAME
    USING VJOB, VDEPTNO;
    
    DBMS_OUTPUT.PUT_LINE('EMPNO : '||VEMPNO );
    DBMS_OUTPUT.PUT_LINE('ENAME : '||VENAME );
END;​

EMPNO : 7900
ENAME : JAMES​

바인드 변수 사용

CREATE TABLE MYINFO (
    IDS NUMBER,
    NM VARCHAR2(20),
    BD DATE
);​

DECLARE
    VIDS MYINFO.IDS%TYPE :=1;
    VNM MYINFO.NM%TYPE := 'ALBERT';
    VBD MYINFO.BD%TYPE := TO_DATE('2000-01-01', 'YYYY-MM-DD');
--    VIDS MYINFO.IDS%TYPE := 2;
--    VNM MYINFO.NM%TYPE := 'MAX';
--    VBD MYINFO.BD%TYPE := TO_DATE('2005-03-05', 'YYYY-MM-DD');
    VSQL VARCHAR2(1000);
BEGIN
    VSQL := 'INSERT INTO MYINFO VALUES ( :A, :A, :A)' ;
    
    EXECUTE IMMEDIATE VSQL USING VIDS, VNM, VBD;
    COMMIT; --INSERT DML이라 COMMIT 필요
    DBMS_OUTPUT.PUT_LINE(VIDS||' '||VNM||' '||VBD);
END;​

1 ALBERT 00/01/01

 

AA

DECLARE
    VIDS MYINFO.IDS%TYPE :=1;
    VNM MYINFO.NM%TYPE := 'ALBERT';
    VBD MYINFO.BD%TYPE := TO_DATE('2010-01-01', 'YYYY-MM-DD');
    VMY MYINFO%ROWTYPE;
    CURSOR C_MYINFO IS SELECT * FROM MYINFO;
    
    VSQL VARCHAR2(1000);
BEGIN
    VSQL := 'UPDATE MYINFO SET BD = :A WHERE IDS =:A' ;   
    EXECUTE IMMEDIATE VSQL USING VBD, VIDS ;
    COMMIT; --INSERT DML이라 COMMIT 필요
    
    SELECT NM, BD INTO VNM, VBD FROM MYINFO WHERE IDS = VIDS;
    DBMS_OUTPUT.PUT_LINE('UPDATE문 실행 후 (COMMIT): '||VNM||', '||VBD);
    DBMS_OUTPUT.PUT_LINE('-------------------');
    
    VIDS :=2;
    VSQL:= 'DELETE FROM MYINFO WHERE IDS =:A';
    EXECUTE IMMEDIATE VSQL USING VIDS;
    
    OPEN C_MYINFO;
    LOOP
        FETCH C_MYINFO INTO VMY;
        EXIT WHEN C_MYINFO%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('DELETE문 실행 후: '||VMY.IDS||', '||VMY.NM||', '||VMY.BD);
    END LOOP;
    CLOSE C_MYINFO;
    DBMS_OUTPUT.PUT_LINE('-------------------');
    
    ROLLBACK;
    OPEN C_MYINFO;
    LOOP
        FETCH C_MYINFO INTO VMY;
        EXIT WHEN C_MYINFO%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('DELETE문 실행 후: '||VMY.IDS||', '||VMY.NM||', '||VMY.BD);
    END LOOP;
    CLOSE C_MYINFO;
END;​

UPDATE문 실행 후 (COMMIT): ALBERT, 10/01/01
-------------------
DELETE문 실행 후: 1, ALBERT, 10/01/01
-------------------
DELETE문 실행 후: 1, ALBERT, 10/01/01​

A

--동적쿼리를 이용한 DDL문 사용하기
DECLARE
    VSQL VARCHAR2(1000);
    VCNT NUMBER;
BEGIN
    VSQL := 'SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = ''TEST'' ';
    --COUNT(*) : 값이 없으면 '0'값이라도 반환함.
    EXECUTE IMMEDIATE VSQL INTO VCNT;
    
    IF VCNT <=0 THEN
        DBMS_OUTPUT.PUT_LINE('TEST테이블을 생성합니다.');
        VSQL := 'CREATE TABLE TEST (A NUMBER(10), B VARCHAR2(10))';
        EXECUTE IMMEDIATE VSQL;
    END IF;
    
    VSQL := 'SELECT COUNT(*) FROM ALL_TABLES WHERE TABLE_NAME = ''MYINFO'' ';
    EXECUTE IMMEDIATE VSQL INTO VCNT;
    
    IF VCNT >0 THEN
        VSQL := 'DROP TABLE MYINFO PURGE';
        EXECUTE IMMEDIATE VSQL;
    END IF;
END;​

--실행결과 MYINFO테이블 삭제됨