반응형
/********************************************************************************************
-- Title : [10g] 커서 정의와 활용 예제
-- Reference : netme.kr
-- Key word : cursor 커서 isopen rowcount
********************************************************************************************/
-- 커서 정의
커서란 SQL Plus에서 사용자가 실행한 SQL문의 단위를 의미합니다.
오라클렝서 수행한 모든 쿼리문은 커서 단위로 처리합니다.
PL/SQL의 SQL문처럼 하나의 결과를 리턴하는 경우 커서 없이도 SQL문의 실행결과가
암시적으로 커서에 저장되므로 이를 암시적 커서라고 합니다.
SQL문을 수행한 후에 결과로 얻어지는 행이 여러 개일 경우에는 암시적인 커서에 정보를
저장할 수 없기에 에러가 발생합니다. 이럴 경우에는 반드시 명시적인 커서를 사용해야 합니다.
명시적인 커서는 PL/SQL의 레코드(RECORD)와 PL/SQL의 테이블(TABLE)을 결합한 것으로서
프로그램 언어의 구조체 배열과 유사합니다.
-- 커서 상태
%NOTFOUND 커서 영역의 자료가 모두 FETCH 됬는가를 알려줌
%FOUND 커서 영역에 FETCH가 되지 않은 자료가 있는가를 알려줌
%ISOPEN 커서가 OPEN된 상태인가를 알려줌
%ROWCOUNT FETCH된 RECORD가 몇 개 있는지 알려줌
-- Title : [10g] 커서 정의와 활용 예제
-- Reference : netme.kr
-- Key word : cursor 커서 isopen rowcount
********************************************************************************************/
-- 커서 정의
커서란 SQL Plus에서 사용자가 실행한 SQL문의 단위를 의미합니다.
오라클렝서 수행한 모든 쿼리문은 커서 단위로 처리합니다.
PL/SQL의 SQL문처럼 하나의 결과를 리턴하는 경우 커서 없이도 SQL문의 실행결과가
암시적으로 커서에 저장되므로 이를 암시적 커서라고 합니다.
SQL문을 수행한 후에 결과로 얻어지는 행이 여러 개일 경우에는 암시적인 커서에 정보를
저장할 수 없기에 에러가 발생합니다. 이럴 경우에는 반드시 명시적인 커서를 사용해야 합니다.
명시적인 커서는 PL/SQL의 레코드(RECORD)와 PL/SQL의 테이블(TABLE)을 결합한 것으로서
프로그램 언어의 구조체 배열과 유사합니다.
-- 커서 상태
%NOTFOUND 커서 영역의 자료가 모두 FETCH 됬는가를 알려줌
%FOUND 커서 영역에 FETCH가 되지 않은 자료가 있는가를 알려줌
%ISOPEN 커서가 OPEN된 상태인가를 알려줌
%ROWCOUNT FETCH된 RECORD가 몇 개 있는지 알려줌
-- 커서 활용
SET SERVEROUTPUT ON;
DECLARE
vempno NUMBER(4);
vename VARCHAR2(20);
vsal NUMBER(7, 2);
CURSOR C1
IS
select empno, ename, sal
from scott.emp
where deptno=20;
BEGIN
OPEN C1;
dbms_output.put_line('empno ename sal');
LOOP
FETCH C1 INTO vempno, vename, vsal;
EXIT WHEN C1%NOTFOUND;
dbms_output.put_line(to_char(vempno)||' '||vename||' '||to_char(vsal));
END LOOP;
ENd;
/
-- OPEN-FETCH-CLOSE없이 커서 처리
SET SERVEROUTPUT ON;
DECLARE
vemp emp%ROWTYPE;
CURSOR C1
IS
SELECT empno, ename, sal
FROM scott.emp
WHERE deptno=20;
BEGIN
dbms_output.put_line('empno ename sal');
FOR vemp IN C1 LOOP
EXIT WHEN C1%NOTFOUND;
dbms_output.put_line(to_char(vemp.empno)||' '||vemp.ename||' '||to_char(vemp.sal));
END LOOP;
END;
/
-- %ROW_COUNT 사용
SET SERVEROUTPUT ON
DECLARE
vemp emp%ROWTYPE;
CURSOR C1
IS
SELECT empno, ename, sal
FROM scott.emp
WHERE deptno=20;
BEGIN
dbms_output.put_line('empno ename sal record count');
FOR vemp IN C1 LOOP
EXIT WHEN C1%NOTFOUND;
dbms_output.put_line(to_char(vemp.empno)||' '||vemp.ename||' '||to_char(vemp.sal)||' '||C1%ROWCOUNT);
END LOOP;
END;
/
-- 총합 구하기
SET SERVEROUTPUT ON;
DECLARE
tot NUMBER := 0;
CURSOR emp_cursor
IS
SELECT ename, sal
FROM scott.emp;
BEGIN
dbms_output.put_line('name sal');
dbms_output.put_line('------------------------------------------');
FOR cur_var IN emp_cursor LOOP
tot := tot + cur_var.sal;
dbms_output.put_line(cur_var.ename || '-' || cur_var.sal);
END LOOP;
dbms_output.put_line('------------------------------------------');
dbms_output.put_line('TOTAL '||tot);
END;
/
-- 사원별 급여 현황
SET SERVEROUTPUT ON;
DECLARE
CURSOR emp_cursor
IS
SELECT ename, sal
FROM scott.emp
ORDER BY sal DESC;
star VARCHAR2(100);
cnt NUMBER := 0;
BEGIN
dbms_output.put_line(' sal of emp');
dbms_output.put_line('------------------------------------------');
FOR cur_var IN emp_cursor
LOOP
star := NULL;
cnt := round(cur_var.sal/100, 0);
FOR i IN 1..cnt
LOOP
star := star||'*';
END LOOP;
dbms_output.put_line(cur_var.ename);
dbms_output.put_line('- '||star||'('||cur_var.sal||')');
END LOOP;
END;
/
-- 커서 매개 변수 활용
create or replace procedure scott.up_emp_process
is
v_empno emp.empno%type;
v_ename emp.ename%type;
v_sal number(7,2);
cursor emp_cursor(v_deptno number) is
select empno, ename, sal
from scott.emp where deptno=v_deptno;
begin
dbms_output.put_line('------------------------------');
open emp_cursor(10); /* 10번부서 */
loop
fetch emp_cursor into v_empno, v_ename, v_sal;
exit when emp_cursor%rowcount > 5 or emp_cursor%notfound;
dbms_output.put_line(v_empno||' '||v_ename||' '||v_sal);
end loop;
close emp_cursor;
open emp_cursor(20); /* 20번부서 */
loop
fetch emp_cursor into v_empno, v_ename, v_sal;
exit when emp_cursor%rowcount > 5 or emp_cursor%notfound;
dbms_output.put_line(v_empno||' '||v_ename||' '||v_sal);
end loop;
close emp_cursor;
end up_emp_process;
/
loop
fetch emp_cursor into v_empno, v_ename, v_sal;
exit when emp_cursor%rowcount > 5 or emp_cursor%notfound;
dbms_output.put_line(v_empno||' '||v_ename||' '||v_sal);
end loop;
close emp_cursor;
end up_emp_process;
/
반응형