[DB] PL/SQL, 커서

PL/SQL이어서...

 

* 제어문

- 준비된 코드를 상황에 맞게 실행하기 위해서 사용한다.

- 종류

- 조건문

+ if

- 단일 if : 조건에 맞을 때에만 코드를 실행

if 조건식 then 조건에 맞을 때 수행할 코드 end if;

ex)

------------------------------------------

-- 조건문

------------------------------------------ 

-- *if

 

-- 문)이름을 입력 받아 계급을 출력하라.

-- 단, 계급은 입력되는 이름이 이진기이면 리더를 그 이외에는 멤버를 출력하라. 

set verify off

set serveroutput on    

 

accept name prompt 'name : '

 

declare

grade char(6 byte) := '멤버';  

name varchar2(15byte) := '&name';

begin                        

if name = '이진기' then

grade := '리더';

end if;

dbms_output.put_line(grade || ' ' || name);

end;

/

 

- if~else : 둘 중 하나의 코드가 실행될 때

if 조건식 then 조건에 맞을 때 수행 코드 else 조건에 맞지 않을때 수행 코드 end if;

ex)

------------------------------------------

-- 조건문

------------------------------------------ 

-- *if~else

 

-- 문) 주민번호를 입력받아서 남자 또는 여자를 출력하라

 

set verify off

set serveroutput on    

 

accept ssn prompt 'ssn : '

 

declare

sex char(6 byte); 

-- 891214-1234567

ssn char(14byte) := '&ssn';

begin                        

--suki ver. if substr(ssn,8,1) = '1' or substr(ssn,8,1) = '3' then 

if mod(substr(ssn,8,1),2) = 1 then                                 

-- └ 강사님 ver

sex := '남자';

else

sex := '여자';

end if;

dbms_output.put_line(sex);

end;

/

 

- 다중 if : 여러 조건을 부여해서 조건마다 다른 코드를 실행(문법상 else에 e가 들어가지 않는다.)

if then elsif 조건식2 then 조건식2에 맞을 때 수행 코드 else 모든 조곤에 맞지 않을 때 수행 코드 end if;

ex)

------------------------------------------

-- 조건문

------------------------------------------ 

-- *다중if(elsif)

 

-- 문) 입력되는 이름이 이진기이면 리더를 최민호면 서브막내를 이태민 혹은 태민이면 막내를 그 이외라면 김형제를 출력하라.

set verify off

set serveroutput on    

 

accept name prompt 'name : '

 

declare

grade varchar2(15 byte);  

name varchar2(15byte) := '&name';

begin                        

if name = '이진기' then

grade := '리더';

elsif name = '최민호' then

grade := '서브막내';

elsif name = '이태민' or name = '태민' then

grade := '막내';

else

grade := '김형제';

end if;

dbms_output.put_line(grade || ' ' || name);

end;

/

 

- 반복문

- 특정 코드를 여러번 실행해야 할 때 사용한다.

+ for : 개발자가 처음과 끝을 알 때(+1씩 증가한다.)

for 변수명(1씩 증가될) or counter in 시작 .. 끝 loop 반복수행문장; end loop;

[참고] 역순으로 반복하려면 in 다음에 reverse를 쓰고 반복할 횟수를 쓰면 된다. 이때 시작 번호화 끝 번호는 작은 숫자부터 써야 한다. 시작과 끝부분에 꼭 숫자값이 들어가야 하는 것은 아니며, 컬럼이나 커서등으로 대체할 수 있다.

 ex)

------------------------------------------

-- 반복문

------------------------------------------ 

-- *for

set verify off

set serveroutput on

accept num prompt '단을 입력하세요 : '

declare                              

begin

dbms_output.put_line(&num || '단'); 

-- for의 증가값을 저장하는 변수는 선언하지 않고 사용할 수 있다.     

for i in 1 .. 9 loop

dbms_output.put_line(&num || '*' || i || '=' || &num*i);

end loop;

end;

/

 

+ while : 시작과 끝을 모를 때 사용

초기값 while 조건식 loop 반복수행할 문장; 증감식 end loop;

ex)

------------------------------------------

-- 반복문

------------------------------------------ 

-- *다중if(elsif)

  

-- 문) 1~10까지 출력하는 반복문을 작성하라

