✿∘˚˳°∘°

23일차 : PL/SQL 본문

국비수업/DB

23일차 : PL/SQL

_HYE_ 2022. 12. 28. 15:36

2022/12/28

 

[ 1. PL/SQL ]

  : Procedual Language extention to SQL

   오라클 자체에 내장되어있는 절차적언어

   SQL의 단점을 보완하여 SQL문장내에서 변수정의, 조건처리, 반복처리등을 지원

SET SERVEROUTPUT ON; --접속시마다 해줘야함

-- < 기본구조 >
DECLARE
BEGIN 
    DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
END;
/

 

1 - 1 ) PL/SQL 유형


  1. 익명블록 (Anonymous Block)
   : 이름없는 블록(블록:SQL코드의 덩어리), 간단한 로직을 처리할 때 사용(일회성작업을 모아서하려고)
  2. 프로시저(Procedure)
   : 지정된 특정 처리를 실행하는 서브프로그램, 익명블록을 저장해놓고 사용
    (JAVA로 따지면 리턴타입이 없는 메소드)
  3. 함수(Function)
   : 프로시저와 거의동일, 차이점은 반환값이 반드시 존재
   ( JAVA로 따지면 리턴타입이 있는 메소드)

< PL/SQL 기본구조 >
  DECLARE     -- 선언부[생략가능] 
      -- 필요한 변수나 상수를 선언
  BEGIN       -- 실행부
      -- 필요한 기능을 작성(반복, 제어)
  END;        -- 블록종료
  /           -- 실행

PL/SQL에서 변수에 값을 대입하는 대입연산자는 := 

DECLARE
    TEST_NO     NUMBER; -- 선언방법 : 변수명 자료형 
    TEST_STR    VARCHAR2(30);
BEGIN
    TEST_NO := 100;         -- 변수에 값 대입
    TEST_STR := 'HELLO';    -- 변수에 값 대입
    DBMS_OUTPUT.PUT_LINE('TEST_NO : ' || TEST_NO);
    DBMS_OUTPUT.PUT_LINE('TEST_STR : ' || TEST_STR);
END;
/

-- EMPLOYEE테이블의 사번이 200번인 직원의 이름, 급여 출력
SELECT EMP_NAME, SALARY FROM EMPLOYEE WHERE EMP_ID = 200;

DECLARE
    E_NAME  VARCHAR2(20);
    SAL     NUMBER;
BEGIN
    SELECT EMP_NAME, SALARY
    -- SELECT와 FROM 사이에 결과를 넣을 변수를 작성
    -- SELECT로 나온 결과가 순서에 맞춰서 대입된다
    INTO E_NAME, SAL        
    FROM EMPLOYEE 
    WHERE EMP_ID = '&사원번호'; -- 스캐너역할, 주석에서도 동작
    DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
    DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
END;
/

-- PL/SQL 작성
-- 사원번호를 입력하면 이름, 부서명, 직급명이 출력
-- 부서가 없는 사원은 부서없음으로 출력되도록
DECLARE
    E_NAME      VARCHAR2(20);
    D_TITLE     VARCHAR2(35);
    J_NAME      VARCHAR2(35);
BEGIN
    SELECT EMP_NAME, NVL(DEPT_TITLE, '부서없음'), JOB_NAME
    INTO E_NAME, D_TITLE, J_NAME
    FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    JOIN JOB USING(JOB_CODE)
    WHERE EMP_ID = '&사원번호';
    DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
    DBMS_OUTPUT.PUT_LINE('부서명 : '||D_TITLE);
    DBMS_OUTPUT.PUT_LINE('직급명 : '||J_NAME);
END;
/

 

1 - 2 ) PL/SQL 변수의 종류


  1. 일반변수(스칼라변수) 
      : SQL의 기본자료형과 유사한타입
  2. 상수형변수
      : 일반변수와 유사한 형태이지만 CONSTANT라는 키워드가 붙음
      : 변수선언 시 값을 대입해야하며, 한번대입된 값은 변경불가능

