Oracle® 사용자를 PostgreSQL용 Cloud SQL로 마이그레이션: 쿼리, 저장 프로시져, 함수, 트리거

이 문서는 PostgreSQL용 Cloud SQL 버전 12로 Oracle® 11g/12c 데이터베이스 마이그레이션 계획 및 수행과 관련된 핵심 정보와 지침을 제공하는 시리즈의 일부입니다. 이 시리즈에는 소개 설정 부분 외에도 다음 부분이 포함됩니다.

• Oracle 사용자를 PostgreSQL용 Cloud SQL로 마이그레이션: 용어 및 기능
• Oracle 사용자를 PostgreSQL용 Cloud SQL로 마이그레이션: 데이터 유형, 사용자, 테이블
• Oracle 사용자를 PostgreSQL용 Cloud SQL로 마이그레이션: 쿼리, 저장 프로시져, 함수, 트리거(이 문서)
• Oracle 사용자를 PostgreSQL용 Cloud SQL로 마이그레이션: 보안, 작업, 모니터링, 로깅
• Oracle 데이터베이스 사용자 및 스키마를 PostgreSQL용 Cloud SQL로 마이그레이션

쿼리

Oracle 및 PostgreSQL용 Cloud SQL에서는 ANSI SQL 표준이 지원됩니다. 따라서 스칼라 함수 또는 기타 Oracle 확장 기능을 지정하지 않고도 기본 구문 요소만 사용하여 일반적으로 쉽게 SQL 문을 마이그레이션할 수 있습니다. 다음 섹션에서는 일반적인 Oracle 쿼리 요소와 PostgreSQL용 Cloud SQL 해당 요소에 대해 설명합니다.

기본 SELECT 및 FROM 구문

Oracle 기능 이름 또는 구문 이름	Oracle 개요 또는 구현	PostgreSQL용 Cloud SQL 지원	PostgreSQL용 Cloud SQL 해당 또는 대체 솔루션
데이터 검색을 위한 SQL 기본 구문	SELECT FROM WHERE GROUP BY HAVING ORDER BY	예	SELECT FROM WHERE GROUP BY HAVING ORDER BY
출력 인쇄를 위한 SELECT	SELECT 1 FROM DUAL	예	SELECT 1
열 별칭	SELECT COL1 AS C1	예	SELECT COL1 AS C1 OR SELECT COL1 C1
테이블 이름 대소문자 구분	대소문자 구분 없음 (예: orders 또는 ORDERS 모두 테이블 이름으로 사용 가능).	예	따옴표로 묶지 않는 한 이름은 대소문자를 구분하지 않습니다. 예를 들어 orders 및 ORDERS는 같은 것으로 취급되지만 "orders" 및 "ORDERS"는 서로 다르게 취급됩니다.

PostgreSQL용 Cloud SQL의 SELECT 구문은 문서를 참조하세요.

인라인 뷰

• 인라인 뷰(파생된 테이블이라고도 함)는 FROM 절에 있는 SELECT 문이며, 서브 쿼리로 사용됩니다.
• 인라인 뷰는 복합 계산을 없애거나 조인 연산을 제거하고, 여러 개별 쿼리를 간소화된 단일 쿼리로 압축하여 복잡한 쿼리를 간단하게 만드는 데 도움이 될 수 있습니다.
• 변환 참고사항: Oracle 인라인 뷰는 별칭을 사용할 필요가 없지만 PostgreSQL용 Cloud SQL은 각 인라인 뷰에 특정 별칭을 사용해야 합니다.

다음 표에서는 Oracle에서 PostgreSQL용 Cloud SQL로의 인라인 뷰 변환 예시를 제공합니다.

Oracle 11g/12c

PostgreSQL용 Cloud SQL 12

SQL> SELECT FIRST_NAME, DEPARTMENT_ID, SALARY, DATE_COL FROM EMPLOYEES, (SELECT SYSDATE AS DATE_COL FROM DUAL); 출력은 다음과 비슷합니다. FIRST_NAME DEPARTMENT_ID SALARY DATE_COL -------------------- ------------- ---------- --------- Steven 90 24000 30-JUL-19 Neena 90 17000 30-JUL-19 Lex 90 17000 30-JUL-19

인라인 뷰에 별칭 없음: postgres=> SELECT FIRST_NAME, postgres-> DEPARTMENT_ID, postgres-> SALARY, postgres-> DATE_COL postgres-> FROM EMPLOYEES, (SELECT NOW() AS DATE_COL); ERROR: subquery in FROM must have an alias LINE 5: FROM EMPLOYEES, (SELECT NOW() AS DATE_COL); HINT: For example, FROM (SELECT ...) [AS] foo. 인라인 뷰에 별칭 추가: postgres=> SELECT FIRST_NAME, postgres-> DEPARTMENT_ID, postgres-> SALARY, postgres-> DATE_COL postgres-> FROM EMPLOYEES, (SELECT NOW() AS DATE_COL) AS C1; 출력은 다음과 비슷합니다. first_name | department_id | salary | date_col -------------+---------------+----------+-------------------------------- Steven | 90 | 24000.00 | 10/16/2020 08:35:18.470089 UTC Neena | 90 | 17000.00 | 10/16/2020 08:35:18.470089 UTC Lex | 90 | 17000.00 | 10/16/2020 08:35:18.470089 UTC

JOIN 문

Oracle JOIN 문은 PostgreSQL용 Cloud SQL JOIN 문에서 지원됩니다. 하지만 Oracle 조인 연산자 (+)는 PostgreSQL용 Cloud SQL에서 지원되지 않습니다. 동일한 결과를 얻기 위해서는 외부 조인을 위한 표준 SQL 구문으로 변환해야 합니다.

다음 표에서는 JOIN 변환 예시를 보여줍니다.

Oracle JOIN 유형	PostgreSQL용 Cloud SQL 지원	PostgreSQL용 Cloud SQL JOIN 구문
INNER JOIN	예	SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
CROSS JOIN	예	SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E CROSS JOIN DEPARTMENTS D;
FULL JOIN [ OUTER ]	예	SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E FULL JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
LEFT JOIN [ OUTER ]	예	SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E LEFT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
RIGHT JOIN [ OUTER ]	예	SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E RIGHT JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
SUBQUERY	예	SELECT E.FIRST_NAME, D.DEPARTMENT_NAME FROM EMPLOYEES E JOIN (SELECT * FROM DEPARTMENTS)D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

