🤔 SQL 끝장내기

• 오늘은 저번 주에 배우지 못했던 SQL의 나머지 부분을 배워보면서 SQL에서도 심화 내용이지만, 꼭 알고 넘어가야하는 개념들을 짚고 넘어가보았습니다.

 

---

👏🏻 SQL 실력 높여보기!

✨ 스토어드 프로시저

• 스토어드 프로시저는 SQL로 프로그래밍하여 DB에 저장하고 그 내용을 재사용할 수 있도록 만들어주는 기능을 합니다.
• 복잡한 쿼리들을 이 프로시저 내부에 저장하고 호출해 사용함으로써 다양한 이점이 존재합니다.

 

💡 프로시저의 장점

1. 절차적인 기능 구현이 가능해, IF나 WHILE 같은 제어 문장으로 프로그래밍을 할 수가 있습니다.
2. 호출한 곳에서는 별도의 수정 작업이 필요하지 않기 때문에 유지보수에 용이합니다.
3. 한 번의 요청으로 여러 SQL 문을 실행할 수 있어서 트래픽이 감소됩니다.
4. MySQL의 스토어드 프로시저는 자체적인 보안이 설정되어 있습니다.

 

💡 그러나, 단점

• 처리 성능이 다른 프로그래밍 언어에 비해서는 느립니다.
• DB 제품에 따라 구문 및 규칙이 다르기에 다른 제품과의 호환성이 낮습니다.
• 업무를 파악하거나 관리할 때, 추가적으로 관리해야 할 요소들이 늘어나게 됩니다.

 

💡 프로시저의 기본 형식

• 스토어드 프로시저는 아래와 같이 구성이 됩니다.

DELIMITER &&(선택)
CREATE PROCEDURE 프로시저명([IN | OUT | INOUT 매개변수]) -- 헤더
BEGIN
    -- 바디 (변수 선언, 함수 실행 등을 위한 코드)
    DECLARE cnt INT; -- 변수 선언
    SET cnt = 0; -- 변수 초깃값 설정
	
    SET cnt = (SELECT COUNT(*) FROM customer); -- 쿼리문으로도 설정 가능
	
    SELECT cnt; -- 바디 영역에 쿼리문이 들어가게 되면 ;이 뒤따라야함
END &&
DELIMITER ;

• 우선 프로시저의 바디에 있는 코드는 ALTER 문으로 수정할 수 없습니다. 그래서 삭제한 후에 다시 생성하는 방식으로만 수정할 수 있습니다.
• 스토어드 프로시저는 기본 반환값이 없기 때문에 RETURN 명령문을 사용할 수가 없습니다.
• IN과 함께 정의된 매개변수는 입력 전용 매개변수, OUT은 출력 전용, INOUT은 입력 및 출력 매개변수를 의미합니다.

 

💡 프로시저의 내용을 확인하려면?

• CALL 문을 사용해서 생성된 프로시저를 호출할 수 있습니다.
• 그리고 SHOW CREATE PROCEDURE 명령문으로 생성한 프로시저의 내용까지도 확인할 수 있는데,
  • 내용 중에서 CREATE DEFINER 관련 내용은 해당 프로시저의 접근 권한을 상세하게 보여줍니다.
• 프로시저를 삭제하려면 DROP을 사용하면 됩니다.

 

🧑🏻‍💻 SQL 프로그래밍

💡 IF 문

• IF문은 다음과 같이 IF와 ELSE로만 이루어져 있습니다.
• 그리고 IF 문이 끝나면 END로 반드시 닫아주어야 합니다.

IF 조건식 THEN (조건식이 참일 때) 실행할 식
ELSE (조건식이 거짓일 때) 실행할 식
END IF;

 

💡 CASE 문

• CASE는 여러 조건 가운데 하나를 선택해서 실행해야 할 때 사용됩니다.
• SQL에는 ELSE IF와 같은 구문이 없기 때문에 CASE 문을 이용해 다중 분기시켜줄 수 있습니다.

CASE
    WHEN 조건 1 THEN 명령문 1
    WHEN 조건 2 THEN 명령문 2
    WHEN 조건 3 THEN 명령문 3
    ELSE 명령문 4
END

• 스토어드 프로시저로 특정 고객 번호를 입력했을 때 해당 고객의 등급만을 조회하는 기능같은 것을 만들어서 재사용하는 식으로 쓰입니다.

 

💡 WHILE 문

• IF, CASE 문과는 달리 WHILE 문은 반드시 스토어드 프로시저 내에서만 사용할 수 있습니다.
• 추가적으로 반복문의 라벨도 설정할 수 있습니다. (일명 별칭)

반복문라벨:
WHILE 조건식 DO 
    명령문
    IF 조건 THEN LEAVE 반복문라벨;
    END IF;