set serveroutput on 

set verify off

accept dan prompt '단을 입력하세요 : '

declare

i number;

dan number := &dan;

begin        

i := 1;

while i<10 loop 

dbms_output.put_line(dan || '*' || i || '=' || (dan*i));

i := i+1;

-- ++연산자는 없음

end loop;

end;

/

 

+ loop : 무한 loop를 사용할 때

loop 반복수행 문장; exit(조건식); end loop;

ex)

------------------------------------------

-- loop

------------------------------------------ 

-- *다중if(elsif)

 

-- 문) 무한을 열번 출력하는 반복문을 작성하라

set serveroutput on 

set verify off

declare

i number := 1;

begin        

loop

dbms_output.put_line('무한');

exit when(i=10);

i:= i+1;

end loop;

end;

/

 

[참고] 인터럽트 : cpu를 점유하고 있는 프로세스를 강제로 뜯어낸다.

 

- 분기문

+ exit : 반복문을 빠져 나갈때 사용한다.

exit, exit wher(빠져나갈 조건(관계연산자));

 

* PL/SQL내에서 sql문 사용

- PL/SQL은 DML 및 트랜잭션 명령을 지원한다. 그러나 DML문과 TCL문을 사용할 경우 주의해야 할 부분이 있다.

└ end키워드는 트랜잭션의 끝이 아니라 PL/SQL블록의 끝을 나타낸다.

└ DDL문을 직접 지원하지 않는다. DDL문은 동적 SQL문이다. 동적 SQL문은 런타임에 문자열로 작성되며 파라미터의 위치 표시자를 포함할 수 있다. 따라서 동적 SQL문을 사용하면 PL/SQL에서 DDL문을 실행할 수 있다.

└ GRANT, REVOKE와 같은 DCL문을 직접 지원하지 않는다. 그러나 앞의 DDL문과 마찬가지로 동적 SQL을 사용하여 DCL문을 실행 할 수 있다.

- 쿼리가 수행된 후 변경된 행의 수를 제공하는 암시적 커서(sql%rowcount|커서명%속성)를 사용할 수 있다.(아래 커서부분 참고)

- where절에 변수의 이름, 컬럼의 이름이 같다면(where 1=1상태) 항상 '참'인 상태가 되기 때문에 원하지 않는 수행결과를 보여줄 수 있다.(이를 방지하기 위해 변수의 이름을 다르게 해주거나 변수인 경우에는 앞에 '&'를 붙여준다.)

 

+ PL/SQL 내에서의 SELECT 문장 사용하기

- 문법

select 컬럼명,,,

into 변수명,,,레코드명,,, (into절 : 조회결과를 변수에 저장하는 절)

from 테이블명

- select list항목과 into절의 변수 개수와 테이터 타입이 동일해야만 한다.

- 반드시 1건의 데이터만 조회되어야 하며 이럴 경우 where절은 필수적으로 사용되어야 한다.(여러행 또는 0행이 조회되면 error)

 

 ex)

------------------------------------------

-- PL/SQL내에서 sql문 사용

------------------------------------------ 

-- 사원번호, 사원명, 부서번호, 입사일, 연봉을 추가하는 PL/SQL 작성하라

-- 단, 입사일은 현재 날짜로 넣어라

-- 단, 10번 부서 사원은 입력된 연봉의 10%를 더하여 연봉을 계산하고 테이블에 추가하라.

set verify off

set serveroutput on

 

accept empno prompt '사원번호 : '

accept ename prompt '사원명 : '

accept deptno prompt '부서번호 : '

accept sal prompt '연봉 : '

declare

empno emp.empno%type := &empno; 

ename emp.ename%type := '&ename';

deptno emp.deptno%type := &deptno;

sal emp.sal%type := &sal;

begin 

if deptno = 10 then

sal := trunc(sal+sal*0.1,1);

end if;

                      

insert into cp_emp5(empno, ename, deptno, hiredate, sal)

values (empno, ename, deptno, sysdate, sal);

 

-- 커서 사용(아래 커서 부분 참고)

-- dbms_output.put_line(sql%rowcount || '행 추가');

  -- 추가 행 수가 1이므로 행 수 대신 자동으로 커밋하고 사용자에게 추가 성공 메시지 보여주기

if sql%rowcount = 1 then

