(29)[ORACLE] 12일차 : PL/SQL - 3 (커서와 동적쿼리)

 

 

커서(Cursor)란?
하나의 레코드가 아닌 여러 레코드로 구성된 작업영역에서 SQL문을
실행하고 그 과정에 생긴 정보를 저장하기 위해서 CURSOR를 사용한다.
오라클 서버에 의해 실행되는 모든 SQL문은 연관된 각각의 커서를 소유하고 있다.

커서의 종류
암시적 커서 : 모든 DML과 PL/SQL SELECT문에 대해 선언된다.
명시적 커서 : 프로그래머에 의해 선언되며 이름이 있는 커서.

암시적 커서

암시적인 커서는 오라클이나 PL/SQL실행 메커니즘에 의해 처리되는 SQL문장이 처리되는 곳에 대한 익명의 주소. 오라클 데이터베이스에서 실행되는 모든 문장은 암시적인 커서가 생성되며, 커서 속성을 사용할 수 있다.

암시적 커서는  SQL 문이 실행되는 순간 자동으로 OPEN과 CLOSE를 실행한다.

 

--커서의 종류--
SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수
SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 1개 이상일 경우 TRUE
SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE
SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색

 

--0 레코드 삭제
DECLARE
    vempNO emp.empNo%TYPE;
    vcount  NUMBER;
BEGIN    
    vempNo := '100l';
    DELETE FROM emp WHERE empNo = vempNo;
    vcount := SQL%ROWCOUNT;
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE(vcount||' 레코드 삭제');
END;
/

 

 

 

명시적 커서

명시적 커서는 프로그래머에 의해 선언되며 이름이 있는 커서로 여러 row를 다룰 수 있다. 

CURSOR 선언 → 커서 OPEN → FETCH → 커서 CLOSE

• CURSOR선언 : 검색할 쿼리를 선언한다.
• 커서 OPEN : OPEN은 커서에서 선언된 SELECT문의 실행을 의미한다. 데이터행을 추출하지 못해도 에러가 발생하지 않는다. 
• FETCH : 커서의 FETCH는 현재 데이터 행을 OUTPUT변수에 반환한다. 커서의 SELECT문 컬럼의 수 및 데이터 타입은 OUTPUT변수의 수 및 데이터 타입과 일치해야 한다. 
• 커서 CLOSE : OPEN된 커서는 사용 완료후 반드시 CLOSE 해야 한다. 선언된 SELECT문의 선언을 해제 한다. 커서가 된 CLOSE된 상태에서는 FETCH를 할 수 없다. 

-- name,sal 전체 가져오기 
DECLARE
        vname emp.name%TYPE;
        vsal    emp.sal%TYPE;
        --CURSOR 선언
        CURSOR cur_emp IS SELECT name,sal FROM emp;
        
BEGIN   
        --CURSOR OPEN 
        OPEN cur_emp;
        
        LOOP
            --FETCH
            FETCH cur_emp INTO vname,vsal;
            EXIT WHEN cur_emp%NOTFOUND;
            
            DBMS_OUTPUT.PUT_LINE(vname||':'||vsal);
        END LOOP;
            --CLOSE
        CLOSE cur_emp;
END;
/

 

-- '김'씨 성을 가진 사람 검색

CREATE OR REPLACE PROCEDURE pEmpSelect
(
    pName VARCHAR2
)
IS
    vname emp.name%TYPE;
    vsal    emp.sal%TYPE;
    
    
    CURSOR cur_emp IS
            SELECT name,sal FROM emp WHERE INSTR(name,pName)>0;
BEGIN   
        
        OPEN cur_emp;
        
        LOOP
            --FETCH
            FETCH cur_emp INTO vname,vsal;
            EXIT WHEN cur_emp%NOTFOUND;
            
            DBMS_OUTPUT.PUT_LINE(vname||':'||vsal);
        END LOOP;
            --CLOSE
        CLOSE cur_emp;
END;
/

EXEC pEmpSelect('김');

 

 

 

커서 변수

