Differences between SQL Server and Oracle

. Partial SQL Statement Differences

(1) Sql:select top * FROM table
Ora:select * FROM table where rownum<11
(2) Sql:select * from T1 join T2 on T1.C1=T2.C1
Ora:select * from T1,t2 where t1.c1=t2.c1
(3) SELECT * from T1 LEFT join T2 on T1.C1=T2.C1
Ora:select * from T1,t2 where T1.C1=T2.C1 (+)
(4) Sql:select * from T1 right join T2 on T1.C1=T2.C1
Ora:select * from T1,t2 where T1.C1 (+) =T2.C1

2. Other technical differences
The areas where GPRS technology can be applied are:
In Oracle, the FROM clause in the DELETE statement cannot involve more than one table, and only the subquery is used to delete records based on more than one table. ；
In Oracle, an UPDATE statement cannot modify data based on more than one table, and only use subqueries to modify data based on multiple tables;
In Oracle, a stored procedure cannot return a recordset, and if you want the stored procedure to return a recordset, you can create a view that can execute the SELECT statement individually (NOTE: The SELECT statement is not meant to be used in a stored procedure, but select can only be used in the following cases: INSERT INTO Table SELECT * FROM T1/select col1 to a from table where ... (A is a variable));
The syntax for insert in Oracle is: INSERT into table values () or INSERT into table select * from T1;;
Date-type constants in Oracle must be obtained using the to_date () function. ；
The dual table is a virtual table that is dedicated to the purpose of testing, paying attention to the use of the dual table:
In SQL Server, if we test a function, for example: select CONVERT (int, ' 13 '), this representation is wrong in Oracle and should be expressed as: Select To_number (' ") from dual;;
Number of days to get the difference: two date subtraction, and then the number of months to get the difference by ceil or floor function: Months_between ()
Conversion of data types;
Object names in Oracle cannot exceed 30 characters. The boundary character in Oracle is "" (corresponding to [] in SQL Server) (my suggestion is that it is best not to use the boundary character to avoid the reserved words of the utility system as much as possible);
The Oracle hollow string is considered null;
The comparison of characters in Oracle is case-sensitive;
If an order by is used in the view and the field name in the ORDER BY clause is an alias, all the fields in the ORDER BY clause must be displayed when the view is displayed, for example, a view such as the following:
Create VIEW V
As
Select name as c1,address from T1 order by C1
Then when you select the view
Select C1,address from V is correct, the Select address from V is wrong;
Questions about the Temp table
CREATE TABLE #table1 (c int) in SQL Server;
The solution in Oracle is:
Create global temporary table table1 (c int) on commit preserve rows
This table1 is permanent in the database, so do not use the drop command, in your program before any action on this table, first execute TRUNCATE TABLE table1, do not delete this table with the drop command. ；
Http://technet.oracle.com/doc/server815.htm This site has many online tutorials, but be sure to register first;

3. Data type Conversion

SQL SERVER ORACLE
bigint number (19, 0)
Binary (50) RAW
Bit number (1, 0)
Char (Ten) char (10)
DateTime DATE
Decimal (0) Number (18, 0)
float float
Image BLOB
int number (10, 0)
Money Number (19, 4)
NCHAR (+) CHAR (20)
ntext CLOB
Numeric (0) Number (18, 0)
nvarchar (VARCHAR2) (100)
Real FLOAT
smalldatetime DATE
SmallInt number (5, 0)
SmallMoney Number (10, 4)
sql_variant LONG RAW
Text CLOB
Timestamp RAW (8) not
Tinyint number (3, 0)
uniqueidentifier LONG RAW
varbinary (50) RAW
varchar (VARCHAR2) (50)

Differences between SQL Server and Oracle