Oracle MSSQL Compared

From Minor Miracle Software
Revision as of 13:08, 7 May 2018 by WikiSysop (talk | contribs) (Internal Links)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Oracle MSSQL Compared

Structured Query Language (SQL)[1]
This article describes SQL and the differences between PL/SQL[2] and MS-SQL.[3]
SQL Snippets[4]

Oracle PL/SQL Reference[5][6]

MS-SQL T/SQL Reference[7]

Oracle: Case-Sensitive by default.
MS-SQL: Case-Insensitive by default.

Operators

Operator Description Example
= Equal to Author = 'Alcott'
<> or != Not equal to Dept <> 'Sales'
> Greater than Hire_Date > '2012-01-31'
< Less than Bonus < 50000.00
>= Greater than or equal Dependents >= 2
<= Less than or equal Rate <= 0.05
BETWEEN Between an inclusive range Cost BETWEEN 100.00 AND 500.00
LIKE Match a character pattern First_Name LIKE 'Will%'
IN Equal to one of multiple possible values DeptCode IN (101, 103, 209)
IS or IS NOT Compare to null (missing data) Address IS NOT NULL

Arrays

PL/SQL[8]
Arrays are part of Collections, along with bags, lists, nested tables, sets, and trees.

T/SQL[9]
T/SQL has no array functionality or other storage class type. But it can be improvised with a temporary table.

Conditional (CASE) expressions

  • No difference between PL/SQL and T/SQL

SQL has a case/when/then/else/end expression, which was introduced in SQL-92[10]. In its most general form, which is called a "searched case" in the SQL standard, it works like Else if|else if in other programming languages:

CASE WHEN n > 0 
          THEN 'positive' 
     WHEN n < 0 
          THEN 'negative'
     ELSE 'zero'
END

The WHEN conditions are tested in the order in which they appear in the source. If no ELSE expression is specified, it defaults to ELSE NULL. An abbreviated syntax exists mirroring switch statement[11]s; it is called "simple case" in the SQL standard:

CASE n WHEN 1 
            THEN 'one' 
       WHEN 2
	    THEN 'two' 
       ELSE 'i cannot count that high'
END

This syntax uses implicit equality comparisons, with the usual caveats for comparing with NULL.

For the Oracle-SQL dialect, the latter can be shortened to an equivalent DECODE construct:

SELECT DECODE(n, 1, "one", 
	         2, "two",
                 "i cannot count that high")
FROM   some_table;

The last value is the default; if none is specified, it also defaults to NULL. However, unlike the standard's "simple case", Oracle's DECODE considers two NULLs to be equal with each other.

Handling Errors

PL/SQL[12]
Offers EXCEPTIONS, both internal and user defined.
Offers RAISE, which calls a pre-defined exception.

T/SQL[13][14]
Offers EXCEPTIONS, both internal and user defined.
Offers RAISERROR, which behaves like an ASSERT in the C language.

CURSOR

PL/SQL[15]

-- CURSOR syntax
CURSOR cursor_name [(parameter[, parameter]...)]
   [RETURN return_type] IS select_statement;

-- CURSOR example
DECLARE
   CURSOR c1 IS SELECT empno, ename, job, sal FROM emp 
      WHERE sal > 2000; 
   CURSOR c2 RETURN dept%ROWTYPE IS 
      SELECT * FROM dept WHERE deptno = 10

T/SQL[16]

-- ISO and T/SQL syntax
ISO Syntax
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]

Note: T/SQL is a whole lot more complex

IF - THEN - ELSE

PL/SQL[17]

IF condition THEN
   sequence_of_statements1
ELSE
   sequence_of_statements2
END IF;

T/SQL[18]

IF 1 = 1 PRINT 'Boolean_expression is true.'
ELSE PRINT 'Boolean_expression is false.' ;

Note: T/SQL has no THEN or END IF clause.



JOINs

Cartesian Product
A select statement with two tables and no join clause. For example:

SELECT pt.product_type_id, p.product_id
FROM product_types pt, products p;

will produce all the rows in product_types matched with all the rows in products.
If product_types has M rows and products has N rows the result set will be M * N rows.


Equi Joins use the equality operator (=).

Natural Joins is a type of equi-join where the join predicate arises implicitly by comparing all columns in both tables that have the same column-names in the joined tables. The resulting joined table contains only one column for each pair of equally named columns. This assumes a consistent naming convention in a Level 3 Normalized Database.

Non-equijoins use an operator other than the equality operator, such as <, >, BETWEEN, and so on.

Self Joins return rows joined on the same table.

Inner Joins return a row only when both columns in the tables satisfy the join condition.

