Aurora Postgre에서 과부하된 Oracle 함수 처리 SQL- 호환

작성자: Sumana Yanamandra(AWS)

환경: PoC 또는 파일럿	‬소스:‭ Oracle Database	대상: Aurora Postgre SQL-호환 가능
R 타입: 리플랫포밍	워크로드: Oracle	기술: 데이터베이스, 마이그레이션
AWS 서비스: Amazon Aurora

요약

온프레미스 Oracle 데이터베이스에서 Amazon Aurora Postgre SQL-Compatible Edition으로 마이그레이션하는 코드에는 과부하된 함수가 포함될 수 있습니다. 이러한 함수는 정의 즉, 함수 이름이 같고 입력(IN) 파라미터의 수와 데이터 유형이 동일하지만 데이터 유형이나 출력(OUT) 파라미터의 수는 다를 수 있습니다. 

이러한 파라미터 불일치는 실행할 함수를 결정하기 어렵기 SQL때문에 Postgre에서 문제를 일으킬 수 있습니다. 이 패턴은 데이터베이스 코드를 Aurora Postgre SQL-Compatible로 마이그레이션할 때 과부하된 함수를 처리하는 방법을 보여줍니다.

사전 조건 및 제한 사항

사전 조건 

• Oracle Database 인스턴스를 소스 데이터베이스로 사용

• 대상 데이터베이스로 Aurora Postgre SQL호환 DB 인스턴스(Aurora 설명서의 지침 참조)

제품 버전

• Oracle Database 9i 이상

• Oracle SQL 개발자 버전 18.4.0.376

• pgAdmin 클라이언트 4개

• Aurora Postgre SQL- 호환 버전 11 이상(Aurora 설명서의 Amazon Aurora Postgre 버전 식별SQL 참조)

도구

AWS 서비스

• Amazon Aurora Postgre SQL-Compatible Edition은 PostgreSQL 배포를 설정, 운영 및 확장하는 데 도움이 되는 완전 관리형 ACID규정 준수 관계형 데이터베이스 엔진입니다.

기타 도구

• Oracle SQL 개발자는 기존 배포와 클라우드 배포 모두에서 Oracle 데이터베이스SQL에서 작업할 수 있는 무료 통합 개발 환경입니다. 

• pgAdmin 는 Postgre용 오픈 소스 관리 도구입니다SQL. 데이터베이스 객체를 생성, 유지 관리 및 사용하는 데 도움이 되는 그래픽 인터페이스를 제공합니다.

에픽

단순 함수 생성

작업	설명	필요한 기술
PostgreSQL에서 하나의 입력 파라미터와 하나의 출력 파라미터가 있는 함수를 생성합니다.	다음 예제에서는 Aurora Postgre SQL-Compatibletest_overloading에 이름이 지정된 함수를 보여줍니다. 이 함수에는 두 개의 파라미터가 있습니다. 하나는 입력 텍스트 파라미터이고 다른 하나는 출력 텍스트 파라미터입니다. CREATE OR REPLACE FUNCTION public.test_overloading(          str1 text,          OUT str2 text)     LANGUAGE 'plpgsql'     COST 100     VOLATILE AS $BODY$ DECLARE BEGIN          str2 := 'Success';     RETURN ;     EXCEPTION         WHEN others THEN              RETURN ; END; $BODY$;	데이터 엔지니어, Aurora Postgre SQL-호환 가능
Postgre 에서 함수를 실행합니다SQL.	이전 단계에서 생성한 함수를 실행합니다. select public.test_overloading('Test'); 다음 출력이 표시됩니다. Success	데이터 엔지니어, Aurora Postgre SQL-호환 가능

함수 오버로드

작업	설명	필요한 기술
동일한 함수 이름을 사용하여 Postgre 에서 과부하된 함수를 생성합니다SQL.	이전 함수와 동일한 함수 이름을 사용하는 과부하된 함수를 Aurora Postgre SQL-Compatible에서 생성합니다. 다음 예제도 test_overloading 이름이 지정되었지만 입력 텍스트 파라미터 하나, 출력 텍스트 파라미터 하나, 출력 정수 파라미터 하나 등 세 개의 파라미터가 있습니다. CREATE OR REPLACE FUNCTION public.test_overloading(          str1 text,          OUT str2 text,          OUT num1 integer)     LANGUAGE 'plpgsql'       COST 100     VOLATILE AS $BODY$ DECLARE str3 text;   BEGIN            str2 := 'Success';          num1 := 100;       RETURN ;     EXCEPTION         WHEN others THEN              RETURN ; END; $BODY$;	데이터 엔지니어, Aurora Postgre SQL-호환 가능
Postgre 에서 함수를 실행합니다SQL.	이 함수를 실행하면 실행이 실패하고 다음 오류 메시지가 표시됩니다.  ERROR: cannot change return type of existing function HINT:      Use DROP FUNCTION test_overloading(text) first. 이는 Aurora Postgre SQL-Compatible이 함수 과부하를 직접 지원하지 않기 때문입니다. 입력 파라미터는 동일하지만 두 번째 버전의 함수에서는 출력 파라미터 수가 다르기 때문에 실행할 함수를 식별할 수 없습니다.	데이터 엔지니어, Aurora Postgre SQL-호환 가능

해결 방법 적용

작업	설명	필요한 기술
첫 번째 출력 파라미터에 INOUT 를 추가합니다.	이 문제를 해결하려면 첫 번째 출력 파라미터를 INOUT으로 표현하여 함수 코드를 수정하십시오. CREATE OR REPLACE FUNCTION public.test_overloading(          str1 text,          INOUT str2 text,          OUT num1 integer)     LANGUAGE 'plpgsql'       COST 100     VOLATILE AS $BODY$ DECLARE str3 text; BEGIN            str2 := 'Success';          num1 := 100;       RETURN ;     EXCEPTION         WHEN others THEN              RETURN ; END; $BODY$;	데이터 엔지니어, Aurora Postgre SQL-호환 가능
수정된 함수를 실행합니다.	다음 쿼리를 사용하여 업데이트한 함수를 실행합니다. 오류를 방지하기 위해 이 파라미터를 INOUT으로 선언했으므로 이 함수의 두 번째 인수로 null 값을 전달합니다.  select public.test_overloading('Test', null); 이제 함수가 성공적으로 생성되었습니다. Success, 100	데이터 엔지니어, Aurora Postgre SQL-호환 가능
결과 검증	오버로드된 함수가 포함된 코드가 성공적으로 변환되었는지 확인합니다.	데이터 엔지니어, Aurora Postgre SQL-호환 가능

관련 리소스

• Amazon Aurora Postgre 작업SQL(Aurora 설명서)

• Oracle의 함수 오버로딩(Oracle 설명서)

• Postgre의 함수 과부하SQL(PostgreSQL 설명서)