MYSQL PROCEDURE PARAMETER[IN, OUT](프로시저에 파라미터받기 보내기)

 

밑의 명령문이 실행되는 procedure을 만들었다 

SELECT * FROM product WHERE 가격 > 5000;

 

나중에 가격이 6000 이상인걸 조회하고 싶으면 어쩌죠? 

그러면 만들어둔 procedure는 아무 쓸모가 없어지겠군요.

그래서 파라미터로 값을 받아 광범위하게 사용가능한 프로시저를 만들어보자

 

일반 프로시저

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

 

이러면 항상 5000원이 넘는 상품밖에 출력하지 못합니다.

 

파라미터를 받는 프로시저

CREATE PROCEDURE mart.get_all(구멍 INT)
BEGIN
    SELECT * FROM product WHERE 가격 > 구멍;
END

 

(1) 작명

가변적인 자리에 마음대로 작명합니다. 저는 구멍이라고 작명했음  

(2) 등록

작명한걸 소괄호 안에도 등록해줍니다. 뒤에 데이터타입도 넣어야하는데 마음대로 넣어봅시다.  

(3) 사용 

그럼 이제 get_all() 사용할 때 소괄호 안에 아무 INT 자료를 넣을 수 있는데 

그 숫자가 구멍자리에 쏙 박혀서 get_all() 이 실행됩니다. 

 

3번이 뭔소리냐면 

CALL get_all(6000)

이러면 SELECT * FROM product where 가격 > 6000 이게 실행되어서

6000원 넘는 상품만 가져옵니다. 

 

CALL get_all(7000)

이러면 SELECT * FROM product where 가격 > 7000 이게 실행되어서

7000원 넘는 상품만 가져옵니다. 

 

 

 

파라미터 문법 세부사항 

 

CREATE PROCEDURE mart.get_all( 구멍1 INT, 구멍2 varchar(100) )
BEGIN
    SELECT * FROM product WHERE 가격 > 구멍1 OR 상품명 = 구멍2;
END

1. 구멍은 여러개 뚫을 수 있습니다. 맘대로 작명가능 

2. 구멍을 소괄호 안에 등록할 때 여러개면 구멍1, 구멍2 이렇게 콤마로 구분하면 됩니다. 

3. 구멍에 들어갈 데이터의 타입을 강제로 정해줘야합니다. 

그럼 앞으로 get_all() 쓸 때 무조건

구멍자리엔 INT,

구멍2 자리엔 varchar(100) 자료만 입력할 수 있습니다. 

 

 

OUT 파라미터 

 

구멍을 뚫으면 procedure 안으로 숫자나 문자를 입력할 수 있는데

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

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

프로그래밍 아는 분들은 함수 안에서 쓰는 return이랑 똑같습니다  

 

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

그래서 procedure 안에 구멍이라는 이름의 OUT 파라미터를 등록해봤습니다. 

그리고 그 파라미터에 20이라는 숫자를 집어넣었구요. (SET 어쩌구는 변수에 값집어넣는 문법임)

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

 

CALL get_all(@total);
SELECT @total

그럼 get_all() 쓸 때 구멍자리에 @변수를 하나 작명해주면 (변수는 @변수명 이렇게 작명한다고 했습니다)

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

SELECT로 @변수 출력해보거나 그러면 진짜로 들어있습니다.

 

그래서 결론은 procedure 안에 있던 유용한 자료를 바깥에서 쓰고 싶은 경우 

OUT 파라미터를 사용하면 됩니다. 

SQL로 프로그래밍할 일이 없으면 별로 쓸모는 없습니다. 

 

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;
$$ 

▲ Postgre

 

- IS 아니면 DECLARE 뒤에 변수같은걸 미리 선언할 수 있는 자리도 있습니다. 

- Postgres는 procedure 대신 function을 사용합니다. 용도는 같음