-- 상수형 변수
DECLARE
    USER_NAME CONSTANT VARCHAR2(20) := '테스트1'; -- 선언시 값대입
    USER_NAME2 VARCHAR2(20);
BEGIN
 -- USER_NAME := '테스트1'; 이러면 에러발생, 선언하면서 값을 넣어줘야한다 / 값수정X
    USER_NAME2 := '테스트2';
    DBMS_OUTPUT.PUT_LINE('USER_NAME : '||USER_NAME);
    DBMS_OUTPUT.PUT_LINE('USER_NAME2 : '||USER_NAME2);
END;


  3. 참조형변수(%TYPE)
      : 이전에 선언된 다른 변수 또는 테이블의 자료형과 동일한 타입의 변수
      : EMPLOYEE에 있는 EMAIL자료형을 가지고싶어 - 이것만 가져옴

-- 사번을 입력받아 이름, 이메일, 급여를 출력하는 PL/SQL
DECLARE
 -- E_NAME      VARCHAR2(20); 이렇게 선언하는 경우 자료형사이즈가 값보다 작으면 에러가 발생
    E_NAME      EMPLOYEE.EMP_NAME%TYPE;    
    MAIL        EMPLOYEE.EMAIL%TYPE; -- EMPLOYEE테이블의EMAIL컬럼의 자료형을 가져와라
    SAL         EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT EMP_NAME, EMAIL, SALARY
    INTO E_NAME, MAIL, SAL
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
    DBMS_OUTPUT.PUT_LINE('이메일 : '||MAIL);
    DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
END;
/


  4. 참조형변수(%ROWTYPE)
      : 참조할 테이블의 모든컬럼의 자료형을 한번에 가지고옴
      : EMPLOYEE에 있는 EMAIL자료형을 가지고싶어 - 전부다 가져옴

-- 사번을 입력받아 사번, 이름, 주민등록번호, 이메일, 부서코드, 직급코드, 급여 출력
/*
    E_ID    EMPLOYEE.EMP_ID%TYPE;
    E_NAME  EMPLOYEE.EMP_NAME%TYPE;
    E_NO    EMPLOYEE.EMP_NO%TYPE;
    E_EMAIL EMPLOYEE.EMAIL%TYPE;
    E_DCODE EMPLYOEE.DEPT_CODE%TYPE;
    E_JCODE EMPLOYEE.JOB_CODE%TYPE;
    E_SAL   EMPLOYEE.SALARY%TYPE;
    H_DATE  EMPLYOEE.HIRE_DATE%TYPE;
    이렇게 일일히 선언해주려면 너무힘듬
*/
DECLARE
    -- ROWTYPE으로 선언시
    -- EMPLOYEE테이블 내부의 모든 컬럼과 동일한 이름으로 변수가 생성
    -- 변수명.컬럼명으로 사용
    EMP EMPLOYEE%ROWTYPE; 
