del文件转换成oracle,Oracle迁移到PostgreSQL的一些经验（from tians的博客http://blog.sina.com.cn/s/blog

1 安装及配置

1.1 安装

在windowXP下安装 PostgreSQL 9.1:

Installation Directory àC:\Program Files\PostgreSQL\9.1

Data Directory à D:\PostgreSQL\9.1\data

Password à postgres

Port à5432

Locale à [Default locale]

1.2 配置

1.1.1远程连接:

修改文件：D:\PostgreSQL\9.1\data\pg_hba.conf, 增加:

**********************************

# IPv4 local connections:

host all all 127.0.0.1/32 md5host all all 192.168.0.0/24 md5

**********************************

使得ip在 192.168.0.0 到 192.168.0.255 之间的客户端可以通过密码访问数据库.

修改文件：D:\PostgreSQL\9.1\data\postgresql.conf，确保:

**********************************

listen_address = '*'

**********************************

1.1.2表空间访问权限

创建表空间文件夹: E:\PostgreSQL\mytablespace，修改文件夹权限，使postgres用户有读写权限。

2 创建数据库脚本

2.1 创建用户及表空间脚本

CreateTableSpace.sql

**********************************

CREATE ROLE myuser LOGIN

PASSWORD 'mypassword'

SUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;

CREATE TABLESPACE mytablespace

OWNER myuserLOCATION 'E:/PostgreSQL/mytablespace';

**********************************

2.2 创建数据库脚本

CreateTableSpace.sql

**********************************

CREATE DATABASE mydb

WITH OWNER = myuser

ENCODING = 'UTF8'

TABLESPACE = mytablespace

LC_COLLATE = 'Chinese_People''s Republic of China.936'

LC_CTYPE = 'Chinese_People''s Republic of China.936'CONNECTION LIMIT = -1;

**********************************

2.3 建表脚本

**********************************

CREATE TABLEmytable

(

id integer unique not null,

value varchar(50)

);ALTER TABLE mytable OWNER TO myuser;

**********************************

2.4 导入缺省数据

Insert.sql

**********************************

insert into mytable (id, value)values (1, 'V1');

insert into mytable (id, value)values (2, 'V2');

**********************************

2.5 批处理文件

**********************************

set PGUSER=postgres

set PGPASSWORD=postgres

c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -f CreateTableSpace.sql

c:\Progra~1\PostgreSQL\9.1\bin\psql.exe -d mydb -f CreateTable.sqlc:\Progra~1\PostgreSQL\9.1\bin\psql.exe -d mydb -f Insert.sql

**********************************

3sql语句与Oracle的区别

3.1数据类型OraclePostgreSQL

VARCHAR2VARCHAR(character varying)

Long、CLOBTEXT

DATEDATE/TIME/TIMESTAMP

NUMBERSMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION

BLOBSMALLINT/INTEGER/BIGINT/NUMERIC/REAL/DOUBLE PRECISION

