day12 - 오라클 (PL/SQL, 커서, 명시적 커서, 묵시적 커서, 프로시저

[SQL 커서]

- SQL 커서 : 오라클 서버에서 SQL 문을 실행할 때마다 처리(Parse, Execution)를 위한 메모리공간

   -> SQL문을 실행했을 때 해당 SQL문 처리 정보를 저장

   -> SELECT문의 결과 행 별로 특정 작업 수행

 

- select into 방식

   -> 조회되는 행이 하나 일 때

   -> 커서는 조회되는 행의 수와 상관없이 사용

 

- 즉 사용자가 요청하는 데이터를 데이터베이스 버퍼 캐쉬에서 커서로 복사해온 후 커서에서 원하는 데이터를 추출하여(Fetch) 후속 작업을 하게 된다는 뜻.

 

- 이 메모리 공간을 Private SQL Area한다

 

 

 

 

- SQL 커서는 크게 묵시적 커서(Implicit Cursor)와 명시적 커서(Explicit Cursor)로 나눌 수 있다.

 

 

[명시적 커서]

-> 직접 커서를 선언하고 사용

[예시]

 

•명시적 커서 속성(Cursor Attribute) 

   - 커서이름%ROWCOUNT 

   - 커서이름%FOUND 

   - 커서이름%NOTFOUND
   - 커서이름%ISOPEN

 

 

 

 

 

 

[명시적 커서 loop 예시]

 

 

 

 

 

 

[명시적 커서 for문]

-> 더 가독성이 좋고 간단하다.

-> 자동으로 알아서 open close 해준다.

 

[명시적 커서 for문 구조]

 

 

 

 

 

 

 

[명시적 커서 for문 예시1]

 

 

DECLARE

   CURSOR emp_cur IS -- 커서 선언됩니다 
   SELECT empno ,ename

   FROM emp;
BEGIN

   FOR emp_rec IN emp_cur -- 커서의 데이터를 저장할 emp_rec 변수 선언됨
   LOOP 

      DBMS_OUTPUT.PUT_LINE(emp_rec.empno||' '||emp_rec.ename); 

   END LOOP;
END;

 

 

 

 

 

 

 

 

[명시적 커서 for문 예시2]

-> 서브쿼리를 사용하는 예시

-> 이 방법으로 DECLARE 부분에 커서를 선언할 필요가 없어짐

-> 대신 커서에 들어가는 서브쿼리 부분을 IN 뒷부분에 바로 사용

 

DECLARE 

BEGIN

   FOR emp_rec IN (SELECT empno, ename, sal FROM emp)
   LOOP

        DBMS_OUTPUT.PUT_LINE(emp_rec.empno||' '||emp_rec.ename||' '||emp_rec.sal); 

   END LOOP;
END ;

 

 

 

[명시적 커서 for문 예시3]

-> 커서에서 alias (aaa) 를 지정했을 경우 실제 커서가 실행될때 알리아스를 활용한다. 

 

 

 

 

[묵시적 커서]

- SQL문을 사용했을 때 자동으로 선언되는 커서 

- OPEN, FETCH, CLOSE 지정없음

 

 

- 묵시적 커서는 오라클에서 자동적으로 선언해주는 SQL 커서로서, 사용자는 생성 유무를 알 수 없음

 

- 묵시적 커서에 저장되는 데이터는 1 행만 가능.

 

•묵시적 커서 속성(Cursor Attribute) 

   - SQL%ROWCOUNT 

   - SQL%FOUND 

   - SQL%NOTFOUND
   - SQL%ISOPEN

 

 

--ex

DECLARE v_deptno number :=&dno; 

begin 

   delete emp where deptno = v_deptno; 

   dbms_output.put_line('삭제된 건수는 '||sql%rowcount||' 입니다'); 

rollback;
end;

 

결과 화면

 

 

 

-> 일반적으로는 커서와 for문을 활용하는 방식이 가장 많이 쓰인다. 

 

 

 

 

 

 

[PL/SQL sub program]

-> 저장 서브프로그램: 이름을 지정하여 저장해두는 PL/SQL 프로그램을 뜻한다.

 

 

 

 

저장 서브 프로그램

 

-> 예전에는 트리거를 자주 썼었으나 현재에는 그다지 많이 쓰이지는 않음.

-> 정보처리기사에는 개념이 나오니 개념은 공부할 것

 

 

 

[프로시저]

-> 파라미터를 사용하는 프로시저

-> [] 안은 생략이 가능

-> 4,5,6 번은 기존과 동일한 개념