BEGIN
    SELECT EMP_ID, EMP_NAME, EMP_NO, EMAIL, DEPT_CODE, JOB_CODE, SALARY, HIRE_DATE
    INTO EMP.EMP_ID, EMP.EMP_NAME, EMP.EMP_NO, EMP.EMAIL, EMP.DEPT_CODE,
         EMP.JOB_CODE, EMP.SALARY, EMP.HIRE_DATE
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    DBMS_OUTPUT.PUT_LINE('사번 : '||EMP.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('이름 : '||EMP.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('주민번호 : '||EMP.EMP_NO);
    DBMS_OUTPUT.PUT_LINE('이메일 : '||EMP.EMAIL);
    DBMS_OUTPUT.PUT_LINE('부서코드 : '||EMP.DEPT_CODE);
    DBMS_OUTPUT.PUT_LINE('직급코드 : '||EMP.JOB_CODE);
    DBMS_OUTPUT.PUT_LINE('급여 : '||EMP.SALARY);
    DBMS_OUTPUT.PUT_LINE('입사일 : '||EMP.HIRE_DATE);
END;
/


  5. RECORD타입
      : ROWTYPE은 테이블의 컬럼을 모두 가져온다면 RECORED는 내가 지정한것만
      : 사용자지정 ROWTYPE이라고 보면된다

-- 사번을 입력받아서 이름, 주민등록번호, 이메일, 급여 출력
DECLARE
    TYPE MY_RECORD_TYPE IS RECORD(
        E_NAME      EMPLOYEE.EMP_NAME%TYPE,
        E_NO        EMPLOYEE.EMP_NO%TYPE,
        MAIL        EMPLOYEE.EMAIL%TYPE,
        SAL         EMPLOYEE.SALARY%TYPE
    );
    -- MY_RECORD_TYPE이라는 자료형을 만든 것
    USERINFO    MY_RECORD_TYPE;
BEGIN
    SELECT EMP_NAME, EMP_NO, EMAIL, SALARY
    INTO USERINFO -- RECODE타입은 필요한만큼만 생성해주기때문에 INTO가 좀더 편해진다
    -- ROWTYPE도 전부다 출력할거면 이렇게 작성해도 되지만, 
    -- 위처럼 부분출력할 컬럼이 많은 경우 INTO가 길어져서 힘들다.
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    DBMS_OUTPUT.PUT_LINE(' 이름 : '||USERINFO.E_NAME);
    DBMS_OUTPUT.PUT_LINE(' 주민등록번호 : '||USERINFO.E_NO);
    DBMS_OUTPUT.PUT_LINE(' 이메일 : '||USERINFO.MAIL);
    DBMS_OUTPUT.PUT_LINE(' 급여 : '||USERINFO.SAL);
END;
/

 

-- 실습문제 --

--@실습문제1
--사번, 사원명, 직급코드, 급여를 담을수 있는 참조변수를 통해서 (%TYPE)
--송종기사원의 사번, 사원명, 직급코드,급여를 익명블럭을 통해 스크립트 출력하세요.
DECLARE
    E_ID    EMPLOYEE.EMP_ID%TYPE;
    E_NAME  EMPLOYEE.EMP_NAME%TYPE;
    E_JCODE EMPLOYEE.JOB_CODE%TYPE;
    SAL     EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
    INTO E_ID, E_NAME, E_JCODE, SAL
    FROM EMPLOYEE
    WHERE EMP_NAME = '송종기';
    DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
    DBMS_OUTPUT.PUT_LINE('사원명 : '||E_NAME);
    DBMS_OUTPUT.PUT_LINE('직급코드 : '||E_JCODE);
    DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
END;
/

--@실습문제2
-- 사번, 사원명, 부서명,직급명을 담을수 있는 참조변수를 통해서 (record)
-- 사원명을 검색하여 해당 사원의 사번, 사원명, 부서명,직급명을 
-- 익명블럭을 통해 스크립트 출력하세요.
DECLARE
    TYPE MY_R IS RECORD(
        E_ID    EMPLOYEE.EMP_ID%TYPE,
        E_NAME  EMPLOYEE.EMP_NAME%TYPE,
        D_TITLE DEPARTMENT.DEPT_TITLE%TYPE,
        J_NAME  JOB.JOB_NAME%TYPE
    );
    USERINFO    MY_R;
BEGIN
    SELECT EMP_ID, EMP_NAME, NVL(DEPT_TITLE, '부서없음'), JOB_NAME
    INTO USERINFO
    FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID) 
    JOIN JOB USING(JOB_CODE)
    WHERE EMP_NAME = '&사원명';
    DBMS_OUTPUT.PUT_LINE('사번 : '||USERINFO.E_ID);
    DBMS_OUTPUT.PUT_LINE('사원명 : '||USERINFO.E_NAME);
    DBMS_OUTPUT.PUT_LINE('부서명 : '||USERINFO.D_TITLE);
    DBMS_OUTPUT.PUT_LINE('직급명 : '||USERINFO.J_NAME);
END;
/

--@실습문제3
-- 사원번호를 입력하여 해당 사원을 찾아(%ROWTYPE을 사용)
-- 사원명, 주민번호, 입사일, 부서명을 
-- 익명블럭을 통해 스크립트 출력하세요.​
DECLARE
    EMP     EMPLOYEE%ROWTYPE;
    DEP     DEPARTMENT%ROWTYPE;
BEGIN
    SELECT EMP_NAME, EMP_NO, HIRE_DATE, NVL(DEPT_TITLE, '부서없음')
    INTO EMP.EMP_NAME, EMP.EMP_NO, EMP.HIRE_DATE, DEP.DEPT_TITLE
    FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
    WHERE EMP_ID = '&사원번호';
    DBMS_OUTPUT.PUT_LINE('사원명 : '||EMP.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('주민등록번호 : '||EMP.EMP_NO);
    DBMS_OUTPUT.PUT_LINE('입사일 : '||EMP.HIRE_DATE);
    DBMS_OUTPUT.PUT_LINE('부서명 : '||DEP.DEPT_TITLE);
END;
/

 

1 - 3 ) 조건문

  IF문

    IF(조건식)

    THEN 조건일 참일때 수행할문장;

    END IF;

  -- 조건식에는 오라클의 모든 연산자를 사용할 수 있다.

-- 오라클의 IF문
-- 사원번호를 입력받아서 사번, 이름, 급여, 보너스율을 출력
-- 보너스를 받지않는 직원은 '보너스를 받지 않는 직원입니다.' 추가출력
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, NVL(BONUS, 0)
    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);
    DBMS_OUTPUT.PUT_LINE('보너스 : '||BO*100||'%');
    IF(BO = 0)
    THEN DBMS_OUTPUT.PUT_LINE('보너스를 받지않는 직원입니다.');
    END IF;
