Procedure

✒️ 2025-05-28 17:28 내용 수정

참고 자료 : 위키백과 저장 프로시저, iBM SQL 프로시저, 코딩의성지's MSSQL 저장 프로시저(Stored Procedure)란?, logical-code PL/SQL 프로시저(PROCEDURE)

일련의 query를 마치 하나의 함수처럼 실행하기 위한 query의 집합

• 데이터베이스에 대한 일련의 작업을 정리한 절차를 RDBMS에 저장한 것으로 영구 저장 모듈(Persistent Storage Module)이라고도 불린다.
• 각 DBMS(Oracle, MySQL 등)에서 저장 프로시저 기능을 제공하고 있다.
• Oracle에선 PL/SQL을 사용하여 생성할 수 있다.

특징

• 저장 프로시저를 처음 실행 시 최적화 및 컴파일을 거쳐 그 결과가 메모리에 저장되는데, 이후에 해당 프로시저를 실행하면 메모리에 저장된 것을 가져와 사용하므로 실행 속도가 빨라진다.
• 위의 과정으로 인해 대용량 데이터 연산이 필요할 시, 프로시저나 함수로 데이터를 연산 후 가공한 뒤 결과만 서버에 전송하면 되므로 네트워크 부하를 줄일 수 있다.
• query문을 직접 노출시키지 않기 때문에 보안이나 SQL injection의 위험성이 줄어든다.

1. 장점

   • 하나의 요청으로 여러 SQL문을 실행할 수 있다.
   • 미리 구분 분석 및 내부 중간 코드로 변환을 끝내야 하므로 처리 시간이 줄어든다.
   • 데이터베이스 트리거와 결합하여 복잡한 규칙에 의한 데이터의 참조무결성 유지를 할 수 있다.
     • 응용 프로그램 측 로직을 가지지 않아도 데이터베이스의 앞 뒤가 맞는다.
   • Java와 같은 호스트 언어와 SQL 문장이 확실하게 분리된 소스 코드로 인해 웹 사이트 등을 운용 중이더라도 프로시저 교체에 의한 수정이 가능해 유지 보수에 유용하다.
   • SQL을 따로 저장한 파일을 수정하기 쉽고, 한 번 저장해 놓은 프로시저를 다시 실행할 수 있어 재사용도 쉽다.

2. 단점

   • 문자나 숫자 연산에 사용하면 오히려 성능이 느릴 수 있다.
   • 최적화 단계에서 인덱스 사용 여부를 결정하는데, 대량의 데이터 처리에 인덱스를 사용하면 오히려 성능이 하락한다.
   • 비즈니스 로직의 일부로 사용할 경우 업무 사양 변경 등으로 인해 응용 프로그램과 저장 프로시저의 정의를 변경해야 할 경우, 수정 과정에서 발생하는 실수로 인한 오류 등이 발생할 수 있다.

Oracle의 Procedure

• 상세한 예시는 PLSQL#프로시저 생성 및 호출 참고.

CREATE OR REPLACE PROCEDURE procedure_name
( -- 매개변수 지정
	param1 IN DATATYPE:= 값,
	param2 IN DATATYPE%TYPE,
	리턴값 OUT DATATYPE -- 리턴값
)
IS -- 선언부. 함수 내에서 사용한 변수, 상수 등 선언.
	VAR1 DATATYPE;
	VAR2 TABLE_A.COLUMN_A%TYPE;
BEGIN -- 실행부
	실행할 명령;
END procedure_name;

MySQL의 Procedure

• Oracle과 구문이 비슷하지만 매개변수 선언 시 DECLARE로 작성할 수 있다.

DELIMITER //
CREATE PROCEDURE procedure_name(
	IN param1 DATATYPE, 
	IN param2 DATATYPE
)
BEGIN
	DECLARE param1 DATATYPE;
    DECLARE param2 DATATYPE;

	
	SELECT * FROM test 
	WHERE id = param1 AND name = param2;
END//
DELIMITER ;

• 프로시저 호출은 CALL procedure_name(param1, param2)로 호출한다.

CALL procedure_name(param1, param2)

Procedure를 사용한 검색 함수

• 팀 프로젝트를 진행했을 때 검색 기능을 제작하던 중 query문이 꽤 길어져 이를 그대로 쓰기엔 조금 난잡하다 생각이 들었다.
• 다른 기능을 위해 procedure를 사용하면서 검색 기능도 procedure로 만들어 사용하면 편리하지 않을까 생각이 들어 만들게 되었다.
• 아래 코드는 키워드와 검색 결과 개수를 매개변수로 받으면 검색 결과를 원하는 위치부터 50개까지 가져오는 Procedure이다.
  1. 먼저 매개변수로 keyword와 new_offset을 설정해주고, 각각의 데이터 타입을 문자와 정수로 설정했다.
  2. 이후 BEGIN절 내에서 DECLARE로 각 매개변수를 선언해준다.
  3. 문자열 비교는 keyword와 검색할 column의 데이터를 모두 소문자로 변환하여 검색할 예정이므로 LOWER()함수를 사용한다.
  4. 이제 평소처럼 SELECT를 사용해서 데이터를 불러오는데, 여기서 WHERE절엔 판매가 안된 상품 중 키워드와의 일치 여부를 LIKE로 확인한다.
  5. LOWER(column_name)을 사용하면 해당 column의 문자열도 소문자로 변환 시킬 수 있고, '%kewyword%'를 사용하기 위해 CONCAT() 함수를 사용하여 문자들을 연결 시켰다.
  6. keyword가 정수로 변환 가능한지 확인하기 위해 REGEXP를 사용하여 숫자 정규 표현식 ^[0-9]+$으로 검사한 후, 검사를 통과했다면 CAST()를 사용하여 문자열을 정수로 변환한 후에 productId라는 column의 데이터에서 비교한다.
     • 정규 표현식은 정규 표현식을, CAST는 MySQL SQL 함수#1. CAST 참고.

DELIMITER //
CREATE PROCEDURE test_search(IN keyword VARCHAR(50), IN new_offset INT)
BEGIN
	-- 변수 선언
	DECLARE lower_keyword VARCHAR(50);
    DECLARE new_offset INTEGER;

	-- 전달 받은 키워드를 소문자로 변환
    SET lower_keyword = LOWER(keyword);
    
	SELECT * FROM productList
	WHERE isSold != 1 AND
		(LOWER(product_name) LIKE CONCAT('%', lower_keyword, '%')
		OR LOWER(nickname) LIKE CONCAT('%', lower_keyword, '%')
        OR (lower_keyword REGEXP '^[0-9]+

```sql
-- 시작점을 0으로 설정하고, 특정 키워드를 포함하는 데이터를 출력
CALL test_search('test_expression', 0)

--- AND CAST(lower_keyword AS UNSIGNED) = productId))
LIMIT 50 OFFSET new_offset;
END//
DELIMITER ;

{{CODE_BLOCK_4}}

---