Comparison between oracle pl/SQL and SQL SERVER T-SQL

1. External Connection
· Ms SQL SERVER supports two forms of table connection
① The form inherited from Sybase:
Field 1 * = Field 2 (left join)
Field 1 = * Field 2 (Right join)
No full outer join syntax in this form
② Standard outer join syntax
Left [outer] join on logical expression
Right [outer] join on logical expression
Full [outer] join (all outer join) on logical expression
The logical expression can be A complex expression, for example, A. ID = B. ID AND (A. Parebt_ID = 1 OR A. Parent_ID = 2)

Please note that the query statement you wrote has reported such errors.
Joined tables cannot be specified in a query containing outer join operators. Joined tables cannot be specified in a query containing outer join operators. View or function 'dbo. VU_CAF_BILLS 'contains joined tables
This statement tells you that the view or subquery referenced by the query statement also uses an external connection, but the reference view or subquery outer connection syntax is not consistent with your outer connection syntax.
Example: select A. [ZONE], A. FLAG, A. FlagDesc, A. CAF_NO
From dbo. VU_CAF_BILLS A, TU_Flag
Where A. CAF_NO * = TU_Flag.ObjNo
View the external connection Syntax of dbo. VU_CAF_BILLS is a standard SQL syntax, while the external connection syntax in this statement is a Sybase-style external connection syntax.


· Oracle does not support standard external connection syntax or full external connection, which is a defect
Field 1 = Field 2 (+) (left join)
Field 1 (+) = Field 2 (Right join)

· Use of external connection statements
① You do not want to lose data rows in the primary table because of table connection.
① If A record exists in Table A but does not exist in Table B, use the not in (select query clause) syntax as A general practice,
The biggest disadvantage of using not in is slow because each data row is executed: select query clause
Use the following syntax:
Select TU_COMPANY .*
From TU_COMPANY left join TU_Comp_Agent on TU_COMPANY.ID = TU_Comp_Agent.CompCode
Where TU_Comp_Agent.Id is null

2. triggers
· From what I learned, ms SQL SERVER, only table triggers, and the trigger time is not rich enough
For example, when an insert task is triggered on a subaccount, the single or multiple inserts are not distinguished, and the pre-insertion or post-insertion triggers are not distinguished.
When multiple data entries are inserted, you need to use a cursor to process each inserted data.

· The triggers provided by Oracle include not only table-based triggers, but also other types, such as database-level triggers: Database startup and database Shutdown
For Table-level triggers, You can differentiate whether single insert or multiple insert operations are triggered before or after insert operations.

3. Table Data Replication
· Data replication in the database
· Ms SQL Server
Insert into copy table name select statement (copy table already exists)
Select field list into copy table name from table (copy table does not exist)
· Oracle
Insert into copy table name select statement (copy table already exists)
Create table copy table name as select statement (the copy table does not exist)

· Batch processing of text file transfer and transfer
· Ms SQL Server
BCP command line program
· Oracle
SQLLDR command line program

4. Update and delete multiple tables
An update statement cannot update multiple tables. Unless the trigger is used for implicit update, I mean to update the table to be updated based on the data of other tables.
General format:
· Ms SQL Server
Update
SET field 1 = B Table field expression,
Field 2 = B Table field expression
From B
WHERE logical expression
· Oracle
Update
SET field 1 = (select field expression from B WHERE ...),
Field 2 = (select field expression from B WHERE ...)
WHERE logical expression

From the above, I feel that oracle does not have A good ms SQL, mainly because: If A requires multiple field updates, the MS_ SQL statement is more concise

Do you know how the people who just learned the database do the above? They use cursors to process one by one

In addition, the from clause of Delete in Oracle does not support multi-table join. It can only be done through subqueries:
Delete from Table A where exists (select * from Table B where Table A. empid = Table B. empid)
Delete from Table A where Table A. empid in (select empid from Table B)


5. Both of the temporary tables used in stored procedures or functions provide this function.
Temporary tables, the main advantage is that the operation does not leave any trace, do not generate logs,
So fast
· MS SQL SERVER
Create table # TABLE Name (...) or SELECT field expression list INTO # TABLE name FROM
Add # before the table name. These temporary tables are only valid during a database connection session.

· Oracle
Create [Global] Temporary Table, plus [Global] is a Global Temporary Table (all database connection sessions are visible ),
Not private (valid during a database connection session)


6. dynamically Execute SQL statements
· Ms SQL server 7.0 does not seem to have this function, ms SQL SERVER 2000 has this function.
Do you want to pass a table name in the parameters of the stored procedure or dynamically
It is difficult to generate an SQL statement. I have read the following example: you have solved all your previous problems.
Declare @ count int
Declare @ SQL nvarchar (200)
Set @ SQL = n' select count (*) from sysobjects'
Exec sp_executesql @ SQL, n' @ I int output', @ count output


