[SQL] SQL 프로그래밍

목차

1. 저장 프로시저(Stored procedures)
2. IF-ELSE
3. CASE
4. WHILE
5. ITERATE / LEAVE

---

저장 프로시저(Stored procedures)

저장 프로시저는 일련의 SQL 문을 데이터베이스 내에 재사용 가능한 단일 개체로 저장할 수 있는 MySQL을 비롯한 관계형 데이터베이스의 강력한 기능입니다. 저장 프로시저를 사용하면 복잡한 작업을 수행하고, 비즈니스 로직을 캡슐화하며, 성능을 개선하고, 데이터베이스 내에서 중앙 집중화하여 SQL 코드의 유지 관리를 간소화할 수 있습니다.

다음은 저장 프로시저의 주요 특징입니다.

1. 매개변수
   저장 프로시저는 입력 매개변수를 받아들이고 출력 매개변수를 반환할 수 있어 더욱 유연하고 다양하게 활용할 수 있습니다. 저장 프로시저를 호출할 때 값을 전달하고 실행된 후에 결과를 검색할 수 있습니다.
   
   
2. 제어 구조
   저장 프로시저는 조건문(IF, CASE), 루프(WHILE, REPEAT), 예외 처리(DECLARE, SIGNAL) 등 다양한 프로그래밍 구성을 지원합니다. 이를 통해 저장 프로시저 내에서 보다 정교한 로직을 만들 수 있습니다.
   
   
3. 보안
   저장 프로시저 내에서 SQL 코드를 캡슐화하면 기본 테이블에 대한 직접 액세스를 제한하고 사용자가 수행할 수 있는 작업을 제어할 수 있습니다. 이는 데이터 무결성과 보안을 강화하는 데 도움이 됩니다.
   
   
4. 성능
   저장 프로시저는 생성 시 데이터베이스 엔진에 의해 컴파일되고 최적화되므로 성능을 향상시킬 수 있습니다. 즉, 저장 프로시저에 대한 후속 호출이 동등한 임시 SQL 쿼리보다 빠르게 실행될 수 있습니다.

다음은 MySQL에서 저장 프로시저를 생성하고 호출하는 기본 예제입니다. 저장 프로시저를 만드는 방법은 다음과 같습니다.

DELIMITER //
CREATE PROCEDURE GetEmployeeCountByDepartment(IN department_id INT, OUT count INT)
BEGIN
  SELECT COUNT(*)
  INTO count
  FROM employees
  WHERE department_id = department_id;
END //
DELIMITER ;

이 예에서는 입력 매개변수 department_id를 받고 해당 부서의 직원 수를 출력 매개변수 count로 반환하는 GetEmployeeCountByDepartment라는 저장 프로시저를 만듭니다.

저장 프로시저를 호출하는 방법입니다.

CALL GetEmployeeCountByDepartment(1, @employee_count);
SELECT @employee_count;

여기서는 부서_id가 1인 저장 프로시저를 호출하고 결과를 사용자 정의 변수 @employee_count에 저장합니다. 마지막으로 @employee_count의 값을 SELECT하여 결과를 검색합니다.

저장 프로시저는 강력하고 유연한 SQL 코드 관리 방법을 제공함으로써 데이터베이스 애플리케이션의 기능과 유지보수성을 크게 향상시킬 수 있습니다.

---

IF-ELSE

저장 프로시저에서 IF-ELSE를 사용하면 데이터베이스 작업에 조건부 논리를 추가할 수 있습니다. 이는 데이터 조작 및 유효성 검사를 관리하는 데 유용하며, 저장 프로시저를 보다 유연하고 동적으로 만들 수 있습니다. 다음은 MySQL의 저장 프로시저 내에서 IF-ELSE를 사용하는 예제입니다:

다음과 같은 열이 있는 'employees'라는 테이블이 있다고 가정해 보겠습니다: 'id', 'name', 'salary', 'department_id'. 직원의 급여를 업데이트하는 저장 프로시저를 만들되, 해당 급여가 부서의 지정된 범위 내에 속하는 경우에만 업데이트합니다.

IF-ELSE를 사용하여 저장 프로시저를 만들 수 있습니다.

