Differences between oracle and sqlserver

1. Some SQL statement differences (1) SQL: select top 10 * 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.c12. Other technical differences The GPRS technology can be applied in the following fields: ◆ In oracle, The from clause in the delete statement cannot involve multiple tables. To delete records based on multiple tables, only subqueries are used .; ◆ In oracle, the update statement cannot modify data based on multiple tables. To modify data based on multiple tables, only subqueries are used; ◆ In oracle, a stored procedure cannot return a record set. to return a record set to a stored procedure, you can create a view and execute the select statement separately (note: it does not mean that the select statement cannot be used in the stored procedure, but the select statement can only be used in the following situations: insert into table select * from t1/select col1 into a from table where ...... (A is a variable )); ◆ In oracle, the insert syntax is: insert into table values () or insert into table select * from t1 ;; ◆ The date type constant in Oracle must be obtained using the to_date () function .; ◆ Dual table is a virtual table for testing purposes. Pay attention to the usage of dual table: In SQL server, if we test a function, such as select convert (int, '13'), this representation method in oracle is incorrect and should be represented: select to_number ('13') from dual ;; ◆ Number of days for difference: Subtract two dates, and use the ceil or floor function to get the number of months for difference: months_between () ◆ Data type conversion; ◆ The object name in ORACLE cannot exceed 30 characters. In ORACLE, The Delimiter is "" (corresponding to [] In SQLSERVER) (My suggestion is: it is best not to use the delimiter to avoid reserved words in the utility system ); ◆ ORACLE hollow string is treated as null; ◆ Character comparison in ORACLE is case sensitive; ◆ If order by is used in the view and the field names in the order by clause are all aliases, all fields in the order by clause must be displayed in the view. For example, the following view is available: Create view v As Select name as c1, address from t1 order by c1 When you select this view Select c1, address from v is correct, and select address from v is incorrect; ◆ Questions about temporary tables Create table # table1 (c int) in sqlserver ); In oracle, the solution is: Create global temporary table table1 (c int) on commit preserve rows This table1 exists permanently in the database, so do not use the drop command. Before performing any operations on this table in your program, run truncate table table1 first, do not use the drop command to delete this table .; ◆ Http://technet.oracle.com/doc/server815.htmthis website has many tutorials, but you must first register ;3. Data type conversion SQL SERVER ORACLE Bigint NUMBER (19, 0) Binary (50) RAW (50) Bit NUMBER (1, 0) Char (10) CHAR (10) Datetime DATE Decimal (18, 0) NUMBER (18, 0) Float FLOAT Image BLOB Int NUMBER (10, 0) Money NUMBER (19, 4) Nchar (10) CHAR (20) Ntext CLOB Numeric (18, 0) NUMBER (18, 0) Nvarchar (30) 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 (50) Varchar (50) VARCHAR2 (50)