Differences between Oracle and SQL Server statements and Data Types

From: http://dev.csdn.net/article/56/56449.shtm

 

T-SQL is the language engine of SQL Server, while Oracle's language engine is PLSQL. Both query languages have extended the ANSI SQL-92 standard to provide additional support. Almost all applications you create use these complementary features. This article describes the most common and non-standard Oracle extensions. It also describes how to convert these extensions for use in the SQL server environment.

Column Selection

When PLSQL is used to perform data queries, the from clause is required, which is the same as that of SQL Server. The SELECT statement must select the target data table. There is a special table dual in the Oracle database. A dual table is an existing table in Oracle and can be read by any user. It is often used in select statements without a target table. The dual table is created by Oracle along with the data dictionary. All users can access the table with the name dual. There is only one dummy column in this table, which is defined as varchar2 (1) type and has a row value of X. Selecting data from the dual table is often used to calculate the constant expression through the SELECT statement. Because dual only has one row of data, the constant is returned only once.

The dual query in Oracle is as follows:
Select 'x' from dual
The equivalent SQL Server Query looks like this:
Select 'x'

The following are null-related knowledge and dual is used:
SQL> select 1 from dual where null = NULL;
No records found
SQL> select 1 from dual where null = '';
No records found
SQL> select 1 from dual where ''= '';
No records found
SQL> select 1 from dual where NULL is null;
1
---------
1
SQL> select 1 from dual where nvl (null, 0) = nvl (null, 0 );
1
---------
1
View connected users
SQL> Select User from dual;

View current date and time
SQL> select sysdate from dual;

Connection

Oracle uses the | symbol as the connector, while SQL server uses the plus sign: +.

The Oracle query is as follows:
Select 'name' | 'last name' from tablename

The corresponding SQL Server query is as follows:
Select 'name' + 'last name'

Number trade-offs

Oracle Database has a trunc function, which returns n digits of the M-bit decimal number. If M is omitted, n is 0 digits. The m value can be negative, indicating that the M digit on the left of the decimal point is truncated.

You can use round or floor in SQL Server.

Oracle query:
Select trunc (15.79, 1) "truncate" from dual;

The following are SQL Server versions of similar queries:
Select round (15.79, 0) rounded, round (15.79,) truncated
Select floor (round (15.79, 0), floor (round (15.79 ))

Digital Conversion

Oracle's to_char function can convert the n-bit number data type to the varchar2 data type and use an optional numeric format.

SQL Server Returns the character data after numeric conversion through the STR function. However, this function does not have a convenient format parameter.

Oracle query:
Select to_char (123.45, 99999999999999) from Tab
Select to_char (expiry_date, 'ddmonyyyy') from Tab

The following are SQL Server version queries:
Select STR (123.45, 14)
Select STR (round (123.455, 2)
Select cast (replace (convert (varchar (12), expirydate, 106), '','') as varchar (9 ))

Length and Len

Oracle queries are as follows:
Select length ('sqlmag') "Length in characters" from dual;

The preceding query is written in the following way in SQL Server:
Select Len ('sqlmag') "Length in characters"

Date

Both systems have their own current date and time formats.

The date obtained by Oracle is as follows:
Sysdate

SQL Server is like this:
Getdate ()

You can use various syntaxes to operate dates. The following code adjusts the format of the month in the Oracle date value (return date plus N months ):
Select add_months (sysdate, 12) from dual

SQL Server performs the same functions as follows:
Select dateadd (mm, 12, getdate ())

Data Subtraction is also different. The following code directly deletes data in Oracle:
Select sysdate-add_months (sysdate, 12) from dual

SQL Server does this:
Select datediff (DD, getdate (), dateadd (mm, 12, getdate ()))