스토어드 프로시저 1 - 스토어드 프로시저 사용 방법

저녁을 먹기 전에 이 단원을 학습해보려고 한다. 지금까지 배운 SQL을 자동화하지 않고 계속 반복적으로 사용하기에는 상당한 불편함과 한계가 있다. 스토어드 프로시저를 사용하면 MySQL 안에서도 다른 프로그래밍 언어처럼 프로그램 로직의 코딩이 가능하다.

 

시작하기 전에

SQL은 데이터베이스에서 사용되는 언어이다. 그런데 SQL을 사용하다 보면 다른 프로그래밍 언어의 기능이 필요할 때가 있다. MySQL의 스토어드 프로시저는 SQL에 프로그래밍 기능을 추가해서 일반 프로그래밍 언어와 비슷한 효과를 낼 수 있다.

---

 

 

 

 

스토어드 프로시저 기본

스토어드 프로시저의 완전한 형식은 어렵게 느낄 수도 있지만, 실제로 사용하는 형식은 간단하다. 

 

스토어드 프로시저의 개념과 형식

스토어드 프로시저(저장 프로시저)란 MySQL에서 제공하는 프로그래밍 기능이다. C, 자바, 파이썬 등의 프로그래밍과는 조금 차이가 있지만, MySQL 내부에서 사용할 때 적절한 프로그래밍 기능을 제공해 준다.

또한 스토어드 프로시저는 쿼리 문의 집합으로도 볼 수 있으며, 어떠한 동작을 일괄 처리하기 위한 용도로도 사용한다. 자주 사용하는 일반적인 쿼리를 반복하는 것보다는 스토어드 프로시저로 묶어 놓고, 필요할 때마다 간단히 호출만 하면 훨씬 편리하게 MySQL을 운영할 수 있다.

① 필수 항목으로 스토어드 프로시저를 묶어주는 기능을 한다. $$는 $ 1개만 사용해도 되지만 명확하게 표시하기 위해 2개를 사용한다. ##, %%, &&, // 등으로 바꿔도 된다..

② 스토어드 프로시저의 이름을 정해준다. 이름은 마음대로 지어도 되지만, 가능하면 이름만으로 스토어드 프로시저라는 것을 알 수 있도록 표현하는 것이 좋다. 여기서는 procedure라는 의미도 끝에 _proc 등을 붙여서 이름을 지었다.

 

CREATE ROCEDURE는 스토어드 프로시저를 만든 것뿐이며, 아직 실행(호출)한 것은 아니다. 비유하자면 커피 자판기를 만든 것이지, 커피를 뽑은 것은 아니다.

 

 

스토어드 프로시저를 호출하는 형식은 다음과 같이 한다. CALL 다음에 스토어드 프로시저의 이름과 괄호를 붙여주면 된다. 필요하다면 괄호 안에 매개변수를 넣어서 사용할 수도 있다.

---

 

 

 

 

스토어드 프로시저의 생성

간단한 스토어드 프로시저의 생성을 예로 들어보겠다.

 

---

 

 

 

스토어드 프로시저의 삭제

삭제할 때는 DROP PROCEDURE를 사용할 수 있다. 주의할 점은 CREATE PROCEDURE에서는 스토어드 프로시저 이름 뒤에 괄호를 붙이지만, DROP PROCEDURE에서는 괄호를 붙이지 않아야 한다.

 

---

 

 

 

 

스토어드 프로시저 실습

스토어드 프로시저에는 프로그래밍 기능을 사용하고 싶은 만큼 적용할 수 있다. 그러면 더 강력하고 유연한 기능을 포함하는 스토어드 프로시저를 생성할 수 있다.

 

매개변수의 사용

스토어드 프로시저에서는 실행 시 입력 매개변수를 지정할 수 있다. 입력 매개변수를 쉽게 비유하면 자판기를 사용할 때 동전을 넣고 버튼을 누르는 동작으로 생각하면 된다.

 

입력 매개변수 형식은 다음과 같다.

 

입력 매개변수가 있는 스토어드 프로시저를 실행하기 위해서는 다음과 같이 괄호 안에 값을 전달하면 된다.

 

