Jaeun Choi | SQL 정리

필요할 때마다 검색해서 사용하는 대신, 자주 쓰는 SQL 문법들을 한 번에 정리해 두려고 합니다. Oracle과 MySQL의 문법 차이점을 중심으로, 각 문법에 짧은 예시를 덧붙여 실무나 공부 중에도 빠르게 참고할 수 있도록 구성했습니다. 자주 사용하는 DDL, DML, JOIN, Subquery, 함수, 프로시저, 트리거 등을 비교하여 정리했습니다.

DDL (Data Definition Language)

공통 명령어

CREATE TABLE
ALTER TABLE
DROP TABLE

예시

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY, -- MySQL
    -- Oracle에서는 SEQUENCE를 사용하여 id 관리
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DML (Data Manipulation Language)

-- INSERT
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

-- UPDATE
UPDATE users SET name = 'Bob' WHERE id = 1;

-- DELETE
DELETE FROM users WHERE id = 1;

-- SELECT
SELECT * FROM users;

JOIN (공통)

INNER JOIN

SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

LEFT JOIN

SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

RIGHT JOIN

SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

FULL OUTER JOIN

-- Oracle
SELECT u.name, o.order_date
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- MySQL (대체 방법)
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

NATURAL JOIN

SELECT *
FROM users
NATURAL JOIN orders;

CROSS JOI

SELECT u.name, p.product_name
FROM users u
CROSS JOIN product

Subquery (공통)

WHERE 절

SELECT name FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE order_total > 100
);

SELECT 절

SELECT name,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

FROM 절

SELECT sub.user_id, sub.total
FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) sub;

EXISTS 사용

SELECT name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

DCL (Data Control Language)

-- Oracle
GRANT SELECT, INSERT ON users TO user1;
REVOKE INSERT ON users FROM user1;

-- MySQL
GRANT SELECT, INSERT ON users TO 'user1'@'localhost';
REVOKE INSERT ON users FROM 'user1'@'localhost';

Oracle은 ROLE 기반 권한 관리, WITH GRANT OPTION 등 고급 기능 지원

MySQL은 사용자@호스트 방식, FLUSH PRIVILEGES 필요 가능성 있음

TCL (Transaction Control Language)

BEGIN;
UPDATE users SET age = 35 WHERE name = 'Alice';
COMMIT;

-- 또는
ROLLBACK;

Oracle은 BEGIN … END; 블록 사용

MySQL은 InnoDB 엔진일 경우 자동 트랜잭션 지원

PL/SQL, SQL/PSM - 제어문 (Control Statements)

IF/ELSE문 (PL/SQL 블록) - oracle

BEGIN
    IF v_score >= 90 THEN
        DBMS_OUTPUT.PUT_LINE('A 학점');
    ELSIF v_score >= 80 THEN
        DBMS_OUTPUT.PUT_LINE('B 학점');
    ELSE
        DBMS_OUTPUT.PUT_LINE('C 학점 이하');
    END IF;
END;

IF/ELSE문 (PL/SQL 블록) - mysql

DELIMITER //
CREATE PROCEDURE grade_check(IN v_score INT)
BEGIN
    IF v_score >= 90 THEN
        SELECT 'A 학점';
    ELSEIF v_score >= 80 THEN
        SELECT 'B 학점';
    ELSE
        SELECT 'C 학점 이하';
    END IF;
END;
//
DELIMITER ;

CALL grade_check(85);

CASE문 (PL/SQL 블록) - oracle

BEGIN
    CASE v_grade
        WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
        WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Good');
        WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Pass');
        ELSE DBMS_OUTPUT.PUT_LINE('Fail');
    END CASE;
END;

CASE문 (PL/SQL 블록) - mysql

DELIMITER //

CREATE PROCEDURE grade_case(IN v_grade CHAR(1))
BEGIN
    CASE v_grade
        WHEN 'A' THEN SELECT 'Excellent';
        WHEN 'B' THEN SELECT 'Good';
        WHEN 'C' THEN SELECT 'Pass';
        ELSE SELECT 'Fail';
    END CASE;