명시적 커서와 암시적 커서는 정적이므로, 커서가 만들어지는 시점에서 쿼리문이 정의된다. 따라서 런타임까지 커서에 이용할 쿼리를 정의할 수 없기 때문에 REF CURSOR와 커서변수를 제공하여 런타임시에 커서의 쿼리를 정의하고 결과 셋을 처리할 수 있도록 하고 있다.

 

-- SYS_REFCURSOR
-- : 약한 참조 커서 타입
-- : 데이터 타입을 포함하고 있지 않기 때문에 모든 결과 셋을 저장할 수 있음
-- : 프로시저 실행결과(SELECT 문)를 자바 등 프로그램에 전달 할때 사용

-- 커서 변수 선언
커서변수 SYS_REFCURSOR;

-- 커서 변수 사용
OPEN 커서변수 FOR SELECT 문

-- 커서 변수에서 값 가져 오기
FETCH 커서변수 INTO 변수, 변수;
FETCH 커서변수 INTO 레코드변수;

 

--SYS_REFCURSOR


CREATE OR REPLACE PROCEDURE pEmpSelect
(
        pName VARCHAR2,
        pResult OUT SYS_REFCURSOR
)
IS      
BEGIN   
    OPEN pResult FOR
        SELECT name,sal FROM emp WHERE INSTR(name,pName)>0;
END;
/

 

--확인

DECLARE
        vName emp.name%TYPE;
        vSal    emp.sal%TYPE;
        vResult SYS_REFCURSOR;
BEGIN
        pEmpSelect('정',vResult);
        
        LOOP
            FETCH vResult INTO vName,vSal;
            EXIT WHEN vResult%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(vName||':'||vSal);
        END LOOP;
END;
/

 

 

---

 

 

동적쿼리란?
프로시저등에서 동적으로 쿼리를 생성하거나 텍스트 쿼리를 입력 받아 처리하는 경우

-- EXECUTE IMMEDIATE
-- : DDL, DML 구문을 실행
-- : SELECT 구문 실행 시 INTO 절을 사용하여 단일 값을 반환 받을 때 사용
-- : 프로시저 등에서 동적으로 쿼리를 생성하거나 텍스트 쿼리를 입력 받아 처리하는 경우 사용
-- : RESOURCE 권한만 있으면 기본적으로 테이블생성, 시퀀스 생성등을 할수 있지만
-- : EXECUTE IMMEDIATE 에서는 불가능하다.
-- : EXECUTE IMMEDIATE 로 테이블을 생성하거나 시퀀스를 만들기 위해서는 다음의 시스템 권한이 필요 한다.

 

 

 CREATE OR REPLACE PROCEDURE pboardcreate
     (
        pname VARCHAR2
     )
     IS
        S VARCHAR2(4000);
     BEGIN
        S := ' create table ' || pname;
        S := S|| '(num NUMBER PRIMARY KEY, ';
        S := S|| ' name VARCHAR2(30) not null, ';
        S := S|| ' subiect VARCHAR2(255) not null, ';
        S := S|| ' content VARCHAR2(4000) not null, ';
        S := S|| ' hitCount NUMBER DEFAULT 0, ';
        S := S|| ' reg_date DATE DEFAULT SYSDATE) ';
        
        FOR T IN (SELECT tname FROM TAB WHERE tname = UPPER(pname))LOOP
            EXECUTE IMMEDIATE 'drop table ' || pname || ' PURGE';
            Dbms_Output.Put_Line(Pname||'테이블 삭제...');
        END LOOP;
        
        EXECUTE IMMEDIATE S;
        Dbms_Output.Put_Line(Pname||'테이블 생성...');
        
        S := 'CREATE SEQUENCE ' ||pname||'_seq';
        FOR T IN ( SELECT * FROM seq WHERE sequence_name = UPPER(pname||'_seq'))LOOP
            EXECUTE IMMEDIATE 'DROP SEQUENCE '||pname||'_seq';
            DBMS_OUTPUT.PUT_LINE(PNAME||'_seq 시퀀스 삭제...');
        END LOOP;
        
        EXECUTE IMMEDIATE S;
        DBMS_OUTPUT.PUT_LINE(PNAME||'_seq 시퀀스 생성...');
END;
/ 
     

SELECT * FROM TAB;

EXEC pboardcreate('board');
SELECT * FROM TAB;
SELECT * FROM seq;