프로그래밍 언어 | 컴퓨터 관련

[SQL] SQL PROCEDURE 사용해보기

영바이트 2022. 5. 29. 22:52

 

데이터 사이언티스트, 웹 백엔드 개발자, 마케터 등 데이터를 직접 다루는 일을 하는 경우 SQL을 사용할 일이 많다. 업무를 보다 보면 웬지 언젠가 했었던 작업을 파라미터만 변경해서 하고 있다는 느낌이 올 때가 있다. 예를 들면 아래와 같은 경우를 생각해보자.

 

1. 계량경제학 교과목을 수강한 수학과 학생들의 이수 학점을 알고 싶다.

2. 데이터마이닝 교과목을 수강한 경영학과 학생들의 이수 학점을 알고 싶다.

3. ...

 

작업에 필요한 SQL들을 어딘가에 메모해 두었다가 차례로 입력하여 결과를 얻는 것도 방법이 될 수 있다. 하지만 결과를 얻기 위해 테이블을 여러개 참조해야 하는 경우라면 SQL 프로시저Procedure를 한 번 사용해 보자. 작업에 필요한 SQL 명령어 세트를 체계적으로 관리할 수 있고 데이터베이스 엔진이 전체 작업을 최적화 해 주어서 처리 속도도 개선된다.

 

 

위의 예 중 1번 경우를 가지고 한 번 살펴보자.

 

계량경제학을 수강한 수학과 학생들의 이수 학점을 알고싶다.

 

위 결과를 얻기 위한 전체 과정을 순서대로 아래와 같이 나열해 볼 수 있을 것이다.

① (학과정보 테이블에서) 수학과 학과코드를 얻는다.
SELECT department_id FROM departments WHERE department_name='수학'

② (교과목 정보 테이블에서) 계량경제학 교과목 번호를 얻는다.
SELECT class_id FROM class_info WHERE class_name='계량경제학'

③ (수업 테이블에서) 교과목이 계량경제학이고 수강생 학과코드가 수학과인 학생들의 학번을 얻는다.
SELECT student_id FROM classes WHERE department_id=① AND class_id=②

④ (학생정보 테이블에서) 위 학번에 해당하는 재학생들의 이수학점을 얻는다.
SELECT student_id, credits FROM students WHERE student_id IN ③ AND status='active'


전체 과정을 보면 ③번 과정에서 ①, ②번 과정의 결과를 참조하고 다시 ④번 과정에서 ③번 과정의 결과를 사용한다. 위 과정을 프로시저로 아래와 같이 작성할 수 있다.

CREATE PROCEDURE getGradePoints(IN dep_name, IN cls_name)
BEGIN

DECLARE depId TYPE OF departments.department_id;
DECLARE clsId TYPE OF class_info.class_id;

SELECT department_id INTO depId FROM departments WHERE department_name=dep_name;
SELECT class_id INTO clsId FROM class_info WHERE class_name='계량경제학';

DROP TABLE IF EXISTS selected_students;
CREATE TABLE selected_students AS SELECT student_id FROM classes WHERE department_id=depId AND class_id=clsId;

SELECT student_id, credits FROM students WHERE student_id IN selected_students;

DROP TABLE selected_students;

END;

 

예를 통해 프로시저 작성 방법을 살펴보자.

 

CREATE PROCEDURE getGradePoints(IN dep_name, IN cls_name)

프로시저 getGradePoints를 정의한다. 각 입력 변수 앞에는 키워드 IN을 붙여준다.

 

BEGIN

프로시저 시작

 

DECLARE depId TYPE OF departments.department_id;
DECLARE clsId TYPE OF class_info.class_id;

프로시저에서 사용하게 될 변수들을 DECLARE 구문을 통해 선언한다. 학과코드(depId)와 교과목번호(clsId)를 선언하였고 각 변수의 타입은 TYPE OF 명령을 사용하여 테이블을 참조하여 가져오도록 정의하였다.

 

SELECT department_id INTO depId FROM departments WHERE department_name=dep_name;
SELECT class_id INTO clsId FROM class_info WHERE class_name='계량경제학';

리터럴literal 형태 즉, 하나의 숫자 혹은 문자(열)에 해당하는 값은 SELECT INTO 구문은 사용해서 변수에 저장할 수 있다.

 

DROP TABLE IF EXISTS selected_students;

전체 결과를 얻기 위해 테이블 형태의 중간 결과물을 사용하는 경우가 있을 것이다. 만약 이전에 사용했던 중간 결과물이 있다면(IF EXISTS) 삭제한다.

 

CREATE TABLE selected_students AS SELECT student_id FROM classes WHERE department_id=depId AND class_id=clsId;

테이블 형태 즉 행과 열이 존재하는 2차원 데이터는 변수에 저장할 수 없다. 이 경우 CREATE TABLE AS 명령어를 사용하여 임시 테이블을 생성하여 저장한다. SELECT 구문의 결과를 selected_students 테이블에 저장하였다.

 

SELECT student_id, credits FROM students WHERE student_id IN selected_students;

임시 테이블 결과(selected_students)를 참조하여 최종 결과물인 학번과 이수학점을 얻었다.

 

DROP TABLE selected_students;

임시로 생성했던 중간 결과물 테이블을 삭제해준다.

 

END;

프로시저 종료

 

프로시저를 작성하는 방법을 예를 통해 살펴보았다. 몇몇 문법을 제외하면 SQL문을 순차적으로 입력하는 것과 크게 다르지 않다. 일일히 SQL문을 입력하는 것이 번거로웠다면 한 번쯤 사용해볼 것을 권한다.

 

 

실제 데이터베이스에 입력할 때는 아래와 같은 행 구분 문자(delimiter, SQL문이 끝났음을 알려주는 구분 문자) 재정의 과정이 필요하다. 아래 예는 MySQL(MariaDB) 문법을 참조하였다.

 

DELIMITER //

CREATE PROCEDURE getGradePoints(IN dep_name, IN cls_name)
BEGIN

...

END;

//

DELIMITER ;

 

DELIMITER //

행 구분 문자를 //로 변경한다. SQL 프로시저를 입력할 때 행 구분 문자로 세미콜론(;)을 입력할 수 있도록 해 준다.

 

(프로시저 입력)

 

//

//를 입렵하여 프로시저 입력이 끝났음을 알려준다.

 

DELIMITER ;

행 구분 문자를 //에서 세미콜론(;)으로 다시 변경해 준다.

 

입력한 프로시저는 SHOW PROCEDURE STATUS 명령으로 확인할 수 있다.

> SHOW PROCEDURE STATUS

 

프로시저 내용을 보기 위해서는 SHOW PROCEDURE CODE 명령을 사용한다. 프로시저 내용은 데이터베이스 엔진에 의해 최적화되어서 저장된다. 따라서 사용자가 입력한 그대로의 형태를 볼 수는 없다.

> SHOW PROCEDURE CODE 프로시저 이름

 

프로시저는 CALL 명령을 이용해서 호출한다.

> CALL 프로시저 이름(변수 1, 변수 2, ...)

 

등록한 프로시저를 삭제할 때는 DROP 명령을 이용한다

> DROP PROCEDURE 프로시저 이름

 

■