< 커서(Cursor) >

* SQL의 작업 영역

* 모든 SQL 문은 연관된 각각의 커서를 쇼유함

* 커서의 종류

- 암시적 커서: 모든 DML과 PL/SQL select 문에 대해 선언됨

- 명시적 커서 : 프로그래머에 의해 선언되며 이름이 있는 커서

 

 <암시적 커서>

* 암시적인 커서 : 오라클이나 PL/SQL 실행 메커니즘에 의해 처리되는 SQL 문장이 처리되는

곳에 대한 익명의 주소값

* 암시적 커서의 속성

- SQL%ROWCOUNT : 해당 SQL 문에 영향을 받는 행의 수

- SQL%FOUND : 해당 SQL 영향을 받는 행의 수가 1개 이상일 경우 TRUE

- SQL%NOTFOUND : 해당 SQL 문에 영향을 받는 행의 수가 없을 경우 TRUE

- SQL%ISOPEN : 항상 FALSE, 암시적 커서가 열려 있는지의 여부 검색

* 암시적 커서는 SQL 문이 실행되는 순간 자동으로 열림과 닫힘 실행

* 저장 프로시저의 장점
- sql 명령어의 트래픽 감소
- 보안성 향상
- 실행 속도 향상
    parsing(명령어 해석)
    실행계획 수립
    run
    
* 저장 프로시저의 단점    
- 개발이 어렵다
- 디버깅이 어렵다
- 유지보수가 어렵다

-- select 컬럼 into 변수 from 테이블

create or replace procedure implicit_cursor
(p_empno emp.empno%type)
is
   v_sal emp.sal%type;
   v_update_row number;
begin

  select sal into v_sal from emp where empno=p_empno;
  if sql%found then -- 레코드가 존재하면
   dbms_output.put_line('급여:' || v_sal);
 end if;
 update emp set sal =sal*1.1 where empno=p_empno;
 v_update_row := sql%rowcount;  -- SQL 이 적용된 레코드 수
 dbms_output.put_line('급여 인상 사원수:' || v_update_row);
end;
/

execute implicit_cursor(7369);

 

>>>출력
급여:220
급여 인상 사원수:1

 

 

< 커서의 사용 방법 >

* 커서 열기(OPEN)
- OPEN cursor_name;

 

* 커서 패치(FETCH)

 -

FETCH cursor_name INTO variable1, variable2;

 - 현재 레코드를 OUTPUT 변수에 저장 (한 라인씩 데이터를 읽음)

 - 커서의 SELECT 문의 컬럼수와 OUTPUT 변수의 수와 데이터 타입이 동일해야 함

* 커서 닫기(CLOSE)

 - CLOSE cursor_name;
 
 - 사용을 마친 커서는 반드시 닫아 주어야 함

 - 커서을 닫은 상태에서 FETCH 를 할 수 없음

-- 실행  콜론은 변수라는 의미 :변수이름

create or replace procedure cursor_test(v_deptno number)
is
-- cursor 커서이름 is sql문장
  cursor dept_avg is
    select dname, count(empno) cnt, avg(sal) salary
    from emp e, dept d
    where e.deptno=d.deptno and e.deptno=v_deptno
    group by dname;
   v_dname dept.dname%type;
   emp_cnt number;
   sal_avg number;
begin
  open dept_avg;  --open 커서이름 (커서 오픈)
  -- 커서가 가리키는 1개의 레코드를 읽어와서 변수에 저장
  -- 읽어들인 후 포인터가 다음 레코드로 이동함

  fetch dept_avg into v_dname, emp_cnt, sal_avg;
  dbms_output.put_line('부서:'|| v_dname);
  dbms_output.put_line('사원수:'||emp_cnt);
  dbms_output.put_line('평균:'||sal_avg);
  close dept_avg; --커서 닫기
end;
/

execute cursor_test(10);

>>>출력

부서:경리팀
사원수:3
평균:491.666666666666666666666666666666666667

 

create or replace procedure forcursor_test
is
-- 명시적 커서 선언
 cursor dept_sum is
  select dname, count(empno) cnt, sum(sal) salary
  from emp e, dept d
  where e.deptno=d.deptno
  group by dname;
begin
-- open , fetch, close 가 생략됨
-- open dept_sum 생략
 for emp_list in dept_sum loop -- fetch 생략
   dbms_output.put_line('부서:'|| emp_list.dname);
   dbms_output.put_line('사원수:'|| emp_list.cnt);
   dbms_output.put_line('급여합계:'|| emp_list.salary);
   dbms_output.put_line('---------------------------');
 end loop;
-- close dept_sum ; 생략 
end;
/
execute forcursor_test;

 

>>>출력

부서:총무팀
사원수:6
급여합계:2040
---------------------------
부서:경리팀
사원수:3
급여합계:1475
---------------------------
부서:연구팀
사원수:5
급여합계:2049
---------------------------

<트리거>

create[OR REPLACE] trigger tigger_name
before|after
 tigger_event ON table_name
[for each row]
[when (condition) ]
PL/SQL block

* Trigger(방아쇠) : 데이터베이스에서의 연쇄적인 동작을 정의함

* INSERT, UPDATE, DELETE 문이 실행될 때 묵시적으로 수행되는 PROCEDURE

* Table 에만 정의될 수 있음(View에는 사용할 수 없음)

* Before Trigger: Insert, update, delete 문이 실행되기 전에 실행

* After Trigger : insert, update, delete 문이 실행된 후 실행

* For each row : 행 트리거

- 컬럼의 각각의 행의 데이터 행 변화가 생길 때마다 실행됨

- 문장 트리거 : 1회만 실행됨

-- create or replace trigger 트리거 이름
-- after, before
create or replace trigger sum_trigger
after -- emp 테이블에 isert, update 가 발생한 이후에 자동 실행
 insert or update on emp
declare
 avg_sal number;
begin
 select avg(sal) into avg_sal from emp;
 dbms_output.put_line('급여평균:'|| avg_sal);
end;
/

insert into emp (empno, ename, hiredate, sal) values (3011, '최철호', sysdate, 500);

 

>>>출력
급여평균:410.25

-- dept 테이블에 insert => dept_bk 테이블에도 복사

create table dept_bk as select * from dept;
create or replace trigger dept_copy
after
 insert on dept
 for each row -- 행 트리거 (각각의 행의 데이터가 바뀌 때 실행)
begin
 insert into dept_bk (deptno, dname) values
 (:new.deptno, :new.dname); -- :new 새로운 레코드
end;
/

insert into dept(deptno, dname) values ('60', '기획팀');
select * from dept;
select * from dept_bk;

>>>출력
10    경리팀    부산
20    연구팀    대전
30    총무팀    대구
40    전산팀    인천
50    기획팀    null
60    기획팀    null

-- 백업 및 복원 CMD 에서 실행

< 백업 >

exp userid=계정명/패스워드 file=덤프파일명

exp userid=java/java1234 file=d:test.bak

 

 

< 복원 > - 계정명이 같아야 한다

imp 계정명/패스워드 file=덤프파일명

-- 1. 첫번째 방법

imp java/java1234 file=d:/test.bak

 

-- 2. 두번째 방법 : 전체복원

imp java/java1234 file=d:/test.bak full=y

 

-- 3. 세번째 방법 : 원하는 테이블만 복원

imp java/java1234 file=d:/test.bak tables=테이블이름