UNION, UNION ALL, INTERSECT, MINUS

PostgreSQL용 Cloud SQL은 Oracle UNION, UNION ALL, INTERSECT 연산자를 지원합니다. MINUS 연산자는 지원되지 않습니다. 하지만 PostgreSQL용 Cloud SQL은 EXCEPT 연산자를 구현합니다. 이 연산자는 Oracle의 MINUS 연산자와 동등합니다. 또한 PostgreSQL용 Cloud SQL은 Oracle에서 지원되지 않는 INTERSECT ALL 및 EXCEPT ALL 연산자를 지원합니다.

• UNION: 2개 이상의 SELECT 문의 결과 집합을 연결하고 중복 레코드를 없앱니다.
• UNION ALL: 2개 이상의 SELECT 문의 결과 집합을 연결하고 중복 레코드를 없애지 않습니다.
• INTERSECT: 한 레코드가 두 데이터 집합 모두에 존재할 경우에만 2개 이상의 SELECT 문의 교집합을 반환합니다. 중복 레코드는 제거되지 않습니다.
• INTERSECT ALL(PostgreSQL용 Cloud SQL만 해당): 한 레코드가 두 데이터 세트 모두에 존재할 경우에만 2개 이상의 SELECT 문의 교집합을 반환합니다.
• MINUS (EXCEPT, PostgreSQL용 Cloud SQL): 2개 이상의 SELECT 문을 비교하여 다른 문에서 반환되지 않은 첫 번째 쿼리의 고유 행만 반환합니다.
• EXCEPT ALL(PostgreSQL용 Cloud SQL만 해당): 2개 이상의 SELECT 문을 비교하여 중복 레코드를 삭제하지 않고 다른 문에서 반환되지 않은 첫 번째 쿼리의 행만 반환합니다.

변환 메모

Oracle MINUS 연산자에서 PostgreSQL용 Cloud SQL로 변환할 때 대신 EXCEPT 연산자를 사용합니다.

예시

Oracle 함수	Oracle 구현	PostgreSQL용 Cloud SQL 지원	PostgreSQL용 Cloud SQL 해당 또는 대체 솔루션
UNION	SELECT COL1 FROM TBL1 UNION SELECT COL1 FROM TBL2	예	SELECT COL1 FROM TBL1 UNION SELECT COL1 FROM TBL2
UNION ALL	SELECT COL1 FROM TBL1 UNION ALL SELECT COL1 FROM TBL2	예	SELECT COL1 FROM TBL1 UNION ALL SELECT COL1 FROM TBL2
INTERSECT	SELECT COL1 FROM TBL1 INTERSECT SELECT COL1 FROM TBL2	예	SELECT COL1 FROM TBL1 INTERSECT SELECT COL1 FROM TBL2
MINUS	SELECT COL1 FROM TBL1 MINUS SELECT COL1 FROM TBL2	예(PostgreSQL에서 Convert MINUS에서 EXCEPT로 변환)	SELECT COL1 FROM TBL1 EXCEPT SELECT COL1 FROM TBL2

스칼라(단일 행) 및 그룹 함수

PostgreSQL용 Cloud SQL은 광범위한 스칼라(단일 행) 및 집계 함수 목록을 제공합니다. 일부 PostgreSQL용 Cloud SQL 함수는 해당 Oracle 함수와 비슷합니다. 여기에는 이름과 기능이 같은 경우도 있고 이름은 다르지만 기능이 비슷한 경우도 있습니다. PostgreSQL용 Cloud SQL 함수 이름이 Oracle 함수와 동일하더라도 일부 경우에는 기능이 다를 수 있습니다.

다음 표에서는 Oracle 및 PostgreSQL용 Cloud SQL 함수의 이름과 기능이 같은 경우('예'로 표시됨)와 변환이 권장되는 경우('예'를 제외한 모든 경우)를 보여줍니다.

문자 함수

