[혼자 공부하는 SQL] 스토어드 프로시저 사용 방법

안녕하세요, 혼자 공부하는 SQL를 보고 학습한 자료를 남깁니다.

 

스토어드 프로시저란

스토어드 프로시저 기본

DELIMITER $$

END $$

DELIMITER ;

이렇게 프로시저를 구분을 한다. (##, %%, &&, //) 등을 사용을 하여도 무방하다.

CREATE PROCEDURE [스토어드 프로시저 이름]( IN 또는 OUT 매개변수) 부터 END$$ 까지 

SQL 프로그래밍 코드를 작성할 수 있다.

스토어드 프로시저 이름은 우리가 정할 수 있다.

 

스토어드 프로시저를 만든 것을 커피 자판기를 만든 것으로 볼 수 있다.

실제 실행을 하려면 CALL [스토어드 프로시저 이름](); 명령어를 실행하여

프로시저를 호출할 수 있다. 커피 자판기에서 커피를 뽑는 것이다. 그러면 커피 자판기에서 커피를 뽑는 작동을 한다.

 

스토어드 프로시저를 만들어보았다.

그리고 CALL 명령어를 통해서 실행을 해보았다.

필요할 때마다 실행을 하면 SQL 프로그래밍 언어 코드가 실행이 된다.

프로시저 삭제

DROP PROCEDURE 명령어를 통해서

스토어드 프로시저 삭제가 가능하다.

입력 매개변수

입력 매개변수를 통해서 스토어드 프로시저에 전달할 수 있다.

커피자판기에 500원짜리 동전을 넣는 것과 동일하다.

IN [입력 매개 변수 이름] [데이터 형식]

명령어를 통해서 입력 매개변수를 지정할 수 있다.

그리고 CALL [프로시저 이름]([전달 값]); 명령어를 통해서

스토어드 프로시저에 입력 매개변수를 전달할 수 있다.

출력 매개 변수

실행한 스토어드 프로시저의 결과를 돌려 받을 매개변수가 바로

출력 매개변수이다.

OUT [출력 매개변수 이름] [데이터 형식];

CALL [프로시저 이름](@변수명)

SELECT @변수명; 명령어를 통해서 눈으로 변수에 담긴 값을 확인이 가능하다.

입력 변수 실습

IN 명령어를 통해서 입력 매개변수와 데이터 형식 지정이 가능하다.

그리고 SELECT * FROM member WHERE mem_name = userName; 을 통해서

입력 매개변수를 SQL 프로그래밍 코드에서 활용이 가능하다.

스토어드 프로시저를 실행하고 입력 매개변수를 전달하기 위해서는

CALL user_proc1('에이핑크'); 이렇게 '에이핑크' 문자를 넣어주면 입력 매개변수를 저장하고 스토어드 프로시저에서 활용이 가능하며 그에 대한 결과가 조회된다.

입력 매개변수는 여러개 선언이 가능하다.

콤마를 통해서 구분이 가능하다.

CALL user_proc2(6, 165); 이렇게 두 개의 INT 매개변수 두개를 전달하고

그에 대한 결과를 조회하는 것을 볼 수 있다.

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

INSERT 문을 통해 noTable에 데이터를 입력하는 것을 볼 수 있다.

그리고 SELECT INTO를 통해서 조회한 최대 아이디 값을 outValue라는 출력 매개변수에 저장하는 것을 볼 수 있다.

스토어드 프로시저 생성을 완료하고, 

DESC noTable; 을 실행을 했는데 noTable이 조회가 되지 않는다.

스토어드 프로시저를 생성하는 시점에서는 선언하는 것이므로 문제가 되지 않는다.

하지만 실행을 하면 오류가 발생을 하는 것을 볼 수 있다.

DESC 또는 DESCRIBE 명령어를 통해서 테이블의 정보를 확인이 가능하다.

그래서 noTable을 만들고 다시 user_proc3 스토어드 프로시저를 호출하였다.

그리고 출력 매개변수를 조회를 해보면 다음과 같이 정상적으로 나오는 것을 볼 수 있다.

이번에는 IF 및 ELSE 문이 담긴 스토어드 프로시저를 생성해보았다.

YEAR() 함수를 통해서 연도만 출력을 한 것을 확인할 수 있다.

정상적으로 결과가 조회되는 것을 확인할 수 있다.

추가적으로 YEAR(), MONTH(), DAY()를 통해서 연도, 월, 날을 구할 수 있다.

또한 CURDATE() 함수를 통해서 현재 날짜 (연도-월-일)의 형식으로 구할 수 있다.

다음은 1부터 100까지의 합계를 출력하는 스토어드 프로시저를 생성했다.

WHILE문을 통해서 작성을 했다.

다음과 같이 결과과 조회되는 것을 볼 수 있다.

다음은 동적으로 SQL 프로그래밍을 한 스토어드 프로시저를 생성해보았다.

조회하는 테이블의 명이 동적으로 변경해서 조회할 수 있는 스토어드 프로시저를 생성한 것을 볼 수 있다.

결과가 잘 조회되는 것을 볼 수 있다.

 

감사합니다.

https://www.youtube.com/watch?v=cw1wGN0ZdFA&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=19