DBeaver) 저장 프로시저 stored procedure 쓰면 간지남

join.zip

0.00MB

하루에 10번도 더 쓰는 SQL 문장이 있다고 칩시다.

그 때마다 직접 문장을 길게 타이핑하는게 귀찮으면  

stored procedure (저장프로시저) 를 만들어서 사용하면 됩니다.

 

 

 

 

 

 

 

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

 

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

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

 

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

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

▲ 이런소리하는 책과 강의가 있으면 당장 불태워야합니다. 프로그래밍 배경이 없으니까 이딴거 읽고있는 것 아님?

 

 

 

 

 

 

stored procedure 쓰는 법

 

 

▲ 전에 JOIN 배울 때 쓰던 product 테이블에서 진행해봅시다.  

없으면 밑에 첨부파일 다운받으셈 

일단 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를 이용하는 많은 사람이 그 코드를 활용할 수 있으니까요. 

 

 

 

 

 

 

오늘의 숙제 : 

 

 

Q. product 테이블에 하나의 행을 집어넣어주는 코드를 procedure로 저장해놓고 사용까지 해보십시오.

CALL 어쩌구(); 실행할 때 마다 행이 1개씩 추가되어야합니다. 

행 안의 데이터는 대충 아무거나 넣으쇼 

 

쉬운건 직접 합시다

 

CREATE PROCEDURE mart.add_one()
BEGIN
    INSERT INTO product (id, 상품명, 가격) VALUES (9, '휴지', 2000);
END 

이렇게 만들어놓고 SQL 에디터가서 CALL add_one(); 실행했더니 데이터가 추가되는군요. 

세미콜론 빼먹으면 안됩니다.