END;
/

 

  IF ~ ELSE

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, NVL(BONUS, 0)
    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 = 0) -- 오라클의 모든연산자 사용가능
    THEN DBMS_OUTPUT.PUT_LINE('보너스를 받지않는 직원입니다.');
    ELSE DBMS_OUTPUT.PUT_LINE('보너스 : '||BO*100||'%');
    END IF;
END;
/
-- 사원번호를 입력받아서 사원명, 부서코드, 부서명 출력
-- 부서가 있으면 부서명 출력
-- 만약 부서가 없다면 '부서가 없는 사원입니다' 출력
DECLARE
    E_NAME  EMPLOYEE.EMP_NAME%TYPE;
    E_DCODE EMPLOYEE.DEPT_CODE%TYPE;
    D_TITLE DEPARTMENT.DEPT_TITLE%TYPE;
BEGIN
    SELECT EMP_NAME, DEPT_CODE, DEPT_TITLE
    INTO E_NAME, E_DCODE, D_TITLE
    FROM EMPLOYEE 
    LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
    WHERE EMP_ID = '&사원번호';
    DBMS_OUTPUT.PUT_LINE('사원명 : '||E_NAME);
    DBMS_OUTPUT.PUT_LINE('부서코드 : '||E_DCODE);
    IF(D_TITLE IS NULL)
    THEN DBMS_OUTPUT.PUT_LINE('부서가 없는 사원입니다');
    ELSE DBMS_OUTPUT.PUT_LINE('부서명 : '||D_TITLE);
    END IF;
END;
/

-- 사원번호를 입력받아 사번, 이름, 부서코드, 직급코드 출력
-- 직급코드가 J1, J2이면 '임원진입니다' 아니면 '일반사원입니다 출력'
DECLARE
    E_ID    EMPLOYEE.EMP_ID%TYPE;
    E_NAME  EMPLOYEE.EMP_NAME%TYPE;
    E_DCODE EMPLOYEE.DEPT_CODE%TYPE;
    E_JCODE EMPLOYEE.JOB_CODE%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE
    INTO E_ID, E_NAME, E_DCODE, E_JCODE
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
    DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
    DBMS_OUTPUT.PUT_LINE('부서코드 : '||E_DCODE);
    DBMS_OUTPUT.PUT_LINE('직급코드 : '||E_JCODE);
    IF(E_JCODE IN('J1', 'J2'))
    THEN DBMS_OUTPUT.PUT_LINE('임원진입니다.');
    ELSE DBMS_OUTPUT.PUT_LINE('일반사원입니다.');
    END IF;
