18강. 스토어드 프로시저(stored procedure)의 개념과 사용 방법(입출력 매개변수의 활용 방법)
07-1. 스토어드 프로시저 사용 방법
👉🏻 스토어드 프로시저의 개념과 형식
- SQL과 프로그래밍 기능이 합쳐진 것
- 일반적인 C언어, JAVA, PYTHON과 차이는 좀 있지만 큰 흐름은 프로그래밍 기능이 가능하다
🧩 형식
- $$ 외에 다른 것을 써도 된다 (##, %%, &&, // 등)
- 고정된 형식이니까 따로 신경쓰지 않아도 된다
- 실제 프로시저를 만드는 것은 CREATE PROCEDURE
- 이름은 우리가 직접 만드는 것
- 매개변수
- BEGIN ~ END $$ 사이에 SQL 프로그래밍 코드를 넣음 (몇백줄까지도 가능)
- 형식은 고정된 형식이기 때문에 따로 신경쓰지 않아도 된다
- 프로시저 이름, 매개변수, 내용만 신경쓰면 된다
🧩 실행
- 프로시저를 만드는 것과 호출(실행)하는 것은 다른 얘기다
✅ 만드는 것
- 커피 자판기를 만든 것
- 커피 자판기를 만들었다고 해서 자동으로 커피가 나오지는 않음
✅ 호출(실행)하는것
- 커피 자판기를 실행을 해야만 커피가 나온다
🎮 스토어드 프로시저 생성
- market_db 초기화
- 마켓 DB 사용하기
- 만약 있다면 지워라, 없으면 넘어감
- 프로시저 이름 = user_proc()
- 이름만 가지고도 프로시저임을 알 수 있게 _proc을 붙여주기
- 매개변수는 () 안에 들어 있어야 하는데 없어도 된다
- 프로시저 내용은 member 테이블 조회
- 여기까지 커피 자판기를 만든 것
- 실행
* 커피를 마실 때 마다 커피 자판기를 만드는 것이 아니라,
커피 자판기를 한 번 만들어 두면 커피를 여러번 뽑아 먹을 수 있다
- CALL은 필요시마다 언제는 실행할 수 있다
🎮 스토어드 프로시저 삭제
- () 안 쓰고 이름만 써 주면 됨
👉🏻 매개 변수의 사용
🧩 입력 매개변수
- 커피 자판기를 실행할 때 그냥 실행되게 할 수도 있지만 동전이나 버튼을 이용하게 할 수도 있다
- 입력 매개변수
- 프로시저에게 어떤 값을 전달함
✅ 형식
✅ 호출시
- 전달 값이 매개변수에 들어가서 실행 되는 것
🎮 입력 매개변수 활용
- 마켓 DB 사용
- 만약 user_proc1이 있다면 지우고 아니면 넘어가라
- 입력 매개변수: userName, 이름은 내가 지어줌, 데이터 형식은 VARCHAR, 10글자
- 내용에서 변수를 사용해야함
- mem_name이 입력받은 내용인 userName과 같은 데이터를 조회
- 커피 자판기를 만들었음
- 프로시저를 호출하여 에이핑크를 찾음
- 결과적으로 SELECT * FROM member WHERE mem_name = '에이핑크'; 가 된다
🎮 입력 매개변수 여러개
- 만약 프로시저가 이미 있다면 삭제하고 아니라면 넘어가라
- userNumber (내가 지은 이름) 를 정수형으로 받는다
- userHeight (내가 지은 이름) 를 정수형으로 받는다
- 입력받은 수 보다 멤버가 많고, 입력 받은 수 보다 평균키가 큰 데이터를 조회
- 멤버 수가 6명보다 많고, 평균 키가 165보다 큰 데이터 조회
- SELECT * FROM member WHERE mem_number > 6 AND height > 165;
🧩 출력 매개변수
- 실행 후 결과를 돌려 받을 매개변수
- 커피를 받을 때 빈 컵을 넣어 빈 컵에 커피를 채워 가져 오는 것
✅ 형식
✅ 사용
- 일반적으로 변수를 활용한다
- 변수는 비어있는 컵이라고 생각하면 된다
- SELECT를 통해 변수에 담긴 내용을 확인할 수 있다
🎮 출력 매개변수 활용
- 해당 프로시저가 있다면 삭제 없으면 넘어가기
- 10글자 짜리 입력 매개변수
- 정수형의 출력 매개변수
- noTable에 입력 매개변수를 입력, NULL은 id
- 현재 입력된 가장 큰 값을 조회하는데 그것을 화면에 출력하지 않고 출력매개변수인 outValue에 들어감
➡️ 테이블 구성 확인하기
- 오류남. 우리는 이런 테이블을 만든 적이 없음
- 테이블이 존재하지 않는데도 저장 프로시저가 에러나지 않고 만들어짐
* 저장 프로시저를 만드는 시점에는 해당 테이블이 없어도 된다. 왜냐면 실행한 게 아니니까!
* 저장 프로시저를 실행할 때는 반드시 해당 테이블이 있어야 한다
➡️테이블 만들기
- ID는 자동증가
- 이제 호출 가능
- 입력 매개변수만 넣어주고
- 출력 매개변수는 변수명을 적어줌, @붙이기
- outValue의 값이 @myValue로 들어가는 것
- 실행이 됨
- 한 번 넣었으니 1
- CALL을 여러번 실행해보면
🎮 SQL 프로그래밍 활용 - IF ELSE
- 만약 이미 있다면 지우고 없다면 넘어가
- 입력 변수는 10글자짜리 문자열
- DECLARE를 통해 변수를 만듦, 데뷔 년도, 정수형
* 저장 프로시저 밖의 변수 선언은 @만 붙이면 됨
* 저장 프로시저 안에서 변수 선언은 DECLARE 활용(DECLARE 변수명 변수타입;)
- 입력 받은 이름에 해당하는 데이터의 데뷔 날짜에서 연도만 추출해서 debutYear 변수에 넣어준다
- 그룹의 데뷔 날짜가 2015년 이후면 신인, 이전이면 고참
🎮 SQL 프로그래밍 활용 - 반복문
- DECLARE로 합과 증가할 숫자 변수 선언
- SET(변수에 값 대입)을 통해 변수 초기화
- num이 100보다 작은 동안 num을 계속 더함
🎮 SQL 프로그래밍 활용 - 동적 SQL
- SQL문이 고정되지 않고 계속 바뀌도록 하는 것
- SET을 통해 SQL 쿼리문을 변수로 준비
- CONCAT을 통해 ' '안의 SQL문과 tableName을 연결
- PREPARE를 통해 @sqlQuery에 담긴 쿼리문이 myQuery에 준비됨
- EXECUTE를 통해 myQuery에 담긴 SQL문이 실행됨
- 입력되는 테이블명에 따라 SQL문이 계속 바뀌는 것
출처: https://youtu.be/cw1wGN0ZdFA?si=nIYcEtEBoSo_yHie