[230712] PLSQL / 프로시저 / 사용자 함수 / 트리거 (DAY 11) - 구디아카데미후기 IT국비지원 민경태 강사님

MJ·2023년 7월 13일
IT국비지원sql구디아카데미후기민경태

수업 TIL🐣💚

목록 보기
11/68

PLSQL

  1. 오라클 문법
  2. 프로그래밍이 가능한 SQL문 작성 방법
  3. 프로시저, 사용자 함수 등의 기반이 되는 언어 ***
  4. 항상 블록을 잡고 실행해야 함
  5. 형식
[DECLARE 변수 선언]
BEGIN
     실행문
END;

변수 선언

  1. 값을 저장할 때 대입 연산자(:=)를 사용한다.
  2. 타입을 선언하는 방식
    1) 스칼라 변수 : 타입을 직접 지정한다.
    2) 참조 변수 : 특정 칼럼의 타입을 그대로 사용한다.

서버 메시지 출력하기

  1. 기본적으로 서버 메시지는 출력되지 않는다.
  2. 서버 메시지 출력을 위해서 최초 1회 아래 쿼리문을 실행한다.
SET SERVEROUTPUT ON;
  1. 출력하는 방법
DBMS_OUTPUT.PUT_LINE(출력할내용);

코드

-- 서버 메시지 출력 ON
SET SERVEROUTPUT ON;
-- Hello World 출력하기
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
END;

스칼라 변수

: 직접 타입을 명시

DECLARE
    NAME VARCHAR2(20 BYTE);
    AGE NUMBER(3);
BEGIN
    NAME := 'TOM';
    AGE := 30;
    DBMS_OUTPUT.PUT_LINE('이름은'||NAME||'입니다.');
    DBMS_OUTPUT.PUT_LINE('나이는'||AGE||'살입니다.');
END;

참조 변수

: 특정 칼럼의 타입을 명시

DECLARE
    EMPLOYEEID EMPLOYEES.EMPLOYEE_ID%TYPE; 
    --변수명 테이블.칼럼%TYPE (변수가 칼럼이랑 동일한 타입이라는 뜻)
    FIRSTNAME EMPLOYEES.FIRST_NAME%TYPE;
    LASTNAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
    SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME
      INTO EMPLOYEEID, FIRSTNAME, LASTNAME 
      --EMPLOYEE_ID는 EMPLOYEEID 변수에 넣어달라
      --FIRST_NAME도 FIRSTNAME에 LAST_NAME도 LASTNAME에 이런 뜻
      FROM EMPLOYEES
     WHERE EMPLOYEE_ID = 100;
    DBMS_OUTPUT.PUT_LINE(EMPLOYEEID || ',' || FIRSTNAME || ',' || LASTNAME);
END;

IF문

    IF 조건식 1 THEN
        실행문1
    ELSIF 조건식 2 THEN
        실행문2
    ...
    ELSE
        실행문N
    END IF;
-- 1. 스칼라 변수를 이용한 IF문 처리
DECLARE 
    SCORE NUMBER(3);
    GRADE VARCHAR2(1 BYTE);
BEGIN
    SCORE := 50; --임의의 점수 저장
    IF SCORE >= 90 THEN 
        GRADE := 'A';
    ELSIF SCORE >= 80 THEN
        GRADE := 'B';
    ELSIF SCORE >= 70 THEN
        GRADE := 'C';
    ELSIF SCORE >= 60 THEN
        GRADE := 'D';
    ELSE
        GRADE := 'F';
    END IF;
    DBMS_OUTPUT.PUT_LINE(SCORE || '점은' || GRADE || '학점입니다.');
END;
-- 2. 참조 변수를 이요한 IF문 처리
DECLARE
    EMPLOYEEID EMPLOYEES.EMPLOYEE_ID%TYPE;
    SAL        EMPLOYEES.SALARY%TYPE;
BEGIN
    EMPLOYEEID := 150; -- 임의의 사원번호 저장
    SELECT SALARY
      INTO SAL
      FROM EMPLOYEES
     WHERE EMPLOYEE_ID = EMPLOYEEID;
    IF SAL >= 10000 THEN
        DBMS_OUTPUT.PUT_LINE('고액연봉');
    ELSE
        DBMS_OUTPUT.PUT_LINE('보통연봉');
    END IF;