END;
/

DECLARE
    E_ID    EMPLOYEE.EMP_ID%TYPE;
    E_NAME  EMPLOYEE.EMP_NAME%TYPE;
    E_DCODE EMPLOYEE.DEPT_CODE%TYPE;
    E_JCODE EMPLOYEE.JOB_CODE%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE
    INTO E_ID, E_NAME, E_DCODE, E_JCODE
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
    DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
    DBMS_OUTPUT.PUT_LINE('부서코드 : '||E_DCODE);
    DBMS_OUTPUT.PUT_LINE('직급코드 : '||E_JCODE);
    IF(E_JCODE = 'J1')
    THEN 
        DBMS_OUTPUT.PUT_LINE('임원진입니다.');
    ELSIF(E_JCODE = 'J2')
    THEN
        DBMS_OUTPUT.PUT_LINE('임원진입니다.');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('일반사원입니다.');
    END IF;
END;
/

 

1 - 4 ) 반복문

-- 반복문1
-- JAVA의 WHILE()문과 비슷 
DECLARE
    I   NUMBER := 1; --상수는아님
BEGIN
    LOOP        -- 반복시작지점
        DBMS_OUTPUT.PUT_LINE('I : '||I);
        I := I+1;
        IF(I = 5)
        THEN 
            EXIT; -- 반복종료코드
        END IF;
    END LOOP;   -- 반복끝나는지점
END;
/

-- 반복문 2
-- JAVA의 FOR()문과 비슷 
DECLARE
-- 딱히 선언하지않아도 됨
BEGIN
    FOR I IN 1..5 LOOP -- 1~5까지 동작
        DBMS_OUTPUT.PUT_LINE('I : '||I);
    END LOOP;
END;
/

-- 반복문3
DECLARE
BEGIN
    FOR I IN REVERSE 1..5 LOOP -- 5~1까지 동작
        DBMS_OUTPUT.PUT_LINE('I : '||I);
    END LOOP;
END;
/

SELECT * FROM EMPLOYEE;
-- 반복문을 이용하여 전직원의 사번, 이름, 급여, 입사일 출력
DECLARE
    E_ID    EMPLOYEE.EMP_ID%TYPE;
    E_NAME  EMPLOYEE.EMP_NAME%TYPE;
    SAL     EMPLOYEE.SALARY%TYPE;
    H_DATE  EMPLOYEE.HIRE_DATE%TYPE;
BEGIN
    -- 전체 직원을 조회하고 싶을 때 변수는 4개이기때문에 
    -- 한번에 23명의 데이터를 넣을 수 없음
    -- 반복문을 통해 한명씩 넣어줄 것
    FOR I IN 0..22 LOOP
        SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE
        INTO E_ID, E_NAME, SAL, H_DATE
        FROM EMPLOYEE
        WHERE EMP_ID = 200 + I; 
        DBMS_OUTPUT.PUT_LINE('사번 : '||E_ID);
        DBMS_OUTPUT.PUT_LINE('이름 : '||E_NAME);
        DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
        DBMS_OUTPUT.PUT_LINE('입사일 : '||H_DATE);
        DBMS_OUTPUT.PUT_LINE('-------------------------');
    END LOOP;
END;
/

 

1 - 5 ) PROCEDURE

 

-- 회원정보를 저장하는 테이블 생성과 INSERT

