开发人员从SQL服务器迁移到Oracle

We are bringing a new project in house and whereas previously all our work was on SQL Server the new product uses an oracle back end.

我们将引入一个新项目，而之前我们的工作都是在SQL Server上，而新产品使用的是oracle后端。

Can anyone advise any crib sheets or such like that gives an SQL Server person like me a rundown of what the major differences are - Would like to be able to get up and running as soon as possible.

有谁能给像我这样的SQL服务器人员提供一个关于主要区别的简要说明吗?

8 个解决方案

#1

@hamishcmcn

Your assertion that '' == Null is simply not true. In the relational world Null should only ever be read to mean "I don't know". The only result you will get from Oracle (and most other decent databases) when you compare a value to Null is 'False'.

“== = Null”的断言是不正确的。在关系世界中，Null只能被解读为“我不知道”。当您将值与Null进行比较时，您将从Oracle(以及大多数其他优秀的数据库)获得的唯一结果是“False”。

Off the top of my head the major differences between SQL Server and Oracle are:

在我的脑海中，SQL Server和Oracle的主要区别是:

Learn to love transactions, they are your friend - auto commit is not.
学会爱交易，他们是你的朋友——自动提交不是。
Read consistency and the lack of blocking reads
读一致性和缺乏阻塞读
SQL Server Database == Oracle Schema
SQL Server数据库= Oracle Schema
PL/SQL is a lot more feature rich than T-SQL
PL/SQL比T-SQL丰富得多
Learn the difference between an instance and a database in Oracle
了解Oracle中的实例和数据库之间的区别
You can have more than one Oracle instance on a server
在服务器上可以有多个Oracle实例
No pointy clicky wizards (unless you really, really want them)
没有尖锐的点击向导(除非你真的，真的想要)

Everyone else, please help me out and add more.

各位，请帮助我，并补充更多。

#2

The main difference I noticed in moving from SQL Server to Oracle was that in Oracle you need to use cursors in the SELECT statements. Also, temporary tables are used differently. In SQL Server you can create one in a procedure and then DROP it at the end, but in Oracle you're supposed to already have a temporary table created before the procedure is executed.

我在从SQL Server迁移到Oracle时注意到的主要区别是，在Oracle中，您需要在SELECT语句中使用游标。此外，临时表的使用也不同。在SQL Server中，您可以在过程中创建一个，然后在末尾删除它，但是在Oracle中，您应该已经在执行过程之前创建了一个临时表。

I'd look at datatypes too since they're quite different.

我也会看看数据类型，因为它们是完全不同的。

#3

String concatenation:
Oracle: || or concat()
Sql Server: +

字符串连接:Oracle: ||或concat() Sql Server: +

These links could be interesting:
http://www.dba-oracle.com/oracle_news/2005_12_16_sql_syntax_differences.htm
http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm (old one: Ora9 vs Sql 2000)

这些链接可能很有趣:http://www.dba-oracle.com/oracle_news/2005 _12_ 16_sql_syntax_. htm http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm (old one: Ora9 vs Sql 2000)

#4

@hamishmcn

Generally that's a bad idea.. Temporary tables in oracle should just be created and left (unless its a once off/very rarely used). The contents of the temporary table is local to each session and truncated when the session is closed. There is little point in paying the cost of creating/dropping the temporary table, might even result in *es if two processes try to create the table at the same time and unexpected commits from performing DDL.

一般来说，那是个坏主意。oracle中的临时表应该只创建和保留(除非它是一次性的/很少使用)。临时表的内容在每个会话中都是本地的，在会话关闭时被截断。在支付创建/删除临时表的成本方面没有什么意义，如果两个进程同时尝试创建表，并且执行DDL的意外提交，可能会导致冲突。

#5

What you have asked here is a huge topic, especially since you haven't really said what you are using the database for (eg, are you going to be going from TSQL -> PL/SQL or just changing the backend database your java application is connected to?)

您在这里要问的是一个很大的主题，特别是因为您还没有真正说明您使用数据库的目的(例如，您是要从TSQL -> PL/SQL开始，还是仅仅更改您的java应用程序连接的后端数据库?)

If you are serious about using your database choice to its potiential, then I suggest you dig a bit deeper and read something like Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions by Tom Kyte.

如果您真的想要将您的数据库选择应用到它的功能上，那么我建议您深入挖掘，阅读一些专家Oracle数据库架构:9i和10g编程技术和Tom Kyte的解决方案。

#6

Watch out for the difference in the way the empty string is treated.
INSERT INTO atable (a_varchar_column) VALUES ('');

注意处理空字符串的方式的不同。插入到表(a_varchar_column)值(")中;

is the same as

是一样的

INSERT INTO atable (a_varchar_column) VALUES (NULL);

I have no sqlserver experience, but I understand that it differentiates between the two

我没有sqlserver经验，但我知道它可以区分这两者

#7

If you need to you can create and drop temporary tables in procedures using the Execute Immediate command.

如果需要，可以使用Execute Immediate命令在过程中创建和删除临时表。

#8

to andy47, I did not mean that you can use the empty string in a comparison, but oracle treats it like null if you use it in an insert. Re-read my entry, then try the following SQL:

对于andy47，我并不是说可以在比较中使用空字符串，但是如果在插入中使用空字符串，oracle会将其视为null。重新读取我的条目，然后尝试以下SQL:

CREATE TABLE atable (acol VARCHAR(10));
INsERT INTO atable VALUES( '' );
SELECT * FROM atable WHERE acol IS NULL;

And to avoid a "yes it is, no it isn't" situation, here is an external link

为了避免“是，不是，不是”的情况，这里是一个外部链接。

#1