sysdatenow()、O_TIMESTAMP(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS')、CURRENT_TIMESTAMP

Tips: PostgreSQL 中字段名区分大小写，为保证兼容行，强烈建议脚本中的字符均用小写，这样在Sql语句中将忽略大小写。

3.2方法FunctionReturnsExample

to_char(timestamp, text)textto_char(timestamp 'now','HH12:MI:SS')

to_char(interval, text)textto_char(interval '15h 2m 12s','HH24:MI:SS')

to_char(int, text)textto_char(125, '999')

to_char(double precision, text)textto_char(125.8, '999D9')

to_char(numeric, text)textto_char(numeric '-125.8', '999D99S')

to_date(text, text)dateto_date('05 Dec 2000', 'DD Mon YYYY')

to_timestamp(text, text)timestampto_timestamp('05 Dec 2000', 'DD Mon YYYY')

to_number(text, text)numericto_number('12,454.8-', '99G999D9S')

3.3sqlOraclePostgreSQL

Constraintalter table schema.prefix_info add (constraint pk_prefix_info primary key (info_id));alter table schema.prefix_info add constraint prefix_info_pkey primary key(info_id);

Default Maximun in sequencecreate sequence prefix_info_sequenceincrement by 1start with 582minvalue 1maxvalue 9999999999999999999999999999nocyclecache 20noorder;create sequence schema.prefix_info_sequenceincrement 1minvalue 1maxvalue 9223372036854775807start 582cache 20;

||select a||b from table1;

returns null when there is one null value in a and b.

Dual tableselect sysdate from dual;select now();

Associated queryselect count(distinct(a.col1)) as rcount fromschema.prefix_table1 a,schema.prefix_table2 bwhere 1 = 1and a.col2 = b.col2(+)and a.col3 > 0and a.col4 = '1'select count(distinct(a.col1)) as rcount fromschema.prefix_table1 a left outer join schema.prefix_table2 b on (a.col2 = b.col2)where 1 = 1

select count(distinct(a.col1)) as rcount fromschema.prefix_table1 a,schema.prefix_table2 b,schema.prefix_table3 c,schema.prefix_table4 dwhere 1 = 1and a.col2 = b.col2and a.col3 = c.col3(+)and a.col4 = d.col4(+)and a.col5 > 0and a.col6 = '1'select count(distinct(a.col1)) as rcount fromschema.prefix_table1 a inner join schema.prefix_table2 b on (a.col2 = b.col2)left outer join schema.prefix_table3 c on (a.col3 = c.col3)left outer join schema.prefix_table4 d on (a.col4 = d.col4)where 1 = 1and a.col5 > 0and a.col6 = '1'

Subqueryoracle:select * from (select * from (select * from schema.prefix_table order by col1) where x=1 order by col2) where y=2 order by col3select * from (select * from (select * from schema.prefix_table order by col1 alias1) where x=1 order by col2 alias2) where y=2 order by col3

Rownum

limitselect * from ( select * from (select * from schema.prefix_table1 order by col1 desc,col2 asc) where rownum <= 50 order by col3 asc,col4 desc)where rownum <= 20 order by col5 desc,col6 asc;select * from ( select * from (select * from schema.prefix_table1 order by col1 desc,col2 asc) selb order by col3 asc,col4 desc limit 50 ) selaorder by col5 desc,col6 asc limit 20;

limit must be used after order by

sequenceselect schema.prefix_table1_sequence.nextval as ncode from dualselect nextval('schema.prefix_table1_sequence') as ncode

ASselect a.col1 a_col1，a.col2 a_col2 from a_table aselect a.col1 as a_col1，a.col2 as a_col2 from a_table a

NVLselect nvl(sum(value11),0) fs_value1, nvl(sum(value21),0) fs_value2 from field_sumselect coalesce(sum(value11),0) as fs_value1,coalesce(sum(value21),0) as fs_value2from field_sum

Decodeselect decode(endflag,'1','a','b') endflagfrom testselect (caseendflag when '1' then 'a'else 'b' end) as endflag from test

3.4函数 / 存储过程

例子:

1.CREATE ORREPLACEFUNCTIONmessage_deletes(ids"varchar",userid int8)

2.RETURNSint4AS

3.$BODY$

4.DECLARE

5.r RECORD;

6.del bool;

7.num int4:=0;

8.sql"varchar";

9.BEGIN

10.sql:='select id,receiveuserid,senduserid,senddelete,receivedelete from message where id in('||ids||')';

11.FORrIN EXECUTE sqlLOOP

12.del:=false;

13.IFr.receiveuserid=useridandr.senduserid=useridTHEN

14.del:=true;

15.ELSEIFr.receiveuserid=useridTHEN

16.IFr.senddelete=false THEN

17.updatemessagesetreceivedelete=true whereid=r.id;

18.ELSE

19.del:=true;

20.END IF;

21.ELSEIFr.senduserid=useridTHEN

22.IFr.receivedelete=false THEN

23.updatemessagesetsenddelete=true whereid=r.id;

24.ELSE

25.del:=true;

26.END IF;

27.END IF;

28.IFdelTHEN

29.delete frommessagewhereid=r.id;

30.num:=num+1;

31.END IF;

32.END LOOP;

33.returnnum;

34.END;

35.$BODY$

36.LANGUAGE'plpgsql'VOLATILE;

4C#里的连接及查询语句

4.1Npgsql

下载 Npgsql.Net Data Provider for Postgresql组件根据 .Net famework 的版本选择正确地Npgsql版本

解压zip 文件, 复制 Npgsql.dll、Mono.Security.dll 文件到 C# 工程的obj目录下，在 VS2010中把Npgsql.dll文件加入到 References.

在需要使用Npgsql的C#头文件加入如下 using 语句.

1.usingNpgsql;

4.2连接字符串

创建 PostgreSQL 数据库连接

2.stringconnectionString ="Server=127.0.0.1;Port=5432;User Id=myuser;Password=mypassword;Database=mydb;"

3.NpgsqlConnection conn =newNpgsqlConnection(stringconnectionString);

4.//打开一个数据库连接，在执行相关SQL之前调用

5.conn.Open();

6.//关闭一个数据库连接，在执行完相关SQL之后调用

7.conn.Close();

4.3查询方法

用NpgsqlCommand.ExecuteScalar()方法获得唯一值的检索结果.

1.try

2.{

3.stringsql ="select count(*) from mytable";

4.conn.Open();

5.NpgSqlCommand objCommand =newNpgSqlCommand(sql, conn);

6.intcount = Convert.ToInt32(objCommand.ExecuteScalar());

7.}

8.finally

9.{

10.conn.Close();

11.}

用 NpgsqlCommand.ExecuteReader() 方法获得一个结果集的检索结果.

1.stringsql ="select * from mytable";

2.NpgsqlCommand objCommand =newNpgsqlCommand(sql,conn);

3.NpgsqlDataReader dr = command.ExecuteReader();

4.while(dr.Read())

5.{

6.for(i = 0; i

7.{

8.Console.Write("{0} /t", dr[i]);//获得字段名

9.}

10.inttestId = dr["id"];//获得指定字段的值。(id是test表的一个字段)

11.……

12.Console.WriteLine();

13.}

14.dr.Close();

4.4修改方法

用NpgsqlCommand.ExecuteNonQuery()方法获得对指定表进行添加、更新和删除一条记录的操作

添加记录

1.stringsql ="insert mytable values (3,'V3')";

2.NpgsqlCommandobjCommand =newNpgsqlCommand(sql, conn);

3.objCommand.ExecuteNonQuery();

更新记录

1.sql ="update mytable set value='V30' where id=3";

2.NpgsqlCommandobjCommand =newNpgsqlCommand(sql, conn);

3.objCommand.ExecuteNonQuery();

删除记录

1.sql ="delete from mytable where id=1";

2.NpgsqlCommandobjCommand =newNpgsqlCommand(sql, conn);

3.objCommand.ExecuteNonQuery();

用NpgsqlDataAdapter.Fill方法，把检索的结果集放到DataSet object中，这样可以使用DataSet object设置DotNet的DataGridView控件的DataSource属性，这样在DataGridView中显示从表中取出的所有记录。

1.stringsql ="select id,value from test";

2.DataSet ds =newDataSet();

3.NpgsqlDataAdapter objAdapter =newNpgsqlDataAdapter(sql, conn);

4.objAdapter.Fill(ds,"a");//“a”这个表是自定义的

5.dgvBaseResult.DataSource = ds.Tables["a"];//dgvBaseResult是DataGridView的一个Object。

del文件转换成oracle,Oracle迁移到PostgreSQL的一些经验（from tians的博客http://blog.sina.com.cn/s/blog_8742444201012y0m...