2022-02-08 PL-SQL2 / TRIGGER / ERD

이전 글을 보고오신 분이라면 아시겠지만, 바로 다음 글을 포스팅하러 왔다...
그치만 나의 공부 기록은 내가 아니면 안보기 때문에 아무도 모를 것 이다.
바로 시작해보자.

PL/SQL

선택문

• 선택문
  PL/SQL의 모든 문장들은 기술한 순서대로 순차적으로 수행
  문장을 선택적으로 수행하려면 선택문을 사용
• 선택문 종류
  IF ~ THEN ~ END IF
  IF ~ THEN ~ ELSE ~ END IF
  IF ~ THEN ~ ELSIF ~ ELSE ~ END IF
  CASE

선택문의 종류를 하나씩 살펴보자.

1. IF ~ THEN ~ END IF

이 선택문은 다음과 같은 형식으로 쓰여진다.

DECLARE
    변수 선언
BEGIN
	실행
    IF 조건
      THEN 
    END IF;
END;
/

보너스 유무 여부에 따라 그 결과를 알려주는 코드를 짜봤다.

DECLARE
    E_ID    EMPLOYEE.EMP_ID%TYPE;
    E_NAME  EMPLOYEE.EMP_NAME%TYPE;
    SAL     EMPLOYEE.SALARY%TYPE;
    BO      EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, BONUS
    INTO E_ID,E_NAME,SAL,BO
    FROM EMPLOYEE WHERE EMP_ID = '&사번';
    DBMS_OUTPUT.PUT_LINE('사번 :'||E_ID);
    DBMS_OUTPUT.PUT_LINE('이름 :'||E_NAME);
    DBMS_OUTPUT.PUT_LINE('급여 :'||SAL);
    IF BO IS NULL
    THEN DBMS_OUTPUT.PUT_LINE('보너스를 받지 않습니다.');
    END IF;
END;
/

2. IF ~ THEN ~ ELSE ~ END IF

이 선택문은 나올 수 있는 조건이 2개만 있을 때 주로 사용한다.

DECLARE
    변수 선언
BEGIN
	실행
    IF 조건
      THEN	실행문 
      ELSE	실행문
    END IF;
END;
/

이렇게 사용하고, 아까 했던 예시에서 보너스를 받는 직원을 표시해주고 싶다면

 IF BO IS NULL
    THEN DBMS_OUTPUT.PUT_LINE('보너스를 받지 않습니다.');
    ELSE DBMS_OUTPUT.PUT_LINE('보너스를 받는 직원입니다.');
 END IF;

이렇게 사용하면 된다.

3. IF ~ THEN ~ ELSIF ~ ELSE ~ END IF

이 선택문은 조건이 다양할 때 사용한다.

DECLARE
    
BEGIN
    
    IF 조건
        THEN 실행문 
    ELSIF 조건
        THEN 실행문 
    ELSIF 조건
        THEN 실행문 
    ELSIF 조건
        THEN 실행문 
    ELSIF 조건
        THEN 실행문 
    ELSE
      
    END IF;
END;
/

위와 같이 실행한다.

4. CASE

CASE 선택문은 변수의 값을 대입해서 비교하는 조건으로 사용한다.
다음 코드는 급여를 받는 정도에 따라 등급을 나누어 결과를 출력해주는 코드이다.
근데 이거 좀 슬프다... 내가 지금 능력이 없어서 찔린다.
내가 돈 잘벌었으면 그냥 이렇게 하면 안된다고 웃어 넘길 수 있을 것 같은데 지금은 못하겠다...

DECLARE
    SAL     EMPLOYEE.SALARY%TYPE;
    SAL_GRADE CHAR(1);
BEGIN
    SELECT SALARY
    INTO SAL
    FROM EMPLOYEE WHERE EMP_ID ='&사번';
    DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
    SAL := FLOOR(SAL/1000000);		-- 급여를 1000000으로 나누고 버림해서 값을 내주고있다.
    CASE SAL -- CASE 변수
        -- WHEN 값 THEN 실행문;
        WHEN 0 THEN SAL_GRADE := 'F';
        WHEN 1 THEN SAL_GRADE := 'E';
        WHEN 2 THEN SAL_GRADE := 'D';
        WHEN 3 THEN SAL_GRADE := 'C';
        WHEN 4 THEN SAL_GRADE := 'B';
        ELSE SAL_GRADE := 'A';
    END CASE;
    DBMS_OUTPUT.PUT_LINE('급여등급 : '||SAL_GRADE);
