Oracle到PG的DDL转换

目录

前言

Table

Trigger

Sequence

Index

Store Procedure

转换工具

参考

---

前言

最近在做Oracle数据库迁移到PG的项目，第一步是把Oracle的DB schema转成PG的。Oracle和PG的DDL必然存在不同之处，本文是个人心得。

Table

两个数据库存在数据类型差异，以下是我遇的转换方法。

Oracle和PG数据类型对比

Oracle	PG	备注
VARCHAR2	VARCHAR
NUMBER	NUMBERIC
TIMESTAMP	TIMESTAMP	PG的TIMESTAMP精度最大是6，即TIMESTAMP(6)

举例，以下是Oracle表格的DDL

-- Oracle DDL
create table MRGN
(
    country_code VARCHAR2(255),
    is_alert NUMBER(1) not null,
    txn_id NUMBER(22,6) not null,
    audit_time TIMESTAMP(9) not null
)

转换为PG的DDL： 

-- PG DDL
create table MRGN
(
    country_code VARCHAR(255),
    is_alert NUMBERIC(1) not null,
    txn_id NUMBER(22,6) not null,
    audit_time TIMESTAMP(6) not null
)

Trigger

以下是两个数据库中查看trigger的方法。

Oracle中如何查看trigger

select * from all_tiggers

PG中如何查看trigger

select * from information_schema.triggers

Oracle trigger DDL

CREATE OR REPLACE TRIGGER CHANGE_REASON_TRIGGER
AFTER INSERT OR UPDATE OR DELETE
ON CHANGE_REASON
REFERENCING NEW AS NEW_ROW OLD AS OLD_ROW
FOR EACH ROW

BEGIN
	IF INSERTING
	THEN
		INSERT INTO CHANGE_REASON_ADT (
			TXN_ID,
			REASON_CODE,
			REMARK,
			AUDIT_TIME,
			AUDIT_ACTION
		) VALUES (
			:NEW_ROW.TXN_ID,
			:NEW_ROW.REASON_CODE,
			:NEW_ROW.REMARK,
			SYSTIMESTAMP,
			'I'
		);
	ELSIF DELETING
	THEN
		INSERT INTO CHANGE_REASON_ADT (
			TXN_ID,
			REASON_CODE,
			REMARK,
			AUDIT_TIME,
			AUDIT_ACTION
		) VALUES (
			:NEW_ROW.TXN_ID,
			:NEW_ROW.REASON_CODE,
			:NEW_ROW.REMARK,
			SYSTIMESTAMP,
			'D'
		);
	ELSE
		INSERT INTO CHANGE_REASON_ADT (
			TXN_ID,
			REASON_CODE,
			REMARK,
			AUDIT_TIME,
			AUDIT_ACTION
		) VALUES (
			:NEW_ROW.TXN_ID,
			:NEW_ROW.REASON_CODE,
			:NEW_ROW.REMARK,
			SYSTIMESTAMP,
			'U'
		);
	END IF;
END;

 PG创建trigger语法与Oracle不同，需要先定义一个函数，在创建触发器。

CREATE OR REPLACE FUNCTION CHANGE_REASON_TRIGGER_FUC() RETURNS TRIGGER AS
$BODY$
BEGIN
	CASE
		WHEN TG_OP = 'INSERT'
			THEN
				INSERT INTO CHANGE_REASON_ADT (
					TXN_ID,
					REASON_CODE,
					REMARK,
					AUDIT_TIME,
					AUDIT_ACTION
				) VALUES (
					NEW.TXN_ID,
					NEW.REASON_CODE,
					NEW.REMARK,
					now(),
					'I'
				);
		WHEN TG_OP = 'INSERT'
			THEN
				INSERT INTO CHANGE_REASON_ADT (
					TXN_ID,
					REASON_CODE,
					REMARK,
					AUDIT_TIME,
					AUDIT_ACTION
				) VALUES (
					NEW.TXN_ID,
					NEW.REASON_CODE,
					NEW.REMARK,
					now(),
					'D'
				);
		ELSE
			THEN
				INSERT INTO CHANGE_REASON_ADT (
					TXN_ID,
					REASON_CODE,
					REMARK,
					AUDIT_TIME,
					AUDIT_ACTION
				) VALUES (
					NEW.TXN_ID,
					NEW.REASON_CODE,
					NEW.REMARK,
					now(),
					'U'
				);
		END CASE;
	RETURN NEW;
END;
$BODY$
	LANGUAGE plpgsql;
	
	
DROP TRIGGER IF EXISTS CHANGE_REASON_TRIGGER ON CHANGE_REASON;
CREATE TRIGGER CHANGE_REASON_TRIGGER
	AFTER INSERT OR UPDATE OR DELETE
	ON CHANGE_REASON
	FOR EACH ROW
	EXECUTE PROCEDURE CHANGE_REASON_TRIGGER_FUC();

Sequence

序列的DDL没有区别。

Index

index的DDL没有区别。以下是两个数据库查看index的方法。

Oracle中如何查看index

select * from all_indexes

PG中如何查看index

select tablename, indexname, indexdef
from pg_indexes
where schemaname='public'
order by tablename, indexname;

Store Procedure

TBC

转换工具

使用sqlines可以帮助转换DDL，但不是百分百准确，仅供参考。

它有是开源的，也有在线版本。

参考

Data and Analytics Platform Migration - SQLines Tools