commit;

dbms_output.put_line(empno || '번 사원 정보가 추가 되었습니다.');

end if;

 

------------------------------------------ -- 1007 예외처리 추가 ------------------------------------------ exception

when dup_val_on_index then

dbms_output.put_line(empno || '번 사원은 이미 존재하는 사원입니다.');

when others then

dbms_output.put_line('개발자가 인지 못한 예외');

-- └declare에서 발생한 예외에는 해당되지 않는다. 범위 파악 중요!

 

end;

/

 

-- 커밋하기전까지 데이터는 메모리상에만 올라가 있다. 이를 데이터베이스에 넣기 위해서는 커밋해주어야 한다.

-- 또한 현재 골덴과 sqlplus 두가지 서로 다른 세션으로 접속하고 있기 때문에 서로 다른 메모리를 가지고 있다.

-- 따라서 골덴에서는 insert후 그 결과를 바로 조회할 수 있었지만(골덴의 메모리에서 참조)

-- 다른 세션인 sqlplus에서는 그 결과를 조회할 수 없다. 

-- 따라서 다른 세션에서 그 결과를 확인하고 싶다면 커밋 후 확인하여야 한다. 

 

* 커서(Cursor)

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

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

- 이 메모리 공간을 Private SQL Area라고도 부르며, 오라클의 서버 프로세스 구성이 Dedicated Server환경이냐 또는 MTS환경이냐에 따라 서버 내에 위치되는 곳이 다르다.

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

- 묵시적 커서는 필요한 경우 오라클이 자동적으로 선언하여 사용한 후 자동적으로 정리한다. PL/SQL 블록이 실행될 때 내부에 포함된 SQL문장에 대해 SQL커서가 자동적으로 생성된다. 지금까지 사용했던 모든 PL/SQL문장들은 이 묵시적 커서가 자동으로 생성되어 사용되고 정리된 것이다.

- 명시적 커서는 사용자가 정의한 커서를 선언하여 사용하고, 커서의 사용이 끝난 후에는 별도의 정리 작업을 수행해야 한다.

 

묵시적 커서

- 오라클이 자동적으로 선언해주는 SQL커서로서 사용자 입장에서는 생성 유무를 알 수 없다. 기본적으로 PL/SQL블록 내에서의 select문, DML문이 실행될 때마다 묵시적 커서가 선언된다. 단, 묵시적 커서의 경우 세션 내에 단 한 개만이 선언되어 사용되다가 문장이 종료됨과 동시에 정리된다.

- 묵시적 커서에 저장되는 데이터는 1행만 가능하다. 즉 여러 행을 저장해야 작업해야 할 경우에는 명시적 커서를 사용해야 한다.

- 사용자는 커서 내의 실행 결과를 커서 속성을 통해 확인할 수 있다. 묵시적 커서 속성은 총 4가지가 있는데, 묵시적 커서라는 뜻의 SQL%을 접두어(Prefix)로 사용하게 된다.

+ SQL%ROWCOUNT

해당 커서에게 실행한 총 행의 개수(가장 마지막 행이 몇번째행인지 카운트한다.)를 반환한다.

+ SQL%FOUND

해당 커서 안에 아직 수행해야 할 데이터가 있을 경우 TRUE값을 반환하고 없을 경우 FALSE값을 반환한다.

+ SQL%NOTFOUND

해당 커서 안에 수행해야 할 데이터가 없을 경우 TRUE값을 반환하고 있을 경우 FALSE값을 반환한다.

[참고] 강의 필기 1007의 cursor예제 중 마지막과 마지막 전 예제때 꼭 참고!

 

+ SQL%ISOPEN

현재 묵시적 커서가 메모리에 OPEN되어 있을 경우에는 TRUE 값을, 그렇지 않을 경우에는 FALSE값을 가진다.

------------------------------------

-- 묵시적 커서 예제

------------------------------------

SET SERVEROUTPUT ON

DECLARE

BEGIN

DELETE FROM CP_EMP2 WHERE SAL > 1500;

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '행이 삭제되었습니다.');

END;

/  

 

-- 실행결과

-- 5행이 삭제되었습니다.

------------------------------------

 

명시적 커서

- 사용자가 선언하여 생성 후 사용하는 SQL커서로, 주로 여러 개의 행을 처리하고자 할 경우 사용한다.

