Less13_SQLMigration

13-8
Copyright © 2004, Oracle. All rights reserved.
The SELECT Statement
The Oracle SELECT statement is similar to the SQL Server SELECT statement.
Clause SELECT Description Columns returned
In SQL Server, an example using a column alias:
SQL> SELECT email = cust_email 2 FROM customers
In Oracle, the column alias is placed after the column name:
13-2
Copyright © 2004, Oracle. All rights reserved.
What Is SQL?
Structured Query Language (SQL) provides statements for a variety of tasks: • Querying data • Inserting, updating, and deleting rows in a table • Creating, replacing, altering, and dropping objects • Controlling access to the database and its objects
Migrating SQL Statements
Copyright © 2004, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do the following: • Identify SQL similarities and incompatibilities between SQL Server and Oracle databases • Convert SQL statements to run in an Oracle database
In SQL Server, the SELECT INTO clause is used.
SQL> SELECT cust_first_name, cust_last_name 2 INTO contacts 3 FROM customers
For the Oracle database, rewrite the statement by using the INSERT INTO clause.
FROM
WHERE GROUP BY
Tables where data is retrieved
Conditions to restrict rows returned Returns a single row of summary information
for each group
HAVING ORDER BY
•
Convert SQL Server statements to Oraclesupported syntax wherever they are used:
– – – – Application code Stored procedures Triggers Scripts
13-4
Copyright © 2004, Oracle. All rights reserved.
13-3
Copyright © 2004, Oracle. All rights reserved.
Migrating SQL Statements
•
Both SQL Server and Oracle databases:
– Support ANSI SQL-92 standard – Provide extensions to SQL-92 standard Similar in function Different in syntax
SQL> SELECT getdate()
In Oracle, the FROM clause is required.
SQL> SELECT sysdate
2
FROM dual;
13-11
Copyright © 2004, Oracle. All rights reserved.
SELECT Statement: SELECT INTO Clause
13-17
Copyright © 2004, Oracle. All rights reserved.
Built-In Functions
Both SQL Server and Oracle have proprietary built-in functions:
SQL Server Character Null test char() isnull(qty, 0) Oracle chr() nvl(qty,0)
SQL> SELECT cust_email email
2
FROM customers;
13-13
Copyright © 2004, Oracle. All rights reserved.
The INSERT Statement
In SQL Server, the INTO clause is optional.
13-19
Copyright © 2004, Oracle. All rights reserved.
Data Type Conversion
SQL Server uses the CONVERT function to convert data types.
SQL> SELECT CONVERT(char, GETDATE())
The UPDATE Statement
•
SQL Server example:
SQL> 2 3 4 5 UPDATE inventories SET quantity_on_hand = 0 FROM inventories i, product_information p WHERE i.product_id = p.product_id and product_status=‘planned’
SQL> INSERT regions
2
VALUES (202, ‘Southeast’)
In Oracle, the INTO clause is required.
SQL> INSERT INTO regions
2
VALUES (202, ‘Southeast’);
13-14
Copyright © 2004, Oracle. All rights reserved.
Object Name Changes
The way to reference a table or view in a SQL statement is different: • SQL Server
– database_name.owner_name.table_name
•
Oracle database
13-9
Conditions to restrict groups returned Sorts rows returned
Copyright © 2004, Oracle. All rights reserved.
SELECT Statement: FROM Clause
In SQL Server, the FROM clause is optional.
•
Rewrite in Oracle:
SQL> 2 3 4 5 UPDATE inventories SET quantity_on_hand = 0 WHERE product_id IN (SELECT product_id FROM product_information WHERE product_status = ‘planned’);
SQL> CONNECT hr/hr; SQL> SET ROLE hr_clerk;
13-7
Copyright © 2004, Oracle. All rights reserved.
Data Query and Manipulation
In both databases, the following data query and manipulation statements have similar constructs, with some exceptions: • SELECT • INSERT • UPDATE • DELETE
Copyright © 2004, Oracle. All rights reserved.
13-15
The DELETE Statement
•
SQL Server:
DELETE FROM inventories FROM inventories i, product_information p WHERE i.product_id = p.product_id AND supplier_id = 102066
Connecting to the Database
With multiple databases in SQL Server, you use the following command to switch databases:
SQL> Use hr
With only one database in Oracle, you issue one of the following commands to switch schemas:
13-16
Copyright © 2004, Oracle. All rights reserved.
Operators
ຫໍສະໝຸດ Baidu
Examples of operator differences:
SQL Server Value comparison Null value comparison String concatenation and literals WHERE status = NULL SELECT fname + ‘ ‘ + lname AS name WHERE status IS NULL SELECT fname || ‘ ‘ || lname AS name WHERE qty !< 100 Oracle WHERE qty >= 100
SQL> INSERT INTO contacts 2 SELECT cust_first_name, cust_last_name 3 FROM customers;
13-12
Copyright © 2004, Oracle. All rights reserved.
SELECT Statement: Column Alias
– user_schema.table_name
Example accessing other schema:
SQL Server
SELECT * FROM oe.sa_dba.orders
Oracle
SELECT * FROM sa.orders
13-5
Copyright © 2004, Oracle. All rights reserved.
Replace with the appropriate equivalent Oracle function:
SQL> SELECT TO_CHAR(sysdate)
2
FROM dual;
13-20
Copyright © 2004, Oracle. All rights reserved.
SQL> 2 3 4
•
Rewrite in Oracle:
DELETE FROM inventories WHERE product_id IN (SELECT product_id FROM product_information WHERE supplier_id = 102066);
SQL> 2 3 4