Oracle 함수	Oracle 함수 사양 또는 구현	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
CONCAT	str1와 연결된 str1을 반환합니다. CONCAT('A', 1) = A1	예	CONCAT	Oracle과 동일: CONCAT('A', 1) = A1
LOWER/UPPER	모든 문자를 소문자 또는 대문자로 바꿔서 char을 반환합니다. LOWER('SQL') = sql	예	LOWER/UPPER	Oracle과 동일: LOWER('SQL') = sql
LPAD/RPAD	expr1 문자 길이가 되도록 왼쪽 또는 오른쪽에 expr1의 문자 시퀀스가 채워진 상태로 expr1을 반환합니다. LPAD('A',3,'*') = **A	예	LPAD/RPAD	Oracle과 동일: LPAD('A',3,'*') = **A
SUBSTR	문자 위치부터 시작하여, 하위 문자열 만큼 char의 부분을 반환합니다. 문자 길이 SUBSTR('PostgreSQL', 8, 3) = SQL	일부만	SUBSTR	시작 위치가 양수일 때 Oracle과 동일합니다. SUBSTR('PostgreSQL', 8, 3) = SQL 음수가 Oracle의 시작 위치에 제공된 경우 문자열의 끝에서 하위 문자열 작업을 수행합니다. 이것은 PostgreSQL용 Cloud SQL과 다릅니다. Oracle 동작이 선호되는 경우 대신 RIGHT 함수를 사용합니다.
INSTR	제공된 문자열에서 특정 문자열의 위치(색인)를 반환합니다. INSTR('PostgreSQL', 'e') = 7	아니요	해당 사항 없음	PostgreSQL용 Cloud SQL에는 기본 제공되는 instr 함수가 없습니다. Oracle 호환 instr 함수는 PL/pgSQL을 사용하여 구현될 수 있습니다.
REPLACE	검색어로 발견된 모든 문자를 대체 문자열로 바꿔서 반환합니다. REPLACE('ORADB', 'ORA', 'PostgreSQL') = PostgreSQLDB	일부만	REPLACE	대체 문자열 매개변수는 Oracle에서 선택사항이지만 PostgreSQL용 Cloud SQL에서 필수입니다. 매개변수가 생략된 경우 Oracle이 발견된 검색어를 모두 삭제합니다. 빈 문자열을 대체 문자열로 제공하면 PostgreSQL용 Cloud SQL에서 동일한 동작을 얻을 수 있습니다. REPLACE('ORADB', 'ORA', 'PostgreSQL') = PostgreSQLDB
TRIM	문자열에서 선행 또는 후행 문자(또는 둘 다)를 자릅니다. TRIM(both '-' FROM '-PostgreSQL-') = PostgreSQL TRIM(' PostgreSQL ') = PostgreSQL	예	TRIM	Oracle과 동일: TRIM(both '-' FROM '-PostgreSQL-') = PostgreSQL TRIM(' PostgreSQL ') = PostgreSQL
LTRIM/RTRIM	문자열의 왼쪽 또는 오른쪽에서 검색에 표시된 모든 문자를 제거합니다. LTRIM(' PostgreSQL', ' ') = PostgreSQL	예	LTRIM/RTRIM	Oracle과 동일: LTRIM(' PostgreSQL', ' ') = PostgreSQL
ASCII	데이터베이스 문자 집합에서 char의 첫 번째 문자의 십진수 표현을 반환합니다. ASCII('A') = 65	예	ASCII	Oracle과 동일: ASCII('A') = 65
CHR	0~225 사이의 숫자 값인 ASCII 코드 값을 해당 문자로 반환합니다. CHR(65) = A	예	CHAR	Oracle과 동일: CHR(65) = A
LENGTH	제공된 문자열의 길이를 반환합니다. LENGTH ('PostgreSQL') = 10	예	LENGTH	Oracle과 동일: LENGTH ('PostgreSQL') = 10
REGEXP_REPLACE	정규 표현식 패턴의 문자열을 검색합니다. REGEXP_REPLACE('John', '[hn].', '1') = Jo1	예	REGEXP_REPLACE	Oracle과 동일: REGEXP_REPLACE('John', '[hn].', '1') = Jo1
REGEXP_SUBSTR	정규 표현식 패턴에 대해 문자열을 검색하여 SUBSTR 함수의 기능을 확장합니다. REGEXP_SUBSTR('https://console.cloud.google.com/sql/instances','https://([[:alnum:]]+\.?){3,4}/?') = https://console.cloud.google.com/	아니요	해당 사항 없음	비슷한 기능을 얻으려면 PostgreSQL의 REGEXP_MATCH를 사용합니다.
REGEXP_COUNT	패턴이 소스 문자열에 있는 횟수를 반환합니다.	아니요	해당 사항 없음	비슷한 기능을 얻으려면 PostgreSQL의 REGEXP_MATCH를 사용합니다.
REGEXP_INSTR	정규 표현식 패턴에 대해 문자열 위치(색인)를 검색합니다.	아니요	해당 사항 없음	이 기능을 애플리케이션 레이어로 변환합니다.
REVERSE	역순으로 표시된 문자열을 반환합니다. REVERSE('PostgreSQL') = LQSergtsoP	예	REVERSE	Oracle과 동일: REVERSE('PostgreSQL') = LQSergtsoP

숫자 함수

Oracle 함수	Oracle 함수 사양 또는 구현	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
ABS	n의 절댓값: ABS(-4.6) = 4.6	예	ABS	Oracle과 동일: ABS(-4.6) = 4.6
CEIL	n보다 크거나 같은 가장 작은 정수를 반환합니다: CEIL(21.4) = 22	예	CEIL	Oracle과 동일: CEIL(21.4) = 22
FLOOR	n보다 작거나 같은 가장 큰 정수를 반환합니다. FLOOR(-23.7) = -24	예	FLOOR	Oracle과 동일: FLOOR(-23.7) = -24
MOD	m을 n으로 나눈 나머지를 반환합니다. MOD(10, 3) = 1	예	MOD	Oracle과 동일: MOD(10, 3) = 1
ROUND	n을 소수점 오른쪽 정수 자릿수로 반올림하여 반환합니다. ROUND(1.39, 1) = 1.4	예	ROUND	Oracle과 동일: ROUND(1.39, 1) = 1.4
TRUNC (숫자)	n1을 n2 소수점 자릿수로 잘라서 반환합니다. TRUNC(99.999) = 99 TRUNC(99.999, 0) = 99	예	TRUNCATE (숫자)	Oracle과 동일: TRUNC(99.999) = 99 TRUNC(99.999, 0) = 99

날짜/시간 함수