-- 회원정보를 저장하는 MEMBER_TBL 생성
/*
    회원번호       NUMBER          PRIMARY KEY
    아이디         VARCHAR2(30)    UNIQUE NOT NULL
    비밀번호       VARCHAR2(30)     NOT NULL
    이름          VARCHAR2(15)     NOT NULL
    나이          NUMBER           기본값 0
    전화번호       CHAR(13)         기본값 '010-0000-0000'
    가입일         DATE             
*/
CREATE TABLE MEMBER_TBL(
    MEMBER_NO       NUMBER          PRIMARY KEY,
    MEMBER_ID       VARCHAR2(30)    UNIQUE NOT NULL,
    MEMBER_PW       VARCHAR2(30)    NOT NULL,
    MEMBER_NAME     VARCHAR2(15)    NOT NULL,
    MEMBER_AGE      NUMBER          DEFAULT 0,
    MEMBER_PHONE    CHAR(13)        DEFAULT '010-0000-0000',
    JOIN_DATE       DATE
);
CREATE SEQUENCE MEMBER_SEQ; -- 회원번호시퀀스
-- 기본값으로 생성됨 ( START 1, INCREMENT 1, 정수최대치까지) 

-- 회원 30명 INSERT
DECLARE
BEGIN
    FOR I IN 1..30 LOOP
        INSERT INTO MEMBER_TBL VALUES
        (MEMBER_SEQ.NEXTVAL, 'user'||I, 'PWD'||I, '유저'||I, 20+I,DEFAULT, SYSDATE);
    END LOOP; 
END;
/

 

-- 탈퇴할 회원의 정보를 저장하는 테이블

-- 탈퇴한 회원의 정보를 저장하는 테이블생성
CREATE TABLE DEL_MEMBER_TBL(
    DEL_NO          NUMBER      PRIMARY KEY, -- 탈퇴번호
    MEMBER_NO       NUMBER,                  -- 회원번호
    MEMBER_ID       VARCHAR2(30),            -- 회원아이디
    MEMBER_NAME     VARCHAR2(15),            -- 회원이름
    JOIN_DATE       DATE,                    -- 가입일
    DEL_DATE        DATE                     -- 탈퇴일
);

CREATE SEQUENCE DEL_MEMBER_SEQ; --탈퇴한회원번호 시퀀스

 

-- 탈퇴한 회원의 정보를 멤버테이블에서는 삭제하고, 삭제테이블에는 추가해주는 작업

-- 회원번호로 탈퇴하면, 회원정보를 MEMBER_TBL에서 삭제, 
-- DEL_MEMBER_TBL에는 데이터 입력
-- 1번회원이 탈퇴하는 경우
-- 1. 1번회원의 정보를 조회(MEMBER_NO, MEMBER_ID, MEMBER_NAME, JOIN_DATE)
--    DEL_MEMBER_TBL에 INSERT해주기위해
-- 2. 1번회원을 MEMBER_TBL에서 삭제
-- 3. 조회된 정보를 DEL_MEMBER_TBL 테이블에 INSERT

-- 1번작업
SELECT MEMBER_NO, MEMBER_ID, MEMBER_NAME, JOIN_DATE
FROM MEMBER_TBL WHERE MEMBER_NO = 1;
-- 2번작업
DELETE FROM MEMBER_TBL WHERE MEMBER_NO = 1;
-- 3번작업
INSERT INTO DEL_MEMBER_TBL VALUES
(DEL_MEMBER_SEQ.NEXTVAL,조회된회원번호, 조회된아이디, 조회된이름, 조회된가입일, SYSDATE);

 

-- 위 작업을 PL/SQL(익명블록) 으로 작성

-- 이런작업을 PL/SQL을 통해서 묶어주는 작업을 할 것(익명블록)
DECLARE
    MEM_NO      MEMBER_TBL.MEMBER_NO%TYPE;
    MEM_ID      MEMBER_TBL.MEMBER_ID%TYPE;
    MEM_NAME    MEMBER_TBL.MEMBER_NAME%TYPE;
    J_DATE      MEMBER_TBL.JOIN_DATE%TYPE;
    DEL_NO      NUMBER; -- 삭제회원번호