END;
/

선택문은 이렇게 사용하면 된다.
다음은 반복문이다.

반목문

• 반복문이랃
  문장을 반복적으로 수행하기 위해 사용되는 구문

반복적으로 수행하도록 하는 반복문은 종류가 여러개 있다.

반복문의 종류
1. BASIC LOOP : 조건없이 무한반복
2. FOR LOOP : 지정한 반복 횟수 만큼 반복
3. WHILE LOOP : 제어 조건이 TRUE인 동안만 반복

이번에도 하나하나 살펴보자.

1. BASIC LOOP

DECLARE
    NUM NUMBER :=1;
BEGIN
    LOOP        --반복문 시작지점
        DBMS_OUTPUT.PUT_LINE(NUM);	--현재 NUM의 값을 출력
        NUM := NUM+1;	--NUM을 1씩 증가시킴
        
        IF NUM>20 
            THEN EXIT;	-- EXIT으로 반복문을 나감
        END IF;
    END LOOP;   --반복문 끝지점
END;
/

위 반복문은 NUMBER 타입의 변수 NUM을 지정하여 그 숫자가 20보다 커질때까지 계속 수를 늘리는 반복문이다. LOOP로 반복문을 시작하고, END LOOP로 반복문의 끝지점을 나타낸다. EXIT을 사용해 반복문을 나갈 수 있다.

2. FOR LOOP

DECLARE
    
BEGIN
    FOR NUM IN 1..5 LOOP	--1~5까지
        DBMS_OUTPUT.PUT_LINE(NUM); --NUM을 출력
    END LOOP;
END;
/

FOR LOOP문은 미리 범위를 지정해주는 반복문이다.
위의 반복문은 NUM이 1부터 5까지 증가할때까지 반복시키는 반복문이다.
반대로 할 수도 있다.

DECLARE
    
BEGIN
    FOR NUM IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
    END LOOP;
END;
/

이렇게 NUM 뒤에 REVERSE를 붙여주면 5에서 1까지 가는동안 반복하는 반복문이다.
이게 뭔 의미가 있는지는 사실 필자도 잘 모르겠다..??

3. WHILE LOOP

DECLARE
	  N NUMBER := 1;
BEGIN
	  WHILE N <= 5 LOOP -- 반복여부를 체크할 조건식
	   	  DBMS_OUTPUT.PUT_LINE(N);
 	 	  N := N + 1;
	  END LOOP; -- 반복 끝
END;
/

아까 위에서(1번 BASIC LOOP) 했던 IF문의 조건을 WHILE에 넣어준 것 이라고 보면 편하다.
반복문이 한번 실행될 때마다 N 값을 1 올려주어서 값이 5가 될때까지 반복하는 것 이다.

이번에는 LOOP의 예외를 살펴보자.

EXCEPTION

DECLARE
    E_NAME  EMPLOYEE.EMP_NAME%TYPE;
    NUM     NUMBER:=0;
BEGIN
    LOOP
        SELECT EMP_NAME
        INTO E_NAME
        FROM EMPLOYEE WHERE EMP_ID=200+NUM;
        DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
        NUM := NUM+1;    
        -- EMP_ID의 값이 없으면 에러가 나면서 EXCEPTION으로 빠져가지고 LOOP가 끝남
    END LOOP;
EXCEPTION 
    WHEN NO_DATA_FOUND
        THEN DBMS_OUTPUT.PUT_LINE('데이터가 없습니다.');
END;
/

이렇게 반복문을 설정해주다가 EXCEPTION을 이용해 반복문을 나가줄 때 사용하는 것 이다.

LOOP는 여기까지 보자.
다음은 TRIGGER다.

TRIGGER

TRIGGER

• 데이터베이스가 정해놓은 조건을 만족하거나 어떤 동작이 수행되면 자동적으로 수행되는 행동
• 트리거는 테이블이나 뷰가 INSERT, DELETE 등의 DML문에 의해 데이터가 입력, 수정, 삭제 될 경우 자동으로 실행

코드를 보면서 알아보자.

