oracle转pgsql数据库
公司因⼀招标项⽬，对⽅要求使⽤pgsql数据库，在这⾥将遇到的问题和解决问题的办法记录⼀下
⼀、整个数据库表转换
⾸先我们做的是将整个oracle数据库转换为可执⾏的pgsql数据库执⾏语句，主要是因为pgsql的数据类型与oracle⼤不相同，从oracle直接导出的执⾏语句根本⽆法在pgsql数据库执⾏。我们是使⽤java程序⽣成pgsql建表建库脚本，下⾯贴出⼀部分代码：
StringBuffer dropSb = new StringBuffer();
dropSb.append("-- DROP table \n");
dropSb.append("DROP TABLE IF EXISTS "+pgUser+table+";\n");
dropTable.String());
StringBuffer createSb = new StringBuffer();
createSb.append("-- Create table \n");
createSb.append("create table if not exists "+pgUser+table+"(\n");
StringBuffer commentsSb = new StringBuffer();
// 查询表信息
Table tableInfo = iDataInfoDao1.findTableInfo(table);
if (!StringUtils.Comments())) {
// 表注释
commentsSb.append("-- Add comments to the table \n");
commentsSb.append("COMMENT ON TABLE "+pgUser+table+" IS '"+Comments()+"';\n");
}
// 约束相关信息
StringBuffer constraintSb = new StringBuffer();
constraintSb.append("-- Create/Recreate primary, unique and foreign key constraints \n");
List<Constraint> listConstraint = iDataInfoDao1.findTableConstraint(table);
for (Constraint constraint: listConstraint) {
if ("P".ConstraintType())) {
constraintSb.append("alter table "+pgUser+table+" add constraint "+ConstraintName()+" primary key ("
+ColumnName()+");\n");
} else if ("R".ConstraintType())) {
fConstraintSb.append("-- Create/Recreate foreign key constraints \n");
fConstraintSb.append("alter table "+pgUser+table+" add constraint "+ConstraintName()+" foreign key ("
+ColumnName()+")\n");
fConstraintSb.append("references "+TableName()+" ("+ColumnName()+");\n");
fConstraintSb.append("\n");
fConstraintSb.append("\n");
} else if ("U".ConstraintType())) {
constraintSb.append("alter table "+pgUser+table+" add constraint "+ConstraintName()+" unique ("
+ColumnName()+");\n");
}
}
// 索引相关信息
StringBuffer indexSb = new StringBuffer();
indexSb.append("-- Create/Recreate indexes \n");
List<Index> listIndex = iDataInfoDao1.findTableIndex(table);
for (Index index: listIndex) {
if ("UNIQUE".Uniqueness())) {
indexSb.append("create unique index "+IndexName()+" on "+TableName()+" ("+ColumnName()+");\n");
} else if ("NONUNIQUE".Uniqueness())) {
indexSb.append("create index "+IndexName()+" on "+TableName()+" ("+ColumnName()+");\n");
}
}
//类型转换
StringBuffer createSb = new StringBuffer();
if (column.isNullable()) {// 如果⾮空
if ("BLOB".DataType())) {// ⼤数据原4G，text限度1G，实际已⾜够使⽤
createSb.append(" "+ColumnName()+" TEXT"+" NOT NULL,\n");
} else if ("CLOB".DataType())) {// ⼤数据原4G，text限度1G，实际已⾜够使⽤
createSb.append(" "+ColumnName()+" TEXT"+" NOT NULL,\n");
} else if ("CHAR".DataType())) {// 固定长度字符串
createSb.append(" "+ColumnName()+" VARCHAR"+"("+DataLength()+") NOT NULL,\n");
} else if ("DATE".DataType())) {// ⽇期
createSb.append(" "+ColumnName()+" TIMESTAMP(6)"+" NOT NULL,\n");
} else if ("LONG".DataType())) {// ⼤数据因此处只⽤来存储时间戳，故转换为VARCHAR(32)
createSb.append(" "+ColumnName()+" VARCHAR(32)"+" NOT NULL,\n");
} else if ("NUMBER".DataType())) {// 数值
if (!StringUtils.isEmpty(String.DataPrecision()))&&0!=DataPrecision()) {
if (StringUtils.isEmpty(String.DataScale()))||0==DataScale()) {
createSb.append(" "+ColumnName()+" numeric"+"("+DataPrecision()+") NOT NULL,\n");
} else {
createSb.append(
" "+ColumnName()+" numeric"+"("+DataPrecision()+","+DataScale()+") NOT NULL,\n");
}
} else {
if (StringUtils.isEmpty(String.DataScale()))||0==DataScale()) {
createSb.append(" "+ColumnName()+" numeric NOT NULL,\n");
} else {
createSb.append(" "+ColumnName()+" numeric"+"(38,"+DataScale()+") NOT NULL,\n");
}
}
} else if ("VARCHAR2".DataType())) {// 字符数据类型
createSb.append(" "+ColumnName()+" VARCHAR"+"("+DataLength()+") NOT NULL,\n");
} else if ("NVARCHAR2".DataType())) {// 字符数据类型
createSb.append(" "+ColumnName()+" VARCHAR"+"("+DataLength()+") NOT NULL,\n");
} else if (DataType().contains("TIMESTAMP")) {// 包含TIMESTAMP时，直接去
createSb.append(" "+ColumnName()+" "+DataType()+" NOT NULL,\n");
} else {
createSb.append(" "+ColumnName()+" "+DataType()+" NOT NULL),\n");
}
} else {
if ("BLOB".DataType())) {// ⼤数据原4G，text限度1G，实际已⾜够使⽤
createSb.append(" "+ColumnName()+" TEXT"+" ,\n");
} else if ("CLOB".DataType())) {// ⼤数据原4G，text限度1G，实际已⾜够使⽤
createSb.append(" "+ColumnName()+" TEXT"+" ,\n");
} else if ("CHAR".DataType())) {// 固定长度字符串
createSb.append(" "+ColumnName()+" VARCHAR"+"("+DataLength()+") ,\n");
} else if ("DATE".DataType())) {// ⽇期
createSb.append(" "+ColumnName()+" TIMESTAMP(6)"+" ,\n");
} else if ("LONG".DataType())) {// ⼤数据因此处只⽤来存储时间戳，故转换为VARCHAR(32)
createSb.append(" "+ColumnName()+" VARCHAR(32)"+" ,\n");
} else if ("NUMBER".DataType())) {// 数值
if (!StringUtils.isEmpty(String.DataPrecision()))&&0!=DataPrecision()) {
if (StringUtils.isEmpty(String.DataScale()))||0==DataScale()) {
createSb.append(" "+ColumnName()+" numeric"+"("+DataPrecision()+") ,\n");
} else {
createSb.append(" "+ColumnName()+" numeric"+"("+DataPrecision()+","+DataScale()+") ,\n"); }
} else {
if (StringUtils.isEmpty(String.DataScale()))||0==DataScale()) {
createSb.append(" "+ColumnName()+" numeric ,\n");
} else {
createSb.append(" "+ColumnName()+" numeric"+"(38,"+DataScale()+") ,\n");
}
}
} else if ("VARCHAR2".DataType())) {// 字符数据类型
createSb.append(" "+ColumnName()+" VARCHAR"+"("+DataLength()+") ,\n");
} else if ("NVARCHAR2".DataType())) {// 字符数据类型
createSb.append(" "+ColumnName()+" VARCHAR"+"("+DataLength()+") ,\n");
} else if (DataType().contains("TIMESTAMP")) {// 包含TIMESTAMP时，直接去
createSb.append(" "+ColumnName()+" "+DataType()+" ,\n");
} else {
createSb.append(" "+ColumnName()+" "+DataType()+" ),\n");
}
}
⼆、sql中使⽤语法修改
在使⽤中，pgsql语法与oracle有所不同，我将我⼤概遇到的列在下⽅：
1. 别名不能取关键字，如name；我们在使⽤oracle数据库时其实也是有这个规定的，字段最好使⽤多段式的，但是pgsql的⼀些关键字或许与oracle不同，需注意
2. 当⼀条sql语句中同时使⽤DISTINCT和ORDER BY时，可能会出现ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list错误信息----ORDER BY表达式必须出现在选择列表; 意思就是在使⽤DISTINCT时order by 后⾯跟着的表达式必须出现在select查询列表中
3. 删除语句delete后必须带有from,oracle的delete的语句可带可不带
4. ''不能作为null处理，查询结果集时getTimestamp(),getDate()时值不能为''，⽽且sql⾥遇到判空时，需考虑 is null or = ''
5. update table t lumn = '' ==> update table set column = '' update语句不能⽤别名，但是可以使⽤表的全名update table t lumn = '' ==> update table lumn = ''
6. 所有被括号包裹的⼦查询语句都要加上别名
7. 分页不同：rownum不能使⽤，rownum <= 5 ==> limit 5 offset 0
8. 序列的使⽤：NEXTVAL('序列名')
9. select 1 from dual ==> select 1
三、sql中使⽤函数修改
函数对于我们来说是改造量⽐较⼤的，因为oracle的⼤部分函数在pgsql中都不能使⽤，⽽我们对于oracle函数的使⽤还是⽐较多的
to_number() ==> to_number(text,text)
字符串转数字函数有两个参数，第⼀个是你要转的数据，第⼆个是格式(如'999999.99')
cast(varchar as numeric)
数据类型转换函数，在很多函数⾥都能⽤上，因为很多函数对于参数的类型是有明确定义的类型不满⾜时就需求做⼀个转换；如sum(varchar) ==> sum(cast(va rchar as numeric))
NVL() ==> COALESCE()
对字段的⾮空处理，⽤法⼀样
SYSDATE ==> NOW()
取数据库当前时间(我们⼀般使⽤NOW()函数)
trunc
pgsql中的时间处理函数也有trunc但是功能不同，这⾥我们做了⼀个重写，函数内容如下:
CREATE OR REPLACE FUNCTION "poc_wechat_test_chema"."trunc"("p_timestamp" timestamptz, "p_formart" varchar='DD'::character varying)
RETURNS "pg_catalog"."timestamp" AS $BODY$
declare
v_timestamp timestamp := null;
v_formart varchar(10) := upper(p_formart);
begin
/*
* 函数功能：对⽇期值进⾏格式化
* 参数说明:
* P_TIMESTAMP ( 需要格式话的⽇期值 )
* P_FORMART ( YYYY：年第⼀天; MM|MONTH|MON|RM:⽉第⼀天;IW: 当前周第⼀天[周⼀为第⼀天]; NULL|DD：当⽇; D：当前周第⼀天[周⽇为第⼀天]; ....) * 返回格式:YYYY-MM-DD HH24(12):MI:SS (具体值由第⼆个参数决定)
*/
if p_timestamp is not null then
if v_formart in ('YYYY', 'YEAR') then
-- 当前年的第⼀天(YYYY-01-01 00:00:00)
v_timestamp := date_trunc('year', p_timestamp);
elsif v_formart in ('MONTH', 'MON', 'MM', 'RM') then
-- 当前⽉第⼀天(YYYY-MM-01 00:00:00)
v_timestamp := date_trunc('month', p_timestamp);
elsif v_formart in ('DD', 'DAY', 'DY') then
-- 当天(YYYY-MM-DD 00:00:00)
-- 当天(YYYY-MM-DD 00:00:00)
v_timestamp := date_trunc('day', p_timestamp);
elsif v_formart = 'IW' then
-- 当前周第⼀天[周⼀为第⼀天](YYYY-MM-DD 00:00:00)
v_timestamp := (date_trunc('WEEK', p_timestamp));
elsif v_formart = 'D' then
-- 当前周第⼀天[周⽇为第⼀天](YYYY-MM-DD 00:00:00)
oracle中trunc函数用法v_timestamp := (date_trunc('WEEK', p_timestamp) - interval'1 day');
elsif v_formart in ('W1', 'W2', 'W3', 'W4', 'W5', 'W6', 'W7') then
-- 当前周第⼏天[周⽇为第⼀天](YYYY-MM-DD 00:00:00)
v_timestamp := date_trunc('WEEK', p_timestamp)::date + substr(v_formart, 2, 1)::integer - 2;
elsif v_formart ~ '^D\+?[0-9]*$' then
-- 当年第⼏天(YYYY-MM-DD 00:00:00)
if substr(v_formart, 2, length(v_formart)-1)::integer between 1 and 366 then
v_timestamp := date_trunc('year', p_timestamp)::date + substr(v_formart, 2, length(v_formart)-1)::integer - 1;
if date_trunc('year', v_timestamp)::date > date_trunc('year', p_timestamp)::date then
v_timestamp := date_trunc('year', v_timestamp)::date - interval'1 day';
end if;
else
raise exception 'U-2001 [%] is not recognize. please enter "D[1~366]"', p_formart;
end if;
elsif v_formart in ('HH', 'HH24') then
v_timestamp := date_trunc('hour', p_timestamp);
elsif v_formart = 'HH12' then
v_timestamp := to_char(p_timestamp, 'yyyy-mm-dd hh12:00:00')::timestamp;
elsif v_formart in ('MINUTE', 'MI') then
v_timestamp := date_trunc('minute', p_timestamp);
elsif v_formart = 'CC' then
v_timestamp := to_date((trunc(date_part('years', p_timestamp)::integer/100)*100+1)::varchar, 'yyyy');
elsif v_formart in ('HELP', '?') then
raise exception 'U-2001 please enter formart code in ( YYYY|YEAR, MONTH|MON|MM|RM, DD|DAY|DY, IW|D, W[1~7], D[1~366], HH|HH24, HH12, MIN UTE|MI, CC )';
else
raise exception 'U-2001 [%] is not recognize. you can try [help]', p_formart;
end if;
else
v_timestamp := p_timestamp;
end if;
return v_timestamp;
end;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
⼤概情况就是这样，可能有⼀些东西忘记了，后⾯有机会再补充⼀下
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论