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
권한 필요 - 서버 프로세스에서 해당 파일 경로에 읽기 권한 필요