[DB/Oracle] SQL & PL/SQL 기초 완벽 정리 (11g) [3]

Oralce은 많은 기업이랑 개발자가 사용하는 강력한 관계형 데이터베이스 관리 시스템이다.
하지만 유로 버전이기 때문에 직장을 다니지 않는다면 접하지 않는 경우가 많고 입사 후에 처음 접하는 일이 많다.

처음 접하는 사람도 따라가기 쉽게 글을 작성해 보려고 한다.

요번 글에서 진행할 내용은 SQL, PL/SQL이다.

---

1. SQL 기초 개념과 명령어

SQL은 데이터베이스와 상호작용하는 언어로 크게 DDL, DML, DCL 세 가지 명령어로 나눌 수 있다.

1.1 DDL (Data Definition Language) - 데이터 정의

테이블, 뷰, 인덱스 등 데이터베이스 객체를 생성하거나 수정하는 명령어이다.

자동으로 COMMIT 되기 때문에 ROLLBACK이 불가능하다.

(여기서 인덱싱, Transaction에 대해서는 자세히 다루지 않는다.)

• CREATE : 객체 생성
• ALTER : 객체 수정
• DROP : 객체 삭제
• TRUNCATE : 테이블 전체 삭제
• RENAME : 객체 이름 변경
• COMMENT : 주석 추가

데이터형

데이터 타입	설명	특징	예제
VARCHAR2(N)	가변 길이 문자열	VARCHAR도 사용가능하나 자동적으로 VARCHAR2로 변환된다. N은 생략 가능하다.	EMP_NAME VARCHAR2 EMP_NAME2 VARHCAR2(50)
CHAR(N)	고정 길이 문자열	단일 문자등을 다룰 때 사용	GENDER CHAR(1) DEFAULT 'M'
NUMBER(p, s)	숫자 타입, 정밀도와 스케일 지정 ()는 선택이며 p는 정수 자리수 크기 s는 소수점 자리수 크기 이다.		SALARY_NUMBER(10, 2)
DATE	날짜와 시간 저장 7바이트 고정 길이 바이너리 형식으로 저장됨 년,월,일,시,분,초 로 저장		HIRE_DATE DATE ( 2024-06-09 14:30:45 )
TIMESTAMP	날짜와 시간 (초 이하의 정밀도) 11바이트 이상이됨(소수점 이하에 따라 다름) 년,월,일,시,분,초,소수점 이하 초단위		CREATED_AT TIMESTAMP ( 2024-06-09 14:30:45.123456 )
CLOB	대용량 문자열 데이터 저장 최대 4GB까지 저장 가능하다. XML,JSON, 로그 데이터등 문자 데이터만 저장한다.		DESCRIPTION CLOB
BLOB	바이너리 대용량 데이터 저장 이미지, 비디오, 오디오, PDF등 바이너리 데이터 저장할 때 사용 가능		IMAGE BLOB
RAW(N)	고정 길이 바이너리 데이터		PASSWORD RAW(16)

제약 조건

제약조건	설명	특징	예시
NOT NULL	컬럼에 NULL 값을 허용하지 않음	특정 컬럼의 값은 반드시 입력되어야 함	EMP_NAME VARCHAR2(50) NOT NULL
UNIQUE	컬럼 값이 중복되지 않음	중복 불가, NULL은 허용 자동 인덱스가 생성됨	EMP_NAME VARCHAR2(50) UNIQUE
PRIMARY KEY	테이블의 기본 키	NOT NULL + UNIQUE 결합 테이블당 하나만 설정 가능	EMP_ID NUMBER PRIMARY KEY
FOREIGN KEY	다른 테이블의 컬럼을 참조	참조 무결설 보장 부모 테이블 값만 참조 가능 위험하기 떄문에 잘 안씀	DEPT_ID NUMBER REFERENCES DEPT(DEPT_ID)
CHECK	특정 조건을 만족하는 값만 입력 가능하도록 제한	조건식에 따라서 데이터 제한	SALARY NUMBER CHECK (SALARY > 0)
DEFAULT	컬럼에 기본값 설정	데이터 입력 시 값이 없으면 기본값 사용	STATUS VARCHAR2(10) DEFAULT 'ACTIVE'

CREATE