Oracle 함수	Oracle 함수 사양 또는 구현	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
SYSDATE	데이터베이스 서버가 있는 운영체제에 설정된 현재 날짜 및 시간을 반환합니다. SELECT SYSDATE FROM DUAL = 31-JUL-2019	부분적으로 함수 이름 및 형식이 다릅니다.	CURRENT_TIMESTAMP	CURRENT_TIMESTAMP는 Oracle SYSDATE 함수와 다른 날짜/시간 형식을 반환합니다. SELECT CURRENT_TIMESTAMP = 2019-07-31 06:46:40.171477+00
SYSTIMESTAMP	소수점 이하 초와 시간대를 포함하여 시스템 날짜를 반환합니다. SELECT SYSTIMESTAMP FROM DUAL = 01-JAN-19 07.37.11.622187000 AM +00:00	부분적으로 다른 함수 이름	CURRENT_TIMESTAMP	PostgreSQL용 Cloud SQL은 Oracle과 다른 날짜/시간 형식을 반환합니다. 날짜 형식은 원래 날짜/시간 형식과 일치해야 합니다. SELECT CURRENT_TIMESTAMP = 2019-01-31 07:37:11.622187+00
LOCAL TIMESTAMP	세션 시간대에서 현재 날짜 및 시간을 TIMESTAMP 데이터 형식의 값으로 반환합니다: SELECT LOCALTIMESTAMP FROM DUAL = 01-JAN-19 10.01.10.123456 PM	부분적으로 날짜/시간 형식이 다릅니다.	LOCAL TIMESTAMP	PostgreSQL용 Cloud SQL은 Oracle과 다른 날짜/시간 형식을 반환합니다. 날짜 형식은 원래 날짜/시간 형식과 일치해야 합니다. SELECT LOCALTIMESTAMP = 2019-01-31 07:37:11.622187+00
CURRENT_DATE	세션 시간대에서 현재 날짜를 반환합니다. SELECT CURRENT_DATE FROM DUAL = 31-JAN-19	부분적으로 날짜/시간 형식이 다릅니다.	CURRENT_ DATE	PostgreSQL용 Cloud SQL은 Oracle과 다른 날짜/시간 형식을 반환합니다. 날짜 형식은 원래 날짜/시간 형식과 일치해야 합니다. SELECT CURRENT_DATE = 2019-01-31
CURRENT_TIMESTAMP	세션 시간대에서 현재 날짜 및 시간을 반환합니다. SELECT CURRENT_TIMESTAMP FROM DUAL = 31-JAN-19 06.54.35.543146 AM +00:00	부분적으로 날짜/시간 형식이 다릅니다.	CURRENT_TIMESTAMP	PostgreSQL용 Cloud SQL은 Oracle과 다른 날짜/시간 형식을 반환합니다. 날짜 형식은 원래 날짜/시간 형식과 일치해야 합니다. SELECT CURRENT_TIMESTAMP FROM DUAL = 2019-01-31 07:37:11.622187+00s
ADD_MONTHS	날짜와 정수 월을 반환합니다. ADD_MONTHS(SYSDATE, 1) = 31-JAN-19	아니요	해당 사항 없음	PostgreSQL용 Cloud SQL에서 동일한 기능을 얻으려면 + / - 연산자를 사용하고 시간 간격을 지정합니다. SELECT CURRENT_TIMESTAMP + INTERVAL '1 MONTH' = 2019-01-31 07:37:11.622187+00s
EXTRACT (날짜 부분)	날짜/시간 또는 간격 표현식에서 지정된 날짜/시간 필드의 값을 반환합니다. EXTRACT(YEAR FROM DATE '2019-01-31') = 2019	예	EXTRACT (날짜 부분)	Oracle과 동일: EXTRACT(YEAR FROM DATE '2019-01-31') = 2019
LAST_DAY	지정된 날짜가 포함된 월의 마지막 일자의 날짜를 반환합니다. LAST_DAY('01-JAN-2019') = 31-JAN-19	아니요	해당 사항 없음	이를 해결하려면 DATE_TRUNC 및 + 연산자를 사용하여 해당 월의 마지막 일자를 계산합니다. 날짜 형식은 원래 날짜/시간 형식과 일치해야 합니다. SELECT DATE_TRUNC('MONTH', DATE '01-JAN-2019') + INTERVAL '1 MONTH -1 DAY' = 2019-01-31
MONTH_BETWEEN	date1과 date2 날짜 사이의 개월 수를 반환합니다. MONTHS_BETWEEN(SYSDATE, SYSDATE-60) = 1.96	부분적으로 다른 함수 날짜/시간 형식	AGE	PostgreSQL용 Cloud SQL AGE 함수는 두 타임스탬프 간의 간격을 반환합니다. AGE(DATE '01-JAN-2019', DATE '01-JAN-2019' - 60) = 1 mon 29 days Oracle MONTH_BETWEEN 함수와 동일한 값을 얻으려면 보다 구체적인 변환이 필요합니다.
TO_CHAR(날짜/시간)	날짜 형식으로 지정된 형식에 따라 날짜/시간 또는 타임스탬프 데이터 유형을 VARCHAR2 데이터 유형의 값으로 변환합니다. TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS') = 01-01-2019 10:01:01	예	To_CHAR	Oracle과 동일: TO_CHAR(CURRENT_TIMESTAMP,'DD-MM-YYYY HH24:MI:SS'); 01-01-2019 10:01:01

인코딩 및 디코딩 기능

Oracle 함수	Oracle 함수 사양 또는 구현	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
DECODE	IF-THEN-ELSE 문을 사용하여 표현식을 각 검색 값과 하나씩 비교합니다.	아니요	CASE	비슷한 기능을 얻으려면 PostgreSQL용 Cloud SQL CASE 문을 사용합니다.
DUMP	데이터 유형 코드, 바이트 길이, 내부 표현식 표현이 포함된 VARCHAR2 값을 반환합니다.	아니요	해당 사항 없음	지원되지 않음
ORA_HASH	제공된 표현식의 해시 값을 계산합니다.	아니요	MD5 / SHA224 / SHA256 / SHA385 / SHA512	해시 값을 생성하기 위해 128비트 체크섬의 경우 PostgreSQL용 Cloud SQL MD5 함수를 사용하거나 160비트 체크섬의 경우 SHA 함수를 사용합니다.

변환 함수

Oracle 함수	Oracle 함수 사양 또는 구현	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
CAST	기본 제공되는 한 가지 데이터 유형 또는 컬렉션 유형의 값을 다른 기본 제공되는 데이터 유형 또는 컬렉션 유형의 값으로 변환합니다. CAST('1' as int) + 1 = 2	일부만	CAST	PostgreSQL용 Cloud SQL CAST 함수는 Oracle의 CAST 기능과 비슷하지만 일부 경우에 두 데이터베이스 간 데이터 유형 차이로 인해 조정이 필요합니다. CAST('1' as int) + 1 = 2
CONVERT	한 가지 문자 집합에서 다른 문자 집합으로 문자 문자열을 변환합니다. CONVERT ( 'Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') = ?? ?? ?? A B C	일부만	CONVERT	PostgreSQL용 Cloud SQL CONVERT 함수는 VARCHAR 또는 TEXT 대신 바이너리 문자열인 bytea 값을 반환합니다. PostgreSQL에서 지원되는 문자 집합도 Oracle과 다릅니다. CONVERT('Ä Ê Í Õ Ø A B C D E', 'UTF8', 'LATIN1') = [Binary representation of the string in LATIN1 encoding]
TO_CHAR (문자열/숫자)	이 함수는 숫자 및 날짜를 문자열로 변환합니다. TO_CHAR(22.73,'$99.9') = $22.7	일부만	TO_CHAR	PostgreSQL용 Cloud SQL TO_CHAR 함수는 Oracle과 기능이 비슷합니다. PostgreSQL용 Cloud SQL은 형식 문자열의 목록과 약간 다릅니다. 기본적으로 PostgreSQL용 Cloud SQL은 부호에 대해 하나의 열을 예약하므로 양수 앞에 공백이 하나 옵니다. 이 문제는 FM 프리픽스를 사용하여 없앨 수 있습니다. TO_CHAR(22.73,'FM$99.9') = $22.7
TO_DATE	Oracle TO_DATE 함수는 소스 특정 날짜/숫자 형식에 따라 문자열을 날짜로 변환합니다. TO_DATE('2019/01/01', 'yyyy-mm-dd') = 01-JAN-2019	일부만	TO_DATE	PostgreSQL용 Cloud SQL TO_DATE 함수는 Oracle과 기능이 비슷합니다. PostgreSQL용 Cloud SQL은 형식 문자열의 목록과 약간 다릅니다. TO_DATE('2019/01/01', 'yyyy-mm-dd') = 2019-01-01
TO_NUMBER	표현식을 NUMBER 데이터 유형의 값으로 변환합니다. TO_NUMBER('01234') = 1234	일부만	TO_NUMBER	PostgreSQL용 Cloud SQL TO_NUMBER 함수는 입력으로 형식 지정 문자열이 필요하지만 Oracle에서는 선택사항입니다. TO_NUMBER('01234','99999') = 1234 다른 방법은 복잡한 형식 지정 문자열이 필요하지 않은 변환에 대해 CAST 함수를 사용하는 것입니다. CAST('01234' AS NUMERIC) = 1234

