01/12 국비학원 34회차 오라클 수업(PL/SQL)

PL/SQL은

상용 관계형 데이터베이스 시스템인 오라클 DBMS에서 SQL 언어를 확장하기 위해 사용하는

컴퓨터 프로그래밍 언어 중 하나이다.

주로 자료 내부에서 SQL 명령문만으로 처리하기에는 복잡한 자료의 저장이나 프로시저와 트리거 등을

작성하는 데 쓰인다.

SQL을 확장한 절차적 언어(Procedural Language)이다. 

 

 PL/SQL 프로그램의 종류는 크게 Procedure, Function, Trigger 로 나뉘어 진다.

 모듈식 프로그램 개발 가능 : 논리적인 작업 을 진행하는 여러 명령어들을 하나의 블록을 만들 수 있다.

 

오라클에서 지원하는 프로그래밍 언어의 특성을 수용하여 SQL에서는 사용할수없는 절차적 프로그래밍 기능을 가지고 있어 SQL의 단점을 보완하였다.

 

커서를 사용하여 여러 행을 검색 및 처리

https://goddaehee.tistory.com/99

[Oracle] PL/SQL 기초 (정의, 특징, 사용방법, 변수선언 방법)

 

 

 

PL/SQL표준 프로시저, 함수, 트리거

PL/SQL문법

 

SELECT INTO문은 PL/SQL문에서만 지원함

단일행만 사용이 가능하다.***********주의***********

다중행이 나오는 경우도 존재한다.-LOOP문이 필요하다. 반복문, CURSOR가 필요하다

 

회원이 2500만명이다... 이럴때 반복문 무조건 필요하다.

조건문-10000원 55000원 75000원 가격이 다 다르다...

반기문-flow chart-순서도-중요- 알고리즘- 비즈니스

-개발자-함수 프로시저(select, insert, update, delete 함께 사용가능)

-함수 단위로 나눠야한다. 그리고 함수들을 프로시저안에서 재사용해야한다.

 

회원탈퇴의 경우

update 회원_copy

     set 회원상태 = 'N'

where 회원아이디='tomato'

 

insert into 회원_copy values(?,?,.....)

 

delete from 회원

where 회원아이디 = 'toamto'

 

트리거(Trigger)

한 테이블에 날짜로 선언된 컬럼이 있다고 가정했을 때 

이 컬럼에 데이터는 항상 토요일과 일요일만 입력되어야 한다고 했을 때
원천적으로 막을 수 있는 방법이 있다.

트리거를 이용해서 UPDATE, INSERT시에 해당 컬럼의 데이터를
checking하면 된다. 또 Insert, Delete, Update시에 항상 특정 테이블에
작업실행에 대한 history가 필요할 경우에도  Trigger를 사용하면, 별도의
작업없이도 Trigger에서 이를 실행 할 수 있다.

자동으로 해준다-->꿀이다

 

[Syntax]
Create Trigger 트리거명
  Before (or After)
  UPDATE OR DELETE OR INSERT ON 테이블명
  [FOR EACH ROW]
DECLARE
  변수선언부
BEGIN
  프로그램 코딩부
END;

그리고 다음은 Update 작업시에 특정컬럼이 Update 되는 경우에만 Trigger가
실행되는 타입이다.

Create Trigger 트리거명
  BEFORE (or AFTER)
  UPDATE [OF 컬럼1, 컬럼2...] ON 테이블명
  [FOR EACH ROW]
DECLARE
  변수 선언부;
BEGIN
  프로그램 코딩부;
END;
표준 형식
Create or Replace Trigger 트리거명
  Before
  Update or Delete or Insert on 테이블명
  [for each row]
Declare
  변수 선언부;
Begin
  IF UPDATETING THEN
     Update작업시 프로그램 코딩부;
  END IF;
  IF DELETE THEN
     Delete작업시 프로그램 코딩부;
  END IF;
  IF INSERTING THEN
     INSERT작업시 프로그램 코딩부;
  END IF;
END;

트리거의 변경
트리거의 상태를 비활성화 또는 활성화 할 수 있다.
ALTER trigger trigger명 DISABLE | ENABLE;
해당 테이블과 관련된 모든 트리거의 상태를 비활성화 및 활성화 할 수 있다.
ALTER trigger trigger명 DISABLE | ENABLE ALL TRIGGER;
트리거를 재컴파일 할 수 있다.
ALTER TRIGGER trigger명 compile;

 

