DBeaver)procedure와 비슷한 function 문법

실은 procedure와 비슷한 문법이 하나 있습니다.

function (함수) 문법인데 용도는 같습니다만 기능만 살짝 다릅니다.

procedure 왜 만들어쓴다고 했죠? 

그냥 자주 쓰는 긴 코드 한 단어로 만들어놓고 싶을 때 쓴다고 했습니다.

function도 똑같음 

 

 

 

 

 

DBeaver에서 function 만드는 법 

 

 

▲ 데이터베이스 누르면 있는 procedure 만드는 곳에서 똑같이 만들 수 있습니다.

다만 procedure 말고 function으로 선택하고 이름 아무렇게나 작명하면 됩니다. 

 

 

 

 

▲ 그리고 똑같이 BEGIN / END 사이에 자주 쓸 코드 집어넣어두고 저장하면 됩니다. 

그럼 앞으로 함수명() 사용할 때 마다 BEGIN / END 사이에 있던 긴 코드가 실행됩니다. 

용도는 procedure와 비슷하죠?

하지만 문법 차이점을 좀 알아봅시다. 

 

 

 

 

 

 

function 만들 때 필요한 문법 

 

CREATE FUNCTION DB이름.함수이름(구멍 INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN 100;
END 

1. CREATE FUNCTION 뒤에 이름 작명하고 소괄호 안에 파라미터 넣을 수 있는건 procedure와 비슷하고

2. RETURNS 뒤에 RETURN할 자료의 데이터타입을 넣어둬야합니다. 

3. DETERMINISTIC 도 넣어둬야하는데 설명은 뒤에서 합시다. 

 

4. RETURN 뒤에 function 사용 후에 뱉을 값을 기입해줘야합니다. 

RETURN은 전에 했던 OUT 파라미터랑 동일한 용도입니다. 

무조건 어떤 데이터를 RETURN 해야한다는게 procedure와 가장 큰 차이점입니다. 

 

 

 

 

 

 

 

 

 

 

 

그럼 procedure 쓰지 왜 function 만드냐

 

문법 배우는게 중요한게 아니라 이걸 왜 쓰는지 아는게 중요합니다. 

- 긴 쿼리문을 자주 재사용하고 싶을 때는 procedure 쓰면 됩니다.

- 계산기능을 만들었는데 그걸 자주 재사용하고 싶을 때는 function 쓰면 됩니다.

알겠습니까 function은 자주쓰는 계산기능을 쉽게 재사용하고 싶을 때 쓰십시오. 

 

왜냐면 function 문법은 파라미터 문법과 RETURN 문법을 매우 쓰기 쉽게 만들어주기 때문에 

뭔가 집어넣고 결과를 바깥으로 뱉는걸 매우 쉽게 할 수 있기 때문입니다. 

 

 

그래서 예를 들어

- 제품가격을 입력하면 부가세가 얼마인지 계산을 자주 해야할 때

- 제품가격을 입력하면 배송비가 얼마인지 계산을 자주 해야할 때 

- 입대날짜를 입력하면 전역까지 얼마 남았는지 계산을 자주 해야할 때

- 사람 이름에 A가 몇개 들어있는지 자주 구해야할 때

 

이런거 계산하려면 그때그때 SQL을 작성하면 되는데

너무 자주 작성한다면 function으로 만들어두면 쉽게 재사용가능합니다.  

예를 들어서 가격을 입력하면 부가세가 얼마인지 계산해주는 계산기를 만들어봅시다. 

 

 

 

 

 

 

부가세 계산 자주하고 싶은데 

 

상품가격이 있으면 부가세가 얼마인지 출력하고 싶으면 어떻게하죠?

가격에 0.1 곱하면 됩니다.

 

SET @가격 = 10000;
SET @가격 = @가격 * 0.1;
SELECT @가격; 

@가격이라는 변수를 만들어서 0.1을 곱해서 출력했습니다.

이러면 부가세가 1000원이라고 구할 수 있겠군요. 

 

근데 변수에 0.1 곱하는 짓거리를 너무 자주하는겁니다.

그러면 function으로 만들어두면 편리할 수 있습니다. 

예를 들어서 어떤 숫자를 집어넣으면 부가세를 남겨주는 function을 만들어봅시다. 

 

 

 

CREATE FUNCTION mart.vat(구멍 INT) 
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN 구멍 * 0.1;
END 

1. vat라고 함수 이름 작명했습니다. 

2. 그리고 구멍으로 INT 자료를 입력할 수 있다고 해놨음 

3. 그리고 RETURN은 함수 사용하고 나서 그 자리에 뭘 뱉어줄지 적는 부분입니다.

구멍에 0.1을 곱해서 뱉으라고 코드짜놨습니다.

 

 

 

SELECT mart.vat(10000)

그럼 이제 아무데서나 vat() 함수를 사용가능한데

구멍에 숫자 입력하면 0.1이 되어서 그 자리에 뱉어줍니다. 

그걸 SELECT로 출력해보면 진짜로 0.1 되어서 나옴 

이제 부가세계산을 좀 편리하게 할 수 있겠군요.

 

 

이거 그대로 따라 적으면서

"아 부가세계산하려면 저렇게 코드를 짜는구나~"

를 외워가면 평생 부가세계산밖에 못하다가 늙어죽는 것입니다.

그거 말고 function 쓰는 이유를 잘 외워가야 혼자 코드 잘짜는 사람이 됩니다. 

자주쓰는 계산식을 편리하게 재사용하려고 function 만들어쓰는 것임

 

 

 

 

 

 

 

 

 

 

 

 

직접 SQL 코드짜서 function 만들고 싶으면 

 

DROP FUNCTION IF EXISTS 함수명;
DELIMITER $$

CREATE FUNCTION 함수명() 
RETURNS INT
BEGIN 
  어쩌구
  RETURN 저쩌구;
END 
$$
DELIMITER ;

아무데서나 실행하면 됩니다. 

함수 이름 적을 때 DB이름.함수명() 적는게 안전함 

 

 

 

 

 

 

procedure vs function 비교 정리

 

- 둘 다 SQL 코드를 저장해두고 재사용하는 용도입니다.

- procedure는 CALL 문법으로 소환해야함, function은 CALL 문법 필요없음 

- procedure는 RETURN (그니까 OUT 파라미터) 없어도 됩니다. function은 무조건 RETURN 있어야함 

- procedure는 쿼리문 중간에 갑자기 사용불가능, function은 자유롭게 거의 아무데서나 사용가능 

 

그래서 그냥 SQL 쿼리문 재사용 용이면 procedure, 계산기 만들고 싶으면 function 쓰셈 

 

 

 

 

 

 

function 문법 주의사항 

 

MySQL의 function 안에서는 SELECT 문법으로 출력하는건 금지되어있습니다. 

SELECT한걸 변수에 집어넣어주는 SELECT INTO 문법 이런건 허용되고 

서브쿼리용도로 쓰는 (SELECT 어쩌구) 이런 것도 허용됩니다. 

테이블 출력만 금지임 

 

 

 

 

 

 

 

 

 

DETERMINISTIC 왜 넣음?

 

MySQL은 이상한 보안장치가 있는데 

함수를 만들어 사용할 때 함수가 

1. 구멍에 뭘 넣어도 항상 같은 값을 RETURN 하면 DETERMINISTIC 

2. SQL 문법을 사용안하면 NO SQL 표기해야하고

3. 안에 SELECT를 사용하면 READS SQL DATA

4. 안에 INSERT DELETE를 사용하면 MODIFIES SQL DATA

를 표기해둬야합니다. 

 

 

 

▲ 표기안하면 이런 소리를 합니다. 

근데 표기한게 맞는지 컴퓨터가 정확히 체크하는건 아니라 그래서 대충 아무거나 하나 표기해둬도 됩니다. 

하지만 나중에 데이터가 많아지면 이 정보들로 execution plan을 만들 때 컴퓨터가 혼란스러워할 수도 있어서

정확히 적는게 좋습니다만 지금은 아무렇게나 해도 상관없습니다. 

 

 

 

 

 

오늘의 숙제 : 

1. 문자 하나를 집어넣으면 '안녕하세요' 를 앞에 붙여서 뱉어주는 함수를 만들어서 사용까지 해봅시다. 

(실행예시)

SELECT 함수('kim'); 실행시 '안녕하세요 kim' 이 출력되어야합니다.

 

저는

 

어려우면 이 함수가 무슨 기능을 하는지 부터 한글로 정의하면 됩니다.

1. 파라미터로 문자를 넣으면 2. '안녕하세요' 뒤에 파라미터를 붙여서 뱉는

함수를 만들면 되는 것이 아닐까요

이제 그대로 코드로 옮기면 됩니다. 

 

 

DROP FUNCTION IF EXISTS mart.함수; 
DELIMITER $$ 
CREATE FUNCTION mart.함수(구멍 varchar(100)) 
RETURNS varchar(100) 
DETERMINISTIC
BEGIN 

    RETURN CONCAT('안녕하세요 ', 구멍);

END $$ 
DELIMITER ;

SELECT mart.함수('kim'); 

이렇게 해봤더니 '안녕하세요 kim'이 잘 출력됩니다.

 

[collapse]

 

 

2. 입대날짜를 입력하면 전역날짜를 뱉어주는 함수를 만들어봅시다. 복무일은 545일로 합시다.

(실행예시)

SELECT 함수('2022-01-01'); 실행시 '2023년 06월 30일'이 출력되면 됩니다. 

 

까먹은건 당연히 찾아봐야

 

이 함수는 

1. 파라미터로 날짜형식 데이터를 넣으면

2. 545일을 더해서 뱉어주면 되는데

3. 근데 date_format() 같은거 써서 이쁜 문자열로 뱉어주면 되겠군요. 

기능 정의를 한글로 잘 했으면 코드로 옮기기만 하면 됩니다. 

 

 

 

 

 

DROP FUNCTION IF EXISTS mart.함수; 
DELIMITER $$ 
CREATE FUNCTION mart.함수(구멍 date) 
RETURNS varchar(100) 
DETERMINISTIC
BEGIN 

    DECLARE 전역일 date;
    SET 전역일 = date_add(구멍, INTERVAL 545 DAY);
    RETURN date_format(전역일, '%Y년 %m월 %d일');

END $$ 
DELIMITER ;
SELECT mart.함수('2022-01-01'); 

그래서 

1. 구멍으로 입력한 날짜타입 데이터를 

2. 545일 더해서 전역일이라는 변수에 저장해봤습니다.

변수는 심심해서 써봤습니다. 

3. 그 변수에 있던 날짜를 date_format() 으로 포맷팅해서 뱉으라고 코드짜봤습니다.

 

실행해보니 잘되는군요.