조건부 SELECT 함수

Oracle 함수	Oracle 함수 사양 또는 구현	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
CASE	CASE 문은 일련의 조건을 선택하여 다음 구문으로 해당 문을 실행합니다. CASE WHEN condition THEN result [WHEN ...] [ELSE result] END	예	CASE	Oracle과 동일: CASE WHEN condition THEN result [WHEN ...] [ELSE result] END

Null 함수

Oracle 함수	Oracle 함수 사양 또는 구현	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
COALESCE	표현식 목록에서 null 아닌 첫 번째 표현식을 반환합니다. COALESCE(null, '1', 'a') = a	예	COALESCE	Oracle과 동일: COALESCE(null, '1', 'a') = 1
NULLIF	expr1 및 expr1를 비교합니다. 두 표현식이 동일하면 함수가 null을 반환합니다. 서로 다르면 함수가 expr1을 반환합니다. NULLIF('1', '2') = 1	예	NULLIF	Oracle과 동일: NULLIF('1', '2') = 1
NVL	null(공백으로 반환됨)을 쿼리 결과의 문자열로 바꿉니다. NVL(null, 'a') = a	아니요	COALESCE	대신 COALESCE 함수를 사용합니다. COALESCE(null, 'a') = a
NVL2	지정된 표현식이 null인지 여부에 따라 쿼리로 반환되는 값을 결정합니다.	아니요	COALESCE	대신 COALESCE 함수를 사용합니다. COALESCE(null, 1, 'a') = 1

환경 및 식별자 함수

Oracle 함수	Oracle 함수 사양 또는 구현	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
SYS_GUID	최대 16바이트로 구성된 전역 고유 식별자(RAW 값)를 생성하고 반환합니다. SELECT SYS_GUID() FROM DUAL = 8EFA4A31468B4C6DE05011AC0200009E	부분적으로 함수 이름 및 형식이 다릅니다.	UUID_GENERATE_V4	PostgreSQL용 Cloud SQL은 UUID_GENERATE_V4와 같은 UUID 생성 함수 목록을 제공하는 uuid-ossp 확장을 지원합니다. SELECT UUID_GENERATE_v4() = eaf356a6-2847-478d-af3b-6883f5ac6af2
UID	세션 사용자(로그온된 사용자)를 고유하게 식별하는 정수를 반환합니다. SELECT UID FROM DUAL = 43	아니요	해당 사항 없음	해당 사항 없음
USER	현재 세션 사용자의 이름을 반환합니다. SELECT USER FROM DUAL = UserName	예	USER	Oracle과 동일: SELECT USER; = postgres
USERENV	현재 매개변수 구성을 사용하여 현재 사용자 세션에 대한 정보를 반환합니다. SELECT USERENV('LANGUAGE') FROM DUAL = ENGLISH_AMERICA.AL32UTF8	아니요	해당 사항 없음	PostgreSQL용 Cloud SQL에 동일한 USERENV 함수가 없지만 PG_BACKGROUND_PID()와 같은 시스템 정보 함수를 사용하여 USERENV('SID')와 같은 개별 매개변수를 검색할 수 있습니다.
ROWID	Oracle 서버는 테이블의 행을 식별하기 위해 각 테이블의 각 행에 고유한 ROWID를 할당합니다. ROWID는 데이터 객체 번호, 행의 데이터 블록, 행 위치, 데이터 파일이 포함된 행의 주소입니다.	부분적으로 다른 함수 이름	ctid	PostgreSQL용 Cloud SQL의 ctid는 Oracle의 ROWID와 비슷하게, 해당 테이블 내에서 행 버전의 물리적 위치를 식별합니다.
ROWNUM	Oracle이 테이블 또는 조인된 테이블에서 행을 선택한 순서를 나타내는 숫자를 반환합니다.	아니요	LIMIT or ROW_NUMBER()	ROWNUM을 사용하여 쿼리로 반환되는 결과 수를 제한하는 대신 PostgreSQL용 Cloud SQL는 비슷한 목적으로 LIMIT 및 OFFSET을 지원합니다. ROW_NUMBER() 윈도우 함수는 다른 시나리오에서 Oracle의 ROWNUM에 대한 해결 방법으로 사용될 수 있습니다. 하지만 이를 대체 항목으로 사용하기 전 결과 순서 및 성능 델타를 고려해야 합니다.

집계(그룹) 함수

Oracle 함수	Oracle 함수 사양 또는 구현	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
AVG	열 또는 표현식의 평균 값을 반환합니다.	예	AVG	Oracle과 동일
COUNT	쿼리로 반환된 행 수를 반환합니다.	예	COUNT	Oracle과 동일
COUNT (DISTINCT)	열 또는 표현식에 있는 고유 값 수를 반환합니다.	예	COUNT (DISTINCT)	Oracle과 동일
MAX	열 또는 표현식의 최댓값을 반환합니다.	예	MAX	Oracle과 동일
MIN	열 또는 표현식의 최솟값을 반환합니다.	예	MIN	Oracle과 동일
SUM	열 또는 표현식의 값 합계를 반환합니다.	예	SUM	Oracle과 동일
LISTAGG	측정 열의 값을 연결하여 ORDER BY 절에 지정된 단일 행에 따라 각 그룹 내의 데이터를 표시합니다. SELECT LISTAGG( DEPARTMENT_NAME, ', ') WITHIN GROUP (ORDER BY DEPARTMENT_NAME) DEPT FROM DEPARTMENTS; -- Single line results = Accounting, Administration, Benefits, Construction	아니요	STRING_AGG	일부 경우의 구문 차이를 제외하고 PostgreSQL용 Cloud SQL STRING_AGG 함수를 사용하여 Oracle과 비슷한 결과를 반환합니다. SELECT STRING_AGG(DEPARTMENT_NAME, ', ' order by DEPARTMENT_NAME) FROM DEPARTMENTS; -- Single line results = Accounting, Administration, Benefits, Construction

