[Database] SQL 심화

와 오늘 수업은 어디서 검색만 해봤지, 실제로는 처음 공부해보는 부분이었다. 정말 SQL에는 많은 기능이 있고 나는 기본적인 것들만 겨우 사용하고 있었구나... 배울수록 신기하고 재밌는 게 많은 것 같다. 전부를 샅샅히 정리하진 못하겠지만 내가 보기에 중요한 것들 위주로 한 번 정리를 해봤다!

🛢️ 내장함수

데이터베이스에서 데이터를 처리하고 조작하는 데 사용되는 미리 정의된 함수
→ 내장함수를 호출하면 MySQL 엔진이 알아서 해줌

특성

• 다양성
• 효율성
• 다중 기능
• NULL 처리
• 복합 사용 가능

문자열 함수, 수학 함수, 날짜/시간 함수, 집계 함수, 변환 함수 등이 있다.

---

🛢️ 저장 프로시저

Stored Procedure, MySQL에서 미리 작성된 SQL 쿼리의 집합

여러 SQL 문을 하나의 루틴으로 결합하여, 반복적인 작업을 쉽게 처리하고 코드 재사용성을 높이는데 유용함

호출되면 즉시 실행, 복잡한 작업을 자동화하는데 자주 사용

구성 내역

• Client: 사용자가 저장 프로시저를 호출하는 환경 (ex. 웹 애플리케이션, CLI 등)

• MySQL 서버: 저장 프로시저가 실행되는

• DBMS.저장 프로시저: 미리 정의된 SQL 작업 블록

• 데이터베이스: 저장 프로시저가 상호작용는 데이터 저장소

Client → MySQL 서버 → 저장 프로시저 → 데이터베이스

저장 프로시저의 특징

• 재사용성
• 성능 최적화 : SQL이 미리 컴파일되므로 다중 호출 시 성능 향상 효과
• 보안 : 보잡한 작업을 숨겨 보안성을 높이고, 권한 제어 간편화
• 트랜잭션 처리 : 트랜잭션 내에서 사용될 수 있으며, 복잡한 트랜잭션 로직을 캡슐화
• 입력 및 출력 매개변수 : 입력값을 받아 처리할 수 있고, 결과를 반환할 수 있다

키워드 구분
DELIMITER //

• 구분자를 //로 변경 → 프로시저 내에서 사용된 ; 구문과 혼동되지 않도록 설정
• 여러 SQL문을 하나로 묶어 처리할 수 있게 함
• 그래서 프로시저나 트리거를 사용할 때 자주 사용하게 된다!

CREATE PROCEDURE

• 저장 프로시저 생성(정의)

IN

• 저장 프로시저로 값을 받을 때 IN 키워드를 사용해서 매개변수를 정의하고, 데이터 타입을 정의한다.

BEGIN ... END

• 여러 개의 SQL문을 하나의 블록으로 묶는 데 사용
• 저장 프로시저 내에서 실행되는 모든 SQL문이 이 안에 작성되어야 한다!

END //

• 저장 프로시저 SQL 블록이 끝났음을 나타냄 (저장 프로시저 작업 종료)

DELIITER ;

• 구분자를 다시 기본 값인 ;로 되돌리는 명령
• 이후에 작성될 다른 SQL문을 정상적으로 구분하기 위해서 다시 구분자를 돌려두어야 한다

저장 프로시저 호출/삭제
CALL / DROP PROCEDURE IF EXISTS

• IF EXISTS 구문을 통해서 해당 프로시저가 존재하지 않을 때 발생할 수 있는 오류를 방지한다

저장 프로시저 적용 분야

• 데이터 분석 - 복잡한 데이터 분석 작업을 저장 프로시저로 미리 정의해두어 반복적으로 사용 가능하다
• 데이터베이스 유지 관리 - 데이터 백업, 복원, 정리 작업을 저장 프로시저로 관리하여 일관성 유지
• 트랜잭션 처리 - 여러 SQL 작업을 트랜잭션으로 묶어서 저장 프로시저로 처리하면 효율적이며 일관성 있는 데이터 처리 가능

---

🔫 트리거

데이터베이스 테이블에서 데이터가 삽입, 갱신 또는 삭제 될 때 자동으로 실행되는 SQL 코드 블록

데이터 무결성 유지, 이벤트 기반의 자동화된 작업 수행

