[Day +45 / SQL]SYNONYM, PL

210825 수

SYNONYM(동의어)
: 다른 DB가 가진 객체에 대한 별명 혹은 줄임말
-여러 사용자가 테이블을 공유할 경우 다른 사용자가 테이블에 접근할 때 '사용자명.테이블명'으로 표현하는데 이 때 동의어를 사용하면 간단하게 사용할 수 있음

1) 동의어 구분
(1) 비공개 동의어 : 객체에 대한 접근 권한을 부여받은 사용자가 정의한 동의어로 해당 사용자만 사용 가능
(2) 공개 동의어 : 모든 권한을 주는 사용자가 정의한 동의어는 모든 사용자가 사용할 수 있음 (EX. DUAL)

2) SYNONYM 생성 방법 (비공개 동의어) -> 해당 사용자 계정에서 만드는 법
[표현식]

CREATE SYNONYM 별명(줄임말)
FOR 사용자명.객체명;

2-1) SAMPLE 계정으로 접속하여 실행

SELECT *
FROM KH.EMPLOYEE;

2-2) SELECT 권한 다시 부여 받기 (SYS 계정에서 실행)

GRANT SELECT ON KH.EMPLOYEE TO SAMPLE;

2-3) KH.EMPLOYEE 테이블의 별명을 EMP로 지정

CREATE SYNONYM EMP
FOR KH.EMPLOYEE;
--"insufficient privileges" 오류 발생

2-4) SYSNONYM 객체 생성 권한을 부여 받아야 함(SYS 계정에서 실행)

GRANT CREATE SYNONYM TO SAMPLE;

2-5) 동의어로 조회(여기서 가능)

SELECT * FROM EMP;

--> 앞으로 KH 계정의 EMPLOYEE 테이블을 EMP라는 동의어로 사용 가능
단, SAMPLE 계정에서만 사용 가능. 다른 계정에서는 오류 발생

3) SYNONYM 생성 방법(공개 동의어) -> 시스템 계정에서 만드는 방법
[표현식]

CREATE PUBLIC SYNONYM 별명(줄임말)
FOR 사용자명.객체명;

3-1) SYS 계정으로 실행

CREATE PUBLIC SYNONYM DEPT
FOR KH.DEPARTMENT;

SELECT * FROM DEPT;
--insufficient privileges 오류 실행

Why? SYS계정의 경우 조회 가능. SAMPLE 계정의 경우 SELECT DEPARTMENT 권한이 없어서 오류 발생. 권한 부여 받으면 공개 동의어 사용 가능

GRANT SELECT
ON KH.DEPARTMENT TO SAMPLE;

SELECT * FROM DEPT;
--지금 조회 가능

4) 동의어 삭제

DROP SYNONYM EMP;

SELECT * FROM EMP;

--공개 동의어 삭제(SYS 계정으로 실행해야 삭제 가능)
DROP PUBLIC SYNONYM DEPT;

SELECT * FROM DEPT;

PL / SQL(PROCEDURAL LANGUAGE EXTENSION TO SQL)
: SQL에 대한 절차적 언어 확장
-오라클 자체에 내장되어 있는 절차적 언어(PROCEDURE LANGUAGE)
-SQL 문장 내에서 변수의 정의, 조건 처리, 반복 처리 등을 지원하여 SQL의 단점 보완

*PL/SQL 구조
-선언부(DECLARE SECTION) : DECLARE로 시작, 변수나 상수를 선언하는 부분
-실행부(EXCUTABLE SECTION) : BEGIN으로 시작, 제어문, 반복문, 함수 등의 로직을 기술
-예외처리부(EXCEPTION SECTION) : EXCEPTION으로 시작, 예외 상황 발생 시 해결하기 위한 문장 기술

<간단하게 HELLO WORLD 화면에 출력>

BEGIN 
    DBMS_OUTPUT.PUT_LINE('HELLO WORLD');
    END;
/

--DBMS_OUTPUT 패키지에 포함되어 있는 PUT_LINE이라는 프로시저를 이용하여 출력하는 기능
--END; 뒤어 '/' 기호는 PL/SQL 블록을 종결시킨다는 의미

Q. HELLO WORLD가 출력 안되는 이유
A. 프로시저 사용 시 출력하는 내용을 화면에 보여주도록 설정하는 환경 변수를 ON 시켜줘야 함. 기본값이 OFF임

1. DECLARE
1) 타입 변수 선언
-변수의 선언 및 초기화 변수 값 출력
-[표현식] 변수명 자료형[(크기)];

SET SERVEROUTPUT ON;

