Oracle迁移Pgsql对应数据

最新推荐文章于 2024-05-12 23:57:47 发布

Mervin_Fmy

最新推荐文章于 2024-05-12 23:57:47 发布

阅读量1.1k

点赞数 4

分类专栏：数据库文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/Mervin_Fmy/article/details/109690640

版权

数据库专栏收录该内容

2 篇文章 1 订阅

订阅专栏

Oracle迁移Pgsql对应数据（持续更新中）

JDBC连接方式
- Oracle
- Pgsql
数据类型
- pgsql语法
表空间
函数

PostgreSQL 12.2 手册：http://postgres.cn/docs/12/index.html

JDBC连接方式

Oracle

连接方式：

jdbc连接：db.url=jdbc:oracle:thin:@127.0.0.1:1521:ORCL
驱动 ： driver-class-name: org.postgresql.Driver

Springboot配置：

spring: 
  datasource:
    url:  jdbc:oracle:thin:@127.0.0.1:1521:orcl
    driver-class-name: oracle.jdbc.driver.OracleDriver
    username: nms
    password: nms

Pgsql

连接方式：

jdbc连接：jdbc:postgresql://127.0.0.1:5432/dbtest
驱动 ： driver-class-name: oracle.jdbc.driver.OracleDriver

Springboot配置：

spring:
 datasource:
    url:  jdbc:postgresql://127.0.0.1:5432/dbtest
    driver-class-name: org.postgresql.Driver
    username: pgtest
    password: pg123456

java连接方式：

Class.forName("org.postgresql.Driver");
Connection c = DriverManager.getConnection("jdbc:postgresql://10.9.31.33:5432/dbtest", "pgtest", "pg123456");
stmt = c.createStatement();
String sql = "select * from table";
int s= stmt.executeUpdate(sql);
stmt.close();

创建数据库用户
在这里插入图片描述
创建模式schame

create schema authorization username1;
SET search_path TO username1;

数据类型

数据类型修改点

Oracle	Postgresql
Varchar2	varchar
number	numeric
date	timestamp/date/time
不支持boolean，可通过0/1代替	支持boolean
null	null

语法区别

Oracle	Postgresql
unique	distinct （查询用select distinct ）

同语句语义区别
迁移语法总结

语句	Oracle	Postgresql
SUBSTR函数	如果从第一个开始取子串，可以从0开始，也可以从1开始，如果不是第一个开始，则从1开始计数，可以为负值，从字符串结尾计数，用于取最后几位。	从1开始计数。如果要取最后几位，可以用RIGHT函数解决。
当前系统时间	sysdate	now()、current_timestamp（）
时间差	sysdate-TO_DATE(‘19700101’, ‘yyyymmdd’)	date_part(‘day’, now() - ‘19700101’::timestamp)
字符时间格式化	to_date(a.STARTTIME,‘yyyyMMddHH24miss’)	to_timestamp (‘20201127113000’,‘yyyyMMddhh24miss’)

Pgsql系统列名：
PostgreSQL：系统字段

列名	含义
oid	一行的对象标识符（对象ID）。该列只有在表使用WITH OIDS创建时或者default_with_oids配置变量被设置时才存在。该列的类型为oid（与列名一致）。tableoid
tableoid	包含这一行的表的OID。该列是特别为从继承层次中选择的查询而准备，因为如果没有它将很难知道一行来自于哪个表。tableoid可以与pg_class的oid列进行连接来获得表的名称。
xmin	插入该行版本的事务身份（事务ID）。一个行版本是一个行的一个特别版本，对一个逻辑行的每一次更新都将创建一个新的行版本。
cmin	插入事务中的命令标识符（从0开始）。
xmax	删除事务的身份（事务ID），对于未删除的行版本为0。对于一个可见的行版本，该列值也可能为非零。这通常表示删除事务还没有提交，或者一个删除尝试被回滚。
cmax	删除事务中的命令标识符，或者为0。
ctid	行版本在其表中的物理位置。注意尽管ctid可以被用来非常快速地定位行版本，但是一个行的ctid会在被更新或者被VACUUM FULL移动时改变。因此，ctid不能作为一个长期行标识符。OID或者最好是一个用户定义的序列号才应该被用来标识逻辑行。