Oracle 12c 가져오기

Oracle 함수	Oracle 함수 사양 또는 구현	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
FETCH	다중 행 쿼리의 결과 집합에서 데이터 행을 검색합니다. SELECT * FROM EMPLOYEES FETCH FIRST 10 ROWS ONLY;	아니요	LIMIT	PostgreSQL용 Cloud SQL LIMIT 절을 사용하여 특정 레코드 집합만 검색합니다. SELECT * FROM EMPLOYEES LIMIT 10;

기본 필터링, 연산자, 서브 쿼리

변환 중 기본 필터링, 연산자 함수, 하위 쿼리는 최소한의 추가 작업으로 비교적 직관적으로 수행됩니다.

변환 메모

Oracle 및 PostgreSQL용 Cloud SQL 형식이 서로 다른 기본값 결과를 반환하기 때문에 날짜 형식을 검사하고 해결합니다.

• Oracle SYSDATE 함수는 기본적으로 01-AUG-19를 반환합니다.
• 기본적으로 PostgreSQL CURRENT_DATE 함수는 2019-08-01을 반환합니다(형식 지정이 있어도 시간 없음). 현재 날짜와 시간을 검색하려면 CURRENT_TIMESTAMP 함수를 사용합니다. 이 함수는 기본적으로 2019-08-01 00:00:00.000000+00을 반환합니다.
• 날짜 및 시간 형식은 PostgreSQL용 Cloud SQL TO_CHAR 함수를 사용하여 설정할 수 있습니다.

Oracle 함수 또는 하위 쿼리	PostgreSQL용 Cloud SQL 동일	PostgreSQL용 Cloud SQL 해당 함수 또는 서브 쿼리	PostgreSQL용 Cloud SQL 함수 사양 또는 구현
EXISTS/ NOT EXISTS	예	EXISTS/ NOT EXISTS	SELECT * FROM DEPARTMENTS D WHERE EXISTS (SELECT 1 FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID);
IN/NOT IN	예	IN/NOT IN	SELECT * FROM DEPARTMENTS D WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM EMPLOYEES E); -- OR SELECT * FROM EMPLOYEES WHERE (EMPLOYEE_ID, DEPARTMENT_ID) IN((100, 90));
LIKE/NOT LIKE	예	LIKE/NOT LIKE	SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '_e_n%';
BETWEEN/ NOT BETWEEN	예	BETWEEN/ NOT BETWEEN	SELECT * FROM EMPLOYEES WHERE EXTRACT(YEAR FROM HIRE_DATE) NOT BETWEEN 2001 and 2004;
AND/OR	예	AND/OR	SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN(100, 101) AND (SALARY >= 1000 OR HIRE_DATE <= '2006-02-05');
SubQuery	예	SubQuery	PostgreSQL용 Cloud SQL은 JOIN 문의 경우 SELECT 수준의 하위 쿼리, 그리고 필터링의 경우 WHERE/AND 절의 하위 쿼리를 지원합니다: -- SELECT SubQuery SELECT D.DEPARTMENT_NAME, (SELECT AVG(SALARY) AS AVG_SAL FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID) AVG_SAL FROM DEPARTMENTS D; -- JOIN SubQuery SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES E JOIN (SELECT * FROM DEPARTMENTS WHERE LOCATION_ID = 2700) D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID; -- Filtering Subquery SELECT FIRST_NAME, LAST_NAME, SALARY FROM EMPLOYEES WHERE SALARY < (SELECT AVG(SALARY) FROM EMPLOYEES);
연산자	예	연산자	PostgreSQL용 Cloud SQL은 모든 기본 연산자를 지원합니다. > | >= | < | <= | = | <> | !=

분석 함수(또는 윈도우 및 순위 함수)

Oracle 분석 함수는 RANK(), ROW_NUMBER(), FIRST_VALUE()와 같이 행 그룹에 대해 집계 값을 계산하는 기능을 제공하여 표준 SQL 연산의 기능을 확장합니다. 이러한 함수는 단일 쿼리 표현식의 범위 내에서 논리적으로 분할된 레코드에 적용됩니다. 이러한 함수는 비즈니스 인텔리전스 보고서 및 애널리틱스와 함께 데이터 웨어하우징에 일반적으로 사용됩니다.

변환 메모

PostgreSQL용 Cloud SQL은 많은 분석 함수를 지원합니다. Postgres에서는 이를 집계 함수 및 윈도우 함수라고 부릅니다. 애플리케이션에 Postgres에서 지원되지 않는 덜 일반적인 함수가 사용될 경우, 지원되는 확장을 검색하거나 논리를 애플리케이션 계층으로 이동해야 합니다.

다음 표에서는 Oracle의 가장 일반적인 분석 기능을 함수를 보여줍니다.

함수 계열	관련 함수	PostgreSQL용 Cloud SQL 지원
분석 및 순위	RANK AVERAGE_RANK DENSE_RANK ROW_NUMBER PERCENT_RANK CUME_DIST NTILE FIRST_VALUE LAST_VALUE OVER (PARTITION BY...)	예(AVERAGE_RANK 제외)
계층	CONNECT BY HIER_ANCESTOR HIER_CHILD_COUNT HIER_DEPTH HIER_LEVEL HIER_ORDER HIER_PARENT HIER_TOP	아니요
지연	LAG LAG_VARIANCE LAG_VARIANCE_PERCENT LEAD LEAD_VARIANCE LEAD_VARIANCE_PERCENT	예(LAG 및 LEAD만 해당)

공통 테이블 표현식(CTE)

CTE는 여러 용도로 사용하기에 너무 복잡하거나 비효율적일 수 있는 SQL 코드를 재사용하기 위해 순차적 코드의 논리를 구현할 수 있는 방법을 제공합니다. CTE는 이름을 지정하고 WITH 절을 사용하여 SQL 문의 서로 다른 부분에서 여러 번 사용될 수 있습니다. CTE는 Oracle 및 PostgreSQL용 Cloud SQL 모두에서 지원됩니다.

예시