-> 기본적으로 PL/SQL은 결과물을 보여주지 않는다. 결과물을 보고 싶다면 SERVEROUTPUT 설정을 ON 으로 설정해 주어야 한다.

DECLARE -- 선언부 시작을 알리는 구문
    EMP_ID NUMBER; --NUMBER 타입의 변수 EMP_ID 선언
    EMP_NAME VARCHAR2(30); --VARCAHR2 타입의 변수 EMP_NAME 선언
    PI CONSTANT NUMBER := 3.14; --NUMBER 타입의 상수 PI 선언 및 초기화

BEGIN -- 실행부 시작을 알리는 구문
    EMP_ID := 888; -- EMP_ID 변수에 값을 888로 초기화
    EMP_NAME := '배장남'; --EMP_NAME 변수에 값을 '배장남'으로 초기화
    
    --PI := 3.15; -> 일부러 오류 발생시킴
    --expression 'PI' cannot be used as an assignment target
    --오류 발생
    --상수 값은 다시 초기화 불가
    
     -- 변수 출력(문자열 연결 연산자 ||)
    DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP_ID);
    DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
END;
/

2) 레퍼런스 변수의 선언과 초기화, 변수값 출력
[표현법] 변수명 테이블명.컬럼명%TYPE

DECLARE
    EMP_ID EMPLOYEE.EMP_ID%TYPE;
    --변수 EMP_ID의 타입을 EMPLOYEE 테이블의 EMP_ID 컬럼 타입으로 지정
    EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
    --변수 EMP_NAME의 타입을 EMPLOYEE 테이블의 EMP_NAME 컬럼 타입으로 지정
BEGIN 
    SELECT EMP_ID, EMP_NAME --테이블에서 SELECT 된 값
      INTO EMP_ID, EMP_NAME --DECLARE에서 선언한 변수에 넣는다
      FROM EMPLOYEE
     WHERE EMP_ID = '&ID';
--'&' 기호가 있는 문자열은 대체 변수를 입력(값을 입력)하라는 의미

DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
END;
/

3) 한 행에 대한 ROWTYPE 변수 선언 초기화
[표현법] 변수명 테이블명 %ROWTYPE;

DECLARE 
    E EMPLOYEE%ROWTYPE;
BEGIN
    SELECT * 
      INTO E
      FROM EMPLOYEE
     WHERE EMP_ID = '&아이디';

    DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || E.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || E.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('EMP_NO : ' || E.EMP_NO);
    DBMS_OUTPUT.PUT_LINE('SALARY : ' || E.SALARY);
END;
/

2. BEGIN
1) 조건문
(1) IF ~ THEN ~ END IF(단일 IF문)
EMP_ID를 입력 받아 해당 사원의 사번, 이름, 급여, 보너스율 출력
단, 보너스를 받지 않는 사원은 보너스율 출력 전 '보너스를 지급 받지 않는 사원입니다'출력

DECLARE
    EMP_ID EMPLOYEE.EMP_ID%TYPE;
    EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
    SALARY EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN 
    SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
      INTO EMP_ID, EMP_NAME, SALARY, BONUS
      FROM EMPLOYEE
     WHERE EMP_ID = '&EMP_ID';
     
     DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP_ID);
     DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP_NAME);
     DBMS_OUTPUT.PUT_LINE('급여 : ' || SALARY);
     
     IF(BONUS = 0)
        THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
     END IF;  
     
     DBMS_OUTPUT.PUT_LINE('보너스율 : ' || BONUS * 100 || '%');
END;
/

(2) IF~THEN~ELSE~END IF(IF~ELSE문)
EMP_ID를 입력 받아 해당 사원의 사번, 이름, 부서명, 소속(NATIONAL_CODE)을 출력 THEN 변수를 만들어 소속이 'KO'인 사원은 '국내팀', 아닌 사원은 '해외팀'으로 출력

DECLARE
    EMP_ID EMPLOYEE.EMP_ID%TYPE;
    EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
    DEPT_TITLE DEPARTMENT.DEPT_TITLE%TYPE;
    NATIONAL_CODE LOCATION.NATIONAL_CODE%TYPE;
    
    TEAM VARCHAR2(20);