BEGIN
    DEL_NO := '&삭제회원번호';
    SELECT MEMBER_NO, MEMBER_ID, MEMBER_NAME, JOIN_DATE
    INTO MEM_NO, MEM_ID, MEM_NAME, J_DATE
    FROM MEMBER_TBL WHERE MEMBER_NO = DEL_NO;

    DELETE FROM MEMBER_TBL WHERE MEMBER_NO = DEL_NO;

    INSERT INTO DEL_MEMBER_TBL VALUES
    (DEL_MEMBER_SEQ.NEXTVAL, MEM_NO, MEM_ID, MEM_NAME, J_DATE, SYSDATE);
    COMMIT;
END;
/
SELECT * FROM MEMBER_TBL;
SELECT * FROM DEL_MEMBER_TBL;

 

>> 하지만 이 경우 탈퇴를 할 때마다 익명블록로직을 작성해주어야한다 - 익명블록의 한계

>> 프로시져를 사용

 

    - 프로시져 
    : 일련의 작업절차를 정리해서 저장해 둔 것
    : 여러 SQL문을 묶어서 미리 정의하고, 하나의 요청으로 실행
    : 자주사용되는 복잡한 작업을 미리 만들어두고 계속 재사용
    
    - 프로시져 생성방법(프로시져도 객체이기때문에 CREATE문으로)
    CREATE PROCEDURE 프로시져이름(매개변수) -- 매개변수가 없으면 ()생략
    IS
        변수 선언
    BEGIN
        실행할 로직 작성
    END;
    /

 

-- 익명블록으로 작성했던 로직을 프로시져로 만들기

CREATE PROCEDURE DEL_MEMBER_PRO(DEL_NO MEMBER_TBL.MEMBER_NO%TYPE) 
-- 삭제할 회원번호를 매개변수로 받음
IS
    MEM_NO      MEMBER_TBL.MEMBER_NO%TYPE;
    MEM_ID      MEMBER_TBL.MEMBER_ID%TYPE;
    MEM_NAME    MEMBER_TBL.MEMBER_NAME%TYPE;
    J_DATE      MEMBER_TBL.JOIN_DATE%TYPE;
BEGIN
    SELECT MEMBER_NO, MEMBER_ID, MEMBER_NAME, JOIN_DATE
    INTO MEM_NO, MEM_ID, MEM_NAME, J_DATE
    FROM MEMBER_TBL WHERE MEMBER_NO = DEL_NO;

    DELETE FROM MEMBER_TBL WHERE MEMBER_NO = DEL_NO;

    INSERT INTO DEL_MEMBER_TBL VALUES
    (DEL_MEMBER_SEQ.NEXTVAL, MEM_NO, MEM_ID, MEM_NAME, J_DATE, SYSDATE);
    COMMIT;
END;
/

실행 시 결과콘솔화면

-- 프로시져 실행
--EXECUTE 프로시져명(매개변수);

EXECUTE DEL_MEMBER_PRO(20);
EXEC DEL_MEMBER_PRO(5);

-- 프로시져 삭제 
-- DROP PROCEURE 프로시져명;
DROP PROCEDURE DEL_MEMBER_PRO;

 

>> 익명블록과 프로시저의 차이점 <<
 : 사용자계정에 접속이 해제된 후 재접속 시 익명블록은 다시 작성해야하지만,
 : 프로시져는 저장이 되어있기 때문에 실행문만 작성하면 바로 동작이된다!
 : 테이블을 저장하듯이 프로시져를 저장해두고 사용하는 것

 

[ 후기 ] -------------------------------------------------------------------------------------------------------------------------------------------------------

 

 

오늘 실수한 점

시퀀스 사용시 시퀀스명.NEXTVAL /CURRVAL 을 적어야하는데 깜박함

 

 

 

저작자표시

'국비수업 > DB' 카테고리의 다른 글

24일차 : PL/SQL / DB모델링  (0) 2022.12.29
22일차 : ALTER, DROP / TCL / OBJECT  (0) 2022.12.27
21일차 : 서브쿼리 / DML / DDL  (0) 2022.12.26
20일차 : GROUP BY / JOIN  (0) 2022.12.23
19일차 : 함수  (0) 2022.12.22
'국비수업/DB' Related Articles more
Comments