Oracle 및 PostgreSQL용 Cloud SQL
WITH DEPT_COUNT (DEPARTMENT_ID, DEPT_COUNT) AS (SELECT DEPARTMENT_ID, COUNT(*) FROM EMPLOYEES GROUP BY DEPARTMENT_ID) SELECT E.FIRST_NAME ||' '|| E.LAST_NAME AS EMP_NAME, D.DEPT_COUNT AS EMP_DEPT_COUNT FROM EMPLOYEES E JOIN DEPT_COUNT D USING (DEPARTMENT_ID) ORDER BY 2 DESC;

MERGE 문

MERGE(또는 UPSERT) 문은 개별적으로 실행되는 단일 DML 문과 달리 하나의 MERGE 작업으로 DML 작업을 조건부로 수행하는 단일 SQL 문을 지정할 수 있는 방법을 제공합니다. 소스 테이블에서 레코드를 선택한 후 논리적 구조를 지정하여 대상 테이블에서 여러 DML 작업을 자동으로 수행합니다. 이 기능은 삽입, 업데이트, 삭제를 여러 번 사용하지 않도록 도와줍니다. MERGE는 결정적 문입니다. 즉, MERGE 문으로 행이 처리된 다음에는 동일한 MERGE 문을 사용해서 이를 다시 처리할 수 없습니다.

변환 참고사항

PostgreSQL용 Cloud SQL은 Oracle과 달리 MERGE 기능을 지원하지 않습니다. MERGE 기능을 부분적으로 시뮬레이션하기 위해 PostgreSQL용 Cloud SQL은 INSERT ... ON CONFLICT DO UPDATE 문을 제공합니다.

• INSERT… ON CONFLICT DO UPDATE: 삽입된 행이 고유 위반 또는 제외 제약조건 위반 오류를 일으킬 경우 ON CONFLICT DO UPDATE 절에 지정된 대체 작업을 수행합니다. 예를 들면 다음과 같습니다.

INSERT INTO tbl (a,b,c) VALUES (1,2,3)
  ON CONFLICT (a) DO UPDATE SET b = 2, c = 3;

또 다른 솔루션은 예외 및 중복 처리와 함께 INSERT, UPDATE, DELETE 명령어를 사용해서 DML 작업을 관리하도록 MERGE 기능을 저장 프로시져로 변환하는 것입니다.

SQL 문 힌트

Oracle은 보다 효율적인 쿼리 실행 계획을 만들기 위해 사용자가 옵티마이저 동작에 영향을 줄 수 있는 많은 SQL 쿼리 힌트 모음을 제공합니다. PostgreSQL용 Cloud SQL은 이와 비슷한 정도로 옵티마이저에 영향을 줄 수 있는 SQL 문 수준의 힌트 메커니즘을 제공하지 않습니다.

쿼리 플래너에서 선택된 쿼리 계획에 영향을 주기 위해 PostgreSQL용 Cloud SQL은 세션 수준에서 적용될 수 있는 구성 파라미터 집합을 제공합니다. 이러한 구성 매개변수의 효과는 특정 액세스 방법의 사용 설정/사용 중지부터 플래너 비용 제약조건 조정까지 다양합니다. 예를 들어 다음 문은 전체 테이블 스캔과 같은 순차적 스캔 계획 유형을 쿼리 플래너가 사용하지 못하도록 방지합니다.

SET ENABLE_SEQSCAN=FALSE;

무작위 디스크 페이지 가져오기에 대한 플래너의 비용 예상 값을 조정하려면(기본값 4.0) 다음 문을 사용합니다.

SET RANDOM_PAGE_COST=2.0;

이 값을 낮추면 PostgreSQL용 Cloud SQL에서 인덱스 스캔이 선호됩니다. 값을 늘리면 반대 효과가 적용됩니다.

변환 참고사항

Oracle 및 PostgreSQL용 Cloud SQL 옵티마이저 사이에 근본적인 차이가 있고 PostgreSQL용 Cloud SQL이 Oracle 스타일의 SQL 쿼리 힌트를 지원하지 않기 때문에 PostgreSQL용 Cloud SQL로 마이그레이션하는 중 쿼리 힌트를 삭제하는 것이 좋습니다. 그런 후 PostgreSQL용 Cloud SQL 도구를 통해 엄격한 성능 테스트를 수행하고, 실행 계획을 사용하여 쿼리를 조사하고, 사용 사례에 따라 인스턴스 또는 세션 매개변수를 조정합니다.

실행 계획

실행 계획의 기본 목적은 데이터베이스 데이터에 액세스하기 위해 쿼리 옵티마이저에서 선택한 방법으로 내부 조사를 수행하는 것입니다. 쿼리 옵티마이저는 데이터베이스 사용자를 위해 SELECT, INSERT, UPDATE, DELETE 문에 대한 실행 계획을 생성합니다. 관리자의 경우에도 특정 쿼리 및 DML 작업을 보다 효율적으로 확인할 수 있습니다. 이러한 기능은 인덱스 성능을 확인하거나 생성해야 하는 누락된 인덱스가 있는지를 확인할 때와 같이 특히 쿼리의 성능 조정을 수행해야 할 때 유용합니다.

실행 계획은 데이터 볼륨, 데이터 통계, 인스턴스 매개변수(전역 또는 세션 매개변수)의 영향을 받을 수 있습니다.

변환 고려사항

실행 계획은 마이그레이션이 필요한 데이터베이스 객체가 아닙니다. 오히려 동일 데이터 세트에서 동일 문을 실행하여 Oracle과 PostgreSQL용 Cloud SQL 사이의 성능 차이를 분석하기 위한 도구입니다.

PostgreSQL용 Cloud SQL은 Oracle과 동일한 실행 계획 구문, 기능, 출력을 지원하지 않습니다.

다음은 실행 계획의 예시입니다.

Oracle 실행 계획

PostgreSQL용 Cloud SQL 실행 계획

SQL> EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105; SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE')); Plan hash value: 1833546154 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------

postgres=> EXPLAIN SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 105; QUERY PLAN -------------------------------------------------------------------------------- Index Scan using emp_emp_id_pk on employees (cost=0.14..8.16 rows=1 width=71) Index Cond: (employee_id = '105'::numeric) (2 rows)

저장 프로시져, 함수, 트리거

