PL /SQL) CURSOR(커서)

<cursor>

--[ 커서 ]

--지금까지 집합 단위의 데이터 처리 (전체 row를 대상으로)

--[CURSOR]

--1. [행단위]로 데이터를 처리하는 방법을 제공

--2. 여러건의 데이터를 처리하는 처리하는 방법을 제공 (한 건이상의 row가지고 놀기)

-- <간단한 예시>-------------------------------------

-- 사원급여테이블(건설회사)

-- 정규직 , 일용일 ,시간직

--사번 , 이름 , 직종명 , 월급 , 시간 , 시간급 , 식대

-- 10 홍길동 정규직 120 null null null

-- 11 김유신 시간직 null 10 100 null

-- 12 이순신 일용일 null null 120 10

--정규직

--월급

--일용직

--시간 , 시간급

--시간직

--시간급 , 식대

--한 행식씩 접근해서 직종을 기준으로 계산방법

--if 정규직 > 월급 (총급여)

--elsif 시간직 > 시간 * 시간급 (총급여)

--elsif 일용직 > 시간급 + 식대 (총급여)

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

--SQL CURSOR 의 속성을 사용하여 SQL 문장의 결과를 테스트할 수 있다.

--[종 류 설 명]

--SQL%ROWCOUNT 가장 최근의 SQL 문장에 의해 영향을 받은 행의 수

--SQL%FOUND 가장 최근의 SQL 문장이 하나 또는 그 이상의 행에 영향을 미친다면 TRUE 로 평가

--SQL%NOTFOUND 가장 최근의 SQL 문장이 어떤 행에도 영향을 미치지 않았다면 TRUE 로 평가

--SQL%ISOPEN PL/SQL 이 실행된 후에 즉시 암시적 커서를 닫기 때문에 항상 FALSE 로 평가

/* 기본 틀

DECLARE

CURSOR 커서이름 IS 문자(커서가 실행할 쿼리)

BEGIN

OPEN 커서이름 (커서가 가지고 있는 쿼리를 실행)

FETCH 커서이름 INTO 변수명들...

--커서로 부터 데이터를 읽어서 원하는 변수에 저장

CLOSE 커서이름 (커서닫기)

END

*/

DECLARE

vempno emp.empno%TYPE;

vename emp.ename%TYPE;

vsal emp.sal%TYPE;

CURSOR c1 IS select empno,ename,sal from emp where deptno=30;

BEGIN

OPEN c1; -- 커서가 가지고 있는 문장 실행

-- FOR~ IN~ 이 아니면 모두 OPEN 해줘야 한다

-- FOR~ IN~ 은 내부적으로 끝에 OPEN이 포함되어 있다

LOOP

--Memory(deptno=30인 직원들 정보)

/*

7499 ALLEN 1600

7521 WARD 1250

7654 MARTIN 1250

7698 BLAKE 2850

7844 TURNER 1500

7900 JAMES 950

*/

FETCH c1 INTO vempno , vename, vsal;

EXIT WHEN c1%NOTFOUND; --더이상 row 가 없으면 탈출

DBMS_OUTPUT.PUT_LINE(vempno || '-' || vename || '-'|| vsal);

END LOOP;

CLOSE c1;

END;

-- 위 표현을 좀 더 간단하게(FOR ~ IN ~을 활용)

DECLARE

CURSOR emp_curr IS select empno ,ename from emp;

BEGIN

FOR emp_record IN emp_curr -- row 단위로 emp_record변수 할당

LOOP

EXIT WHEN emp_curr%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(emp_record.empno || '-' || emp_record.ename);

END LOOP;

CLOSE emp_curr;

END;

-- java (for(emp e : emplist){ } 과 유사

DECLARE

vemp emp%ROWTYPE; --Type 정의

CURSOR emp_curr IS select empno ,ename from emp;

BEGIN

FOR vemp IN emp_curr --row 단위로 emp_record변수 할당

LOOP

EXIT WHEN emp_curr%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(vemp.empno || '-' || vemp.ename);

END LOOP;

CLOSE emp_curr;

END;

DECLARE

v_sal_total NUMBER(10,2) := 0;

CURSOR emp_cursor

IS SELECT empno,ename,sal FROM emp

WHERE deptno = 20 AND job = 'CLERK'

ORDER BY empno;

BEGIN

DBMS_OUTPUT.PUT_LINE('사번 이 름 급 여');

DBMS_OUTPUT.PUT_LINE('---- ---------- ----------------');

FOR emp_record IN emp_cursor -- 커서의 데이터가 emp_record에 들어간다

LOOP

v_sal_total := v_sal_total + emp_record.sal;

DBMS_OUTPUT.PUT_LINE(RPAD(emp_record.empno,6) || RPAD(emp_record.ename,12) ||

LPAD(TO_CHAR(emp_record.sal,'$99,999,990.00'),16));

END LOOP;

DBMS_OUTPUT.PUT_LINE('----------------------------------');

DBMS_OUTPUT.PUT_LINE(RPAD(TO_CHAR(20),2) || '번 부서의 합 ' ||

LPAD(TO_CHAR(v_sal_total,'$99,999,990.00'),16));

END;

-- <예제문제>

--emp 테이블에서 사원들의 사번 , 이름 , 급여를 가지고

--와서 cursor_table insert 를 하는데 totalsum 은 급여 + comm 통해서

--부서번호가 10인 사원은 totalsum에 급여 정보만 넣으세요

-- 사번 , 이름 , 직종명 , 월급 , 시간 , 시간급 , 식대

-- 10 홍길동 정규직 120 null null null

-- 11 김유신 시간직 null 10 100 null

-- 12 이순신 일용일 null null 120 10

-- cf) 사실 이건 실습용 테이블이고 이건 잘못 설계된 테이블이다

create table cursor_table

as

select * from emp where 1=2;

select* from cursor_table;

alter table cursor_table

add totalsum number;

insert into CURSOR_TABLE(empno,ename,sal,totalsum)

select empno , ename , sal , sal+nvl(comm,0)

from emp where deptno=20;

select * from CURSOR_TABLE;

DECLARE

result number := 0;

CURSOR emp_curr IS select empno, ename, sal,deptno,comm from emp;

BEGIN

FOR vemp IN emp_curr --row 단위로 emp_record 변수에 할당

LOOP

EXIT WHEN emp_curr%NOTFOUND;

IF(vemp.deptno = 20) THEN

result := vemp.sal+nvl(vemp.comm,0);

insert into cursor_table(empno, ename, sal,deptno,comm,totalsum)

values (vemp.empno,vemp.ename, vemp.sal,vemp.deptno,vemp.comm,result);

ELSIF(vemp.deptno = 10) THEN

result := vemp.sal;

insert into cursor_table(empno, ename, sal,deptno,comm,totalsum)

values (vemp.empno,vemp.ename, vemp.sal,vemp.deptno,vemp.comm,result);

ELSE

DBMS_OUTPUT.PUT_LINE('ETC');

END IF;

END LOOP;

END;

rollback;

commit;

select * from cursor_table order by deptno;