MYSQL STORED PROCEDURE(함수형 프로시저) sql의 함수

stored procedure

 

자주 사용할거 같은 명령문을 프로시저에 저장하여 간편하게 사용이 가능하다

자바와 비교하자면 함수같은 역할이다.

 

stored procedure는 코드 덩어리 저장가능 

 

그래서 SELECT FROM 이런 긴 문장같은걸 저장해서 재사용하고싶으면

stored procedure 기능을 사용하면 됩니다. 문장저장기능임 

 

프로그래밍 배경이 있는 분들은 이해가 쉬울텐데

그냥 평소에 쓰던 함수 문법이라고 생각하면 됩니다. 그거랑 똑같음 

 

stored procedure 쓰는 법

 

 

일단 product 테이블에서 SELECT 문을 사용한다고 칩시다. 

 

SELECT * FROM product WHERE 가격 > 5000

근데 이 코드를 나중에 사용할 일이 많은 겁니다. 

그럼 파일로 저장해놔도 되겠지만 다른 파일에서도 많이 쓰고 싶다면 stored procedure로 저장해놓으면 됩니다. 

타이핑 약간 귀찮아서 procedure 라고 쓰겠습니다.  

만드는 법은 

 

▲ 1. 지금 있는 데이터베이스의 Procedures 아니면 Functions 메뉴에서 우클릭합니다.

그리고 새로운 Procedure 만들기 눌러서 작명 아무렇게나 하면 됩니다. 

저는 get_all로 해봤음 

 

 

 

▲ 2. 그럼 뭐가 나오는데

source 메뉴 들어가서 BEGIN / END 사이에 여러분이 재사용하고싶은 코드를 적으면 됩니다.

적고 저장하셈 

주의사항은 한 줄 끝나고 ; 안넣으면 문법에러납니다. 

 

3. 이제 앞으로 CALL procedure이름() 만 실행하면 아까 BEGIN/END 사이에 저장해놨던 코드가 실행됩니다. 

안되면 CALL 데이터베이스명.procedure이름() 이렇게 써보셈 

SELECT ~ 길게 코드 안짜도 되니까 보다 편리해졌습니다. 

 

procedure 생성하는 SQL 명령어  

 

실은 밑에 나오는 SQL 코드를 전부 실행해야 procedure 생성이 가능합니다. 

근데 DBeaver 프로그램에서 중요한 부분만 간략하게 작성할 수 있게 도와주는 것일 뿐임 

 

DROP PROCEDURE IF EXISTS 데이터베이스명.get_all;

DELIMITER $$
$$
CREATE PROCEDURE 데이터베이스명.get_all()
BEGIN
  SELECT * FROM product where 가격 > 5000;
END 
$$
DELIMITER ; 

procedure 생성하는 원래 SQL 코드는 이런데 

1. 첫 줄은 이미 get_all 이라는 procedure가 있으면 지우라는 뜻입니다. 이미 있으면 같은 이름으로 생성안되어서요. 

 

2. DELIMITER는 줄바꿈 문자를 $$ 이런걸로 임시로 바꾸라는 소리입니다. 

SQL 작성할 때 문장끼리 구분할 때 ; 이걸 씁니다. 

근데 procedure 만드는 코드 안에 ; 이게 들어가면 중간에 실행이 중단될 수 있어서 

임시로 $$ 이런걸로 바꿔놓는 겁니다. 

 

3. CREATE PROCEDURE 뒤에는 만들 procedure 이름 맘대로 작명하면 됩니다. 

 

4. BEGIN / END 사이에 저장할 코드집어넣으면 됩니다. 

 

5. 이제 앞으로 CALL procedure이름() 쓰면 저장해둔 코드가 실행됩니다. 

 

stored procedure 쓰면 코드 작동속도도 빨라지나 

 

여러분이 작성한 SQL 코드는 다음과 같은 작업을 거쳐 실행됩니다.

 

 

1. 님 코드의 문법오류 체크함

2. 테이블 제대로 선택했나, 테이블 열람 권한도 있나 체크함 

3. 아까 한 번 실행되었던 쿼리인지 체크함 (아까 실행되었으면 4, 5번 생략)

4. 이 쿼리를 실행할 여러 방법 (execution plan)을 만들어보고 가장 좋은 방법 1개를 골라옵니다 (optimizing)

5. 해당 쿼리 + 실행방법을 다음에 쓸 수도 있으니 임시로 메모리에 저장해둠 (caching)

6. 드디어 컴파일하고 실행해서 테이블에서 데이터 찾아줌 

 

 

근데 stored procedure 사용하면

4번으로 가지 않고 우측으로 바로 빠져서 실행되는 경우가 많습니다. 

왜냐면 stored procedure 안의 코드는 거의 똑같기 때문에 

아까 만들어놓은거 (caching 해놓은거) 재사용하는 경우가 많아집니다. 

 

그래서 4번까지 가지 않다는 점에서 아주 쬐끔 빠르게 동작할 수 있겠지만 

실제 CPU에서의 SQL 쿼리문 작동속도는 직접 SELECT 쓰나 저장된 stored procedure 쓰나 차이 없습니다. 

 

stored procedure 쓰면 생산성이 늘어남 

 

1. 여러분이 작성하고 있는 SQL 코드가 100줄이 넘어가는데

비슷한 코드덩어리가 매우 자주 반복되는 경우 프로시저로 만들어두면 코드가 비교적 짧고 간결해보일 수 있습니다. 

 

2. 프로그램 개발초기라면 SQL 쿼리문을 보통 개발자만 사용하는 경우가 많아서 

개발자들의 멋진 프로그래밍 문법으로 SQL 쿼리문을 저장해서 쓰고 그럴 수 있는데 

나중에 마케터, 데이터분석가 등 회사 내의 많은 사람들이 비슷한 SQL 쿼리문을 써야한다면

stored procedure 안에 자주쓰는 SQL 코드를 보관해두는게 나을 수도 있습니다.

그럼 개발자 뿐 아니라 DB를 이용하는 많은 사람이 그 코드를 활용할 수 있으니까요. 

 

연습해보기

데이터를 넣을때 set과 각 속성=?다 하는게 너무 귀찮아서 프로시저를 만들어 간단하게 값만 넣으면 값이 들어가는 함수를 생성하려 한다 어떻게 만들어야할까

-- 프로시저 생성문
DELIMITER $$
$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `mart`.`item_add`
(IN name varchar(50),IN category varchar(50),IN price int,IN cnt int)
BEGIN
	
	INSERT INTO product(상품명,카테고리,가격,재고) 
	values(name,category,price,cntt);
	
END$$
DELIMITER ;

-- 사용

CALL item_add("기모양말", "양말", 5000, 10);

 

매개변수 값을 지정할때 (in 컬럼명 자료형식) 이렇게 형식이되는데 자료형식이 완전히 일치않아도 괜찮지만 왠만하면 맞춰주자 무슨말이냐하면 int형식과 varchar는 완전 다르지만 같은 varchar인데 크기지정이 달라도 괜찮다는 말이다.