CREATE OR REPLACE TRIGGER USER_DEL_TRG	-- 이미 생성되어있는 트리거라면 덮어쓰고 없으면 새로 생성
AFTER DELETE		-- DELETE가 실행된 이후에 실행
ON USER_TBL			-- 위 TRIGGER를 적용할 테이블을 지정
FOR EACH ROW        -- 적용된 ROW의 수만큼 트리거가 동작 -> 바인드변수 적용 가능
BEGIN
    INSERT INTO DEL_TBL VALUES(:OLD.USER_NO,:OLD.USER_ID,:OLD.USER_PW);
    DBMS_OUTPUT.PUT_LINE('회원을 삭제하고 삭제회원 목록에 넣었습니다.');
END;
/

CREATE OR REPLACE TRIGGER USER_INS_TRG
AFTER INSERT		-- INSERT가 실행된 이후에 실행
ON USER_TBL
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:NEW.USER_NO||'/'||:NEW.USER_ID||'/'||:NEW.USER_PW);
END;
/

이게 TRIGGER를 생성하는 방법이다.
이렇게 USER_TBL의 INSERT, DELETE에 반응하는 트리거를 생성해놓으면
USER_TBL에 INSERT나 DELETE를 해줄때, TRIGGER가 실행된다는 말이다.

INSERT INTO USER_TBL VALUES(205,'TEST06',1234);
-- 이럴때 205/TEST06/1234 이렇게 출력해준다.

ERD에 들어가기 전
번외 PROCEDURE

PROCEDURE

PROCEDURE는 순서, 진행상의 순서 라는 뜻으로

CREATE PROCEDURE 이름(매개변수 이름 / 매개변수 타입)

의 방법으로 생성한다.
짜놓은 코드나 올려본다.

DROP PROCEDURE USER_DELETE;

CREATE PROCEDURE USER_DELETE(DEL_NO USER_TBL.USER_NO%TYPE)
IS
    U_NO        USER_TBL.USER_NO%TYPE;
    U_ID        USER_TBL.USER_ID%TYPE;
    U_PW        USER_TBL.USER_PW%TYPE;
BEGIN
    SELECT USER_NO, USER_ID, USER_PW
    INTO U_NO, U_ID, U_PW
    FROM USER_TBL WHERE USER_NO =DEL_NO;
    DELETE FROM USER_TBL WHERE USER_NO=U_NO;
    INSERT INTO DEL_TBL VALUES (U_NO,U_ID,U_PW);
EXCEPTION
    WHEN NO_DATA_FOUND
        THEN DBMS_OUTPUT.PUT_LINE('회원정보가 없습니다.');
END;
/

이렇게 미리 짜놓은 PL/SQL코드를 넣어주면 객체로서 사용이 가능한 거다. 함수같은 것 이다.
근데 DECLARE 대신에 IS 를 넣어준다.
위의 코드는 EXEC (SQL의 문장을 실행시키는 쿼리)를 사용해 실행할 수 있고, 이 코드의 내용은 원래 있던 데이터를 삭제하고 삭제한 데이터를 모아놓는 곳에는 그 데이터를 넣어주는 내용이다.

ERD (DB 모델링)

ERD는 데이터베이스의 프로그램 설계를 도와주는 사이트이다. DB의 모델링이라고 부르더라.
https://www.erdcloud.com/
DB 모델링의 구성은 엔티티, 속성, 인스턴스로 구성되어있다.

• 엔티티
  업무의 관심 대상이 되는 정보를 갖고 있거나 그에 대한 정보를 관리할 필요가 있는 유형, 무형 사물(개체)
• 속성
  엔티티에서 관리해야 할 최소 단위 정보 항목으로 엔티티는 하나 이상의 속성을 포함
• 인스턴스
  엔티티의 속성으로 실제로 구현된 하나의 값

엔티티의 조건
1. 업무의 관심 대상이 되는 사물이어야 한다.
2. 속성을 소유해야 한다.
3. 두 개 이상의 인스턴스를 소유해야 한다.

속성의 명명규칙
1. 속성의 의미가 분명하게 드러나게 작성해야한다.
2. 해당 업무에서 사용하는 이름을 부여해야한다.
3. 서술식과 약어의 사용은 금지한다.
4. 엔티티에서 유일하게 식별 가능하도록 지정한다.

위의 조건이 DB 모델링의 구성이다.
오늘 너무 힘들다. 여기까지만 하고, 필자가 짜놓은거 하나만 올려놓고 가본다...
홈페이지 조건을 아주아주 조금 짠 것 이다