END;
//

DELIMITER ;

CALL grade_case('B');

CASE문 (SQL문 안에서 사용) 공통

1. SELECT문에서 CASE

SELECT name,
       CASE
           WHEN score >= 90 THEN 'A'
           WHEN score >= 80 THEN 'B'
           ELSE 'C 이하'
       END AS grade
FROM students;

UPDATE문에서 CASE

UPDATE employees
SET bonus =
    CASE
        WHEN salary >= 5000 THEN salary * 0.2
        ELSE salary * 0.1
    END;

SQL문 안에서 CASE는 값을 조건에 따라 동적으로 변경할 때 자주 사용함.

PL/SQL, SQL/PSM - 반복 제어문 (LOOP, WHILE, FOR)

기본 LOOP - oracle

DECLARE
    v_count NUMBER := 1;
BEGIN
    LOOP
        EXIT WHEN v_count > 5;
        DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
        v_count := v_count + 1;
    END LOOP;
END;

-- 단순 무한 루프 → EXIT 조건으로 종료.

기본 LOOP - mysql

DELIMITER //

CREATE PROCEDURE loop_example()
BEGIN
    DECLARE v_count INT DEFAULT 1;

    my_loop: LOOP
        IF v_count > 5 THEN
            LEAVE my_loop;
        END IF;
        SELECT CONCAT('Count: ', v_count);
        SET v_count = v_count + 1;
    END LOOP my_loop;
END;
//

DELIMITER ;

CALL loop_example();

WHILE LOOP - oracle

DECLARE
    v_count NUMBER := 1;
BEGIN
    WHILE v_count <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
        v_count := v_count + 1;
    END LOOP;
END;

-- 조건을 검사한 후 반복.

WHILE LOOP - mysql

DELIMITER //

CREATE PROCEDURE while_example()
BEGIN
    DECLARE v_count INT DEFAULT 1;

    WHILE v_count <= 5 DO
        SELECT CONCAT('Count: ', v_count);
        SET v_count = v_count + 1;
    END WHILE;
END;
//

DELIMITER ;

CALL while_example();

FOR LOOP - oracle

BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('i: ' || i);
    END LOOP;
END;

-- 시작값 ~ 끝값 범위 안에서 자동 반복.

FOR LOOP - MySQL은 FOR LOOP 없음 → WHILE/LOOP 사용

PL/SQL, SQL/PSM - 예외 제어문 (EXCEPTION)

EXCEPTION - oracle

BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = 9999; -- 없는 ID
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('해당 사원이 존재하지 않습니다.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('기타 에러 발생');
END;

EXCEPTION - MySQL의 HANDLER

DELIMITER //

CREATE PROCEDURE salary_lookup()
BEGIN
    DECLARE v_salary INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        BEGIN
            SELECT '해당 사원이 존재하지 않습니다.' AS message;
        END;

    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = 9999;

    SELECT CONCAT('급여: ', v_salary);
END;
//

DELIMITER ;

CALL salary_lookup();

예상치 못한 오류 상황을 안전하게 처리.

PL/SQL, SQL/PSM - 사용자 정의 함수 (FUNCTION)

Oracle

CREATE OR REPLACE FUNCTION get_user_name(
    uid IN NUMBER
)
RETURN VARCHAR2
IS
    uname VARCHAR2(20);
BEGIN
    IF uid IS NULL THEN
        uname := NULL;
    ELSE
        SELECT user_name INTO uname
        FROM juchoi.users
        WHERE user_id = uid;
    END IF;
    RETURN uname;
EXCEPTION
    WHEN OTHERS THEN
        RETURN '없는 이름';
END;

-- 호출
SELECT get_user_name(1) FROM dual;

MySQL

DELIMITER //

CREATE FUNCTION get_user_name(uid INT)
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    DECLARE uname VARCHAR(255);
    SELECT name INTO uname FROM users WHERE id = uid;
    RETURN uname;
END //

DELIMITER ;

-- 호출
SELECT get_user_name(1);

PL/SQL, SQL/PSM - 프로시저 (PROCEDURE)

Oracle

-- IN 파라미터
CREATE OR REPLACE PROCEDURE add_user_proc(
    param1 IN VARCHAR,
    param2 IN VARCHAR
)
IS
    uid NUMBER := juchoi.user_id.NEXTVAL;
BEGIN
    INSERT INTO juchoi.users VALUES (uid, param1, param2);
END;

-- OUT 파라미터
CREATE OR REPLACE PROCEDURE test(val OUT NUMBER)
IS
BEGIN
    SELECT COUNT(*) INTO val FROM juchoi.users;
END;

-- 파라미터 없는 프로시저
CREATE OR REPLACE PROCEDURE print
IS
    name VARCHAR2(50) := 'juchoi';
BEGIN
    DBMS_OUTPUT.PUT_LINE('Name: ' || name);
END;

-- 호출 (IN)
DECLARE
    PARAM1 VARCHAR2(200);
    PARAM2 VARCHAR2(200);
BEGIN
    PARAM1 := 'proc-juchoitest';
    PARAM2 := 'test';
    ADD_USER_PROC(PARAM1 => PARAM1, PARAM2 => PARAM2);
END;

-- 호출 (OUT)
DECLARE
    val NUMBER;
BEGIN
    TEST(val);
    DBMS_OUTPUT.PUT_LINE('Number of employee: ' || val);
END;

MySQL

DELIMITER //

CREATE PROCEDURE test_proc(
    IN p_name VARCHAR(100),
    OUT total_users INT
)
BEGIN
    INSERT INTO users(name) VALUES(p_name);
    SELECT COUNT(*) INTO total_users FROM users;
END //

DELIMITER ;

-- 호출
CALL test_proc('test_user', @count);
SELECT @count;

PL/SQL, SQL/PSM - TRIGGER (트리거)

Oracle

CREATE OR REPLACE TRIGGER log_user_deletion
AFTER DELETE ON juchoi.users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action) VALUES (:OLD.id, 'DELETE');
END;

MySQL

CREATE TRIGGER log_user_deletion
AFTER DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_logs (user_id, action) VALUES (OLD.id, 'DELETE');
END;

BLOB 데이터 처리

Oracle

빈 BLOB 객체 생성
- empty_blob()을 사용해 BLOB 컬럼에 위치(locator)만 먼저 삽입
외부 파일 경로 설정
- CREATE DIRECTORY로 서버 디렉토리를 정의
- BFILENAME(directory_name, file_name)으로 파일 경로를 BFILE 객체로 생성
파일을 BLOB 컬럼에 로드
- DBMS_LOB.LOADFROMFILE()를 사용해 BFILE → BLOB으로 복사

CREATE DIRECTORY file_dir AS '/path/to/files';
GRANT READ ON DIRECTORY file_dir TO my_user;

INSERT INTO my_table(id, file_data) VALUES (1, empty_blob())
RETURNING file_data INTO :blob_var;

DECLARE
  v_bfile BFILE := BFILENAME('FILE_DIR', 'example.pdf');
BEGIN
  DBMS_LOB.FILEOPEN(v_bfile);
  DBMS_LOB.LOADFROMFILE(:blob_var, v_bfile, DBMS_LOB.getlength(v_bfile));
  DBMS_LOB.FILECLOSE(v_bfile);
END;

Mysql

LOAD_FILE() 사용.

MySQL 서버가 접근 가능한 위치에 있는 파일을 SQL에서 직접 읽어와 BLOB 컬럼에 삽입합니다.

-- MySQL 서버가 접근 가능한 디렉토리 확인
SHOW VARIABLES LIKE 'secure_file_priv';

-- 예: /var/lib/mysql-files/ 가 반환된다면, 해당 경로에 파일을 복사
INSERT INTO files (file_name, file_data)
VALUES ('example.pdf', LOAD_FILE('/var/lib/mysql-files/example.pdf'));

파일은 secure_file_priv 디렉터리 내부에 있어야 함
MySQL 사용자에게 FILE 권한 필요
서버 프로세스에서 해당 파일 경로에 읽기 권한 필요