END;

CASE문

    CASE
        WHEN 조건식1 THEN 
            실행문1
        WHEN 조건식2 THEN
            실행문2
        ...
        ELSE 
            실행문N
    END CASE;
-- 연봉이 가장 높은 사원의 PHONE_NUMBER에 따라 다음을 출력
-- 011 : MOBILE
-- 515 : EAST
-- 590 : WEST
-- 603 : SOUTH
-- 650 : NORTH
DECLARE
    PHONENUMBER EMPLOYEES.PHONE_NUMBER%TYPE; --참조
    MESSAGE     VARCHAR2(6 BYTE);  --스칼라
BEGIN
    SELECT SUBSTR(PHONE_NUMBER,1,3)
      INTO PHONENUMBER
      FROM EMPLOYEES
     WHERE SALARY = (SELECT MAX(SALARY)
                       FROM EMPLOYEES);
    CASE 
        WHEN PHONENUMBER = '011' THEN 
            MESSAGE := 'MOBILE';
        WHEN PHONENUMBER = '515' THEN 
            MESSAGE := 'EAST';
        WHEN PHONENUMBER = '590' THEN 
            MESSAGE := 'WEST';
        WHEN PHONENUMBER = '603' THEN 
            MESSAGE := 'SOUTH';
        WHEN PHONENUMBER = '650' THEN
            MESSAGE := 'NORTH';
    END CASE;
    DBMS_OUTPUT.PUT_LINE(MESSAGE);
END;

WHILE문

    WHILE 조건식 LOOP
        실행문
    END LOOP;
-- 1~5 출력
DECLARE
    N NUMBER(1);
BEGIN
    N := 1;
    WHILE N <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE(N);
        N := N+1;
    END LOOP;
END;
-- 사원번호가 100~109 사이의 사원번호, 사원명 출력
DECLARE
    EMPLOYEEID EMPLOYEES.EMPLOYEE_ID%TYPE;
    FIRSTNAME EMPLOYEES.FIRST_NAME%TYPE;
    LASTNAME EMPLOYEES.LAST_NAME%TYPE;    
BEGIN
    EMPLOYEEID := 100;
    WHILE EMPLOYEEID <= 109 LOOP
        SELECT FIRST_NAME, LAST_NAME
          INTO FIRSTNAME, LASTNAME
          FROM EMPLOYEES
         WHERE EMPLOYEE_ID = EMPLOYEEID;
        DBMS_OUTPUT.PUT_LINE(FIRSTNAME||' '||LASTNAME);
        EMPLOYEEID := EMPLOYEEID+1;
    END LOOP;
END;

FOR문

    FOR 변수 IN 시작값..종료값 LOOP
        실행문
    END LOOP;
-- 1~5 출력
DECLARE 
    N NUMBER(1);
BEGIN
    FOR N IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(N);
    END LOOP;
END;
-- FOR문이 더 쉽지만 DB데이터 가져와야하는데 그 숫자를 모르는 채로 가져올 때 등 WHILE문 써야할 때도 있음

EXIT

  • 반복문 (WHILE, FOR) 종료하기
-- 1부터 누적. 누적 결과가 100 넘으면 누적 종료 후 출력
DECLARE
    N NUMBER;
    TOTAL NUMBER;
BEGIN
    -- 초깃값
    N := 1;
    TOTAL := 0;
    -- 무한루프 (끝내고 싶을 때 EXIT를 적어줌)
    WHILE TRUE LOOP
        IF TOTAL > 100 THEN 
            EXIT;
        END IF;
        TOTAL := TOTAL + N;
        N := N+1;
    END LOOP;
    -- 결과 확인
    DBMS_OUTPUT.PUT_LINE(N || ',' || TOTAL);
END;

CONTINUE

  • 반복문의 시작부터 다시 실행하기 (무언가를 제외하고 싶을 때 씀)
-- 3의 배수는 제외하고 1부터 누적. 누적 결과가 100이 넘어가면 그만 누적하고 결과 출력
DECLARE 
    N NUMBER;
    TOTAL NUMBER;
    MODULAR NUMBER;
