저장 프로시저 stored procedure 쓰면 나도 알파메일

하나부터 열까지다 널 위한 소리 ~ 내 말 듣지 않는 너에게는 뻔한 잔소리… 하루에도 열번씩 짜는 쿼리가 있다고 칩시다.

 

그때마다 직접 문장을 길게 타이핑하고 있으면 화가 좀 나겠죠?

 

그럴때 stored procedure(저장프로시저)를 사용합니다.

 

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

 

그래서 SELECT FROM 이런 긴 쿼리를 저장하고 재사용하고 싶다면

stored procedure 기능을 사용하면 됩니다.(문장저장기능이라 보믄댐.)

 

그냥 평소에 저희가 코딩할때 함수를 떠올리면 쉽습니다. 그거랑 똑같음

stored procedure 쓰는 법

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

SELECT * FROM product WHERE 가격 > 5000

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

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

타이핑 귀찮아서 procedure이라고 앞으로 부르겠습니다^^

 

만드는 법은

 

1. 지금 있는 DB의 procedures 아니면 fuctions 메뉴에서 우클릭합니다. 그리고 새로운 procedure 만들기 눌러서 작명 아무롷게나 하면 됩니다.(저는 get_all로 함 해보겠습니다.)
2. 그럼 뭐가 나오는데요.
   
   

sourece메뉴 들어가서 BEGIN / END 사이에 여러분이 재사용하고 싶은 쿼리 적고 저장하면 됩니다.

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

 

3. 그럼 이제 앞으로 CALL procedure이름()만 실행하면 아까 BEGIN / END 사이에 저장해놨던 코드가 실행됩니다. 안되면 CALL DB명.procedure이름() ← 이렇게 써보십쇼.

 

이제 길게 쿼리 안짜도 되니까 편리해졌네여

 

procedure 생성하는 SQL 명령어

 

실은 밑에 쿼리를 전부 실행해야 procedure 생성이 가능합니다.

 

근데 DBeaver에서는 중요한 부분만 간략하게 작성할 수 있게 도와주는 것입니다.

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

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

procedure 생성하는 쿼리는 원래 이런데

1. 첫줄은 이미 get_all이라는 procedure가 있으면 지우라는 뜻입니다. 이미 있으면 같은 이름으로 생성이 안되기 때문입니다.
2. DELIMITER는 줄바꿈 문자를 $$ 이런걸로 임시로 바꾸라는 뜻입니다. SQL 작성할때 문장끼리 구분할때 ; 이걸 씁니다.
   
   근데 procedure 코드 안에 ;이게 들어가면 중간에 실행이 중단될 수 있어 임시로 $$ 이런걸로 바꿔놓는 겁니다.
3.  CREATE PROCEDURE 뒤에는 만들 procedure 이름 맘대로 작명하면됩니다.
4.  BEGIN / END 안에 넣고 싶은 코드 넣기 
5. 이제 앞으로 CALL procedure이름() 쓰면 저장해둔 코드가 실행

stored procedure 쓰면 코드 작동속도도 빨라질까?

 

여러분이 작성한 쿼리는 다음과 같은 작업을 거쳐서 실행됩니다.

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 쓰면 생산성이 늘어난다.

 

그럼에도 왜 stored procedure을 쓰냐???

 

1. 요즘 회사에서 보통의 경우 SQL로 거의 다 하는 추세입니다. 그래서 보통 쿼리가 100줄이상 넘어가는 경우가 많습니다. 이게 재사용되는 경우도 많구요.

 

그럼 재사용될때마다 SELECT 직접 쿼리 짜실건가요?

 

아니죠. 바로 stored procedure 써서 생산성을 높이고 코드가 비교적 짧고 간결해보일 수 있습니다.

 

2. 그리고 보통 다른 팀과 협업을 하기위한 SQL 쿼리라면 stored procedure 안에 자주 쓰는 SQL코드를 보관해두는게 나을 수 있습니다.

그럼 개발자 뿐아니라 DB를 이용하는 맣은 사람이 그 코드를 재사용 할 수 있습니다.

 

그때마다 직접 문장을 길게 타이핑하고 있으면 화가 좀 나겠죠?

 

그럴때 stored procedure(저장프로시저)를 사용합니다.