Differences in SQL statements between Oracle and SQL Server

both Oracle and SQL Server follow the SQL-92 standard: http://owen.sj.ca.us/rkowen/howto/sql92F.html , but there are some differences, the difference is as follows:

2. Oracle table name, field name, stored procedure name, variable name cannot exceed 30 string lengths.

4. The top syntax is not supported in Oracle. Use Whererownum < n instead. However, it is important to note that if you have a sort operation before top, you need to sort the first to get it using rownum.

6. The use of cursors in Oracle is quite different from SQL Server, mainly in terms of loop control.

8. In Oracle, if a column is of date type, then its default format is YYYY-MM-DD, and if you use where colDate = ' 2005-06-08 17:14:57 ' It will go wrong, ' 2005-06-08 ' can. If you need to accurately compare time (hours, minutes, seconds) At this point, you can use the following methods: To_date (' 2005-6-8 17:14:57 ', ' yyyy-mm-dd hh24:mi:ss '). Oracle and SQL Server cannot be implemented in the same SQL because of the different implementations of SQL standards on DateTime.

10. The timestamp (timestamp) in Oracle differs greatly from SQL Server. Timestamp in SQL Server is stored in a binary format in a database, and you can set this field type in the dataset to the base64binary type. The timestamp in Oracle is stored in a time format. Therefore, you cannot load SQL Server timestamp with the same dataset and have timestamp that loads Oracle. Because this value is rarely used in the application, you can not retrieve this field.

12. Timestamp in Oracle cannot be automatically populated as SQL Server passes in data, and can be set to a default value of Systimestamp for similar functionality.

14. There is no Boolean field type in Oracle, and you can use Intger or char instead of the bit type in SQL Server.

16. In Oracle, the "" empty string is treated as many null, that is, when an application inserts an empty string into the database, the actual database attempts to insert a null. If this field is a notnull type, then an error occurs, in which case a different default value can be used instead of an empty string. When importing data from SQL Server to Oracle, an empty string on the Notnull column causes the export operation to fail. You can manually modify the SQL script in the Export Wizard to set the empty string to a special value.

18. Not supported in Oracle,SELECT: into to create a new table, create a new table with the CREATE TABLE table name as SELECT statement.

20. You cannot use ' + ' in the connection string in Oracle with ' | | ' or concat (col1,col2) function.

22. wildcard [],[^], Oracle does not seem to support it.

24. You can use the & variable sentence When the variable is used, and the value of the input variable name will be required when executing

Oracle Wildcard, operator usage
The conditions used for where comparison are:
equals: =, <, <=, >, >=, <>
Contains: In, not in exists, not exists
Range: Between...and, not Between....and
Match test: like, not like
NULL test: Is NULL, is not NULL
Boolean links: And, or, not
Wildcard characters:
In the WHERE clause, wildcards can be used with a like condition. In Oracle:
Percent (Percent semicolon): used to represent any number of characters, or there may not be any characters at all.
_ (Underscore): represents the exact unknown character.
? (question mark): Used to represent the exact unknown character.
# (pound sign): used to denote the exact Arabic numerals, 0 to 9.
[A-d] (square brackets): Used to represent a range of characters, from a to D here.
Single quotation marks ('): In Oracle, you should enclose text and characters and dates in single quotation marks, and you cannot enclose numbers in quotation marks, including single and double quotes.
Double quotation marks ("): In Oracle, single and double quotes have different meanings. Double quotation marks are used to enclose column aliases that contain specific characters or spaces. Double quotation marks are also used to put text in a date format.
Apostrophe ('): In Oracle, apostrophes can also be written as two single quotes adjacent to each other. In order to find all the vendor names with apostrophes in the middle of the supplier name, you can write code like this:
SELECT * from l_suppliers where supplier_name like '% '% '
& Symbols:,& symbols are commonly used in Oracle to indicate a variable. For example, &fox is a variable, slightly different kind of a && fox. Whenever &fox appears in an Oracle script, you will be asked to provide a value for it. With &&fox, you only need to provide the variable value for & &fox The first time it appears. If you want to use the & symbol as a normal symbol, you should turn off this feature. To turn off this feature, you can run the following command: Set define off, which is a sqlplus command, not an SQL command. Sqlplus sets the environment in which SQL runs in Oracle.
Double vertical bar (| | ): Oracle uses a double vertical bar to represent string join functions.
Asterisk (*): SELECT * means that all columns are selected, and count (*) means that all rows are calculated, representing 0 or any number of characters when the wildcard character is represented.
Forward slash (/): Used in Oracle to terminate SQL statements. More precisely, it means "Run the SQL code that is now in the buffer." The forward slash is also used as a separator.
Multiline Comment:/*......*/.
Not equal to: there are many ways to express:! =, ^=, <>, not xxx=yyy, not (XXX=YYY)

Differences in SQL statements between Oracle and SQL Server