BEGIN
    -- 초깃값
    N := 0;
    TOTAL := 0;
    MODULAR := 0;
    -- 무한루프
    WHILE TRUE LOOP
        -- N의 증가
        N := N+1; 
        -- CONTINUE로 올라온 숫자도 증가시켜주기 위해 증가문을 위에 적음 (아니면 무한루프 빠지니까)
        -- 누적 결과가 100이 넘어가면 그만 누적
        IF TOTAL > 100 THEN 
            EXIT;
        END IF;
        -- 3의 배수는 누적에서 제외하기
        SELECT MOD(N, 3) INTO MODULAR -- MOD(N,3): N을 3으로 나눈 나머지 -> MODULAR 변수에 저장
          FROM DUAL;
        IF MODULAR = 0 THEN 
            CONTINUE; 
            -- WHILE TRUE LOOP문의 첫 실행문으로 되돌아가서 실행하시오 (누적단계까지 못가도록)
        END IF;
        -- 누적
        TOTAL := TOTAL + N;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(N || ',' || TOTAL);
END;

예외처리

    EXCEPTION
        WHEN 예외종류1 THEN
            예외처리1
        WHEN 예외종류2 THEN
            예외처리2
        ...
        WHEN OTHERS THEN
            예외처리N
-- 데이터를 찾지 못하는 경우의 예외 : NO_DATA_FOUND
DECLARE
    EMPLOYEEID EMPLOYEES.EMPLOYEE_ID%TYPE;
    FIRSTNAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
    EMPLOYEEID := 0; --EMPLOYEEID(EMPLOYEE_ID) 값이 0인건 없어서 오류 발생
    SELECT FIRST_NAME INTO FIRSTNAME
      FROM EMPLOYEES
     WHERE EMPLOYEE_ID = EMPLOYEEID;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE(EMPLOYEEID || '번 사원은 없습니다.');
END;
-- 데이터가 너무 많은 경우 : TOO_MANY_ROWS
DECLARE
    FIRSTNAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
    SELECT FIRST_NAME INTO FIRSTNAME
      FROM EMPLOYEES
     WHERE EMPLOYEE_ID BETWEEN 100 AND 206;
EXCEPTION
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('결과가 너무 많다.');
END;
-- 모든 예외를 처리하는 방법 : OTHERS
DECLARE
    FIRSTNAME EMPLOYEES.FIRST_NAME%TYPE;
BEGIN
    SELECT FIRST_NAME INTO FIRSTNAME
      FROM EMPLOYEES
     WHERE EMPLOYEE_ID BETWEEN 100 AND 206;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE); -- 예외코드 확인 가능
        DBMS_OUTPUT.PUT_LINE(SQLERRM); -- 예외메시지 확인 가능    
END;

프로시저(Procedure)

  1. 여러 쿼리문을 한 번에 수행할 수 있다.
  2. "EXECUTE 프로시저명()" 형식으로 실행할 수 있다. (프로시저 호출)
  3. 형식
    CREATE [OR REPLACE] PROCEDURE 프로시저명
    AS             -- IS를 사용해도 무방
        변수 선언  -- 생략 가능
    BEGIN
        프로시저본문
    [EXCEPTION 예외처리]
    END;
-- 프로시저 정의(프로시저 만들기)
CREATE OR REPLACE PROCEDURE MY_PROC_01
AS
    NAME VARCHAR2(20 BYTE);
BEGIN
    NAME := 'tom';
    DBMS_OUTPUT.PUT_LINE(NAME || '님 환영합니다.');
END;

-- 프로시저 호출(프로시저 실행하기)
EXECUTE MY_PROC_01();

보충수업 내용
PLSQL
프로시저
사용자함수
트리거

🟢 구디아카데미후기 IT국비지원 민경태 강사님 수업 11일차! 🟢
너무 어려운 내용이었다고 한다. 난 첫날 보충인 PLSQL밖에 듣지 못해서 뒷내용은 혼자 열심히 공부해야할듯....!

profile
MJ
이전 포스트

[230711] DB 연습문제 (DAY 10) - 구디아카데미후기 IT국비지원 민경태 강사님

다음 포스트

[230712] java 설치, 이클립스 설치, 설정/프로그램작성/식별자/키워드 규칙/데이터 타입/변수/상수/리터럴/이스케이프/타입변환/연산자 (DAY 11) - 구디아카데미후기 IT국비지원 민경태 강사님

0개의 댓글