[Oracle] PL/SQL Cursor

📌cursor

---

커서(cursor)는 SQL 결과 집합을 참조하고 조작하는 데 사용되는 데이터베이스 개체이다.

커서를 사용하면 결과 집합의 행을 하나씩 탐색하여 각 행의 데이터에 접근할 수 있다.

 

select into

select into는 결과셋의 레코드가 1개일 때만 사용할 수 있다.

 

cursor

cursor는 결과셋의 레코드가 다중 레코드일 때 사용한다.

커서를 통해 가져온 데이터를 처리하기 위해 루프를 사용한다. 루프는 커서에서 데이터를 한 행씩 가져와서 처리할 수 있도록 하며, exit when문을 사용하여 루프를 종료한다.

 

cursor의 선언

CURSOR vcursor
IS
SELECT name FROM tblInsa;

cursor를 선언할 때에는 앞에 식별자가 오고, 뒤에 자료형이 온다.
이처럼 cursor는 뒤의 select의 참조 조작을 한다.

 

위 소스코드에서 커서는 60명의 이름을 참조하고 있는 상태이다.

 

cursor의 사용

cursor 형식

declare
	변수 선언;
	커서 선언;
begin
	커서 열기;
		loop
			데이터 접근(루프 1회전 시 레코드 1개씩 접근) --커서 사용		
		end loop;
	커서 닫기;
end;

위의 형식으로 커서를 선언하여 사용하는 게 일반적이다.

실제로 커서를 선언하고 사용해보도록 하자.

 

DECLARE
	CURSOR vcursor
	IS
	SELECT name FROM tblInsa;
	vname tblInsa.name%TYPE;
BEGIN
	
	OPEN vcursor; --커서 열기 > SELECT 실행 > 결과셋을 커서가 참조
	
		FETCH vcursor INTO vname; --SELECT INTO 역할
		dbms_output.put_line(vname);
		
		FETCH vcursor INTO vname; --SELECT INTO 역할
		dbms_output.put_line(vname);
	
	CLOSE vcursor;
	
END;

fetch 문은 데이터를 커서로 가져오는 역할을 한다.

이처럼 fetch 문을 사용하여 커서가 가리키는 결과 집합의 다음 행을 가져올 수 있다.

 

마지막 데이터까지 출력하기

DECLARE
	CURSOR vcursor
	IS
	SELECT name FROM tblInsa;
	vname tblInsa.name%TYPE;
BEGIN
	
	OPEN vcursor; --커서 열기 > SELECT 실행 > 결과셋을 커서가 참조
	
		/*
		FOR i IN 1..60 LOOP
			FETCH vcursor INTO vname;
			dbms_output.put_line(vname);
		END LOOP;
		*/
	
		LOOP
			FETCH vcursor INTO vname;
			EXIT WHEN vcursor%notfound; --bool
			
			dbms_output.put_line(vname);
		END LOOP;
		
	CLOSE vcursor;
	
END;

for loop문을 사용해서 루프를 돌릴 수도 있지만, 이는 내가 60바퀴만 돌린다는 걸 정할 때에만 가능하다.

끝까지 돌리려는데 몇 번째 데이터가 마지막인지 모를 때에는 다른 방법을 사용해야 한다.

exit when vcursor%notfound 으로 exit 조건을 notfound로 작성해주면 데이터가 cursor에 있을 때 true, 없을 때 false를 반환하므로 마지막 데이터까지 출력할 수 있다.

 

'기획부' 직원 이름, 직위, 급여 출력

DECLARE
	CURSOR vcursor
		IS SELECT name, jikwi, basicpay FROM tblInsa WHERE buseo = '기획부';

	vname tblInsa.name%TYPE;
	vjikwi tblInsa.jikwi%TYPE;
	vbasicpay tblInsa.basicpay%TYPE;

BEGIN
	
	OPEN vcursor;

	LOOP
		FETCH vcursor INTO vname, vjikwi, vbasicpay;
		EXIT WHEN vcursor%notfound;
	
		--기획부 직원 한 사람씩 접근
		dbms_output.put_line(vname || ',' || vjikwi || ',' || vbasicpay);
		
	END LOOP;
	
	CLOSE vcursor;	