DELIMITER //
CREATE PROCEDURE UpdateEmployeeSalary(IN employee_id INT, IN new_salary DECIMAL(10,2))
BEGIN
  DECLARE department_range_min DECIMAL(10,2);
  DECLARE department_range_max DECIMAL(10,2);

  SELECT range_min, range_max
  INTO department_range_min, department_range_max
  FROM department_ranges
  WHERE department_id = (SELECT department_id FROM employees WHERE id = employee_id);

  IF new_salary >= department_range_min AND new_salary <= department_range_max THEN
    직원 업데이트
    SET salary = new_salary
    WHERE id = employee_id;
  ELSE
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '새 급여가 부서의 허용 범위에 속하지 않습니다.';
  END IF;
END //
DELIMITER ;

이 예제에서 저장 프로시저 UpdateEmployeeSalary는 employee_id와 new_salary라는 두 개의 입력 매개 변수를 사용합니다. 먼저 부서_범위 테이블에서 직원의 부서에 대한 최소 및 최대 급여 범위를 검색합니다. 그런 다음 IF-ELSE 문을 사용하여 새 급여가 부서에 허용된 범위 내에 있는지 확인합니다. 조건이 참이면 직원의 급여가 업데이트되고, 그렇지 않으면 사용자 지정 메시지와 함께 오류가 발생합니다.

저장 프로시저를 호출할 수 있습니다.

CALL UpdateEmployeeSalary(1, 50000);

이 예에서는 employee_id가 1이고 new_salary가 50,000인 저장 프로시저를 호출합니다. 직원의 부서 급여 범위에 따라 급여가 업데이트되거나 오류가 발생합니다.

저장 프로시저 내에서 IF-ELSE를 사용하면 조건부 논리를 데이터베이스 작업에 통합할 수 있으므로 보다 다양하고 복잡한 작업을 처리할 수 있습니다.

---

CASE

SQL의 CASE 표현식은 쿼리 내에서 조건부 논리를 수행하는 데 사용됩니다. 조건 분기를 수행하고 하나 이상의 조건 결과에 따라 다른 값을 반환할 수 있습니다. CASE 표현식에는 단순 CASE와 검색된 CASE의 두 가지 형태가 있습니다.

1. 단순(Simple) CASE
단순 CASE 표현식은 입력 표현식을 값 집합과 비교하여 가장 먼저 일치하는 값에 해당하는 결과를 반환합니다. 일치하는 값이 하나도 없으면 ELSE 절에 결과를 반환합니다(또는 ELSE 절이 제공되지 않은 경우 NULL).

CASE input_expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE default_result
END

예제:

SELECT name, salary,
  CASE department_id
    WHEN 1 THEN 'Sales'
    WHEN 2 THEN 'IT'
    WHEN 3 THEN 'Marketing'
    ELSE 'Unknown'
  END AS department_name
FROM employees;

이 예에서는 간단한 CASE 표현식을 사용하여 department_id에서 department_name 열을 파생합니다.

2. 검색된(Searched) CASE:
검색된 CASE 표현식은 일련의 부울 조건을 평가하여 첫 번째 조건이 참인 경우 해당 결과를 반환합니다. 조건 중 어느 것도 참이 아닌 경우 ELSE 절에 결과를 반환합니다(또는 ELSE 절이 제공되지 않은 경우 NULL).

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE default_result
END

예제:

SELECT name, salary,
  CASE
    WHEN salary < 30000 THEN 'Low'
    WHEN salary >= 30000 AND salary < 60000 THEN 'Medium'
    WHEN salary >= 60000 THEN 'High'
    ELSE 'Unknown'
  END AS salary_level
FROM employees;

이 예에서는 검색된 CASE 표현식을 사용하여 급여에서 salary_level 열을 파생합니다.

CASE 표현식을 사용하면 SQL 쿼리 내에서 직접 조건부 논리를 수행할 수 있으므로 특정 조건에 따라 데이터를 조작하고 변환할 수 있는 강력한 방법을 제공합니다.

---

WHILE

SQL에서 WHILE 루프는 지정된 조건이 참으로 유지되는 한 코드 블록을 반복적으로 실행할 수 있는 제어 흐름 문입니다. 이 구조는 반복적인 작업을 수행하거나 다양한 값을 반복해야 할 때 저장 프로시저 및 함수 내에서 특히 유용합니다.

모든 SQL 데이터베이스에서 WHILE 루프가 지원되는 것은 아닙니다. 예를 들어 MySQL은 저장 프로시저 및 함수에서 WHILE 루프를 지원하지만 독립형 SQL 쿼리에서는 지원하지 않습니다. 다음은 MySQL 저장 프로시저 내에서 WHILE 루프를 사용하는 예입니다.

