SQL 프로시저와 패키지에 대한 간단 정리

저장 프로시저 (Stored Procedure)

• SQL Server에서 제공하는 프로그래밍 기능
• 데이터베이스에 대한 일련의 작업을 정리한 절차를 관계형 데이터베이스에 저장한 것으로 영구 저장 모듈이라고도 불림
• 쿼리문을 하나의 메서드 형식으로 만들고 어떠한 동작을 일괄적으로 처리하는 용도
• SQL에서는 SQL/PSM 기준으로 책정되어 있음
• 다른 프로그래밍 언어로 작성된 모듈을 호출하는 것으로 실형하는 외부 프로시저도 있음

프로시저의 장점

• 하나의 요청으로 여러 SQL문을 실행할 수 있음
• 네트워크 소요 시간을 줄일 수 있음
• 개발 업무를 구분해 개발 할 수 있음
• 보안 강화 및 SQL Injection 예방 가능
• 프로시저 암호화를 통해 원본 코드를 난독 처리 가능
• 프로시저는 처음 실행할 떄 컴파일 되며 이후 싱행에 다시 사용되는 실행 계획을 만드므로 처리시간이 줄어듦

프로시저의 단점

• 문자나 숫자 연산에 사용 시 처리 성능이 낮음
• 디버깅이 어려움
• DB 확장이 힘듦

※ 프로시저의 종류

• 저장 프로시저
  • 이름이 정의되어 선언 시 DBMS에 저장되어 프로시저명 호출을 통해 언제든지 사용할 수 있는 프로시저
• 익명 프로시저
  • 일회성으로 사용할 수 있는 이름이 없는 프로시저

nodeJS에서 프로시저 호출

프로시저 생성

• CREATE PROCEDURE 문으로 쿼리를 작성하고 이를 호출하여 프로시저를 생성
• 프로시저 안에는 여러 쿼리문이 들어갈 수 있음
• SLQ 쿼리문 사용하듯이 프로시저를 한 번 호출하여 결과를 받아오면 됨

SQL 패키지

• 연관성이 있는 서브 프로그램이나 변수, 상수, 커서, 사용자 정의 타입 등을 하나로 담은 모듈
• 선언부(스펙)과 본문(바디)으로 구성
• 선언부만 있어도 컴파일 한 뒤 저장할 수 있음
• 프로시저와 함수의 집합

※ 예시

: https://logical-code.tistory.com/61

패키지 선언부

CREATE OR REPLACE PACKAGE 패키지명  
IS  
TYPE 구문;  
상수명 CONSTANT 상수타입;  
예외명 EXCEPTION;  
커서구문;  

FUNCTION 함수명 (매개변수1 IN 매개변수1타입, 매개변수2 IN 매개변수2타입, ...)  
RETURN 반환타입  

PROCEDURE 프로시저명 (매개변수1 [IN, OUT, INOUT] 매개변수1타입, ...);  

END 패키지명;  

• AS BEGIN과 END 사이에 원하는 쿼리를 입력
• 수정은 ALTER PROCEDURE

패키지 본문

CREATE OR REPLACE PACKAGE BODY 패키지명  
IS  
상수명 CONSTANT 상수타입;  
변수명 변수타입;  
커서 정의 구문;  

FUNCTION 함수명 (매개변수1 IN 매개변수1타입, 매개변수2 IN 매개변수2타입, ...) RETURN 반환타입;  
IS  
...  
BEGIN  
...  
END 함수명;  

PROCEDURE 프로시저명 (매개변수1 [IN, OUT, INOUT] 매개변수1타입, ...);  
IS  
...  
BEGIN  
...  
END 프로시저명;  

...

END 패키지명;  

nodeJS 프로시저 호출 방법

• CREATE PROCEDURE로 프로시저를 생성한다.
• 생성된 프로시저를 호출하여 실행한다. CALL ${프로시저명} @파라미터 @파라미터

db.query("CALL 프로시저이름(?, ?,@변수명); SELECT @변수명", [  
        var1,   
        var2],   
      function(err, result) {  
        if (err) {  
            console.log(err);  
        }  
        else {  
          for (var i = 0; i < result.length; i++) {  
            console.log(result[i]); // 여기에 전달받은 결과가 표시됨 @변수명으로...  
          };  
        }  
      });  
  });  

※프로시저를 만들면 어디에 생성되는 것인가?

• 프로시저를 생성하면 SQL 서버 자체에 저장된다

※ 프로시저 생성, 수정, 조회, 삭제에 대한 참조

• https://mozi.tistory.com/470

프로시저 생성

CREATE PROCEDURE 프로시저명 (@파라미터, @파라미터)  
AS  
쿼리문  

전체 프로시저 조회

select * from INFORMATION\_SCHEMA.ROUTINES  

프로시저 삭제

DROP PROCEDURE [프로시저명]