修改前	修改后
tableoid	“tableoid”
FREEZE	”FREEZE“
Using	“Using”
cTID	“cTID”

pgsql语法

用 ALTER TABLE 在一张已存在的表上添加列的语法如下：

ALTER TABLE table_name ADD column_name datatype;

在一张已存在的表上 DROP COLUMN（删除列），语法如下：

ALTER TABLE table_name DROP COLUMN column_name;

修改表中某列的 DATA TYPE（数据类型），语法如下：

ALTER TABLE table_name ALTER COLUMN column_name TYPE datatype;

给表中某列添加 NOT NULL 约束，语法如下：

ALTER TABLE table_name MODIFY column_name datatype NOT NULL;

添加新列，并设默认值：

alter table nms_tsconf_draBsfConfig add column  draBsfConfigDea		NUMERIC(10)  DEFAULT 0;

添加多列：

alter table table add colname1 NUMERIC(5) default 9 not null, add colname2 NUMERIC(5) default 0 not null;

类型转化：

CAST(a.DesignedErlang AS INTEGER)

分页查询：
row_number() OVER()：查询行号
pages : 单页数
start : 起始位置

SELECT
	* 
	--,ROW_NUMBER ( ) OVER ( ) 
FROM
	DBNMSVER 
ORDER BY
	createtime DESC,
	labelNo DESC 
	LIMIT 5 OFFSET 10;

表空间

Oracle
oracle表空间操作详解
创建表空间：

create tablespace DATA_128 datafile '/opt/oracle/db02/oradata/orcl/DATA_128.dbf'size 1000M online;

Pgsql
postgresql 表空间创建、删除
1、创建表空间：
用户必须有表空间所在目录访问权限
[root@localhost ~]# mkdir /usr/local/pgdata
[root@localhost ~]# chown postgres:postgres /usr/local/pgdata/

--创建表空间
create tablespace tbs_test owner postgres location '/usr/local/pgdata';
--建表
create table test(a int) tablespace tbs_test;

2、删除表空间
删除表空间前必须要删除该表空间下的所有数据库对象，否则无法删除。

drop table if exists test;
drop tablespace if exists tbs_test;

3、删除数据库

DROP DATABASE mydb;

删除，都会报错提示

ERROR:  database "mydb" is being accessed by other users
DETAIL:  There are 3 other sessions using the database.

解决方式，在执行

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname='mydb' AND pid<>pg_backend_pid();

函数

序列

对应修改点：

Oracle	Pgsql
NOCACHE	CACHE=1(默认)

函数修改点：

Oracle	Pgsql
Raise_application_error(-20001,‘message’);	RAISE EXCEPTION ‘message’ USING ERRCODE = -20001;
topoTriggerEventSeq.nextval	nextval(‘topoTriggerEventSeq’)

Oracle

创建序列：

	CREATE SEQUENCE topoTriggerEventSeq
	INCREMENT BY 1
	START WITH 1
	NOCACHE;

查询序列：

	SELECT topoTriggerEventSeq.NEXTVAL FROM DUAL;

修改序列：

	alter SEQUENCE topoTriggerEventSeq
	INCREMENT BY 1
	START WITH 1
	NOCACHE;

Pgsql

创建序列：

	CREATE SEQUENCE topoTriggerEventSeq
	INCREMENT BY 1
	START WITH 1;
	--默认cache =1;不缓存

查询序列：

	select nextval('topoTriggerEventSeq');

修改序列：

	alter SEQUENCE topoTriggerEventSeq
	INCREMENT BY 1
	START WITH 1;

删除序列：

	DROP SEQUENCE  IF EXISTS   topoTriggerEventSeq;

触发器

对应修改点：

Oracle	Pgsql
:new.xxx	new.xxx
:old.xxx	old.xxx

Oracle

创建触发器：

create or replace trigger ifentity_trigger
    before update on w_IFEntity
    for each row
begin
    insert into w_TopoTriggerEvent(id,type,oldValue,newValue) values(topoTriggerEventSeq.nextval, 13, :old.NEEntityId, :new.AVAILABLESTATUS);
