procedure 많이 만들기 싫다면? 파라미터로 해결하세요~

저번시간에 이어 저희가 쭉 procedure를 만든다면 같은 코드를 쉽게 재사용할 수 있다고 배웠습니다.

 

SELECT * FROM product WHERE 가격 > 5000;

 

이런걸 프로시저로 만들어 봤는데요.

 

그럼 나중에 갑자기 가격이 6000 이상으로 바뀐다면 어떡할까요?

 

그러면 만들어둔 procedure을 삭제하고 다시만들까요?

 

저는 고품격 달려라 국나뇽이기 때문에 파라미터를 사용하면 하나의 procedure로 다양한 상황에 대응가능한 것을 배우겠습니다.

 

구멍뚫기 문법

CREATE PROCEDURE emart.get_all()
BEGIN
	SELECT * FROM product WHERE 가격 > 5000;
END

저번시간에는 이렇게 프로시저를 만들어보았습니다.

 

이러면 항상 5000원이 넘는 상품밖에 출력하지 못하겠죠?

 

그게 싫고 매번 가변적으로 바꾸려면 거기에 구멍을 뚫어놓으면 됩니다.

CREATE PROCEDURE emart.get_all(변동가격 INT)
BEGIN
	SELECT * FROM product WHERE 가격 > 변동가격;
END

구멍 뚫는 법은

 

(1) 작명

가변적인 자리에 마음대로 작명합니다.

저는 변동가격이라고 작명하겠습니다.

 

(2) 등록

작명한 것을 소괄호 안에도 등록해줍니다. 뒤에 데이터 타입도 붙이고요.

 

(3) 사용

그럼 이제 get_all()을 사용할때 소괄호 안에 아무 INT 자료를 넣을 수 있습니다. 그럼 그 숫자가 ‘변동가격’이 쏙박혀 get_all()이 실행됩니다.

 

무슨말이냐면

CALL get_all(6000)

이렇게 쿼리를 날리면

SELECT * FROM product WHERE 가격 > 6000; 이 된다는 소리입니다.

CALL get_all(7000)

이러면 어떨까요?

SELECT * FROM product WHERE 가격 > 7000; 이렇게 됩니다.

 

이제 저번시간까지 배운 프로시저는 싹 잊어버리십쇼. 쓰렉입니다.

이제는 유용해보이는 군요.

 

그래서 코드 실행할때 항상 가변적인 부분이 필요하면 구멍을 뚫어줍니다.

 

이것을 있어보이게 파라미터라고 합니다.

 

파라미터 문법 세부사항

CREATE PROCEDURE emart.get_all(파라미터1 INT, 파라미터2 varchar(100))
BEGIN
	SELECT * FROM product WHERE 가격 > 파라미터1 OR 상품명 = 파라미터2;
END

1. 파라미터는 여러개 뚫을 수 있습니다. (맘대루 작명)
2. 파라미터를 소괄호 안에 등록할때, 여러개를 쓰고 싶다면 콤마(,)로 구분
3. 파라미터에 들어갈 데이터의 타입을 강제로 무족건 정해줘야합니다.
   그럼 앞으로 get_all() 호출할때는
   무족건 파라미터1 자리엔 int 파라미터2 자리엔 varchar(100) 만 입력가능합니다.

 

OUT 파라미터

구멍 뚫으면요 procedure 안으로 숫자나 문자를 입력할 수 있다고 했자나요.

 

역으로 procedure 안에서 출현한 숫자나 문자나 테이블을 밖으로 가져오는 방법도 있습니다.

 

그럴땐 파라미터 등록하는 곳에 OUT 붙이면 됩니다.

 

저희가 코딩할때 함수 만들고 마지막에 return 쓰는 것이랑 똑같습니다.

CREATE PROCEDURE emart.get_all(OUT 구멍 INT)
BEGIN
	SET 구멍 = 20;
END

procedure 안에 구멍이라는 이름의 out 파라미터를 작성해보았습니다.

 

그리고 그 파라미터에 20이라는 정수를 집어넣었구여

 

그럼 신기하게도 CALL get_all() 할 때마다 20이라는 값을 바깥에서 사용할 수 있습니다.

CALL get_all(@total);
SELECT @total

그럼 get_all() 쓸 때 구멍 자리에 @변수를 하나 작성해주면 (변수는 @변수명 이렇게 작성한다고 배웠쥬?)

 

프로시저안에 있던 OUT 파라미터의 값이 저장됩니다.

 

SELECT로 @변수 출력해보면 진짜로 20이 들어있습니다;;

 

그래서 결론은 procedure 안에 있던 유용한 자료를 바깥에서도 쓰고 싶은 경우 OUT 파라미터를 사용하면 됩니다.

 

Postgres, Oracle에서의 procedure 생성문법은 비슷한데요…

CREATE OR REPLACE PROCEDURE 프로시저명(구멍 varchar2(100))
IS 
  변수1 number := 0;
  변수2 varchar2(100) := '안녕';
BEGIN
  보관할 코드;
END;

⬆️ Oracle

CREATE OR REPLACE FUNCTION 프로시저명(구멍 varchar(100)) RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE 변수 integer := 0;
BEGIN
    보관할 코드;
END;
$$ 

⬆️ PostgreSQL

• IS 아니면 DECLARE 뒤에 변수 같은걸 미리 선언할 수 있는 자리도 있습니다.
• postgres는 procedure 대신 function을 사용합니다.(용도는 같음)