END WHILE;

• LEAVE는 반복문을 실행할 때, 특정 조건이 되면 반복문을 빠져나오게 하는 명령어로, 파이썬의 break와 유사하다고 보시면 됩니다.

 

✨ 동적 SQL

• 동적 SQL이란, 변숫값을 할당 받아서 MySQL 서버 내부 또는 스토어드 프로시저에서 쿼리를 재작성하는 것을 의미합니다.
• PREPARE 문으로 쿼리문을 준비하고 EXECUTE 문으로 해당 쿼리를 실행할 수 있습니다.
• 그리고 쿼리문을 해제하려면 DEALLOCATE PREPARE 문을 사용하면 됩니다.

PREPARE 동적 쿼리문 FROM '쿼리 작성'
EXECUTE 동적 쿼리문
DEALLOCATE PREPARE 동적 쿼리명

• 위 코드는 동적 SQL을 사용하기 위한 기본적인 구조고, 아래는 그 예시입니다.

PREPARE query FROM 'SELECT * FROM customer WHERE customer_id = ?';
SET @a = 1;
EXECUTE query USING @a;
DEALLOCATE PREPARE query;

• 위 코드를 보시면, 동적 쿼리 부분이 ''으로 둘러싸여 있는데, 이 사이에 있는 쿼리는 일종의 템플릿입니다.
  • 여기서 ? 키워드를 사용해서 매개변수가 들어갈 수 있는 자리를 세팅할 수 있습니다.
  • 그 후에 SET @a=1; 과 값이 변수 @a의 값을 1로 설정하게 되면, ? 자리에 해당 변수가 세팅되게 되는 것입니다!
• 동적 SQL은 보통 순수 SQL로만 작업할 때 사용됩니다.
  • 특히, 정부 자원(DB)에서는 Spring Framework가 기본적으로 사용되기 때문에 이 과정에서 mybatis를 이용한다고 합니다.
  • 요근래에는 주로 JPA를 이용하여 DB에 접근한다고 하네요!

 

🔖 인덱스

• 인덱스는 필요한 데이터를 바로 찾을 수 있도록 참고할 수 있는 데이터를 의미합니다. (책의 인덱스와 유사하게)
• 인덱스를 생성하면 그만큼 인덱스를 관리하는 비용이 추가적으로 발생합니다. 그래서 잘못 생성하게 되면 오히려 역효과가 발생할 수도 있습니다.
• 인덱스로 지정하는 컬럼은 수정이나 삭제가 빈번하게 일어나지 않아야 합니다.
• 참고로, 인덱스 열의 최대 크기는 767byte라고 합니다.

 

💡 이런 인덱스의 장점은?

• 원하는 데이터를 빠르게 검색할 수 있고,
• 불필요한 검색 비용도 절약해주고, I/O 성능도 향상시켜줍니다.
• 그리고 조인 시에도 빠른 성능을 보여준다고 하네요.

 

💡 그렇다면 문제도 있을까?

• 별도로 저장할 인덱스 페이지를 구성하기 위해서 추가 공간이 필요합니다.
• 데이터를 수정할 때 연결된 인덱스 정보도 함께 수정해야 하는 경우에는 추가 비용이 발생할 수 있습니다.
• 정보 수정 시 잠금이 발생해 DB의 성능이 저하될 가능성도 충분히 있습니다.

 

💡 클러스터형 인덱스

• 인덱스의 유형에는 크게 클러스터형과 비클러스터형이 존재하는데, 우선 클러스터형부터 살펴보겠습니다.
• 클러스터형은 사전식으로 정렬된 데이터이고, 하나의 테이블당 1개만 존재할 수 있습니다.
• 기본키(PRIMARY KEY)로 지정된 열은 클러스터형 인덱스가 자동으로 생성되기 때문에,
  • 따라서 기본키를 변경하게 되면, 클러스터형 인덱스도 같이 변경되게 됩니다.
• 이 클러스터형은 물리적 데이터 페이지 구조를 가지기 때문에 추가 비용이 발생합니다!

 

💡 비클러스터형 인덱스

• 비클러스터형은 데이터의 위치 정보를 인덱스가 가지고 있는 형태로, 실제 데이터가 아니라 데이터의 위치 정보를 관리할 때 사용됩니다.
• 한 테이블에 여러 개를 설정할 수 있고, UNIQUE 명령어로 고유 열을 지정할 때 비로소 비클러스터형 인덱스가 생성됩니다.
• 실제 저장된 데이터와 다른 물리적인 데이터 페이지 구조를 가지게 됩니다.
• 데이터가 정렬되어 있지 않고, CREATE INDEX 문으로 비클러스터 형 인덱스를 직접 생성할 수도 있습니다.

 