데이터가 변동될 때 특정 규칙을 강제하거나 추가 작업을 수행하는데 사용

트리거 실행 시점
BEFORE : 데이터 작업 전
AFTER : 데이터 작업 후

트리거 유형

• INSERT 트리거 : 테이블에 새로운 행이 추가될 때 실행
• UPDATE 트리거 : 기존 데이터가 수정될 때 실행
• DELETE 트리거 : 데이터가 삭제될 때 실행

주요 기능 및 특징

• 데이터 무결성 유지
• 자동화
• 이벤트 기반 처리
• 복합 로직 구현 가능
• 계층적 트리거 호출

트리거 생성 → 이벤트 발생(INSERT/UPDATE/DELETE) → BEFORE / AFTER 트리거 실행 → SQL 실행 (데이터 처리, 로그 기록 등)

CREATE TRIGGER 트리거_이름
[BEFORE|AFTER] [INSERT|UPDATE|DELETE]
ON 테이블_이름
FOR EACH ROW
BEGIN 
	-- 데이터 동작 정의
END;

---

🛢️ 트랜잭션

하나의 작업 단위로, 여러 SQL 쿼리들이 한 번에 실행되어야 할 때 사용

→ 데이터 무결성을 유지하기 위함

ACID 준수

• Atommicity (원자성) : 트랜잭션 내의 모든 작업이 모두 성공하거나 모두 실패
• Consistency (일관성) : 트랜잭션 전후에 데이터 무결성 보장
• Isolation (격리성) : 각각의 트랜잭션이 독립적으로 처리
• Durability (지속성) : 트랜잭션 성공하면 그 결과가 영구적으로 유지

기능
COMMIT - 트랜잭션에서 발생한 모든 변경 사항을 영구적으로 저장

ROLLBACK - 영구적으로 취소

SAVEPOINT - 트랜잭션 내에서 특정 시점으로 돌아가기 위해 설정

ISOLATION LEVEL - 트랜잭션간의 상호작용 정도 제어

START TRANSACTION → COMMIT | ROLLBACK

주의사항

• 트랜잭션이 길어지면 데이터베이스 락이 걸릴 수 있다!
• 반드시 필요한 경우에만 사용해서 데이터베이스 성능을 최적화하는 것이 중요

---

🛢️ 사용자 정의 함수 생성

User-Defined Function, MySQL에서 사용자가 직접 정의한 함수

내장 함수가 제공하지 않는 특정 작업을 수행해야 할 때 사용자가 직접 SQL 코드를 작성하여 함수로 등록할 수 있다! → 코드 재사용 가능, 복잡한 쿼리 논리를 함수로 캡슐화할 수 있다

CREATE FUNCTION 함수_이름 (입력값 데이터타입, ...)
	RETURNS 결과값데이터타입
BEGIN
	DECLARE 임시값 데이터타입;
    SET 임시값 = 입력값;
    -- 함수 내용 
    RETURN 결과값;
END;

-- 함수 삭제
DROP FUNCTION 함수_이름;

특징

• 재사용성
• 캡슐화 : 코드 간소화
• 인수 전달 : 0개 이상의 인수를 받아 처리할 수 있다
• 값 반환
• 성능 : 미리 컴파일되기 때문에 성능 최적화에 유리, 유지 보수에도 유리하다
• 특정 작업 자동화
• 데이터 검증 및 변환 (ex. 이메일 주소 형식 검증)

---

🙄 느낀점

프로그래밍 언어만큼은 아니지만, sql에서도 인자값을 받고, 리턴하는 식의 표현이 된다니!!!! 여기는 깊게 공부해봤던 적이 없던 분야라서 처음 알았고 신기했다. 현업 하면서 '아 쿼리에서는 바로 어떻게 안 되나?' 궁금하기만 했지 찾아볼 생각은 안했었음 ㅎㅎ 하지만 있구나... 이미 있었구나... 내가 생각한 것 = 이미 천재들이 생각하고 해결책까지 내놓음 ㅎㅎ

암튼 트리거랑 사용자 정의 함수는 알아두면 정말정말 편하게 쓸 것 같다는 생각을 했다. 저장 프로시저도 잘 쓸 줄 알아야겠다. 매우매우매우 많이 사용하는 문법을 배운 것 같은 느낌~ 재밌었다.

---

본 포스팅은 글로벌소프트웨어캠퍼스와 교보DTS가 함께 진행하는 챌린지입니다