[DB] Cursor For Loop문 활용하기

* Cursor For Loop문 활용하기

for 변수명 in 커서명 loop

(명시적 커서의 open, fetch가 자동적으로 수행된다.)

처리문;

end loop; (루프문을 빠져나갈 때 자동적으로 커서가 close된다.)

- PL/SQL에서는 for문과 커서를 결합하여 cursor for반복 기능을 제공한다. 이 방법을 사용할 경우 사용자는 커서의 선언만 하고 커서변수의 선언, 열기, 인출, 닫기 작업을 오라클이 자동으로 수행한다.(이때 열기,인출,닫기를 개발자가 명시하면 에러가 난다.)

- for 다음에 선언하는 변수는 자동으로 만들어지며, 커서로부터 fetch된 하나의 레코드를 저장하기 위한 커서 레코드 변수이다. 복합 변수 레코드처럼 사용되며 레코드를 구성하는 필드에 접근할 때는 '레코드명.변수명'으로 접근하여야 한다.

- for문의 반복횟수는 명시적 커서내의 전체 행수만큼 수행된다.

- 커서의 생명주기는 볼 수 없지만 커서로 가져온 데이터를 저장할 변수를 따로 생성하지 않아도 되는 등의 편의를 더해 준다.

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

-- for를 사용한 cursor

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

-- 문) dept테이블을 전체 조회하라

 

set serveroutput on

 

declare

cursor cur_dept is select * from dept;

begin

-- *for문을 통해 커서가 open되므로 따로 open해주지 않아도 된다.

/*

if cur_dept%isopen then

close cur_dept;

end if;

open cur_dept;

 

주석을 제거하고 실행하면 위 open명령어를 통해 커서가 열려진 상태인데

for문에서 또 커서를 open할 때 에러가 난다.

*/                      

for data in cur_dept loop

dbms_output.put_line(data.deptno || ' ' || data.dname || ' ' || data.loc);

-- 변수명.컬럼명으로 접근

end loop;  

-- for문을 사용하면 일반 loop문(exit-fetch-출력의 순)에서 나오던 마지막행 중복과 같은 현상이 나타나지 않는다

end;

/

 

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

 

-- 문) cp_emp5테이블에서 부서별 보너스를 구하여 모든 사원의 보너스를 변경하라

-- 단, 보너스는 10 - 연봉의 10%, 20 - 20%, 30 - 15% 그외는 5%로 책정하라

 

declare

-- 커서 선언

-- 모든 사원의 보너스를 변경해야하므로 여러행을 조회하여 수정해야하기 때문에 명시적 커서 선언

cursor cur_emp is select empno, sal, deptno from cp_emp5;

-- 보너스를 저장할 변수

o_comm number;

-- 쿼리 수행 수를 저장할 변수

cnt number := 0;

begin

for emp in cur_emp loop

 

if emp.deptno = 10 then

o_comm := emp.sal*0.1;

elsif emp.deptno = 20 then

o_comm := emp.sal*0.2;

elsif emp.deptno = 30 then

o_comm := emp.sal*0.15;

else

o_comm := emp.sal*0.05;

end if;

 

dbms_output.put_line(emp.empno || '번 사원의 연봉 ' || emp.sal || ', 보너스 ' || o_comm);

 

update cp_emp5

set comm = o_comm

where empno = emp.empno;

-- 주의! 컬럼명과 변수명이 명확히 구분되게 지어주어야한다.

 

if sql%rowcount != 0 then

-- update쿼리문의 암시적 커서. 현재 커서가 꺼내온 행의 수

commit;

end if;

/* └예제를 위해 if문을 사용했지만 커서를 이용한 패치 다음 바로 수정작업을 수행하는데 

그 짧은 시간동안 침투당할 확률이 매우 낮다. 따라서 없어도 되는 문장*/ 

 

cnt := cnt+1;

-- └총 몇 행이 수행되었는지 카운트 하는 변수

end loop;

dbms_output.put_line(cnt || '건의 정보가 변경되었습니다.');

end;

/

 

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

 

 

* 함수(function)

create or replace function 함수명(paraneter ,,,)

return 데이터형

is

변수, 커서

begin

코드

retuen

end

- 자주 사용될 코드를 미리 정의하고 필요한 곳에서 호출하여 사용할 때

- 컴파일(@파일명) 후, 쿼리문에서 사용된다.(간접실행)

- user_pocedures 테이블에서 확인할 수 있다.

- 가장 마지막줄에 return을 명시해야한다.

-  컴파일 후 에러가 발생하면 show error로 error를 확인할 수 있다.

 

* procedure

- 자주 사용될 코드를 미리 정의하여 필요한 (언어)에서 사용하기 위해서

- user_procedures에서 확인할 수 있다.

- return이 없다.(Out parameter가 존재하여 값을 내보낼 수 있다.)

- 직접실행가능 [sqlplus에서 exec|execute 프로시저명(값,,,)]

- 컴파일 후에 사용

- 쿼리를 주로 저장하여 사용

- 작성법)

create or replace procedure 프로시저명

(매개변수,,,)

is

bgin

end;

/

 

매개변수(프로시저 밖의 값을 프로시저 안으로 가져오기 위한 변수)

값을 받기 위한 변수(in parameter)

변수명 데이터형(기본형, 생략가능)

변수명 in 데이터형

값을 내보내기 위한 변수(out parameter)

- 외부변수에 값 저장

변수명 out 데이터형

 

* 외부변수

- sqlplus에서 만드는 변수

- 작성법

var|variable 변수명 데이터형(크기)

- sqlplus가 종료되면 사라진다.

- 같은 이름의 변수를 만들면 덮어 쓴다

- 사용) pl/sql 내에서 :변수명

- procedure에서 호출시 사용

exec 프로시저명(값,,, :변수명) is begin

- 출력) print 변수명 변수명 . . . 둥;