트리거 삭제하기
DROP trigger trigger명;

프로시저와 트리거의 차이점 

프로시저	트리거
CREATE PROCEURE 문법사용 EXECUTE 명령어로 실행 COMMIT, ROLLBACK 실행가능	CREATE PROCEURE 문법사용  생성 후 자동 실행 입력을 안받아줌 COMMIT, ROLLBACK 실행 안됨

트리거의 응용 범위
범위       |설명
---------------------------------------------------------------------
보안          |데이터베이스 내 테이블에 대한 변경을 제한할 수 있다.
감사          |사용자들의 데이터베이스 사용에 대한 모든 내용을 감시
                할 수 있다.
데이터의무결성 |테이블에 원치 않는 데이터가 저장되는 것을 방지할 수
                있다.
테이블의 복제  |기본 테이블에 대한 똑같은 복사 테이블을 온라인으로
                생성, 관리할 수 있다.
연속적 작업수행|기본 테이블에 데이터가 입력되면 또 다른 테이블에 데이터
                를 변경하는 연속적인 작업을 할 수 있다.
---------------------------------------------------------------------
위에서 Create문 다음 줄의 BEFORE는 Update, Delete, Insert로 인한
데이터변경이 생기기 전을 의미하고, AFTER는 반대를 의미한다.
주로 BEFORE를 사용하는데, Trigger를 사용하는 주목적이 잘못된 데이터를
막고자 함인데, 미리 Checking 하기 위해서는 BEFORE가 적당하다.
또 옵션으로 FOR EACH ROW가 있는데, 이것은 데이터 처리시에 건건이 모두
Trigger가 실행된다는 의미이다.
따라서 건건이 작업할 내용이 아니라면 사용하지 않는 것이 좋다.
왜냐하면 FOR EACH ROW가 선언됨에 따라 필요가 있든  없든 건건이 작업시에
계속해서 Trigger가 발생되기 때문에 필요없이 데이터베이스가 일을 하기
때문이다.
FOR EACH ROW를 선언했을 경우에는 Trigger에서 유용한 데이터 속성을 제공
한다. Update, Delete, Insert는 사실 데이터를 변경하는 SQL문이기 때문에
항상 반영전 데이터와 반영 후 데이터를 분류할 수 있다.
예를 들어 Insert문 같은 경우에는 새로 생기는 것이기 때문에 반영 전
데이터는 아무 것도 없는 것이고, 반영 후 데이터는 해당 데이터가 되겠죠
DELETE는 반대 개념이고, Update는 수정이기 때문에 당연히 수정전과 수정후
를 분류할 수 있다.
바로 이 반영전과 반영 후의 컬럼 데이터 값을 FOR EACH ROW 선언 후에
가져올 수 있다.
:OLD.컬럼명 => SQL반영 전 해당 컬럼 데이터
:NEW.컬럼명 => SQL변영 후 해당 컬럼 데이터
 CREATE TRIGGER trg_test1
   BEFORE
   UPDATE or DELETE or INSERT on emp
 DECLARE
   s_msg varchar2(100):='';
 BEGIN
   IF UPDATING THEN
      dbms_output.put_line('==> Update');
   END IF;
   IF DELETING THEN
      dbms_output.put_line('==> Delete');
   END IF;
   IF INSERTING THEN
      dbms_output.put_line('==> Insert');
   END IF;
 END;
CREATE TRIGGER trg_test2
  BEFORE
  UPDATE OF ename ON emp
  FOR EACH ROW
DECLARE
  s_msg varchar2(100):='';
BEGIN
  dbms_output.put_line('반영전 emp_name ==> '||:old.ename);
  dbms_output.put_line('반영후 emp_name ==> '||:new.ename);
END;
CREATE TRIGGER trg_test3
  BEFORE
  UPDATE OF ename ON emp
  FOR EACH ROW
  WHEN (old.empno < 7000)
DECLARE
  s_msg varchar2(100) :='';
BEGIN
  s_msg :=  'Trigger 발생';
  dbms_output.put_line(s_msg);
  dbms_output.put_line('반영전 emp_name ==> '||:old.ename);
  dbms_output.put_line('반영후 emp_name ==> '||:new.ename);
END;
CREATE TRIGGER trg_test4
  BEFORE
  UPDATE OF ename ON emp
  FOR EACH ROW
DECLARE
  s_msg varchar2(100) :='';
BEGIN
  IF :old.empno = 7369 then
     s_msg :='7369사번은 수정할 수 없습니다.';
     raise_application_error(-20001, s_msg);
   END IF;