PL/SQL은 데이터베이스 내에서 코드 기반 솔루션을 생성, 저장, 적용하기 위한 Oracle 확장 절차적 언어입니다. 일반적으로 데이터베이스 저장 프로시져와 함수는 ANSI SQL과 Oracle용 PL/SQL, MySQL용 MySQL 절차적 언어와 같은 SQL 확장 절차적 언어로 구성되는 코드 요소입니다. PL/pgSQL은 PostgreSQL의 자체 확장 절차적 언어를 위한 것입니다.

이러한 저장 프로시져 및 함수의 목적은 성능, 호환성, 보안 등 애플리케이션 대신 데이터베이스 내에서 실행하기에 더 적합한 경우에 대한 솔루션을 제공하는 것입니다. 저장 프로시져와 함수 모두 PL/SQL이 사용되지만 저장 프로시져는 주로 DDL/DML 작업을 수행하기 위해 사용되고, 함수는 주로 특정 결과 반환을 위한 계산을 수행하기 위해 사용됩니다.

PL/SQL에서 PL/pgSQL로

Oracle PL/SQL에서 PostgreSQL용 Cloud SQL로 마이그레이션할 때 PL/pgSQL은 해당 구조 및 구문이 Oracle PL/SQL과 비슷합니다. 하지만 몇 가지 중요한 차이로 인해 코드 마이그레이션이 필요합니다. 예를 들어 Oracle과 PostgreSQL용 Cloud SQL 간에 데이터 유형이 다르고, PostgreSQL용 Cloud SQL에서 지원되는 해당 데이터 유형 이름이 마이그레이션된 코드에 사용되도록 번역이 필요한 경우가 많습니다. 두 언어 간 차이점에 대한 자세한 내용은 Oracle PL/SQL에서 포팅을 참조하세요.

코드 객체 권한 및 보안

Oracle에서는 저장 프로시져 또는 함수를 만들기 위해 사용자에게 CREATE PROCEDURE 시스템 권한이 있어야 합니다(다른 사용자로 프로시져 또는 함수를 만들려면 데이터베이스 사용자에게 CREATE ANY PROCEDURE 권한이 있어야 함). 저장 프로시져 또는 함수를 실행하기 위해서는 데이터베이스 사용자에게 EXECUTE 권한이 있어야 합니다.

PostgreSQL에서 코드 프로시져 또는 함수를 만들려면 사용자에게 USAGE 권한이 있어야 합니다. 프로시져 또는 함수를 실행하려면 사용자에게 해당 프로시져 및 함수에 대한 EXECUTE 권한이 있어야 합니다.

기본적으로 PL/pgSQL 프로시져 또는 함수는 SECURITY INVOKER로 정의됩니다. 즉, 프로시져 또는 함수를 호출한 사용자의 권한으로 실행해야 합니다. 또는 소유한 사용자의 권한으로 함수가 실행되도록 SECURITY DEFINER를 지정할 수 있습니다.

PostgreSQL용 Cloud SQL 저장 프로시져 및 함수 구문

다음 예시에서는 PostgreSQL용 Cloud SQL 저장 프러시저 및 함수 구문을 보여줍니다.

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

CREATE [ OR REPLACE ] FUNCTION
    name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS rettype
      | RETURNS TABLE ( column_name column_type [, ...] ) ]
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | WINDOW
    | IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
    | CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | PARALLEL { UNSAFE | RESTRICTED | SAFE }
    | COST execution_cost
    | ROWS result_rows
    | SUPPORT support_function
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

트리거

트리거는 특정 이벤트가 발생할 때 실행되는 저장 프로시져입니다. Oracle에서 트리거 이벤트는 테이블, 뷰, 스키마, 데이터베이스와 연관되어 있습니다. 트리거 이벤트의 유형에는 다음이 포함됩니다.

• 데이터 조작 언어(DML) 문(예: INSERT, UPDATE, DELETE)
• 데이터 정의 언어(DDL) 문(예: CREATE, ALTER, DROP)
• 데이터베이스 이벤트(예: LOGON, STARTUP, SHUTDOWN)

Oracle 트리거의 유형은 다음과 같습니다.

• 단순 트리거: 지정된 트리거 이벤트 앞이나 뒤에 정확히 한 번 실행됩니다.
• 복합 트리거: 여러 이벤트에서 실행됩니다.
• INSTEAD OF 트리거: 복잡하고 수정할 수 없는 뷰에 대해 투명한 업데이트 메커니즘을 제공하기 위한 특별한 유형의 DML 트리거입니다.
• 시스템 트리거: 특정 데이터베이스 이벤트에서 실행됩니다.

PostgreSQL용 Cloud SQL에서 트리거는 특정 테이블, 뷰, 외래 테이블에서의 DML 작업 전 또는 후에 실행됩니다. INSTEAD OF 트리거는 뷰에 대한 업데이트 메커니즘을 제공하기 위해 지원됩니다. DDL 작업에 대한 트리거를 이벤트 트리거라고 부릅니다. PostgreSQL용 Cloud SQL은 데이터베이스 이벤트를 기반으로 하는 Oracle의 시스템 트리거를 지원하지 않습니다.

Oracle 트리거와 달리 PostgreSQL용 Cloud SQL 트리거는 트리거 본문으로 익명 PL/pgSQL 블록을 사용하도록 지원하지 않습니다. 0개 이상의 인수를 사용하고 유형 트리거를 반환하는 이름이 지정된 함수를 트리거 선언에 제공해야 합니다. 이 함수는 트리거가 실행될 때 수행됩니다.

PostgreSQL용 Cloud SQL 트리거 및 이벤트 트리거 구문

다음 예시에서는 PostgreSQL용 Cloud SQL 트리거 및 이벤트 트리거 구문을 보여줍니다.

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

event는 INSERT, UPDATE [ OF column_name [, ... ] ], DELETE, TRUNCATE 중 하나일 수 있습니다.

CREATE EVENT TRIGGER name
    ON event
    [ WHEN filter_value IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE { FUNCTION | PROCEDURE } function_name()

event는 ddl_command_start, ddl_command_end, table_rewrite, sql_drop 중 하나일 수 있습니다.

filter_value는 TAG만 가능합니다.

filter_value는 지원되는 명령어 태그 중 하나일 수 있습니다.

다음 단계

• PostgreSQL용 Cloud SQL 사용자 자세히 알아보기
• Google Cloud에 대한 참조 아키텍처, 다이어그램, 권장사항 살펴보기 Cloud 아키텍처 센터 살펴보기