· Oracle provides two methods to implement this function.
① Package DBMS_ SQL:
Open the cursor (open_cursor, this process is not available for non-query statements)
Analysis Statement (Parse)
Bind_variable)
Execute)
Close the cursor (close_cursor, this process is not available for non-query statements)
② Execute immediate ls_ SQL
 
Oracle uses the | symbol as the connector, while SQL Server uses the plus sign: +.

The Oracle query is as follows:
Select 'name' | 'last name' From tableName
The corresponding SQL Server query is as follows:
Select 'name' + 'last name'

Number trade-offs
Oracle Database has a TRUNC function, which returns n digits of the m-bit decimal number. If m is omitted, n is 0 digits. The m value can be negative, indicating that the m digit on the left of the decimal point is truncated.
You can use Round or Floor in SQL Server.
Oracle query:
Select trunc (15.79, 1) "Truncate" from dual;
The following are SQL Server versions of similar queries:
Select round (15.79, 0) rounded, ROUND (15.79,) truncated
Select floor (ROUND (15.79, 0), FLOOR (ROUND (15.79 ))

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:
SELECT to_char (123.45, 99999999999999) from tab
SELECT to_char (EXPIRY_DATE, 'ddmonyyyy') from tab
The following are SQL Server version queries:
Select str (123.45, 14)
Select str (round (123.455, 2)
Select cast (REPLACE (CONVERT (varchar (12), EXPIRYDATE, 106), '','') as varchar (9 ))
LENGTH and LEN
Oracle queries are as follows:
Select length ('sqlmag') "Length in characters" from dual;
The preceding query is written in the following way in SQL Server:
Select len ('sqlmag') "Length in characters"

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 following code adjusts the format of the month in the Oracle date value (return date plus n months ):
Select add_months (sysdate, 12) from dual
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:
SELECT sysdate-add_months (sysdate, 12) FROM dual
SQL Server does this:
SELECT datediff (dd, GetDate (), dateadd (mm, 12, getdate ()))
When querying
The FROM clause is required when PLSQL queries are executed.
SQL SERVER is not necessarily
For example, select 2*5 from dual in ORACLE
In SQL SERVER, select 2*5

PL/SQL with ";" separate each sentence, T-SQL with a return sentence.

 

PL/SQL and T-SQL have the same injection methods.

 

If statement

The if sentence in PL/SQL is similar to vb, and ";" is used at the end:
IF <condition_1> THEN...

ELSIF <condition_2> THEN...

/*......*/

ELSIF <condition_n> THEN...

ELSE...

End if;

If sentence of T-SQL
If <conditon> begin
/*...*/
End

 

PL/SQL is block-based, and all PL/SQL statements are block-based. T-SQL has no block concept. The structure is as follows:
DECLARE
/* Declarative section: variables, types, and local subprograms .*/
BEGIN
/* Executable section: procedural and SQL statements go here .*/
/* This is the only section of the block that is required .*/
EXCEPTION
/* Exception handling section: error handling statements go here .*/
END;

 

Limit Value sentence

PL/SQL values can be set in variable description:

Rochelle DATE: = to_date ('31-JUL-02 ');

The T-SQL needs to be split from the limit value

Declare @ sDate datetime

Set @ sDate = getdate ()

Note that the delimiter value is different. PL/SQL is ": =", T-SQL is "="

 

The following statement shows the explain value of the explain value in the Declaration section of PL/SQL:

Declare
Hire date;/* implicit initialization with null */
Job title varchar2 (80): = 'salesman ';
Emp found boolean;/* implicit initialization with null */
Salary incr constant number (1.5): =;/* constant */
...
Begin... end;

Boolean data may only be true, false, or null.

 

PL/SQL select into vs. T-SQL select

T-SQL, for example:

Select * from author into temp1 where au_id = 8081 -- automatically create a table in temp1 to the previous data volume

The select into function of PL/SQL is much larger than creating a table but returning a value. The value can have two types: % TYPE or % ROWTYPE. For example:

Declare
Employee rec EMP % ROWTYPE;
Max sal EMP. SAL % TYPE;
Begin
Select EMPNO, ENAME, JOB, MGR, SAL, COMM, HIREDATE, DEPTNO
Into employee rec
From EMP where EMPNO = 5698;
Select max (SAL) into max sal from EMP;
...
End;

% ROWTYPE is a sort type that can realize the similar function of select into in the T-SQL, but it is different in the actual method, the T-SQL puts the data required by the memory into a custom table, and PL/SQL saves the Memory set through a kind of changes similar to the structure, it is a variable mechanism, not a table.

PL/SQL uses the select method to return at least one limit to the variable value. Otherwise, it is usually used. If it is in the limit value, select returns multiple limit numbers, you must use cursor