DAY 33 - ORACLE DB - SYNONYM / PL/SQL( 조건제어문) (2024.08.19)

[ SYNONYM ]

- Synonym은 오라클 객체(테이블, 뷰, 시퀀스, 프로시저)에 대한 대체이름(Alias)를 말한다

- Synonym은 Object가 아니라 Object에 대한 직접적인 참조이다

- 데이터베이스의 투명성을 제공하기 위해서 사용 한다

   다른 유저의 객체를 참조할 때 많이 사용 한다

- 객체의 긴 이름을 짧게 만들어 SQL 코딩을 단순화 할 수 있다

- 객체의 실제 이름, 소유자, 위치를 감추기 때문에 데이터베이스의 보안을 유지할 수 있다

---

* 종류

Private Synonym

전용 Synonym은 특정 사용자만 사용할 수 있다

 

Public Synonym

공용 Synonym은 사용자 그룹이 소유하면 그 데이터베이스에 있는 모든 사용자가 공유한다

---

[형식]

CREATE [PUBLIC] SYNONYM 시노님이름 FOR 객체이름

 

CREATE USER / DROP USER

CREATE TABLE / DROP TABLE

CREATE SEQUENCE / DROP SEQUENCE

CREATE NIEW / DROP VIEW

DELETE와 차이알기 !

---

[실습]

1. HR 계정으로 접속해서 C##JAVA 계정에게 EMPLOYEES 테이블을 조작할 수 있는 권한 부여

 

HR 계정에서 실습

grant all on employees to c##java;

---

2. C##JAVA 계정에 접속해서 Synonym(동의어)를 생성

    hr계정의 employees 테이블을 java계정에서 hr_emp 동의어로 사용한다

 

CREATE SYNONYM Synonym이름 FOR 다른 계정의 테이블명

 

C##JAVA 계정에서 실습

create synonym hr_emp for hr.employees;

SYNONYM을 만들 수 있는 권한을 준 적이 없기 때문에 오류가 뜬다.

 

★ 먼저 SYNONYM를 생성할 수 있는 권한이 있어야 한다

     SYSTEM 계정(관리자 계정)에서 권한을 부여한다

 

SYSTEM 계정에서 실습

grant create synonym to c##java;

 

 

다시 C##JAVA 계정에서

create synonym hr_emp for hr.employees;

select * from user_synonyms;

 

 

3. 쿼리

select * from hr.employees;

이런 식으로 사용하면 SQL문이 길어질 때 테이블명이 길어서 문제가 되고 다른 스키마(계정)에 있는 객체의 위치를 알려주게 되어 보안상 안 좋다

 

select * from hr_emp; -- Synonym 이용

Synonym 이름을 짧게 하여 SQL문 길이도 줄이고 보안유지도 되기 때문에 사용 한다

 

4. 삭제

DROP SYNONYM 시노님명

drop synonym hr_emp;

 

---

select * from user_synonyms;

Synonym 동의어가 삭제된 것을 확인할 수 있다

 

---

[문제] C##JAVA 계정에서 HR 계정의 DEPARTMENTS 테이블의 시노님(HR_DEP)을 생성하시오

 

CREATE SYNONYM HR_DEP FOR HR.DEPARTMENTS;

---

PL/SQL (Procedural Language extension to SQL)

PL/SQL 이란?

SQL만으로는 구현이 어렵거나 구현 불가능한 작업을 수행하기 위해 오라클에서 제공하는 프로그래밍 언어이다.

변수, 조건, 반복 처리 등 다양한 기능을 사용할 수 있다.

 

블록(Block) 이란?

PL/SQL은 데이터베이스 관련 특정 작업을 수행하는 명령어와 실행에 필요한 여러 요소를 정의하는 명령 어 등으로 구성된다. 이러한 명령어들을 모아 둔 PL/SQL 프로그램의 기본 단위를 블록이라고 한다.

 

---

[형식] - BEGIN END는 생략하면 안된다.

DECLARE

         선언부 – 변수, 상수, 커서 등을 선언

                      - 생략 가능

BEGIN

         실행하는 명령어

EXCEPTION

          예외처리 – 생략 가능

END;

 

- DECLARE, BEGIN, EXCEPTION 에는 ;(세미콜론)을 사용하지 않는다.

- 주석으로는 --, /* */ 를 사용한다

---

[실습]

② SQLDEVELOPER

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello PL/SQL!!');
END;

---

SET SERVEROUTPUT ON;

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello PL/SQL!!');
END;

---

① CMD

SQL> SET SERVEROUTPUT ON; -- PL/SQL 결과를 화면에 출력

