DB Procedure 활용

개요

• PostgreSQL에서 프로시저를 생성하여 사용해본다
• 입력 파라미터, 출력 파라미터를 사용해본다
• 변수를 선언하여 처리하고 예외 처리를 해본다
• 프로시저에서 사용되는 테이블명을 입력 파라미터에서 받아서 처리해본다

프로시저 생성

• 기본형

  CREATE PROCEDURE procedure_name(parameter1 datatype, parameter2 datatype)
  LANGUAGE plpgsql
  AS $$
  BEGIN
      -- 프로시저 본문 내용 작성
  END;
  $$;
  

• 변수 선언 및 입력 파라미터, 출력 파라미터 사용

  CREATE OR REPLACE PROCEDURE update_seq(IN input_id integer, IN new_seq integer, OUT result boolean)
   LANGUAGE plpgsql
  AS $$
  DECLARE
      old_seq integer;
  BEGIN
  		SELECT seq
        INTO old_seq
        FROM products
       WHERE id = input_id;
  
      -- 성공 메시지
      RAISE NOTICE 'Sequence updated successfully.';
      result := true;
  RETURN;
  END;
  $$
  ;

  • 성공 시 출력 파라미터의 결과 result에 true를 담아서 종료한다
• 예외 처리 및 테이블명을 동적으로 처리

  CREATE OR REPLACE PROCEDURE update_seq(IN input_id integer, IN new_seq integer, IN table_name text, OUT result boolean)
   LANGUAGE plpgsql
  AS $$
  DECLARE
      old_seq integer;
      max_seq integer;
  BEGIN
      -- Get the current sequence value of the product to be moved
      EXECUTE 'SELECT seq FROM ' || quote_ident(table_name) || ' WHERE id = $1' INTO old_seq USING input_id;
  
      -- Raise a success message
      RAISE NOTICE 'Sequence updated successfully.';
      result := true;
  EXCEPTION
      -- Handle errors
      WHEN OTHERS THEN
  	    result := false;    
      	ROLLBACK;
  RETURN;
  END;
  $$
  ;

  • 입력 파라미터 table_name을 프로시저 내에서 처리하기 위해 EXECUTE USING 문으로 처리
  • 프로시저 실행 시 예외가 발생했을 때 출력 파라미터 결과 result에 false를 담고 종료
• 데이터 시퀀스를 변경하는 프로시저 예시

  CREATE OR REPLACE PROCEDURE update_seq(IN input_id integer, IN new_seq integer, IN table_name text, OUT result boolean)
   LANGUAGE plpgsql
  AS $$
  DECLARE
      old_seq integer;
      max_seq integer;
  BEGIN
      -- Get the current sequence value of the product to be moved
      EXECUTE 'SELECT seq FROM ' || quote_ident(table_name) || ' WHERE id = $1' INTO old_seq USING input_id;
  
      -- Get the maximum sequence value
      EXECUTE 'SELECT MAX(seq) FROM ' || quote_ident(table_name) INTO max_seq;
  
      -- Validate the new sequence value
      IF new_seq < 1 OR new_seq > max_seq THEN
          RAISE EXCEPTION 'Invalid sequence value: %', new_seq;
      END IF;
  
      -- If the new position is before the current position
      IF new_seq < old_seq THEN
          -- Increase the sequence values by 1 from the new position to the current position - 1
          EXECUTE 'UPDATE ' || quote_ident(table_name) || ' SET seq = seq + 1 WHERE seq BETWEEN $1 AND $2' USING new_seq, old_seq - 1;
      ELSE
          -- If the new position is after the current position
          -- Decrease the sequence values by 1 from the current position + 1 to the new position
          EXECUTE 'UPDATE ' || quote_ident(table_name) || ' SET seq = seq - 1 WHERE seq BETWEEN $1 + 1 AND $2' USING old_seq, new_seq;
      END IF;
  
      -- Update the sequence value of the product to be moved
      EXECUTE 'UPDATE ' || quote_ident(table_name) || ' SET seq = $1 WHERE id = $2' USING new_seq, input_id;
  
      -- Raise a success message
      RAISE NOTICE 'Sequence updated successfully.';
      result := true;
  EXCEPTION
      -- Handle errors
      WHEN OTHERS THEN
  	    result := false;    
      	ROLLBACK;
  RETURN;
  END;
  $$
  ;

• 프로시저 호출

  CALL procedure_name(1, 2, 'users', result);