END;
위에서 에러번호는 -20000 ~ -20999 까지 임의로 사용하면 된다.
CREATE or REPLACE TRIGGER trg_test5000
  BEFORE
  UPDATE or DELETE or INSERT ON emp
  FOR EACH ROW
DECLARE
  s_msg varchar2(100) :='';
 BEGIN
   IF UPDATING THEN
      IF :old.empno = 1111 THEN
         s_msg:='1111 사번은 수정할 수 없습니다!';
         raise_application_error(-20001,s_msg);
      END IF;
   END IF;
   IF DELETING THEN
      IF :old.empno = 1111 THEN
         s_msg:='1111 사번은 삭제할 수 없습니다!';
         raise_application_error(-20002,s_msg);
      END IF;
   END IF;
   IF INSERTING THEN
      IF :new.empno = 1111 THEN
         s_msg:='1111 사번은 입력할 수 없습니다!';
         raise_application_error(-20003,s_msg);
      END IF;
   END IF;
END;
/
update emp set ename='trigger2'
where empno = 7566
set serveroutput on size 10000;
insert into emp(empno, ename) values(1234,'trigger')
update emp set ename='trigger2'
where empno = 1234
delete from emp
where empno = 1234;
CREATE or Replace TRIGGER check_salary
BEFORE update or delete or insert on emp
BEGIN
  IF (to_char(sysdate,'DY') IN ('토', '일', '목'))
  THEN
    raise_application_error(-20500,'주말에는 변경할 수 없습니다.');
  END IF;
END;
테이블 복제
create or replace trigger trg_table_copy
after insert or update or delete on dept
for each row
begin
  if inserting then
     insert into dept_copy(deptno, dname, loc)
           values(:new.deptno, :new.dname, :new.loc);
  elsif updating then
     update dept_copy
        set dname = :new.dname, loc = :new.loc
      where dept_copy.deptno = :old.deptno;
  elsif deleting then
     delete from dept_copy
      where dept_copy.deptno = :old.deptno;
  end if;
end;
create table dept_copy as
select * from dept
==>  데이터 복제 트리거 적용 여부 확인
insert into dept values(66,'전산과','서울');
select * from dept_copy;
update dept set loc = '대전' where deptno = 66;

 

 

DDL문 - 프로시저 생성하기

 

커밋(물리적인 저장소에 들어감), 롤백

 

깃의 커밋

커밍하면 스테이징 에어리어에 올라간다

로컬레포에서 웹레포로 갈때 푸쉬

 

create(or replace : 생략-재정의 불가) procedure

 

프로시저 파라미터에는 in속성, out속성 inout속성 세가지가 올 수 있다.

in속성 -듣기, 읽기, 입력받기, 사용자가 앞단에 입력한 값을 받아오는 자리

out속성-오라클 서버에서 처리된 결과를 화면으로 내보낼때 사용

 

함수면 function

트리거면 trigger

이름 다음에는 괄호온다. (파라미터) 자리이다.

열거형 연산자를 사용하면 여러개가 올 수 있다.

 

 

is-begin사이에는 선언문이 온다

자바에서는 타입 먼저 이름 나중인데 여기선 이름 먼저 타입나중

 

begin 과 end사이에는 실행문이 온다.

 

is

--선언문

 

▶ 기본 PL/SQL Block 구조

 

영역	설명	옵션/필수
DECLARE (선언부)	PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분으로서 DECLARE로 시작 => 변수/상수/커서 등 을 선언	옵션
BEGIN (실행부)	절차적 형식으로 SQL문을 실행할수있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술할수있는 부분이며 BEGIN으로 시작	필수
EXCEPTION (예외 처리부)	PL/SQL문이 실행되는 중에 에러가 발생할수있는데 이를 예외 사항이라고 한다. 이러한 예외 사항이 발생했을때 이 를 해결하기 위한 문장을 기술할수있는 부분	옵션
END (실행문 종료)		필수

 

dept테이블을 카피하는 트리거를 만들었다.

원본데이터가 바뀌면 카피도 자동 처리가된다.

 

트리거 구조

trg_table_copy 트리거를 만든다.

원본 테이블에 값이 들어가고 난 후에 인서트 딜리트 업데이트 시도한다.

update 는 있던걸 업데이트한다.

 

트리거는 스위치가 켜져있으면 자동으로 실행된다. 

 

 

 

어제 시험 풀이

문제