END;

cursor는 선택하여 값을 가져올 수 없고, 무조건 첫 번째 행부터 가져온다.

 

모든 직원에게 보너스 지급

• a. 과장/부장(1.5) 지급
• b. 사원/대리(2) 지급

SELECT * FROM tblBonus;
DELETE FROM tblBonus;

DECLARE
	CURSOR vcursor
		IS SELECT name, num, jikwi, basicpay FROM tblInsa;
	vname tblInsa.name%TYPE;
	vnum tblInsa.num%TYPE;
	vjikwi tblInsa.jikwi%TYPE;
	vbasicpay tblInsa.basicpay%TYPE;
	vseq NUMBER := 1;
	vbonus NUMBER;
BEGIN
	OPEN vcursor;

	dbms_output.put_line('이름' || ',' || '직위' || ',' || '급여' || ',' || '보너스');

	LOOP
		FETCH vcursor INTO vname, vnum, vjikwi, vbasicpay;
		EXIT WHEN vcursor%notfound;
	
		IF vjikwi = '과장' OR vjikwi = '부장' THEN 
			vbonus := vbasicpay * 1.5;
		ELSIF vjikwi IN ('사원', '대리') THEN
			vbonus := vbasicpay * 2;		
		END IF;
	
		dbms_output.put_line(vname || ',' || vjikwi || ',' || vbasicpay || ',' || vbonus);
	
		INSERT INTO tblBonus VALUES (vseq, vnum, vbonus);
		
		vseq := vseq + 1;
		
	END LOOP;
	
	CLOSE vcursor;	
END;

직위에 따라 보너스를 계산하고 보너스 테이블에 데이터를 삽입할 때 커서를 사용하면 데이터를 한 개씩 가져오므로 같편하게 작업할 수  있다.

 

📌커서 탐색

---

1. 커서 + loop (정석)
2. 커서 + for loop (간결)

커서를 탐색하는 방법에는 loop를 사용하는 정석적인 방법과 for loop를 사용하는 간결한 방법이 있다.

한 방법만을 알고 있는 것보다는 둘 다 사용할 수 있도록 익혀야 한다.

 

커서 + loop (정석)

형식 (틀)

DECLARE
	CURSOR 커서
	IS SELECT * FROM 테이블;
	vrow 테이블%rowtype;
BEGIN
	OPEN vcursor;
	LOOP
		FETCH INTO;
		EXIT WHEN;
	END LOOP;
	CLOSE vcursor;	
END;

 

커서 + loop

--커서 + loop (정석)
DECLARE
	CURSOR vcursor
	IS SELECT * FROM tblInsa;
	vrow tblInsa%rowtype;
BEGIN
	OPEN vcursor;
	LOOP
		FETCH vcursor INTO vrow;
		EXIT WHEN vcursor%notfound;
	
		dbms_output.put_line(vrow.name);
	END LOOP;
	CLOSE vcursor;	
END;

정석대로 코드를 탐색했을 때의 모습이다.

작성해야 하는 부분이 많지만, 그만큼 사용자의 요구사항에 맞게 바꿀 수 있다.

 

커서 + for loop (간결)

지워지는 작업

DECLARE
	CURSOR vcursor
	IS SELECT * FROM tblInsa;
	--vrow tblInsa%rowtype;
BEGIN
	--OPEN vcursor;
	--LOOP
	FOR vrow IN vcursor LOOP --LOOP + FETCH INTO + vrow + EXIT when
		--FETCH vcursor INTO vrow;
		--EXIT WHEN vcursor%notfound;
	
		dbms_output.put_line(vrow.name);
	END LOOP
	--CLOSE vcursor;	
END;

 

커서 + for loop

DECLARE
	CURSOR vcursor
	IS SELECT * FROM tblInsa;
BEGIN
	FOR vrow IN vcursor LOOP
		dbms_output.put_line(vrow.name);
	END LOOP
END;

 

간결하게 작성하면 위와 같은 구문들을 지울 수 있다.