[Oracle] 오라클 - PL/SQL 커서(Cursor) / 동적쿼리

[Oracle]
오라클 - PL/SQL 커서(Cursor) / 동적쿼리

✔️ 커서(Cursor)

: 하나의 레코드가 아닌, 여러 레코드로 구성된 작업영역에서 SQL 문을 실행하고 그 과정에서 생긴 정보를 저장하기 위해 CURSOR 를 사용한다.

: 오라클 서버에 의해 실행되는 모든 SQL 문은 각각의 커서를 소유한다.

: 1)암시적 커서 (선언) 2)명시적 커서 (프로그래머가 선언하는 이름있는 커서)

✔️ 암시적 커서(Cursor)

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

-- %ROWCOUNT

 DECLARE
	vEmpNo emp1.empNo%TYPE;
        vCount NUMBER;
        
BEGIN
	vEmpNo := '1001';
        DELETE FROM emp1 WHERE empNo = vEmpNo;
        vCount := SQL%ROWCOUNT;  --실행된 쿼리의 갯수 알 수 있음
        COMMIT;
        
        DBMS_OUTPUT.PUT_LINE(vCount || '데이터 삭제!');
    END;
    /

-- %NOTFOUND

 DECLARE
        vEmpNo emp1.empNo%TYPE;
        vCount NUMBER;
    
    BEGIN
        vEmpNo := '1001';
        DELETE FROM emp1 WHERE empNo = vEmpNo;
        
        IF SQL%NOTFOUND THEN
            RAISE_APPLICATION_ERROR(-20001, '데이터가 존재하지 않음!');
        END IF;
        
        vCount := SQL%ROWCOUNT;  --실행된 쿼리의 갯수 알 수 있음
        
        COMMIT;
        
        DBMS_OUTPUT.PUT_LINE(vCount || '데이터 삭제!');
    END;
    /

✔️ 명시적 커서(Cursor)

- 사용자가 명시적으로 선언한 커서를 말한다.

- 실행순서 : CURSOR 선언 -> 커서 OPEN -> FETCH -> 커서 CLOSE

  DECLARE
        vName emp.name%TYPE;
        vSal emp.sal%TYPE;
        
        -- 1) 커서 선언
        CURSOR cur_emp IS SELECT name, sal FROM emp;
        
BEGIN
            -- 2) 커서 오픈
            OPEN cur_emp;
            
            LOOP
                -- 3) FETCH
                FETCH cur_emp INTO vName, vSal;
                EXIT WHEN cur_emp%NOTFOUND;
                
                DBMS_OUTPUT.PUT_LINE(vName || '  ' || vSal);
            END LOOP;
            
            -- 4) 커서 close
            CLOSE cur_emp;
            
        END;
        /

💡 커서 선언 : CURSOR 커서명 IS
커서 오픈 : OPEN 커서명
FETCH 해줘야함
CLOSE 커서명

-- 프로시저에서 커서 사용하기

 CREATE OR REPLACE PROCEDURE pSelectEmp
        (
            pName VARCHAR2  
        )
        IS
             vName emp.name%TYPE;
             vSal emp.sal%TYPE;
        
             -- 1) 커서 선언
            CURSOR cur_emp IS SELECT name, sal FROM emp WHERE INSTR(name, pName) > 0;
            
        BEGIN
             -- 2) 커서 오픈
            OPEN cur_emp;
            
            LOOP
                -- 3) FETCH
                FETCH cur_emp INTO vName, vSal;
                EXIT WHEN cur_emp%NOTFOUND;
                
                DBMS_OUTPUT.PUT_LINE(vName || '  ' || vSal);
            END LOOP;
            
            -- 4) 커서 close
            CLOSE cur_emp;
        
        END;
        /
        
        EXEC pSelectEmp('이');

