DBeaver)procedure 많이 만들기 싫으면 파라미터

저번 시간에 procedure 만들면 같은 코드를 쉽게 재사용할 수 있다고 했습니다. 

SELECT * FROM product WHERE 가격 > 5000;

이런걸 procedure로 만들어서 써봤는데

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

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

 하지만 오늘 배울 파라미터 문법을 사용하면 하나의 procedure로 다양한 상황 대응가능

 

 

전에 했던 procedure 만드는 코드

 

DROP PROCEDURE IF EXISTS mart.get_all;

DELIMITER $$
$$
CREATE PROCEDURE mart.get_all()
BEGIN
  SELECT * FROM product where 가격 > 6000;
END 
$$
DELIMITER ; 

 

[collapse]

 

 

 

 

 

 

구멍뚫기 문법 

 

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

저번시간에 했던 procedure 생성하는 원래 SQL 코드는 이랬는데 

이러면 항상 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원 넘는 상품만 가져옵니다. 

 

 

저번시간의 get_all() 코드는 5000원 넘는 상품만 가져올 수 있었던 쓰레기같은 코드였으나

이제 덜 쓰레기같고 유용해졌군요.  

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

구멍을 전문용어로 파라미터라고합니다. 

 

 

 

 

 

 

 

파라미터 문법 세부사항 

 

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을 사용합니다. 용도는 같음