CREATE TABLE 테이블명 (
	컬럼명 데이터형 제약조건,
    컬럼명2 데이터형 제약조건
);
--예시코드
CREATE TABLE EMPLOYEES (
    EMP_ID NUMBER(10) PRIMARY KEY,          -- 기본 키 설정
    EMP_NAME VARCHAR2(50) NOT NULL,         -- NULL 허용하지 않음
    SALARY NUMBER(10, 2),                   -- 소수점 두 자리까지 허용하는 숫자형
    HIRE_DATE DATE DEFAULT SYSDATE,         -- 기본값 설정 (현재 날짜)
    DESCRIPTION CLOB,                       -- 대용량 문자 데이터
    IMAGE BLOB,                             -- 바이너리 데이터
    PASSWORD RAW(16),                       -- 고정 길이 바이너리 데이터
    STATUS CHAR(1) DEFAULT 'A',             -- 고정 길이 문자 타입 (기본값 A)
    CONSTRAINT SALARY_POSITIVE CHECK (SALARY > 0) -- SALARY는 0보다 커야 함
);

ALTER TABLE

ALTER TABLE EMPLOYEES ADD (EMAIL VARCHAR2(100) UNIQUE);
ALTER TABLE EMPLOYEES MODIFY (SALARY NUMBER(12, 2));
ALTER TABLE EMPLOYEES DROP COLUMN PASSWORD;
ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_EMAIL_UQ UNIQUE (EMAIL);
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_EMAIL_UQ;

DROP & TRUNCATE TABLE

DROP TABLE EMPLOYEES;
TRUNCATE TABLE EMPLOYEES;

기능	데이터만 삭제, 테이블 구조는 유지	데이터와 테이블 구조 모두 삭제
속도	매우 빠름	빠름
트랜잭션 롤백	불가능	불가능
인덱스/제약 조건	유지	모두 삭제
공간 반환	테이블 공간 초기화 (구조 유지)	테이블 삭제 후 공간 반환
테이블 구조	유지됨	삭제됨
사용 시나리오	데이터를 초기화하고 테이블을 재사용할 때	테이블을 완전히 제거할 때

RENAME & COMMENT

RENAME EMPLOYEES TO EMPLOYEES_BACKUP;
COMMENT ON TABLE EMPLOYEES IS '직원 정보를 저장하는 테이블';

1.2 DML

주요 명령어

DML의 경우 모두 Transaction이 가능하다.

명령어	설명	기능
SELECT	데이터를 조회	테이블의 데이터를 검색 및 반환
INSERT	데이터를 삽입	테이블에 새로운 행을 추가
UPDATE	데이터를 수정	기존 테이블의 데이터를 변경
DELETE	데이터를 삭제	특정 조건에 맞는 데이터를 삭제
MERGE	데이터를 삽입 또는 수정 (UPSERT)	조건에 따라 데이터 삽입/수정 병합

아래는 예시 코드인데 중간중간 조회해 보면서 어떤 식으로 변화하는지 기억하자

-- 1. EMPLOYEES 테이블에 새로운 데이터 삽입
-- EMP_ID: 101, EMP_NAME: 'John Doe', SALARY: 5500, HIRE_DATE: 현재 날짜(SYSDATE)
INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, SALARY, HIRE_DATE)
VALUES (101, 'John Doe', 5500, SYSDATE);

-- 2. EMP_ID가 101인 직원의 급여(SALARY)를 500 증가시킴
UPDATE EMPLOYEES
SET SALARY = SALARY + 500
WHERE EMP_ID = 101;

-- 3. EMP_ID가 101인 직원을 EMPLOYEES 테이블에서 삭제
DELETE FROM EMPLOYEES
WHERE EMP_ID = 101;

-- 4. 급여(SALARY)가 5000 초과인 직원들의 정보를 조회
-- 조회 컬럼: EMP_ID, EMP_NAME, SALARY, HIRE_DATE, DEPARTMENT_ID
SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE, EMPLOYEES.DEPARTMENT_ID
FROM EMPLOYEES
WHERE SALARY > 5000;

-- 5. EMPLOYEES 테이블에 새로운 행 추가
-- EMP_ID: 102, EMP_NAME: 'Jane Smith', SALARY: 7000
INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, SALARY) 
VALUES (102, 'Jane Smith', 7000);

-- 6. 트랜잭션 저장점 SAVEPOINT 설정
SAVEPOINT sp1;

-- 7. EMP_ID가 102인 직원의 SALARY를 7500으로 수정
UPDATE EMPLOYEES 
SET SALARY = 7500 
WHERE EMP_ID = 102;

-- 8. ROLLBACK: 저장점 sp1까지 롤백
-- SALARY 수정이 취소되고 INSERT 작업만 유지됨
ROLLBACK TO sp1;

-- 9. 트랜잭션 확정: 변경사항(COMMIT) 적용
COMMIT;

SAVEPOIN를 사용하지 않아도 ROLLBACK이 가능하다. 

INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, SALARY)
VALUES (101, 'John Doe', 5500);

UPDATE EMPLOYEES
SET SALARY = 6000
WHERE EMP_ID = 101;

DELETE FROM EMPLOYEES
WHERE EMP_ID = 101;

ROLLBACK;
COMMIT;

이렇게 ROLLBACK이 가능하다.

1.3 DCL

데이터베이스에 대한 권한을 제어하는 명령어이다. 주로 사용자에게 권한을 부여하거나 회수하고 보안을 관리하는 역할을 한다.

Trasaction에 영향을 받지 않으며 자동 COMMIT 된다. DBA(데이터베이스 관리자) 또는 권한을 가진 사용자만 실행할 수 있다.

명령어설명기능사용 예시

명령어	설명	기능	예시
GRANT	특정 사용자에게 권한을 부여	객체(테이블, 뷰 등)에 접근 권한 부여	GRANT SELECT ON EMPLOYEES TO user1;
REVOKE	특정 사용자에게 부여된 권한 회수	기존에 부여한 권한을 취소	REVOKE SELECT ON EMPLOYEES FROM user1;

GRANT 권한명 ON 객체명 TO 사용자명 [WITH GRANT OPTION];

줄 수 있는 권한으로는

SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES 가 있다.

WITH GRANT OPTION를 통해서 권한을 받은 사용자가 다른 사용자에게도 권한을 부여할 수 있다.

REVOKE 권한명 ON 객체명 FROM 사용자명;

권한 회수도 가능하다.

-- 현재 사용자에게 부여된 권한 조회
SELECT * FROM USER_TAB_PRIVS;

-- 다른 사용자에게 부여된 권한 확인
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USER1';

권한 조회도 가능하다

2. PL/SQL

2.1 processer

일련의 SQL 문과 PL/SQL 블록을 묶어서 저장하고 실행할 수 있는 코드 블록이다.

CREATE OR REPLACE PROCEDURE 프로시저명 (파라미터)
IS
BEGIN
    -- 실행할 SQL 문과 로직
END;
/

예시 코드

CREATE OR REPLACE PROCEDURE give_bonus (p_emp_id NUMBER, p_bonus NUMBER) IS
BEGIN
    UPDATE EMPLOYEES
    SET SALARY = SALARY + p_bonus
    WHERE EMP_ID = p_emp_id;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('보너스 넣기 성공');
END;
/

BEGIN
    give_bonus(102, 500);
END;
/

함수의 역할을 한다.

JPA와 프로시저 비교

항목	JPA 쿼리	저장 프로시저
유지보수성	코드 레벨에서 관리 가능	DB에 저장되므로 애플리케이션 코드와 분리됨
가독성	코드 안에서 SQL을 명확하게 작성	SQL이 DB에 있어 코드에서 확인 어려움
유연성	복잡한 로직도 코드로 쉽게 구현 가능	수정하려면 DB에서 변경 필요
성능 최적화	JPA가 자동으로 최적화 (JPQL, Native Query)	네트워크 오버헤드를 줄여 빠름
로직 변경	코드에서 빠르게 수정 가능	프로시저 수정 시 배포와 적용이 필요
재사용성	코드 기반에서 유연하게 재사용 가능	여러 시스템에서 호출 가능

2.2 VIEW

SQL 쿼리의 결과를 저장하는 논리적 객체이다.

실제 테이블의 데이터와 항상 동기화되어있다. 데이터를 저장하는 방식이 아니다

CREATE VIEW emp_view AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 5000;

SELECT * FROM emp_view;

INSERT INTO employees (emp_id, emp_name, salary)
VALUES (103, 'Alice', 8000);

 

2.3 Triger

트리거는 특정 이벤트 (INSERT, UPDATE, DELETE) 같은 게 발생했을 때 자동으로 실행되는 PL/SQL 블록이다.

트리거는 크게 3가지 종류가 있다. 여기서는 간단하게 BEFORE랑 AFTER만 알아보겠다.

• BEFORE : DML 작업 전에 실행
• AFTER : DML 작업 후에 실행
• INSTEAD OF : VIEW에 대해 대신 실행

BEFORE

DML 시작하기 전에 조건을 검사하는 예시를 만들었다.

CREATE OR REPLACE TRIGGER trg_before_insert_salary
    BEFORE INSERT ON EMPLOYEES
    FOR EACH ROW
BEGIN
    IF :NEW.SALARY < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '급여는 음수가 될 수 없습니다!');
    END IF;
END;
/

트리거에 걸리는 예시 삽입