-- FOR ~ LOOP : 자동으로 OPEN / 자동 FETCH , 자동 CLOSE!

  CREATE OR REPLACE PROCEDURE pSelectEmp
        (
            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
            FOR rec IN cur_emp LOOP
                DBMS_OUTPUT.PUT_LINE(rec.name || '   ' || rec.sal);
                END LOOP;
 
        END;
        /
 
                EXEC pSelectEmp('김');

💡 FOR~ LOOP 에서는 커서 오픈/ 클로즈가 불필요하다. (자동으로 해줌)

✔️ 커서 변수(cursor variable)

-- SYS_REFCURSOR ⭐⭐ 중요함!!! 자바랑 연결하기 해야할 때 필요하다.

(FOR 문장은 사용 못한다.)

-- 실행된 결과를 담는 그릇 = SYS_REFCURSOR

: 약한 참조 커서 타입

: 데이터 타입을 포함하고 있지 않기 때문에 모든 결과 셋을 저장할 수 있음

: 프로시저 실행결과(SELECT 문)를 자바 등 프로그램에 전달 할때 사용

⭐ 커서 변수 사용 / 값 가져오기

OPEN 커서변수 FOR SELECT 문
⭐⭐FETCH 커서변수 INTO 변수, 변수;
⭐⭐FETCH 커서변수 INTO 레코드변수;

 -- 확인용 프로시저
    CREATE OR REPLACE PROCEDURE pResultEmp
    (
        pName VARCHAR2
    )
    IS
        vName emp.name%TYPE;
        vSal emp.sal%TYPE;
        vResult SYS_REFCURSOR;
        
    BEGIN
        -- 다른 프로시저 호출
        pSelectEmp(pName, vResult);
        
        LOOP 
            FETCH vResult INTO vName, vSal;
            EXIT WHEN vResult%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(vName || '   ' || vSal);
        END LOOP;
    END;
    /
    
    EXEC pResultEmp('김');

✔️ 동적 쿼리

-- EXECUTE IMMEDIATE

: DDL, DML 구문을 실행

: SELECT 구문 실행 시 INTO 절을 사용하여 단일 값을 반환 받을 때 사용

: 프로시저 등에서 동적으로 쿼리를 생성하거나 텍스트 쿼리를 입력 받아 처리하는 경우 사용
: RESOURCE 권한만 있으면 기본적으로 테이블생성, 시퀀스 생성등을 할수 있지만 EXECUTE IMMEDIATE 에서는 불가능하다.
: EXECUTE IMMEDIATE 로 테이블을 생성하거나 시퀀스를 만들기 위해서는 다음의 시스템 권한이 필요 한다.
CREATE TABLE, CREATE SEQUENCE

 ⭐ 권한부터 줘야한다. 
 
GRANT CREATE TABLE TO sky;
GRANT CREATE SEQUENCE TO sky;
 
 
-- SKY 계정에서 내 권한 확인하기 
SELECT * FROM user_sys_privs;

-- 동적 쿼리를 이용하여 테이블 및 시퀀스 작성

 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(50) NOT NULL,';
        s := s || ' subject VARCHAR2(500) NOT NULL,';
        s := s || ' content VARCHAR2(4000) NOT NULL,';
        s := s || ' hitCount NUMBER DEFAULT 0, ';
        s := s || ' reg_date DATE DEFAULT SYSDATE)';
        
        EXECUTE IMMEDIATE s;
        DBMS_OUTPUT.PUT_LINE(pName || ' 테이블 생성 !!' );
        
        s := 'CREATE SEQUENCE ' || pName || '_seq';
        EXECUTE IMMEDIATE s;
        DBMS_OUTPUT.PUT_LINE(pName || '_seq 시퀀스 생성 !!' );
        
    END;
    /

-- 동적 쿼리를 이용하여 테이블 및 시퀀스 작성 (삭제, 수정 포함)

 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(50) NOT NULL,';
        s := s || ' subject VARCHAR2(500) 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 * FROM tab WHERE tname = UPPER(pName)) LOOP
            EXECUTE IMMEDIATE 'DROP TABLE ' || pName || '  PURGE';
        DBMS_OUTPUT.PUT_LINE(pName || ' 테이블 삭제 !!' ); 
        EXIT;
        END LOOP;
        
        EXECUTE IMMEDIATE s;
        DBMS_OUTPUT.PUT_LINE(pName || ' 테이블 생성 !!' );
        
        
        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 시퀀스 삭제 !!' ); 
        EXIT;
        END LOOP;
        
        s := 'CREATE SEQUENCE ' || pName || '_seq';
        EXECUTE IMMEDIATE s;
        DBMS_OUTPUT.PUT_LINE(pName || '_seq 시퀀스 생성 !!' );
        
    END;
    /

💡 같은 이름의 테이블/시퀀스가 있으면 삭제하고 바로 다시 만든다.

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역

	DECLARE
	vEmpNo emp1.empNo%TYPE;
	vCount NUMBER;

	BEGIN
	vEmpNo := '1001';
	DELETE FROM emp1 WHERE empNo = vEmpNo;
	vCount := SQL%ROWCOUNT; --실행된 쿼리의 갯수 알 수 있음
	COMMIT;

	DBMS_OUTPUT.PUT_LINE(vCount \|\| '데이터 삭제!');
	END;
	/

	DECLARE
	vName emp.name%TYPE;
	vSal emp.sal%TYPE;

	-- 1) 커서 선언
	CURSOR cur_emp IS SELECT name, sal FROM emp;

	BEGIN
	-- 2) 커서 오픈
	OPEN cur_emp;

	LOOP
	-- 3) FETCH
	FETCH cur_emp INTO vName, vSal;
	EXIT WHEN cur_emp%NOTFOUND;

	DBMS_OUTPUT.PUT_LINE(vName \|\| ' ' \|\| vSal);
	END LOOP;

	-- 4) 커서 close
	CLOSE cur_emp;

	END;
	/

	CREATE OR REPLACE PROCEDURE pSelectEmp
	(
	pName VARCHAR2
	)
	IS
	vName emp.name%TYPE;
	vSal emp.sal%TYPE;

	-- 1) 커서 선언
	CURSOR cur_emp IS SELECT name, sal FROM emp WHERE INSTR(name, pName) > 0;

	BEGIN
	-- 2) 커서 오픈
	OPEN cur_emp;

	LOOP
	-- 3) FETCH
	FETCH cur_emp INTO vName, vSal;
	EXIT WHEN cur_emp%NOTFOUND;

	DBMS_OUTPUT.PUT_LINE(vName \|\| ' ' \|\| vSal);
	END LOOP;

	-- 4) 커서 close
	CLOSE cur_emp;

	END;
	/

	EXEC pSelectEmp('이');

	-- 확인용 프로시저
	CREATE OR REPLACE PROCEDURE pResultEmp
	(
	pName VARCHAR2
	)
	IS
	vName emp.name%TYPE;
	vSal emp.sal%TYPE;
	vResult SYS_REFCURSOR;

	BEGIN
	-- 다른 프로시저 호출
	pSelectEmp(pName, vResult);

	LOOP
	FETCH vResult INTO vName, vSal;
	EXIT WHEN vResult%NOTFOUND;
	DBMS_OUTPUT.PUT_LINE(vName \|\| ' ' \|\| vSal);
	END LOOP;
	END;
	/

	EXEC pResultEmp('김');

	⭐ 권한부터 줘야한다.

	GRANT CREATE TABLE TO sky;
	GRANT CREATE SEQUENCE TO sky;


	-- SKY 계정에서 내 권한 확인하기
	SELECT * FROM user_sys_privs;

	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(50) NOT NULL,';
	s := s \|\| ' subject VARCHAR2(500) NOT NULL,';
	s := s \|\| ' content VARCHAR2(4000) NOT NULL,';
	s := s \|\| ' hitCount NUMBER DEFAULT 0, ';
	s := s \|\| ' reg_date DATE DEFAULT SYSDATE)';

	EXECUTE IMMEDIATE s;
	DBMS_OUTPUT.PUT_LINE(pName \|\| ' 테이블 생성 !!' );

	s := 'CREATE SEQUENCE ' \|\| pName \|\| '_seq';
	EXECUTE IMMEDIATE s;
	DBMS_OUTPUT.PUT_LINE(pName \|\| '_seq 시퀀스 생성 !!' );

	END;
	/

✔️ 커서(Cursor)

✔️ 암시적 커서(Cursor)

✔️ 명시적 커서(Cursor)

✔️ 커서 변수(cursor variable)

✔️ 동적 쿼리

블로그의 정보

활동하기

공유하기

다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역