- 묵시적 커서와 마찬가지로 커서 속성 변수로 커서의 내용을 파악하고 보다 쉽게 작업할 수 있게 해준다.

- 명시적 커서는 여러 개가 선언될 수 있으므로, 커서 속성 변수는 '커서명%'을 커서 속성 변수의 접두어로 붙여서 사용한다.

- 명시적 커서 속성

+ 커서명%ROWCOUNT

FETCH문에 읽혀진 데이터의 총 행 수를 가지는 속성이다. 가장 마지막에 처리된 행이 몇 번째인지를 반환한다.

+ 커서명%FOUND

FETCH문이 수행되었을 경우, 읽혀진 행이 있을 경우에는 TRUE값을, 그렇지 않은 경우에는 FALSE값을 가진다.

+ 커서명%NOTFOUND

FETCH문이 수행되었을 경우, 읽혀진 행이 없은 경우에는 TRUE값을, 그렇지 않은 경우에는 FALSE값을 가진다.

+ 커서명%ISOPEN

명시적 커서가 메모리에 확보되어 있을 경우에는 TRUE값을, 그렇지 않을 경우에는 FALSE값을 가진다.

- 명시적 커서 처리 단계

+ 명시적 커서 선언(Declaration)

CURSOR 커서명

IS

커서에 담고 싶은 내용을 가져오는 서브쿼리

- PL/SQL의 선언부(declare)에 다른 변수와 마찬가지로 선언되어야 한다. 이 단계에서는 해당 커서를 사용하겠다라고 알려주는 역할만 하며 실제 메모리 할당이 이루어지는 것은 아니다.

- 기본적으로 명시적 커서는 여러 건을 검색하는 SELECT문을 처리하지 위한 것이므로 처리하고자 하는 데이터를 검색하는 SELECT문을 이 부분에 기술한다.

 

+ 명시적 커서 오픈(Open)

OPEN 커서 이름;

- 커서를 OPEN한다는 뜻은 커서 선언 시 기술했던 서브쿼리를 수행해서 데이터를 커서로 가져온다는 뜻이다. 이렇게 하면 메모리에 실제 커서가 사용할 메모리 공간이 할당된다. 즉 커서를 선언할 때는 사용될 메모리 공간의 양을 모르기 때문에 메모리를 할당할 수 없지만, 커서가 OPEN되면 실제 메모리가 할당되는 것이다. 이때 명시적 커서 영역에 자리잡은 데이터의 첫 번째 행에 커서 포인터가 설정되고 바로 이 포인터 위치의 데이터 행을 다음 단계인 FETCH에서 읽게된다.

 

 

+ 커서에서 데이터 추출(Fetch)

FETCH 커서명 INTO 변수들;

- 명시적 커서의 데이터들(Active Set)로부터 데이터를 한 건씩 읽어 변수로 할당하기 위해 사용한다.

- 읽게 되는 데이터 행은 포인터에 의해서 지정되며 한 행이 Fetch되면 자동적으로 포인터는 다음행으로 이동하게 된다. 일반적으로 여런 데이터들을 읽어 처리하기 위해서 반복문과 함께 사용한다.

- 변수에 값을 할당하기 위해 INTO절을 사용한다. 이때 선언부에 선언된 변수만 올 수 있으며, 단순 변수를 사용한다면 커서에서 정의된 SELECT 리스트의 개수만큼 선언하고 SELECT 리스트의 위치대로 FETCH의 INTO절에 차례대로 적어야한다. 복합 변수를 사용한다면, 커서 레코드 변수(커서명%ROWTYPE)를 선언하여 사용한다.

[참고] 그림에서 INTO TMP는 변수에 할당되는 것을 알기 쉽게 하기 위해 나타낸것뿐이다.

+ 커서 사용 종료(Close)

CLOSE 커서명;

- 명시적 커서의 정리(Clean-up)작업을 하는 명령으로, 작업이 끝난 메모리 공간을 반환하고 정리한다는 뜻이며 닫기를 하지 않으면 메모리 낭비도 많이 되고, 만약 동일한 커서를 다른 PL/SQL BLOCK에서 동일한 이름의 커서를 사용할 경우 에러가 생기게 된다.