INSERT INTO EMPLOYEES (EMP_ID, EMP_NAME, SALARY, HIRE_DATE)
VALUES (106, 'EMP_106', -6000, SYSDATE);

AFTER

누가 언제 가입했는지 감사 테이블을 만들고 시퀀스로 ID값을 만들어준다.

(시퀀스는 아래서 다룬다.)

-- 감사 테이블 생성
CREATE TABLE EMPLOYEES_AUDIT (
                                 AUDIT_ID NUMBER PRIMARY KEY,
                                 EMP_ID NUMBER,
                                 ACTION_DATE TIMESTAMP DEFAULT SYSTIMESTAMP,
                                 ACTION_TYPE VARCHAR2(50)
);

CREATE SEQUENCE AUDIT_ID_SEQ
    START WITH 1          -- 시작 값
    INCREMENT BY 1        -- 증가 값
    NOCACHE;

CREATE OR REPLACE TRIGGER trg_employees_audit_id
    BEFORE INSERT ON EMPLOYEES_AUDIT
    FOR EACH ROW
BEGIN
    :NEW.AUDIT_ID := AUDIT_ID_SEQ.NEXTVAL;
END;
/

트리거를 생성해서 EMPLOYESS 테이블에 값이 INSERT 되면 자동으로 작동해 기록하도록 한다.

CREATE OR REPLACE TRIGGER trg_after_insert_audit
    AFTER INSERT ON EMPLOYEES
    FOR EACH ROW
BEGIN
    INSERT INTO EMPLOYEES_AUDIT (EMP_ID, ACTION_TYPE)
    VALUES (:NEW.EMP_ID, 'INSERTED');
END;
/

2.4 sqeunce

시퀀스는 자동으로 순차적인 숫자를 생성하는 Oracle 객체이다.

primary key 값 같은걸 자동으로 증가시켜 줄 때 사용하면 좋다.

시퀀스는 여러 테이블에서 공유해서 사용할 수도 있다.

CREATE SEQUENCE global_seq
    START WITH 1
    INCREMENT BY 1
    NOCYCLE;

-- 첫 번째 테이블: orders
CREATE TABLE orders (
                        order_id NUMBER PRIMARY KEY,
                        order_name VARCHAR2(50),
                        created_date DATE DEFAULT SYSDATE
);

-- 두 번째 테이블: products
CREATE TABLE products (
                          product_id NUMBER PRIMARY KEY,
                          product_name VARCHAR2(50),
                          price NUMBER(10,2)
);

-- orders 테이블에 데이터 삽입
INSERT INTO orders (order_id, order_name)
VALUES (global_seq.NEXTVAL, 'Order A');

INSERT INTO orders (order_id, order_name)
VALUES (global_seq.NEXTVAL, 'Order B');

-- products 테이블에 데이터 삽입
INSERT INTO products (product_id, product_name, price)
VALUES (global_seq.NEXTVAL, 'Product X', 1000);

INSERT INTO products (product_id, product_name, price)
VALUES (global_seq.NEXTVAL, 'Product Y', 1500);

시퀀스를 2개의 테이블에서 공유해서 사용하기 때문에 앞에서 사용한 테이블은 1,2 가 기록됐고 뒤에서 사용한 테이블에는 3,4가 기록된 걸 확인할 수 있다.
근데 시퀀스는 자신의 것만 기억하고 있기 때문에 임의로 값을 insert 할 경우 문제가 생긴다.

INSERT INTO orders (order_id, order_name)
VALUES (5, 'Manual Insert');

INSERT INTO orders (order_id, order_name)
VALUES (global_seq.NEXTVAL, 'Order C');

이렇게 넣어줬을 경우 다음 sequnce 과정에서

[2024-12-09 13:43:27] [23000][1] ORA-00001: unique constraint (PRACTICE.SYS_C007003) violated
[2024-12-09 13:43:27] Position: 0

이런 에러를 출력시키게 된다.

그럼 이걸 어떻게 해결할 수 있을까?

CREATE OR REPLACE TRIGGER trg_force_sequence
    BEFORE INSERT ON orders
    FOR EACH ROW
BEGIN
    -- 사용자가 값을 넣으려고 해도 시퀀스를 강제 사용
    :NEW.ORDER_ID := global_seq.NEXTVAL;
END;
/
INSERT INTO orders (order_name) VALUES ('Order A');

사용자가 값을 넣으려고 해도 시퀀스를 강제적으로 사용하게 만들면 된다.

INSERT INTO orders (order_id, order_name)
VALUES (5, 'TEST');

이렇게 삽입을 진행해도 정상적으로 SEQENCE 값으로 저장된다.