BEGIN
    SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
      INTO EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
      FROM EMPLOYEE E, DEPARTMENT D, LOCATION L
     WHERE DEPT_CODE = DEPT_ID
       AND LOCATION_ID = LOCAL_CODE
       AND EMP_ID = '&EMP_ID';
       
       IF(NATIONAL_CODE = 'KO')
        THEN TEAM := '국내팀';
       ELSE TEAM := '해외팀';
       END IF;
       
       DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP_ID);
       DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP_NAME);
       DBMS_OUTPUT.PUT_LINE('부서 : ' || DEPT_TITLE);
       DBMS_OUTPUT.PUT_LINE('국가코드 : ' || NATIONAL_CODE);
       DBMS_OUTPUT.PUT_LINE('소속 : ' || TEAM);
END;
/

(3) IF~THEN~ELSEIF~ELSE~END IF(IF~ELSE IF~ELSE문)
점수를 입력 받아 SCRE 변수에 저장
90점 이상은 'A', 80점 이상은 'B', ..., 60점 미만은 'F'로 조건 처리하여 GRADE 변수에 저장하고 '당신의 점수는 90점이고 학점은 A학점입니다'와 같은 형태로 출력

DECLARE 
    SCORE NUMBER;
    GRADE VARCHAR2(1);
BEGIN 
    SCORE := '&점수';
    
    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;
/

(4) CASE~WHEN~THEN~END(SWITCH문)
사원 번호를 입력하여 해당 사원의 사번, 이름, 부서명을 출력하시오

DECLARE 
    EMP EMPLOYEE%ROWTYPE;
    DNAME VARCHAR2(20);
BEGIN
    SELECT *
    INTO EMP
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    DNAME := CASE EMP.DEPT_CODE
                WHEN 'D1' THEN '인사관리부'
                 WHEN 'D2' THEN '회계관리부'
                  WHEN 'D3' THEN '마케팅부'
                   WHEN 'D4' THEN '국내영업부'
                    WHEN 'D5' THEN '해외영업1부'
                     WHEN 'D6' THEN '해외영업2부'
                      WHEN 'D7' THEN '해외영업3부'
                       WHEN 'D8' THEN '기술지원부'
                        WHEN 'D9' THEN '총무부'
                    END;
        DBMS_OUTPUT.PUT_LINE(EMP.EMP_ID || ' ' 
        || EMP.EMP_NAME || ' ' || DNAME);
END;
/

2) 반복문
(1) BASIC LOOP
-내부에 처리문을 작성하고 마지막에 LOOP를 벗어날 조건 명시

[표현식]

LOOP 
    처리문
    조건문
END LOOP;

[조건문(2가지 표현)]

IF 조건식 THEN EXIT; END IF;
EXIT WHEN 조건식;

(1-1) 1~5까지 순차 출력

DECLARE
    N NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(N);
        N := N + 1;
        
        --IF N > 5 THEN EXIT;
        --END IF;
        EXIT WHEN N > 5;
    END LOOP;
END;
/

(2) FOR LOOP
[표현식]

    FOR 인덱스 IN [REVERSE] 초기값.. 최종감
    LOOP
        처리문
    END LOOP;

2-1> 1~5까지 순서대로 출력

BEGIN
    FOR N IN 1...5
    LOOP
        DBMS_OUTPUT.PUT_LINE(N);
        END LOOP;
END;
/

2-2> REVERSE 옵션

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

2-3> 반복문을 이용한 데이터 삽입
테스트 테이블 생성 후 순서대로 데이터 삽입하기

CREATE TABLE TEST1(
    NUM NUMBER(3),
    TODAY DATE
);

BEGIN
    FOR I IN 1..100
    LOOP
        INSERT INTO TEST1 VALUES(I, SYSDATE);
    END LOOP;
END;
/

SELECT * FROM TEST1;

*중첩 반복문
구구단 출력

DECLARE
    RESULT NUMBER;
BEGIN 
    FOR DAN IN 2..9
    LOOP 
        FOR SU IN 1..9
        LOOP
            RESULT := DAN * SU;
            DBMS_OUTPUT.PUT_LINE(DAN || ' X ' || SU || ' = ' || RESULT);
        END LOOP;
    END LOOP;
END;
/

(3) WHILE LOOP
[표현식]

    WHILE 조건
    LOOP 처리문
    END LOOP;

3-1> 1~5 순서대로 출력

DECLARE 
    N NUMBER := 1;
BEGIN 
    WHILE N <= 5
    LOOP
        DBMS_OUTPUT.PUT_LINE(N);
        N := N + 1;
    END LOOP;
END;
/

3-2> WHILE문 구구단

DECLARE 
    RESULT NUMBER;
    DAN NUMBER := 2;
    SU NUMBER;