🪟 뷰

CREATE VIEW 뷰 이름
AS
  <SELECT 문>

• DB에 존재하는 가상의 테이블로, 테이블이나 데이터를 직접 소유하지 않고 테이블의 형태만 차용하는 형태입니다.
• SELECT 문으로 만들 수 있고, 실제 사용자가 뷰를 호출했을 때 SELECT 문이 실행되어 결과를 출력하게 됩니다.
• 한 테이블만 구성(단일 뷰)할 수도 있고, 다수의 테이블로 조인하여 구성(복합 뷰)할 수도 있습니다.
  • 일반적으로 단일 뷰보다는 복합 뷰가 많이 사용됩니다.
• 기본적으로 SQL에서 현재 삭제하려는 테이블이 다른 테이블과 종속 관계에 있고, 상위 테이블일 때는 삭제할 수 없지만,
  • 뷰에서 사용 중인 테이블은 아무런 제약 없이 삭제할 수 있습니다. (그래도 주의하면서 삭제하기!)

 

💡 뷰의 특징

• 예를 들어, 회사에서 부서별로 필요한 고객 정보 외에 다른 정보를 보여주고 싶지 않을 때 뷰를 사용하게 되면 보안이나 편의성이 강화됩니다.
• 뷰에서는 테이블과 열 이름 등을 숨길 수 있으며, 권한에 따라서 필요한 열만 구성하여 사용도 가능합니다.
• 그러나 MySQL에서는 한 번 정의된 뷰를 변경할 수 없고, 삽입, 삭제, 갱신 작업을 하는 데 제약 사항이 많습니다.
  • 뷰 자체의 변경은 불가하지만 ALTER를 이용해서 내부 구조(컬럼명)는 수정할 수 있습니다.

ALTER VIEW 뷰 이름
AS
  <SQL 문>

• 위와 같이 생성된 뷰는 인덱스를 가질 수가 없습니다. 그리고 뷰를 조회할 때 WHERE 절이나 GROUP BY 절 등에서도 사용할 수 있습니다.
• 만약 뷰가 없는 상태에서 뷰를 수정하려고 시도하면 오류가 발생하기 때문에, CREATE OR REPLACE VIEW를 사용해서 대체하거나 생성할 수 있게 됩니다.
  • 그러나 데이터를 입력, 삭제, 수정하는 작업은 가급적 뷰보다는 원본 테이블에서 직접 수행하는 것을 권장합니다.
• 마지막으로 DESCRIBE으로 뷰 정보를 확인할 수도 있고, DROP으로 뷰를 삭제할 수도 있습니다.
• 참고 사항으로 Oracle에서는 View 테이블의 데이터를 수정하거나 삭제하게 되면, 원본 테이블도 같이 변경됩니다.

 

✨ 스토어드 함수

• 스토어드 함수는 사용자 함수라고도 하는데, MySQL이 제공하는 기본 함수를 그대로 사용할 수 없거나 필요로 하는 기능을 제공하는 함수가 없을 경우 사용자가 직접 정의하는 함수입니다.
• 프로시저는 SELECT된 데이터를 반환할 때 사용되지만, 함수는 하나의 데이터(값)만을 반환할 때 사용된다는 차이가 있습니다.
• 프로시저와 작성법은 유사하지만, 세부적인 차이가 존재하는데, 함수는 아래와 같이 구현됩니다.

DELIMITER $$(선택)

CREATE FUNCTION 함수 이름(인수)
    RETURNS 반환 데이터 유형
BEGIN
    로직 작성
    RETURN 반환값;
END $$

DELIMITER ;

• 함수를 생성하려면 먼저 생성 권한을 SET GLOBAL log_bin_trust_function_creators = 1; 이라는 명령어로 부여해줘야 합니다.
• 그리고 함수를 사용하려면 다른 함수들과 마찬가지로 SELECT 문을 사용해서 결과를 확인할 수 있고,
• 프로시저와 마찬가지로 SHOW CREATE FUNCTION으로 함수의 내용을 확인! 그리고 DROP으로 생성된 함수를 삭제할 수 있습니다.

 

💡 커서

• 커서라는 아이는 SQL 프로시저 내에서 테이블의 데이터를 한 행씩 처리하기 위한 도구입니다.
• 이 커서를 이용하면 대량의 데이터를 한 번에 처리하는 대신, 각 행을 개별적으로 확인하면서 조건에 맞는 작업을 수행할 수 있습니다.
• 커서를 사용하는 기본 흐름은 아래와 같습니다.

