SQL 기초(4)

1) Stored Procedure

서버에 일련의 SQL을 저장해 반복 작업 자동화·네트워크 왕복 감소.
변수/조건/반복/예외처리 지원. 권한 분리·보안에 유리.

기본 골격 & 호출

DELIMITER //

CREATE PROCEDURE proc_name ( -- [IN|OUT|INOUT] p_name TYPE, ...
)
BEGIN
    -- SQL 문장들
END //

DELIMITER ;

CALL proc_name(인자들);

매개변수 패턴

• IN: 호출 시 전달 → 내부에서 사용
• OUT: 프로시저가 값을 반환 → @user_var로 받기
• INOUT: 전달받은 값을 수정해 다시 반환

-- OUT 예시
DELIMITER //
CREATE PROCEDURE getEmployeeSalary(IN id VARCHAR(3), OUT sal DECIMAL(10,2))
BEGIN
  SELECT salary INTO sal FROM employee WHERE emp_id = id;
END //
DELIMITER ;

SET @sal := 0;
CALL getEmployeeSalary('201', @sal);
SELECT @sal;

분기/반복/예외

-- IF
IF cond THEN ... ELSE ... END IF;

-- CASE
CASE
  WHEN cond1 THEN ...
  WHEN cond2 THEN ...
  ELSE ...
END CASE;

-- WHILE
WHILE cond DO
  -- 반복 처리
END WHILE;

-- 예외 (SIGNAL)
IF denominator = 0 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '0으로 나눌 수 없습니다.';
END IF;

• DELIMITER 변경 후 원복 꼭 하기.
• 프로시저 권한: CREATE ROUTINE, EXECUTE (필요 시 ALTER ROUTINE).
• 트랜잭션 내에서 호출 시 원자성 고려(ROLLBACK 영향).

---

2) Stored Function

값을 반환하는 저장 루틴. 반드시 하나의 값을 RETURN.
SQL 식 안에서 호출 가능(SELECT func(...)).

Procedure vs Function

• 호출: CALL proc(...) vs SELECT func(...)
• 반환: 여러/없음 vs 반드시 1개
• 파라미터: IN/OUT/INOUT vs IN만
• 용도: 작업 단위 처리 vs 계산/변환

예시

DELIMITER //
CREATE FUNCTION getAnnualSalary(id VARCHAR(3))
RETURNS DECIMAL(15,2)
DETERMINISTIC
BEGIN
  DECLARE m DECIMAL(10,2);
  SELECT salary INTO m FROM employee WHERE emp_id = id;
  RETURN m * 12;
END //
DELIMITER ;

SELECT emp_name, getAnnualSalary(emp_id) AS annual_salary
  FROM employee;

DETERMINISTIC: 같은 입력 → 항상 같은 결과(옵티마이저 힌트로 유용)

---

3) Trigger

테이블의 INSERT/UPDATE/DELETE 이벤트에 자동 실행되는 객체.
데이터 무결성/로깅 등에 유용. 남용 시 성능·복잡도 ↑

문법

DELIMITER //

-- (MariaDB 버전에 따라 OR REPLACE 미지원일 수 있음: 미지원이면 DROP 후 CREATE)
CREATE OR REPLACE TRIGGER trig_name
  BEFORE | AFTER  INSERT | UPDATE | DELETE
  ON table_name
  FOR EACH ROW
BEGIN
  -- NEW.col / OLD.col 사용
END //

DELIMITER ;

예시: 주문 상세 INSERT 후 주문합계 갱신

DELIMITER //
CREATE OR REPLACE TRIGGER after_order_menu_insert
AFTER INSERT ON tbl_order_menu
FOR EACH ROW
BEGIN
  UPDATE tbl_order
     SET total_order_price = total_order_price
                           + NEW.order_amount * (
                               SELECT menu_price FROM tbl_menu WHERE menu_code = NEW.menu_code
                             )
   WHERE order_code = NEW.order_code;
END //
DELIMITER ;

• BEFORE: 값 보정/검증, AFTER: 로깅/집계
• NEW/OLD 가용성:
  INSERT(NEW만), UPDATE(NEW/OLD), DELETE(OLD만)
• 권한: TRIGGER

---

4) DCL (GRANT / REVOKE)

사용자·호스트 단위로 객체 권한 부여/회수.

GRANT

-- 형식
GRANT 권한목록 ON db_or_tbl TO 'username'@'host';

-- 예시
GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'hostname';
GRANT SELECT, INSERT ON database_name.table_name TO 'user'@'hostname';
GRANT SELECT, UPDATE, DELETE ON *.* TO 'user'@'hostname';
GRANT ALL PRIVILEGES ON db.* TO 'user'@'192.168.1.100';

REVOKE

-- 형식
REVOKE 권한목록 ON db_or_tbl FROM 'user'@'host';

-- 예시
REVOKE ALL PRIVILEGES ON database_name.* FROM 'user'@'hostname';
REVOKE INSERT ON database_name.table_name FROM 'user'@'hostname';
REVOKE DELETE ON *.* FROM 'user'@'hostname';

참고

• 일반적으로 GRANT/REVOKE는 즉시 반영.
• 계정 생성/비밀번호 등 계정 관리가 필요하면 CREATE USER, ALTER USER, SET PASSWORD 등 병행.
• 최소 권한 원칙 적용(Need-to-know).