SQL> BEGIN

        2 DBMS_OUTPUT.PUT_LINE('Hello PL/SQL!!');

        3 END;

        4 / -- CMD 창에서 실행할 때

Hello PL/SQL!!

 

PL/SQL 처리가 정상적으로 완료되었습니다.

---

변수

- 데이터를 일시적으로 저장하는 요소이다.

- 선언부에 작성한다.

- 선언한 변수에 값을 할당하기 위해서는 :=를 사용한다.

 

[형식]

변수명 자료형 := 값 또는 표현식

 

변수명

- 같은 블록 안에서는 중복될 수 없다.

- 대소문자를 구별하지 않는다.

- 이름은 문자로 시작해야 한다. (한글도 가능)

- 30 byte (영어는 30자, 한글은 15자)

- 영문자, 한글, 숫자, 특수문자 ($, #, _) 사용할 수 있다.

 

---

[실습]

DECLARE
    NAME VARCHAR2(10);
    AGE NUMBER(4) := 25;
BEGIN
    NAME := '홍길동';
    DBMS_OUTPUT.PUT_LINE('NAME : ' || NAME);
    DBMS_OUTPUT.PUT_LINE('AGE : ' || AGE);
END;

---

변수의 자료형

- 변수에 저장할 데이터가 어떤 종류인지를 정하기 위해 사용된다

- 스칼라(scalar), 복합(composite), 참조(reference), LOB(Large OBject)로 구분된다

1. 스킬라형

- 숫자, 문자열, 날짜 등과 같이 오라클에서 기본으로 정의해 놓은 자료형

 

NUMBER : 소수점을 포함할 수 있는 최대 38자리 숫자 데이터

CHAR / VARCHAR2

DATE

BOOLEAN (PL/SQL에서만 사용할 수 있는 논리 자료형, true, false, NULL을 포함)

 

2. 참조형

- 오라클 데이터베이스에 존재하는 특정 테이블 열의 자료형이나 하나의 행 구조를 참조하는 자료형이다.

- 열을 참조할 때는 %TYPE, 행을 참조할 때는 %ROWTYPE을 사용한다

 

[형식]

변수명 테이블명.열이름%TYPE;

변수명 테이블명%ROWTYPE;

 

3. 복합형

- 여러 종류 및 개수의 데이터를 저장하기 위해 사용자가 직접 정의하는 자료형이다.

- 컬렉션, 레코드로 구분된다.

 

4. LOB형

- 대용량의 텍스트, 이미지, 동영상, 사운드 데이터 등 대용량 데이터를 저장하기 위한 자료형이다.

- BLOB, CLOB 등이 있다

 

---

[실습]

SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_ID = 50;

 

DECLARE
    V_NO DEPARTMENTS.DEPARTMENT_ID%TYPE := 50;
BEGIN
    DBMS_OUTPUT.PUT_LINE('V_NO : ' || V_NO);
END;

 

* V_NO 변수는 DEPARTMENT_ID와 같은 자료형 및 크기가 지정된다

---

[실습]

DECLARE
    V_DEPT DEPARTMENTS%ROWTYPE;
    -- V_DEPT 변수를 DEPARTMENTS 테이블의 행 구조로 선언
BEGIN
    SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID INTO V_DEPT
    FROM DEPARTMENTS 
    WHERE DEPARTMENT_ID = 50;
    -- INTO V_DEPT를 사용하여 SELECT 한 결과를 V_DEPT에 대입한다.
    -- V_DEPT가 가지고 있는 필드 개수, 자료형은 SELECT한 결과의 개수와 자료형이 같아야 한다.
    
    DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID : ' || V_DEPT.DEPARTMENT_ID);
    DBMS_OUTPUT.PUT_LINE('DEPARTMENT_NAME : ' || V_DEPT.DEPARTMENT_NAME);
    DBMS_OUTPUT.PUT_LINE('MANAGER_ID : ' || V_DEPT.MANAGER_ID);
    DBMS_OUTPUT.PUT_LINE('LOCATION_ID : ' || V_DEPT.LOCATION_ID);
END;

 

---

SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
FROM DEPARTMENTS 
WHERE DEPARTMENT_ID = 50;

/를 써줘야 위에 문장과 구분이 되고 정상 실행되는 것을 확인할 수 있다.

/
SELECT DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID
FROM DEPARTMENTS 
WHERE DEPARTMENT_ID = 50;

---

어차피 모든 컬럼을 다 뽑아오는 것이므로 *를 통해 문장을 더 간단하게 하자!

SELECT *
FROM DEPARTMENTS 
WHERE DEPARTMENT_ID = 50;

---

DECLARE
    V_DEPT DEPARTMENTS%ROWTYPE;
    -- V_DEPT 변수를 DEPARTMENTS 테이블의 행 구조로 선언
BEGIN
    SELECT * 
    INTO V_DEPT
    FROM DEPARTMENTS 
    WHERE DEPARTMENT_ID = 50;
    -- INTO V_DEPT를 사용하여 SELECT 한 결과를 V_DEPT에 대입한다.
    -- V_DEPT가 가지고 있는 필드 개수, 자료형은 SELECT한 결과의 개수와 자료형이 같아야 한다.
    
    DBMS_OUTPUT.PUT_LINE('DEPARTMENT_ID : ' || V_DEPT.DEPARTMENT_ID);
    DBMS_OUTPUT.PUT_LINE('DEPARTMENT_NAME : ' || V_DEPT.DEPARTMENT_NAME);
    DBMS_OUTPUT.PUT_LINE('MANAGER_ID : ' || V_DEPT.MANAGER_ID);
    DBMS_OUTPUT.PUT_LINE('LOCATION_ID : ' || V_DEPT.LOCATION_ID);
END;

---

상수

- 한번 저장한 값이 프로그램이 종료될 때까지 변하지를 않는다

 

[형식]

변수명 CONSTANT 자료형 := 값 또는 표현식

---

변수의 기본값 지정

[형식]

변수명 자료형 DEFAULT 값 또는 표현식

---

변수에 NULL 값 막기

[형식]

변수명 자료형 NOT NULL := 값 또는 표현식

---

[실습]

DECLARE 
    PI CONSTANT NUMBER := 3.141592;
    APPLE VARCHAR2(10) DEFAULT '사과';
    ID VARCHAR2(10) NOT NULL := 'hong';
    PWD VARCHAR2(10) NOT NULL DEFAULT '1234';
BEGIN
    DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
    DBMS_OUTPUT.PUT_LINE('APPLE : ' || APPLE);
    DBMS_OUTPUT.PUT_LINE('ID : ' || ID);
    DBMS_OUTPUT.PUT_LINE('PWD : ' || PWD);
END;

 

---

조건 제어문

1. IF문

 

[형식]

① IF – THEN

② IF – THEN – ELSE

③ IF – THEN – ELSIF

 

[실습]

DECLARE 
    NUM NUMBER := 15;
BEGIN
    IF MOD(NUM, 2) = 1 THEN
        DBMS_OUTPUT.PUT_LINE(NUM || '는 홀수이다');
    END IF;
END;

---

DECLARE
    NUM NUMBER := 16;
BEGIN
    IF MOD(NUM, 2) = 0 THEN
        DBMS_OUTPUT.PUT_LINE(NUM || '는 짝수이다');
    ELSE
        DBMS_OUTPUT.PUT_LINE(NUM || '는 홀수이다.');
    END IF;
END;

---

2. CASE문

① 단순 CASE문

: 비교 기준이 되는 변수 또는 식을 명시한다

② 검색 CASE문

: 비교기준을 명시하지 않고 각각의 WHEN 절에서 조건식을 명시한다

: IF-THEN-ELSIF와 큰 차이가 없다.

 

[형식]

CASE 비교기준

         WHEN 값1 THEN

                명령어;

         WHEN 값2 THEN

                명령어;

         ELSE

                명령어;

END CASE;

 

[형식]

CASE

      WHEN 조건1 THEN

            명령어;

      WHEN 조건2 THEN

             명령어;

      ELSE

            명령어;

END CASE;

---

DECLARE
    SCORE NUMBER := 87;
BEGIN
    IF SCORE >= 90 THEN
        DBMS_OUTPUT.PUT_LINE('A 학점');
    ELSIF SCORE >= 90 THEN
        DBMS_OUTPUT.PUT_LINE('B 학점');
    ELSIF SCORE >= 80 THEN
        DBMS_OUTPUT.PUT_LINE('C 학점');
    ELSIF SCORE >= 70 THEN
        DBMS_OUTPUT.PUT_LINE('D 학점');
    ELSIF SCORE >= 60 THEN
        DBMS_OUTPUT.PUT_LINE('F 학점');
    END IF;
END;

---

DECLARE
    SCORE NUMBER := 87;
BEGIN
    CASE TRUNC(SCORE/10)
        WHEN 9 THEN DBMS_OUTPUT.PUT_LINE('A 학점');
        WHEN 8 THEN DBMS_OUTPUT.PUT_LINE('B 학점');
        WHEN 7 THEN DBMS_OUTPUT.PUT_LINE('C 학점');
        WHEN 6 THEN DBMS_OUTPUT.PUT_LINE('D 학점');
        ELSE DBMS_OUTPUT.PUT_LINE('F 학점');
    END CASE;
END;

---

DECLARE
    SCORE NUMBER := 87;
BEGIN
    CASE 
        WHEN SCORE >= 90 THEN DBMS_OUTPUT.PUT_LINE('A 학점');
        WHEN SCORE >= 80 THEN DBMS_OUTPUT.PUT_LINE('B 학점');
        WHEN SCORE >= 70 THEN DBMS_OUTPUT.PUT_LINE('C 학점');
        WHEN SCORE >= 60 THEN DBMS_OUTPUT.PUT_LINE('D 학점');
        ELSE DBMS_OUTPUT.PUT_LINE('F 학점');
    END CASE;
END;

---

오라클 SQL문에 사용하는 CASE 문은 조건에 따라 특정 결과값을 반환하는 것이면,

PL/SQL 문의 CASE 조건문은 조건에 따라 수행할 작업을 지정할 수 있다는 차이가 있다.

 

SQL문의 CASE는 END로 종료되지만, PL/SQL 문의 CASE 조건문은 END CASE로 종료된다

 

---

반복 제어문

 

[형식]

① 기본 LOOP

[형식]

LOOP

      명령어;

END LOOP;

---

② WHILE LOOP

- 조건이 참이면 반복하고, 거짓이면 반복문을 벗어난다.

 

[형식]

WHILE 조건 LOOP

       명령어;

END LOOP;

---

③ FOR LOOP

- 시작값부터 종료값까지 1씩 증가한다.

- 역순으로 반복해도 시작..끝의 위치는 변하지 않는다.

 

[형식]

FOR i IN 시작값 .. 종료값 LOOP

       명령어;

END LOOP;

 

FOR i IN REVERSE 시작값 .. 종료값 LOOP -- 역순 (0 .. 4 라고 쓰는 것은 변하지 않는다)

       명령어;

END LOOP;

---

④ Cusor FOR LOOP

 

반복문을 중단 / 특정 반복 주기를 건너뛰는 명령어

① EXIT : 수행 중인 반복 종료

② EXIT WHEN : 반복 종료를 위한 조건을 지정

③ CONTINUE : 수행 중인 반복의 현재 주기를 건너뛴다

④ CONTINUE WHEN : 조건식을 지정하고 조건식을 만족하면 현재 반복 주기를 건너뛴다

---

[실습] 기본 LOOP

DECLARE 
    NUM NUMBER := 0;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        NUM := NUM + 1;
        EXIT WHEN NUM > 4;
    END LOOP;
END;

 

---

[실습] WHILE LOOP

DECLARE
    NUM NUMBER := 0;
BEGIN
    WHILE NUM < 4 LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        NUM := NUM + 1;
        EXIT WHEN NUM > 4;
    END LOOP;
END;

DECLARE
    NUM NUMBER := 0;
BEGIN
    WHILE NUM < 4 LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        NUM := NUM + 1;
    END LOOP;
END;

---

[실습] FOR LOOP

BEGIN 
    FOR i IN 0..4 LOOP --i는 선언부에서 정의하는 것이 아니라 FOR LOOP에서 정의한다.
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;

 

---

[실습] CONTINUE WHEN

BEGIN
    FOR i IN 0..4 LOOP
        CONTINUE WHEN MOD(i,2) = 1;
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;

---

[문제] 1~10까지의 숫자 중에서 홀수만 출력하고, 홀수의 합을 구하시오

DECLARE
    SUM NUMBER := 0;
BEGIN 
    FOR i IN 1..10 LOOP
        CONTINUE WHEN MOD(i,2) = 0;
        DBMS_OUTPUT.PUT_LINE(i);
        SUM := SUM + i;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('SUM: ' || SUM);
END;

 

주어진 PL/SQL 코드에서 발생한 오류는 SUM := SUM + i; 라인에서 발생한 것이다.

이 오류는 코드의 SUM이라는 변수 이름이 Oracle SQL의 예약어와 충돌했기 때문이다.

예약어를 변수 이름으로 사용할 때는 혼동을 피하기 위해 다른 이름을 사용해야 한다.

---

DECLARE
    TOTAL NUMBER := 0;
BEGIN 
    FOR i IN 1..10 LOOP
        CONTINUE WHEN MOD(i,2) = 0;
        DBMS_OUTPUT.PUT_LINE(i);
        TOTAL := TOTAL + i;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('TOTAL: ' || TOTAL);
END;

DECLARE
    TOTAL NUMBER := 0;
BEGIN 
    FOR i IN 1..10 LOOP
        IF MOD(i,2) = 1 THEN
        DBMS_OUTPUT.PUT_LINE(i);
        TOTAL := TOTAL + i;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('TOTAL: ' || TOTAL);
END;