BEGIN
    WHILE DAN <= 9
    LOOP
        SU := 1;
        WHILE SU <= 9
        LOOP
            RESULT := DAN * SU;
            DBMS_OUTPUT.PUT_LINE(DAN || ' X ' || SU || ' = ' || RESULT);
            SU := SU + 1;
        END LOOP;
        DAN := DAN + 1;
    END LOOP;
END;
/

3. 예외 처리부
*예외(EXCEPTION) : 런타임 중 로직 처리 간 발생하는 오류

[표현식]

EXCEPTION
    WHEN 예외명1 THEN 예외처리구문1
    WHEN 예외명2 THEN 예외처리구문2
    ...
    WHEN OTHERS THEN 예외처리구문N

1) 시스템 예외(미리 정의되어 있는 예외)
: 오라클 내부에 미리 정의되어 있는 예외로 따로 선언할 필요 없이 발생 시 예외 절에 자동 트랩됨
(1) NO_DATA_FOUND : SELECT문이 아무런 데이터 행을 반환하지 못할 때
(2) TOO_MANY_ROWS : 하나만 리턴해야 하는 SELECT문이 하나 이상의 행을 리턴했을 때
(3) ZERO_DIVIDE : 0으로 나눌 때
(4) DUP_VAL_ON_INDEX : UNIQUE 제약을 갖는 컬럼에 중복 되는 데이터가 INSERT될 때

1-1) 숫자를 0으로 나눌 경우 예외처리

DECLARE
    NUM NUMBER := 0;
BEGIN
    NUM := 10 / 0;
    DBMS_OUTPUT.PUT_LINE('SUCCESS!');
EXCEPTION
   WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('ZERO_DIVIDE EXCEPTION 발생');
   WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION 발생'); 
END;
/
--divisor is equal to zero 오류 발생

1-2) UNIQUE 제약 조건 위배시

BEGIN
    UPDATE EMPLOYEE
       SET EMP_ID = '&사번'
     WHERE EMP_ID = 201;
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('이미 존재하는 사번입니다.');
END;
/

2) 사용자 정의 예외처리(제공되지 않는 예외처리)
: 미리 정의된 예외를 제외한 모든 예외. 선언부에 선언해야 하고, 예외 발생 시 자동 트랩 됨

*사용자 정의 예외 처리 순서
-STEP 1 : 예외 이름을 선언(DECLARE)
-STEP 2 : PRAGMA EXCEPTION_INIT 문장으로 예외의 이름과 오라클 오류번호 결합(DECLARE)
-STEP 3 : 예외가 발생할 경우 해당 예외를 참조(EXCEPTION)

2-1) EMPLOYEE 테이블에 EMAIL이라는 컬럼에 NOT NULL 조건이 들어가 있지 않아서 NULL값이 저장 가능한 상태. 이메일을 수정할 때 값을 입력하지 않으면 사용자 정의 예외를 발생시키는 구문 작성

DECLARE 
    NEWEMAIL EMPLOYEE.EMAIL%TYPE;
    NN_EMAIL EXCEPTION; --> STEP 1
    PRAGMA EXCEPTION_INIT(NN_EMAIL, -00001); --> STEP2
BEGIN
    NEWEMAIL := '&EMAIL';
    
    IF NEWEMAIL IS NULL
        THEN RAISE NN_EMAIL; --> 예외 발생 시키기
    ELSE
        UPDATE EMPLOYEE
           SET EMAIL = NEWEMAIL
         WHERE EMP_ID = 201;
    END IF;
EXCEPTION
    WHEN NN_EMAIL THEN DBMS_OUTPUT.PUT_LINE('반드시 값을 입력해주세요'); --> STEP 3
END;
/

ROLLBACK;

저작자표시 비영리 변경금지 (새창열림)

'웹개발 수업 > SQL' 카테고리의 다른 글

[Day +46 / SQL]PL_SQL블록, PROCEDURE, FUNCTION, TRIGGER (0)	2021.08.27
[Day +45 / SQL 과제]PL SQL 9문제 (0)	2021.08.25
[Day +44 / SQL 과제]9문제 (0)	2021.08.25
[Day +44 / SQL]시퀀스, DCL, TCL, INDEX (0)	2021.08.24
[Day +43 / SQL]SQL 2차 시험 (0)	2021.08.24

Je m'en fous ; 아무렴 어때?

[Day +45 / SQL]SYNONYM, PL_SQL

'웹개발 수업 > SQL' 카테고리의 다른 글

티스토리툴바

[Day +45 / SQL]SYNONYM, PL_SQL

'웹개발 수업 > SQL' 카테고리의 다른 글

'웹개발 수업/SQL' Related Articles

티스토리툴바