Migration from Oracle to SQL Server

Baggio785http: // blog.csdn.net/baggio785column 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. 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'FromDual
 

 

The equivalent SQL Server Query looks like this:

  Select'X'
 

 

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'FromTablename
 

 

The corresponding SQL Server query is as follows:

  Select'Name'+'Last name'
 

 

Number trade-offs

Oracle Database has a trunc function, which returns the M-bit decimal numberNBit; If omittedMThenNIt is 0 bits.MThe value can be negative, indicating that the Left decimal point is truncated.MDigit.

You can use round or floor in SQL Server.

Oracle query:

  SelectTrunc (15.79,1)"Truncate"FromDual;
 

 

The following are SQL Server versions of similar queries:

  Select    Round  (  15.79  ,  0  ) Rounded,  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 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:

  SelectTo_char (123.45,99999999999999)FromTab
SelectTo_char (expiry_date,'Ddmonyyyy')FromTab
 

 

The following are SQL Server version queries:

  Select     Str  (  123.45  ,  14  )
  Select     Str  (  Round (  123.455  ,  2  ),  12  ,  2  )
  Select     Cast  (  Replace  ((  Convert  ( Varchar  (  12  ), Expirydate,  106  )),  '     '  ,  ''  )  As     Varchar  (  9 )) 

 

Length and Len

Oracle queries are as follows:

 
  SelectLength ('Sqlmag') "LengthInCharacters"FromDual;
 

 

The preceding query is written in the following way in SQL Server:

  Select Len('Sqlmag') "LengthInCharacters"
 

 

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 followingCodeThe format of the month in the Oracle date value is adjusted (the return date is addedNMonth ):

 
  SelectAdd_months (sysdate,12)FromDual
 

 

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:

  SelectSysdate-Add_months (sysdate,12)FromDual
 

 

SQL Server does this:

  SelectDatediff(DD,Getdate(),Dateadd(Mm,12,Getdate()))