1. 커서 선언하기: 처리할 데이터를 선택하는 SELECT 문을 기반으로 커서를 선언(DECLARE CURSOR FOR)합니다.
2. 커서 열기: 선언된 커서를 열어(OPEN) 데이터에 접근할 수 있도록 합니다.
3. 데이터 가져오기: 커서로부터 한 행씩 데이터를 읽어(FETCH)옵니다.
4. 데이터 처리: 반복문(LOOP)과 조건문(IF, LEAVE 등)을 사용해서 각 행에 대해 원하는 작업을 수행합니다.
5. 커서 닫기: 모든 작업이 완료되면 커서를 닫아서(CLOSE) 자원을 반환합니다.

DELIMITER $$

CREATE PROCEDURE processEmployees()
BEGIN
  -- 종료 조건을 위한 변수 선언
  DECLARE done INT DEFAULT FALSE;
  -- 처리할 데이터 변수 선언 (사원의 ID와 이름)
  DECLARE empId INT;
  DECLARE empName VARCHAR(100);
  
  -- 커서 선언: employees 테이블에서 id와 name 컬럼을 가져온다.
  DECLARE cur CURSOR FOR SELECT id, name FROM employees;
  -- 더 이상 읽을 행이 없을 때를 처리하기 위한 핸들러 선언
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  -- 커서 열기
  OPEN cur;
  
  -- 커서에서 데이터를 한 행씩 가져와 처리
  read_loop: LOOP
    FETCH cur INTO empId, empName;
    IF done THEN
      LEAVE read_loop;  -- 더 이상 데이터가 없으면 루프 탈출
    END IF;
    
    -- 각 행에 대한 처리
    SELECT CONCAT('Employee ID: ', empId, ', Name: ', empName) AS info;
  END LOOP;
  
  -- 커서 닫기
  CLOSE cur;
END $$

DELIMITER ;

 

🔫 트리거

• 트리거는 DB에서 테이블에 입력, 수정, 삭제 등의 이벤트가 발생했을 때 미리 정해진 규칙에 따라 자동으로 실행되는 것을 의미합니다.
• MySQL에는 트리거 기능이 제공되지만, 다른 DBMS와 다르게 DDL 문을 수행할 때는 트리거를 제공하지 않습니다.
• 테이블에 DML 문 이벤트가 발생해야만 자동으로 작동하고, 변경 전을 기록하는 BEFORE, 변경 후 실행되는 AFTER 트리거가 존재합니다.
• IN, OUT 매개변수를 사용할 수 없고, MySQL에서는 뷰에 트리거를 부착할 수도 없습니다.
• 트리거를 통해서 이전의 값과 변경된 값을 트리거 테이블에 기록해 전후를 비교해 데이터를 복구할 수도 있지만, DB에 부하를 유발하므로 트래픽이 많은 DB에서는 사용을 자제하는 것이 좋습니다.

DELIMITER $$
CREATE TRIGGER 트리거명
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON 원본 테이블
FOR EACH ROW

BEGIN
  <트리거 작동 시 실행할 코드>

END $$
DELIMITER ;

 

💡 트리거의 종류

• 행 트리거는 테이블에서 DML 문으로 인해 영향을 받는 행 각각에 이벤트를 실행해줍니다.
• 그리고 문장 트리거는 행의 개수와 관계없이 단 1회만 실행됩니다. 즉, 행 수에 상관없이 트랜잭션에 대해서 트리거가 한 번만 실행되는 구조입니다.

 

💡 실행 시기는?

• 일단 BEFORE, AFTER 트리거가 나뉘어져 있는 이유는 현재 상태값과 변경된 상태값을 비교할 때, 변경 이전에 기록을 확인할 것인지, 이후 기록을 확인할 것인지의 선택을 할 수 있도록 하기 위해서입니다.
• BEFORE 트리거는 데이터 변경 작업 이전에 트리거가 활성화되고, 트리거 테이블에 먼저 변경 사항이 기록되게 됩니다.
• 반면에 AFTER 트리거는 데이터 변경 이벤트가 발생 후, 데이터 변경 작업을 진행하고 트리거가 활성화됩니다. 그리고 마지막으로 변경된 내용이 트리거 테이블에 기록됩니다.

 

---

🤔 25일차 회고

• 가장 최근에 프로젝트를 하고 난 뒤, 간만에 하루 종일 이론 교육을 받은 것 같습니다.
• 연휴를 보내고 와서 그런지 피곤하기도 하고... 더군다나 이론 위주라 살짝 더 피곤했지만, 그래도 잘 마무리되었습니다!
• 내일은 파이썬과 SQL 둘 다 활용해서 주식 분석 시스템을 만들어보고자 합니다!
• 그리고 이어서 자연어 처리도 배울 예정이니 배워놨던 지식들을 총동원해서 열심히 달려보겠습니닷!