select pat.LAST_NAME||', '||pat.FIRST_NAME, pat.INSTITUTIONID as PID, pat.patient_id, to_char( sum(ch.PATIENT_RESPONSIBILITY - ch.PATIENT_PAYMENTS), '$9,999.00') as OWED
from charge ch, patient pat
where ch.patient_id = pat.patient_id
and pat.PATIENT_ACCOUNT_CODE_ID = '34'
and ch.date_of_service <= (SYSDATE - 14)
and ch.balance != '0'
Group By (pat.LAST_NAME||', '||pat.FIRST_NAME, pat.INSTITUTIONID, pat.patient_id)
order by OWED DESC

select pat.LAST_NAME||', '||pat.FIRST_NAME, pat.INSTITUTIONID as PID, pat.patient_id, to_char( sum(ch.PATIENT_RESPONSIBILITY - ch.PATIENT_PAYMENTS), '$9,999.00') as OWED
from charge ch INNER JOIN patient pat
ON ch.patient_id = pat.patient_id
and pat.PATIENT_ACCOUNT_CODE_ID = '34'
and ch.date_of_service <= (SYSDATE - 14)
and ch.balance != '0'
Group By (pat.LAST_NAME||', '||pat.FIRST_NAME, pat.INSTITUTIONID, pat.patient_id)
order by OWED DESC


Outer Joins return a row even when one of the columns in the join condition contains a null value.

Full Outer Joins A full outer join uses all rows in the joined tables, including those that have null values in either of the columns used in the join.

SELECT p.name, pt.name
FROM products p FULL OUTER JOIN product_types pt
USING (product_type_id)
ORDER BY p.name;
NAME NAME
------------------------------ ----------
2412: The Return               Video
Chemistry                      Book
Creative Yell                  CD
From Another Planet            DVD
Modern Science                 Book
My Front Line
Pop 3                          CD
Space Force 9                  DVD
Supernova                      Video
                               Magazine

Left Outer Join returns all the rows on the Left and NULLs for missing rows on the Right.
For example:

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id (+)
ORDER BY p.name;

NAME                           NAME
------------------------------ ----------
2412: The Return               Video
Chemistry                      Book
Classical Music                CD
Creative Yell                  CD
From Another Planet            DVD
Modern Science                 Book
My Front Line
Pop 3                          CD
Space Force 9                  DVD

Right Outer Join returns all the rows on the Right and NULLs for missing rows on the Left.
For example:

SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id (+) = pt.product_type_id
ORDER BY p.name;

NAME                           NAME
------------------------------ ----------
2412: The Return               Video
Chemistry                      Book
Classical Music                CD
Creative Yell                  CD
From Another Planet            DVD
Modern Science                 Book
My Front Line                  Book
Pop 3                          CD
Space Force 9                  DVD
                               Magazine

Outer Joins Limitations
Use (+) on only one side, not both.
You cannot use an outer join condition with the IN operator.
You cannot use an outer join condition with another join using the OR operator.

USING Keyword SQL/92
from charge ch, patient pat
where ch.patient_id = pat.patient_id
becomes
from charge ch INNER JOIN patient pat
USING ( patient_id )
But patient_id can not be in the select clause.

Language Elements

PL/SQL[19]

T/SQL[20]


LOOPS

WHILE LOOP

PL/SQL[21]

LOOP
   ...
   IF credit_rating < 3 THEN
      ...
      EXIT;  -- exit loop immediately
   END IF;
END LOOP;
-- control resumes here

This is a WHILE loop with an embedded IF-EXIT-END IF conditional control.

T/SQL[22]

WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300
BEGIN
   UPDATE Production.Product
      SET ListPrice = ListPrice * 2
END

T/SQL WHILE LOOP example.

FOR LOOP

PL/SQL[23]

FOR i IN 1..3 LOOP  -- assign the values 1,2,3 to i
   sequence_of_statements  -- executes three times
END LOOP;

T/SQL
There is no FOR LOOP in T/SQL but a WHILE LOOP can be written to produce the same behavior.

Native Dynamic SQL

PL/SQL[24]
SQL query based on run-time variables.

T/SQL[25]
SQL query based on run-time variables.


Object Types

PL/SQL[26]
Supports abstract, user-defined objects.

T/SQL[27]
Supports abstract, user-defined objects.

Packages

PL/SQL[28]
A package is a schema object that groups logically related PL/SQL types, items, and subprograms.

T/SQL[29]
Procedures can be grouped in Management Studio script project or in a schema, but there is no package as in PL/SQL.

Sub Programs

PL/SQL[30]
Has procedures and functions where procedures perform an action and functions compute a value.

T/SQL[31][32]
Has procedures and functions where procedures perform an action and functions compute a value.

Tuning Applications

PL/SQL[33]

T/SQL[34]

Internal Links

Parent Article: Microsoft SQL Server (Notes)