스토어드 프로시저에서 처리된 결과를 출력 매개변수를 통해 얻을 수도 있다. 출력 매개변수는 커피 자판기에서 미리 준비하고 있는 컵이라고 보면 된다. 비어 있던 컵에는 커피가 담겨 돌아온다.

 

 

출력 매개변수 형식은 다음과 같다.

출력 매개변수에 값을 대입하기 위해서는 주로 SELECT ~ INTO 문을 사용한다.

 

출력 개개변수가 있는 스토어드 프로시저를 실행하기 위해서는 다음과 같이 사용한다.

---

 

 

 

 

입력 매개변수의 활용

입력 매개변수가 있는 스토어드 프로시저를 생성하고 실행해 보겠다.

 

 

 

이번에는 2개의 입력 매개변수가 있는 스토어드 프로시저를 만들어보겠다.

---

 

 

 

 

출력 매개변수의 활용

이번에는 출력 매개변수가 있는 프로시저를 생성하겠다. 다음 스토어드 프로시저는 noTable이라는 이름의 테이블에 넘겨받은 값을 입력하고, id 열의 최댓값을 알아내는 기능을 한다. id열의 최댓값은 방금 입력한 행의 순차 번호이다.

 

 

CALL은 사용하지 않았으니 아직 실행되지 않는다. noTable의 구조를 확인하기 위해 다음의 코드를 실행해 보았다.

 

 

 

이상한 점은 user_proc3은 오류 없이 잘 만들어졌다는 것이다. 스토어드 프로시저를 만드는 시정에는 아직 존재하지 않는 테이블을 사용해도 된다. 단, CALL로 실행하는 시점에는 사용한 테이블이 있어야 한다. 이제 noTable을 만들어보겠다.

 

 

출력 매개변수 위치에 @변수명 형태로 변수를 전달해 주면 그 변수에 결과가 저장된다. 그리고 SELECT로 출력하면 된다.

 

이 코드를 계속 실행하면 값이 하나씩 증가한다.

---

 

 

 

 

SQL 프로그래밍의 활용

이번에는 스토어드 프로시저 안에 SQL 프로그래밍을 활용해 보겠다. 조건문의 기본인 IF ~ ELSE 문을 사용해본다. 가수 그룹의 데뷔 연도가 2015년 이전이면 '고참 가수', 2015년 이후(2015년 포함)이면 '신인 가수'를 출력하는 스토어드 프로시저를 작성해 볼 것이다.

 

---

 

 

 

날짜와 관련된 MySQL 함수

MySQL은 날짜와 관련된 함수를 여러 개 제공한다. YEAR(날짜), MONTH(날짜), DAY(날짜)를 사용할 수 있는데 날짜에서 연, 월, 일을 구해준다. 또 CURDATE() 함수는 현재 날짜를 알려준다. 다음 SQL은 현재 연, 월, 일을 출력한다.

 

 

이번에는 여러 번 반복하는 while 문을 활용해보겠다. 1부터 100까지의 합계를 계산할 것이다.

 

 

 

 

마지막으로 동적 SQL을 활용해보겠다. 동적 SQL은 이름 그대로 다이내믹하게 SQL이 변경된다. 다음 예제는 테이블을 조회하는 기능을 한다. 그런데 테이블은 고정된 것이 아니라, 테이블 이름을 매개변수로 전달받아서 해당 테이블을 조회한다.

 

스토어드 프로시저는 다른 개체의 삭제와 마찬가지로 DROP PROCEDURE 프로시저_이름 구문을 사용해서 삭제한다.

---

 

 

 

 

단원 마무리하기

 

 

스토어드 프로시저는 테이블이 아니라 데이터베이스 내부에 저장된다.

 

 

 

---

 

 

 

학습을 마치고

저녁식사 전에 이 과정을 마칠 수 있어서 감사했다. 조금 전에 엘리스 SQL 심화 과정을 모두 마쳤다. 그리고 이제 오늘까지 해서 SQL의 모든 기본 과정을 마칠 생각이다. 마지막 단원에 나오는 파이썬과 연결은 지금은 사용하지 않을 것 같아 필요할 때 공부하고 프로시저까지는 학습을 해야 좋을 것 같다.