[SQL] SQL 활용 - 절차형 SQL

절차형 SQL이란

절차형 SQL이란 일반적인 언어형식과 같이 절차적으로 명령을 수행하는 SQL을 뜻한다.

DBMS 벤더별로  PL, SQL, SQL/PL, T-SQL 등의 절차형 SQL이 있다.

 

절차형 SQL은 DBMS 엔진에서 실행되고 단일 SQL 문장으로 실행하기 어려운 연속적인 작업 처리가 가능하다.

또한 로직을 캡슐화할 수 있기 때문에 반복 적인 DB 작업을 효율적으로 수행하고 소프트웨어 개발 생산성을 높인다는 장점이 있다.

 

절차형 SQL은 벤더사마다 약간의 차이가 있을 수 있습니다. 각 벤더사는 자체적인 확장 문법이나 기능을 추가하여 절차형 SQL을 구현하고 있습니다. 

1.Oracle Pl/SQL

- Block 구조로 되어있어 기능별로 모듈화 가능

- 변수, 상수 등을 선언하여 SQL 문장 간 값 교환 가능

- IF, LOOF 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 함

- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용 가능

- PL/SQL은 Oracle에 내장되어있어서, 이를 지원하는 어떤 서버로도 프로그램을 옮길 수 있음

- PL / SQL은 응용 프로그램의 성능을 향상시킴

- 이는 SQL 문장을 여러 블록으로 묶고 한 번에 블록 전부를 서버로 보내기 때문에 통신량을 줄일 수 있음

- PL/SQL 엔진 : 프로그램 문장 처리

- SQL Statement Executor : SQL 문장 처리

 

2.Microsoft T-SQL

T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어로서, SQL에 약간의 기능을 더 추가해 보완적으로 만든 것이다. 이를 이용하여 다양한 저장모듈을 개발할 수 있다. 

 

- 전역변수 선언은 @@, 지역변수 선언은 @

- 전역변수는 이미 SQL 서버에 내장된 값, 지역변수는 사용자의 연결시간동안만 사용하기위해 만들어짐

- int, float, varchar 등의 자료형 사용 가능

- 연산자 사용 가능

- IF-ELSE, WHILE, CASE-THEN 등의 흐름 제어 기능

- 주석 기능은 '--, /**/' 으로 사용 가능

3.MYSQL

 

1) 프로시저(Procedural Language) :

일련의 쿼리들을 하나의 함수처럼 실행하기 위한 쿼리의 집합이다. 자주 사용되는 SQL문을 DB 객체로 생성하여 저장한 후 필요시에 프로시저명을 호출해서 사용할 수 있다.

 

- 프로시저 예시: 특정 부서의 직원 수를 반환하는 저장 프로시저입니다. IN 매개변수로 부서 이름을 받고, OUT 매개변수로 직원 수를 반환합니다.

CREATE PROCEDURE GetEmployeeCountByDepartment(IN department_name VARCHAR(50), OUT employee_count INT)
BEGIN
    SELECT COUNT(*) INTO employee_count FROM employees WHERE department = department_name;
END;

 

- 프로시저 문법

OR REPLACE : 기존 프로시저 존재 시 내용을 대체 한다는 명령어이다.

IN : 변수값을 입력받고 프로시저로 전달한다.

OUT: 프로시저로 처리된 결과를 리턴한다.

INOUT: 변수값을 받고 프로시저 처리 후 리턴한다.

COMMIT: 하나의 트랜잭션이 성공적으로 끝나고 데이터베이스가 일관적인 상태로 끝났을 때 사용한다

ROLLBACK: 하나의 트래잭션이 비정상적으로 끝나 처음부터 다시 시작하거나 부분적으로 연산을 취소할 때 사용한다.

 

2) 사용자 정의함수(User-Defined Function):

절차형 SQL을 사용하여 일련의 SQL 처리를 수행한다. 프로시저와 사용법이 동일하다. 하지만 수행 결과를 단일 값으로 반환 한다는 특징이 있다.

 

- 사용자 정의함수 구조 (데이터 타입과 변수를 리턴하는 부분의 프로시저와 다르다.)

CREATE FUNCTION GetEmployeeAge(employee_id INT) RETURNS INT
BEGIN
    DECLARE employee_birthdate DATE;
    DECLARE employee_age INT;
    
    SELECT birthdate INTO employee_birthdate FROM employees WHERE id = employee_id;
    
    SET employee_age = TIMESTAMPDIFF(YEAR, employee_birthdate, CURDATE());
    
    RETURN employee_age;
END;

- 사용자 정의함수 문법:

프로시저와 동일

 

3) 트리거(Trigger)

트리거는 데이터베이스 시스템에서 삽입, 갱신, 삭제 등의 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL이다. 사용자가 직접 호출하는 것이 아닌 DBMS에서 자동적으로 호출하는 것이 특징이다.

 

- 트리거 종류:

행 트리거: DML의 영향을 받는 모든 행 레코드마다 트리거가 작동되는 트리거를 뜻한다. 데이터 변화가 생길 때마다 실행된다.(FOR EACH ROW 명령어를 사용하면 행 트리거)

명령문 트리거: 데이터가 삽입, 삭제, 갱신되더라도 트리거에 의해 단 한 번 실행되는 트리거를 뜻한다.

 

- 트리거 예시:업데이트 이전과 이후의 가격이 다른 경우에만 트리거 내의 작업을 수행하도록 하는 조건문

CREATE TRIGGER update_product_price_trigger
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price <> OLD.price THEN
        UPDATE sales SET price = NEW.price WHERE product_id = NEW.id;
    END IF;
END;

 

- 트리거 문법:

BEFORE: 테이블명에 대한 작업을 수행하기 전에 트리거가 실행하게 하는 명령어

AFTER: 테이블명에 대한 작업을 성공적으로 실행했을 때 트리가 실행하도록 하는 명령어

INSERT: 새로운 행 삽입

DELETE: 기존 행 삭제

UPDATE: 기존 행 갱신

FOR EACH ROW:  매번 변경되는 데이터 행의 수만큼 실행을 위한 명령어(행 트리거 판별)

 

참고자료

https://for-it-study.tistory.com/67

절차형 SQL 개념 및 종류(프로시저, 사용자정의함수, 트리거)

https://eehoeskrap.tistory.com/81

[SQLD] 28. 절차형 SQL