T-SQL is the language engine of SQL Server, and Oracle's language engine is PLSQL. Both inquiry languages extends the ANSI SQL-92 standard to provide additional support. The applications you create are almost all of these supplementary features. This article describes the most commonly used, non-standard Oracle extensions, as well as how to transform these extensions for use in a SQL Server environment.
When you select the data query by PLSQL, the From clause is required, which is the same as SQL Server. The SELECT statement must select a target for a target. There is a special table DUAL in the Oracle database. The DUAL table is an actually existing table in Oracle, and any user can read, which is often used in the SELECT without a target table. The DUAL table is created by Oracle along with the data dictionary, and all users can access the table with Name DUAL. There is only one column of Dummy in this table, which is defined as a varchar2 (1) type, with a row value X. Selecting data from the DUAL table is often used to calculate constant expressions through the SELECT statement. So only one line of data is only one line, so the constant only returns once. Dual Query under Oracle As shown below: SELECT 'X' from DUAL, the peer SQL Server query is below: SELECT 'X'
The following is NULL-related knowledge, uses Dual: SQL> SELECT 1 from dual where null = NULL; no record SQL> SELECT 1 from dual where null = ''; no record SQL> SELECT 1 from dual where ' '=' '; No recorded SQL> SELECT 1 from Dual Where Null Is Null; 1 --------- 1SQL> SELECT 1 from Dual WHERE NVL (NULL, 0) = NVL (NULL, 0) ; 1 --------- 1 Check the current connection user SQL> select user from dual;
View the current date, time SQL> SELECT SYSDATE.
connection
Oracle uses the || symbol as a connecting manner, and the SQL Server's connection is a plus sign: .
Oracle Query as follows: SELECT 'Name' || 'Last Name' from TableName
The corresponding SQL Server query is as follows: SELECT 'NAME' 'LAST NAME'
Digital
There is a TRUNC function in the Oracle database that returns the N bit of the M-bit decimal number; if M is omitting M is 0 bits. The value of M can be negative, indicating that the M-bit number on the left side of the decimal point is intercepted.
You can use Round or Floor under SQL Server.
The following is an Oracle query: SELECT TRUNC (15.79, 1) "truncate" from DUAL
Here is the SQL Server version of the same query: SELECT ROUND (15.79, 0) Rouded, Round (15.79, 0, 1) Truncated SELECT FLOOR (Round (15.79, 0)), FLOOR (Round (15.79, 0, 1))
Digital conversion
Oracle's To_Char function can convert N-bit Number data types to a VARCHAR2 data type while adopting optional digital formats.
SQL Server returns character data after digital conversion via the STR function. However, this function does not have a convenient Format parameter. Oracle query is as follows: Select to_char (123.45, 99999999999999) from tab select to_char (expiry_date, 'ddmonyyyyyy ") from Tab
The following is a SQL Server version of the query: SELECT STR (123.45, 14) Select Str (Round (123.455, 2), 12, 2) Select Cast (Replace (Convert (varced (VARCHAR (12), EXPIRYDATE, 106), '' , ') as varchar (9))
Length and Len
The following is an Oracle query: SELECT Length ('sqlmag') "Length in Characters" from Dual;
The above query is written in SQL Server: SELECT LEN ('SQLMAG') "Length In Characters"
date
The above two systems have their respective current date and time format.
Oracle Date and use as follows: sysdate
SQL Server is like this: getdate ()
You can use a variety of grammar to operate the date. The following code formats in the month in the Oracle date value (return Date plus N-month): SELECT Add_MontHS (sysdate, 12) from Dual
SQL Server is completed as follows: SELECT DATEADD (mm, 12, getdate ())
Data reduction is also different. The following code directly subtracts data directly in Oracle: SELECT SYSDATE -ADD_MONTHS (Sysdate, 12) from Dual
SQL Server is doing this: SELECT dateDiff (DD, getdate (), dateadd (mm, 12, getdate ())) Reference: Builder.com
