2026년 2월 4일 수요일 - 6주차
PL/SQL 이론
프로시져 - Procedure
여러 개의 SQL문과 로직을 하나로 묶어두고, 필요할 때 호출해서 실행하는 저장 프로그램이다. 주로 데이터의 등록, 수정, 삭제처럼 하나의 작업 흐름을 처리할 때 사용되며, CALL 문을 통해 실행한다. 프로시저는 반환값이 필수는 아니며, 여러 SQL을 순차적으로 실행하거나 트랜잭션 처리가 필요한 경우에 적합하다. 반복되는 작업 로직을 DB 내부에 저장해두고 재사용하기 위해 사용된다.
트리거 - Trigger
사용자가 직접 호출하지 않아도, 특정 이벤트가 발생했을 때 자동으로 실행되는 저장 프로그램이다. 테이블에 INSERT, UPDATE, DELETE가 발생하는 순간을 감지하여 실행되며, 데이터 변경 이력 기록이나 무결성 유지 같은 작업에 주로 사용된다. 트리거는 개발자가 실행을 제어하지 않기 때문에, 의도하지 않은 동작을 막기 위해 신중하게 설계해야 한다.
사용자 정의 함수 - Function
특정 값을 계산하여 하나의 결과를 반환하는 저장 프로그램이다. 함수는 반드시 RETURN을 통해 값을 반환해야 하며, SELECT, WHERE 같은 SQL 문 내부에서 사용할 수 있다는 특징이 있다. 주로 계산 로직이나 변환 로직처럼 “입력값에 따라 결과값을 돌려주는” 기능을 구현할 때 사용되며, SQL을 더 간결하고 가독성 있게 만들어 준다.
실습 코드
프로시져 코드
-- 프로시져, 트리거, 함수
-- [프로시져]
DELIMITER $$
-- 문장 구분하는 ;을 $$로 바꿔줌. 프로시져 안쪽에 쿼리가 여러개 나오기 때문에 문장의 구분을 위해서 ;을 써야하기 때문에 변경하지 않으면 CREATE PROCEDURE와 END를 하나의 문단이 아닌 별개의 문단으로 인식한다. 프로시져 만들때는 필수.
-- 프로시져는 함수를 정의하는 것.
CREATE OR REPLACE PROCEDURE proc_hello()
BEGIN
SELECT 'hello world' as qwer;
END $$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE PROCEDURE proc_var()
BEGIN
-- DECLARE: 변수 선언하는 것
DECLARE aaa INT DEFAULT 1; -- int aaa = 1;
DECLARE bbb INT DEFAULT 2; -- int bbb = 2;
SET aaa = 3; -- =이 대입연산자가 아니기 때문에 SET 써서 대입한다.
SET bbb = aaa + 5;
SET aaa = aaa + 1;
SELECT aaa, bbb, aaa+bbb, CONCAT(aaa, ' + ', bbb, ' = ', aaa+bbb);
-- Java: aaa + " + " + bbb + " = " + (aaa + bbb);
END $$
DELIMITER ;
-- if, for, while
DELIMITER $$
CREATE OR REPLACE PROCEDURE proc_conditional()
BEGIN
DECLARE value INT DEFAULT 3;
SELECT COUNT(*) INTO value FROM Customer;
IF value = 3 THEN
SELECT '값이 3입니다.';
ELSEIF value = 4 THEN
SELECT '값이 3이 아니라 4입니다.';
ELSE
SELECT '값이 해당되지 않습니다.';
END IF;
END $$
DELIMITER ;
-- 1~10 사이의 랜덤값이 변수에 있을때 짝수인지 홀수인지... if...
DELIMITER $$
CREATE OR REPLACE PROCEDURE proc_ex1()
BEGIN
DECLARE value INT;
SET value = (RAND() * 10) + 1;
-- SELECT value;
IF value % 2 = 0 THEN
SELECT value, '짝수입니다';
ELSE
SELECT value, '홀수입니다';
END IF;
END $$
DELIMITER ;
-- for...
DELIMITER $$
CREATE OR REPLACE PROCEDURE proc_loop()
BEGIN
DECLARE idx INT DEFAULT 1;
-- 세션이 유지되는 동안만 존재하는 임시 테이블
CREATE TEMPORARY TABLE t_result(
idx INT
);
lp1: LOOP
IF idx > 10 THEN
LEAVE lp1;
END IF;
-- SELECT idx;
INSERT INTO t_result(idx) VALUES(idx);
SET idx = idx + 1;
END LOOP;
SELECT * FROM t_result;
END $$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE PROCEDURE proc_ex2_hap()
BEGIN
DECLARE value INT DEFAULT 1;
DECLARE sum INT DEFAULT 0;
qwer: LOOP
IF value > 100 THEN
LEAVE qwer;
END IF;
SET sum = sum + value;
SET value = value + 1;
END LOOP;
SELECT sum;
END $$
DELIMITER ;
-- 구구단
DELIMITER $$
CREATE OR REPLACE PROCEDURE proc_mult()
BEGIN
DECLARE x INT DEFAULT 2;
DECLARE y INT DEFAULT 1;
DECLARE r INT;
CREATE OR REPLACE TEMPORARY TABLE t_result(
x INT,
y INT,
r INT,
message VARCHAR(50)
);
xl: LOOP
IF x > 9 THEN
LEAVE xl;
END IF;
SET y = 1;
yl: LOOP
IF y > 9 THEN
LEAVE yl;
END IF;
SET r = x * y;
INSERT INTO t_result(x, y, r, message) VALUES(
x, y, r,
CONCAT(x, ' X ', y, ' = ', r)
);
SET y = y + 1;
END LOOP;
SET x = x + 1;
END LOOP;
SELECT * FROM t_result;
END $$
DELIMITER ;
-- 파라미터 - 개념적으로 값을 리턴. 함수 호출할때 매개변수 넣어야 함.
DELIMITER $$
CREATE OR REPLACE PROCEDURE proc_param(
IN aaa INT,
IN bbb INT,
OUT ccc INT
)
BEGIN
SELECT aaa + bbb;
SET ccc = aaa + bbb;
END $$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE PROCEDURE proc_main()
BEGIN
DECLARE qwer INT DEFAULT 3;
DECLARE v_result INT;
SELECT v_result;
CALL proc_param(5, qwer, v_result);
END $$
DELIMITER ;
DELIMITER $$
CREATE OR REPLACE PROCEDURE proc_cursor()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE qwer VARCHAR(30);
DECLARE v_result VARCHAR(300) DEFAULT '';
DECLARE a VARCHAR(30);
DECLARE b VARCHAR(30);
DECLARE c VARCHAR(30);
DECLARE d INT;
DECLARE cur1 CURSOR FOR SELECT * FROM Book;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
yyy: LOOP
FETCH cur1 INTO a, b, c, d;
IF done = 1 THEN
LEAVE yyy;
END IF;
SET v_result = CONCAT(v_result, '/', a);
END LOOP;
CLOSE cur1;
SELECT v_result;
END $$
DELIMITER ;
-- 복사해서 쓸것
DELIMITER $$
CREATE OR REPLACE PROCEDURE proc_template()
BEGIN
END $$
DELIMITER ;트리거 코드
-- [트리거]
-- CREATE TABLE Book_log(
-- bookid_l INT,
-- bookname_l VARCHAR(30),
-- publisher_l VARCHAR(30),
-- price_l INT
-- ); 테이블 한번 생성 후 삭제. 위에서 차례로 실행시킬것이기에 자꾸 오류가 뜰것이기 때문에.
DELIMITER $$
CREATE OR REPLACE TRIGGER AfterInsertBook
AFTER INSERT ON Book
FOR EACH ROW
BEGIN
INSERT INTO Book_log(bookid_l, bookname_l, publisher_l, price_l)
VALUES(NEW.bookid, NEW.bookname, NEW.publisher, NEW.price);
-- 프로시져랑 다 비슷한데 프로시져는 NEW를 못쓰지만 트리거에서는 쓸 수 있다는게 다르다.
-- 그래서 프로시져만 잘 공부하면 문법이 거의 같아서 잘 이해 될 것이다.
END $$
DELIMITER ;사용자 정의 함수 코드
-- [사용자 정의 함수]
DELIMITER $$
-- 프로시져와 다르게 아예 리턴타입이 있다.
CREATE OR REPLACE FUNCTION fnc_Interest(price INT)
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE myInterest DECIMAL(10, 2);
IF price >= 30000 THEN
SET myInterest = price * 0.1;
ELSE
SET myInterest = price * 0.05;
END IF;
RETURN myInterest;
END $$
DELIMITER ;호출(사용) 코드
-- 프로시져 호출문
CALL proc_hello;
CALL proc_var;
CALL proc_conditional;
CALL proc_ex1;
CALL proc_loop;
CALL proc_ex2_hap;
CALL proc_mult;
CALL proc_param(30, 40);
CALL proc_main;
CALL proc_cursor();
-- 트리거 호출문
SELECT * FROM Book b;
INSERT INTO Book(bookid, bookname, publisher, price)
VALUES(11, '야호', '야호출판사', 500000);
SELECT * FROM Book_log;
-- 사용자 정의 함수 호출문
SELECT SUM(b.price) FROM Book b;
SELECT b.price, fnc_Interest(b.price) FROM Book b;데이터베이스 필수내용 정리
필수 기술 - DML
개발자가 되기위해 1차적으로 도달해야 할 수준: 기본적인 CRUD를 할줄 알아야 함. Java보다 DB가 더 중요하다.
CRUD중에서는 SELECT가 가장 중요한데, SELECT만 되면 사실상 DB는 끝인 수준. 나머지는 쉬워서 하다보면 금방 익힌다. 그중에서도 특히 JOIN, GROUP BY, 서브쿼리 잘 알기.
- GROUP BY: 통계를 낼때 정말 많이 쓰인다. 꼭 할줄 알아야 하니까 될때까지 공부하기.
- JOIN: 최저점. 할줄 모르면 아무것도 할 수 없는 수준이기 때문에 면접에서도 가장 먼저 물어보는 질문이라고 한다.
필수 기술 - DDL
CREATE TABLE: 할줄 알아야 하며, 각종 제약조건을 잘 알고있어야 하고, 테이블을 만들때도 제약조건을 신경써야 한다.
이론 내용
인덱스(이론), 트랜잭션(이론), 정규화(이론), 역정규화(실무)
- 정규화를 반대로 하는것이 역정규화이며, 사실 정석은 아니지만 실무에서는 정규화보다 더 많이 쓰인다.
취업하고싶다...