'id', 'name', 'salary', 'department_id' 열이 있는 'employees'라는 테이블이 있고 모든 직원의 급여를 지정된 비율만큼 증가시키는 저장 프로시저를 만들고 싶다고 가정하겠습니다.

WHILE 루프를 사용하여 저장 프로시저를 만듭니다.

 

DELIMITER //
CREATE PROCEDURE IncreaseSalaries(IN percentage DECIMAL(5,2))
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE emp_id INT;
  DECLARE emp_salary DECIMAL(10,2);
  DECLARE cur CURSOR FOR SELECT id, salary FROM employees;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;

  read_loop: WHILE NOT done DO
    FETCH cur INTO emp_id, emp_salary;
    IF NOT done THEN
      UPDATE employees
      SET salary = emp_salary * (1 + (percentage / 100))
      WHERE id = emp_id;
    END IF;
  END WHILE read_loop;

  CLOSE cur;
END //
DELIMITER ;

이 예제에서는 단일 입력 매개변수 백분율을 사용하는 IncreaseSalaries라는 저장 프로시저를 만듭니다. 커서를 사용하여 'employees' 테이블의 각 행을 반복하고 WHILE 루프를 사용하여 급여 인상을 수행합니다. 커서가 테이블의 끝에 도달할 때까지 루프가 계속되며, 이 시점에서 'done' 변수가 1로 설정되고 루프가 종료됩니다.

저장 프로시저를 호출할 수 있습니다.

CALL IncreaseSalaries(5);

이 예에서는 백분율 값이 5인 저장 프로시저를 호출하여 각 직원의 급여를 5%씩 증가시킵니다.

WHILE 루프는 SQL의 저장 프로시저 및 함수 내에서 반복적인 작업과 반복 처리를 수행할 수 있는 강력한 구조입니다.

---

ITERATE / LEAVE

MySQL에서 ITERATE 및 LEAVE 문은 저장 프로시저 및 함수 내에서 루프의 흐름을 제어하는 데 사용됩니다. 두 문은 모두 WHILE, REPEAT, LOOP와 같은 루프 구문과 함께 사용됩니다.

1. ITERATE
ITERATE 문은 루프를 처음부터 다시 시작하여 현재 반복에 대한 루프 본문 내의 나머지 코드를 건너뛰는 데 사용됩니다. 루프는 고유 식별자로 레이블을 지정해야 하며, ITERATE 문은 해당 식별자를 참조하여 어떤 루프를 다시 시작해야 하는지 나타냅니다.

ITERATE 레이블_이름;

2. LEAVE
LEAVE 문은 루프를 즉시 종료하여 루프 실행을 종료하고 루프 다음 코드를 계속 실행하는 데 사용됩니다. ITERATE 문과 마찬가지로 루프에는 고유 식별자로 레이블을 지정해야 하며, LEAVE 문은 해당 식별자를 참조하여 어떤 루프를 종료해야 하는지 나타냅니다.

LEAVE 레이블_이름;

다음은 MySQL 저장 프로시저 내에서 ITERATE 및 LEAVE를 함께 사용하는 예제입니다:

DELIMITER //
CREATE PROCEDURE IterateAndLeaveExample()
BEGIN
  DECLARE i INT DEFAULT 1;

  MAIN_LOOP: LOOP
    IF i > 10 THEN
      LEAVE main_loop;
    END IF;

    IF i MOD 2 = 0 THEN
      SET i = i + 1;
      ITERATE main_loop;
    END IF;

    -- 홀수 'i'로 무언가를 수행한다.
    SELECT i;

    SET i = i + 1;
  END LOOP main_loop;
END //
DELIMITER ;

이 예제에서는 main_loop로 레이블이 지정된 LOOP가 있습니다. 이 루프는 1부터 10까지의 숫자를 반복합니다. i가 10보다 크면 LEAVE 문이 루프를 종료하는 데 사용됩니다. i가 짝수인 경우 ITERATE 문은 루프를 처음부터 다시 시작하여 루프 본문의 나머지 코드를 효과적으로 건너뛰고 다음 반복으로 이동합니다. i가 홀수이면 SELECT 문이 실행됩니다.

ITERATE 및 LEAVE 문은 저장 프로시저 및 함수에서 루프 실행에 대한 추가 제어 기능을 제공하므로 MySQL에서 보다 복잡하고 유연한 루프 구조를 만들 수 있습니다.

---