end;

删除触发器：

DROP TRIGGER ifentity_trigger

Pgsql

触发器语法：

//创建函数
CREATE OR REPLACE FUNCTION rigger_function() RETURNS TRIGGER AS $rigger_function$
	BEGIN
		[-- 触发器逻辑....]
	END
$rigger_function$ LANGUAGE plpgsql;

//创建触发器
CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name:[INSERT|UPDATE|DELETE]
ON table_name
for each row EXECUTE PROCEDURE trigger_function();

创建触发器：

--创建触发函数
CREATE OR REPLACE FUNCTION ifentity_trigger_function() RETURNS TRIGGER AS $ifentity_trigger_function$
	BEGIN
		insert into w_TopoTriggerEvent(id,type,oldValue,newValue) values(nextval('topoTriggerEventSeq'), 13, old.NEEntityId, new.AVAILABLESTATUS);
		RETURN NEW;
	END
$ifentity_trigger_function$ LANGUAGE plpgsql;

--声明触发器
CREATE TRIGGER ifentity_trigger before update on w_IFEntity FOR EACH ROW EXECUTE PROCEDURE  ifentity_trigger_function()

删除触发器：

	DROP TRIGGER ifentity_trigger ON w_IFEntity ;

声明增删改多条件触发器INSERT OR UPDATE OR DELETE

CREATE TRIGGER GWV5PortChange After INSERT OR UPDATE OR DELETE ON  nms_conf_v5portnew 
	for each row EXECUTE PROCEDURE GWV5PortChange_function();

定义变量DECLARE

CREATE or REPLACE FUNCTION GWV5PortChange_function() RETURNS TRIGGER AS $GWV5PortChange_function$ 
--定义变量
DECLARE
  iBnum NUMERIC(10);
BEGIN
	--变量赋值
   	iBnum:=new.v5portStartNo;
   	[-- 触发器逻辑....]
END
$GWV5PortChange_function$  LANGUAGE plpgsql;
CREATE TRIGGER GWV5PortChange After INSERT ON  nms_conf_v5portnew 
	for each row EXECUTE PROCEDURE GWV5PortChange_function();

创建游标函数

create or replace function package_return_data1()
RETURNS refcursor AS $$
DECLARE
	 r_return_data refcursor;
 BEGIN
		return r_return_data;
 END
$$ language plpgsql;

自定义类型
创建type类型

创建基本类型
CREATE table pgty1  
(
    add              VARCHAR(80),
    age                   NUMERIC(2)
);

创建组合类型
CREATE TYPE pgty2 AS ( pgty2 pgty1[] );

复合类型建表两种方式：
1、使用基本类型创建数组类型：

create table pgtbl
(
    id         NUMERIC(10)  not null,
    name       pgty1[],
    primary key(id)
);

插入数据
INSERT INTO pgtbl (id, name) values(1, ARRAY[('aaa',1)::pgty1,('bbb',2)::pgty1]);

2、使用组合类型创建：

create table pgtb2
(
    id         NUMERIC(10)  not null,
    name       pgty2,
    primary key(id)
);

插入数据
INSERT INTO pgtb2 (id, name) values(1,row(ARRAY[('aaa',1)::pgty1,('bbb',2)::pgty1]));

使用unnest可以解开Nested Table的内容。

create table pgtbl
(
    id         NUMERIC(10)  not null,
    name       pgty1[],
    primary key(id)
);
INSERT INTO pgtbl (id, name) values(1, ARRAY[('aaa',1)::pgty1,('bbb',2)::pgty1]);
select id,(unnest(name)).* from pgtbl ;

Mervin_Fmy

关注

4
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
Oracle迁移Pgsql对应数据

Oracle迁移Pgsql对应数据JDBC连接方式OraclePgsql数据类型函数序列OraclePgsql触发器OraclePgsqlJDBC连接方式Oracle连接方式：jdbc连接：db.url=jdbc:oracle:thin:@127.0.0.1:1521:ORCL驱动： driver-class-name: org.postgresql.DriverSpringboot配置：spring: datasource: url: jdbc:oracle:thin:@
复制链接

扫一扫

专栏目录