Transcript Oracle SQL

Oracle SQL
Types of Database Languages
Used to read, update and store data in a database
• Oracle SQL/ PL SQL
• T-SQL
• SQL PL
• Watcom-SQL etc.
Types of SQL Statements
• DDL – Data Definition Language; commands that define structures in a database. Ex: CREATE,
ALTER and DROP schema objects
• CREATE TABLE table_name (column_name1 data_type(size) , column_name2 data_type
(size))
• DML – Data Manipulation Language; commands that access and manipulate data in existing
schema objects. Ex: UPDATE, DELETE etc.
• UPDATE table_name SET column_name1 = ‘x’ where column_name1 = ‘y’;
• TCL – Transaction Control Language; commands that manage changes made by DML statements
Ex: COMMIT, ROLLBACK
• Ex: DELETE from table_name WHERE column_name1 = some_value
ROLLBACK;
Oracle
• Products and services
• Oracle ERP (Enterprise Resource Planning) – It is a comprehensive suite of integration, global business
applications, also known as E-Business Suite Examples of applications – Customer Relationship
Management, Service Management, Financial Management, Human Capital Management, Project
Portfolio Management, Advanced Procurement, Supply Chain Management, Value Chain Planning etc.
• Oracle PL SQL – Oracle’s procedural extension to SQL.
• Oracle Database
SQL Vs PL SQL
SQL
PL SQL
It is executed one statement/ command at a time
It is executed as a block of code
Can be used to write DDL, DML, TCL commands as Can be used to perform conditional processing
well as manage database security (create users,
within a program block – If Then Else, Loops, CASE
assign privileges/ roles etc.)
statements, exception handling etc.
SQL is a non-procedural language
PL SQL is a procedural language
PL SQL Engine
SGA – System Global Area
PL SQL
PL SQL Anonymous Block – It can be written within a query tool or an application without a name
and Oracle can run it one time. This is not stored anywhere.
PL SQL Named Block – Ex: Stored Procedure. It is a PL SQL block that Oracle stores in the database
and can be called by it’s name from an application.
• PL SQL Named Blocks
Procedures – are not expected to return a value
Functions – returns a value
Packages – collection of procedures and functions
PL SQL
• Once a PL SQL named block is compiled, it can be run anytime after that.
• If there are any syntax errors, these will be generated at the time of compiling the program
• If there are run time errors, these will be generated at the time that the program block is being
run
PL SQL
Declaration
Define variables/ constants
Program Body
Logic
Exception Handling
Handle error conditions
Program Structure
Uses of Oracle PL SQL
•
•
•
•
•
•
Query database from applications
Reporting
Automation and building applications
B2B/ A2A Integrations
Build Application Programming Interface (API)
Data Integrity: Data conversion and validation
High level difference between Microsoft SQL Server and Oracle
Microsoft SQL Server
Oracle
Language
Transact SQL (T-SQL)
PL SQL
Transaction Management
Default Behavior: will execute and
commit each command/task
individually.
A transaction in Oracle begins when
the first executable SQL statement is
encountered. When a series of SQL
queries that modify records have to
be run as a group, oracle SQL makes
changes in memory only until a
COMMIT command is issued. After
the commit, the next command
issued is treated as a new transaction
and the process begins again.
A transaction can be defined as a
group of operations or tasks that
should be treated as a single unit. For
instance, a collection of SQL queries
modifying records that all must be
updated at the same time, where (for
instance) a failure to update any
single records among the set should
result in none of the records being
updated.
If you use explicit BEGIN TRAN /
COMMIT TRAN commands, you can
group these together as an explicit
transaction—a set of statements that
must fail or succeed together.
• Example of Transaction Management
• If a bank user wants to transfer $500 from their savings account to the checking account, the following
transactions need to happen as a unit
UPDATE savings_account
SET balance = balance - 500
Decrement Savings Account
WHERE account = 1234;
UPDATE checking_account
SET balance = balance + 500
Increment Checking Account
WHERE account = 5678;
INSERT INTO journal
VALUES (journal_seq.NEXTVAL, ‘1B’, 1234, 5678, 500);
COMMIT WORK;
Record in Transaction Journal
High level difference between Microsoft SQL Server and Oracle
There are other differences in how Microsoft SQL Server and Oracle deal with data types, data storage,
operators, built-in functions, date functions, mathematical functions, locking etc.
Examples:
SQL Server
Oracle
Data Type
DATETIME
DATE
Data Storage
Log Devices
Redo Log Files
Operators
<= and !>
<=
Functions
isnull(variable, new_value) nvl(variable, new_value)
Date Function
getdate()
sysdate
High level differences between T-SQL and PL SQL
T-SQL and PL SQL have different syntax. The main difference is how they handle variables, stored procedures
and built-in functions. PL SQL can group procedures into packages.
Examples of built-in functions that are different
T-SQL
Oracle SQL
Current date/ time
GETDATE
SYSDATE
Length
LEN()
LENGTH()
Data Type Conversion
CONVERT(data type,
expression, [format])
TO_CHAR, TO_NUMBER,
TO_DATE
Null Value
ISNULL(variable,
new_value)
NVL(variable, new_value)
Examples of SQL Built-in Functions
TO_CHAR(VALUE, [,FORMAT_MASK])
SELECT SYSDATE FROM DUAL
SELECT TO_CHAR (SYSDATE, 'YYYY/MM/DD' ) FROM DUAL
SELECT TO_CHAR (SYSDATE, 'YEAR/MONTH/DAY') FROM DUAL
SELECT (1234) FROM DUAL
SELECT TO_CHAR (1234, '$9,999.00') FROM DUAL
SELECT TO_CHAR (1234, '9999.00') FROM DUAL
SUBSTR(char_exp, n)
Returns the part of the string starting at the position given by n.
SELECT SUBSTR ('PL SQL', 4) FROM DUAL
Examples of SQL Built-in Functions
INSTR(string, substring, position, occurrence)
Returns the position where the substring first occurs
SELECT INSTR ('This is an example of a PLSQL built-in function',
'PLSQL',
1,
1)
FROM DUAL
RPAD(expr1, n, expr2)
Returns expr1, right padded to length n characters with expr2, replicated as many times as necessary
SELECT RPAD ('nolemon', 21, 'nomelon') FROM DUAL
Create and Manage Views in Oracle
This is an example of a view that joins an employee table and a department table
CREATE VIEW emp_dept
AS
SELECT e.emp_no,
e.emp_name,
e.sal,
e.dept_no,
d.dept_name,
d.loc
FROM emp e, dept d
WHERE e.dept_no = d.dept_no;
Create and Manage Views in Oracle
SELECT e.emp_no,
e.emp_name,
e.sal,
e.dept_no,
d.dept_name,
d.loc
FROM emp e, dept d
WHERE e.dept_no = d.dept_no;
• After the view is created, the same data can be queried using this simple statement
SELECT * FROM emp_dept;
Create and Manage Views in Oracle
Now assume that most of the time you need to query/ modify data for employees working one a single
department such as accounts dept. a view can be created for this as well
CREATE VIEW accounts_staff AS
SELECT emp_no, emp_name, dept_no
FROM Emp
WHERE dept_no = 10 --accounts department
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Once this view is created, we can perform functions against the accounts department using this view
SELECT * FROM accounts_staff;
SELECT SUM (sal) FROM accounts_staff;
SELECT MAX (sal) FROM accounts_staff;
Create and Manage Views in Oracle
• For Example:
INSERT INTO accounts_staff VALUES (110, ‘JOHN' , 10); --This is valid
INSERT INTO accounts_staff VALUES (111, ‘MIKE' , 30); --This is invalid. This will be rolled back and
returns an error
FORCE VIEWS
A view can be created even if the defining query of the view is invalid. For instance, if the view refers to a non
-existent table or an invalid column in an existing table. This can be done using the FORCE option
CREATE FORCE VIEW AS ...;
Create and Manage Views in Oracle
Replacing/ Altering Views
There are two options to change the definition of a view
- Drop and re-create: All grants and privileges are lost with this option.
- Use CREATE OR REPLACE in the view definition. This preserves the grants and privileges.
Ex:
CREATE OR REPLACE VIEW accounts_staff AS
SELECT Emp_no, Emp_name, Dept_no
FROM Emp
WHERE Dept_no = 30
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Create and Manage Views in Oracle
To drop a view:
DROP VIEW accounts_staff;
Restrictions on DML operations for views:
If you don’t want any DML operations to be performed on views, create them WITH READ ONLY option. Then
no DML operations are allowed on views.
Create and Manage Views in Oracle
• Join View: View that involves a join operation.
• Key Preserved Table: A table is key preserved if every key of the table can also be a key of the result of the join.
CREATE VIEW emp_dept
AS
SELECT e.emp_no,
e.emp_name,
e.sal,
e.dept_no,
d.dept_name,
d.loc
FROM emp e, dept d
WHERE e.dept_no = d.dept_no;
Emp Table – key preserved
Dept Table – not key preserved
Create and Manage Views in Oracle
UPDATE
UPDATE Emp_dept_view
SET Sal = Sal * 1.10
WHERE Deptno = 10;
Allowed
UPDATE Emp_dept_view
SET Loc = 'AUSTIN'
WHERE emp_name = 'JOHN';
Disallowed
Create and Manage Views in Oracle
DELETE
DELETE FROM Emp_dept_view
WHERE emp_name = 'SMITH'
Allowed
CREATE VIEW emp_emp AS
SELECT e1.emp_name, e2.emp_no, e1.dept_no
FROM Emp e1, Emp e2
WHERE e1.emp_no = e2.emp_no;
Disallowed
CREATE VIEW Emp_mgr AS
SELECT e1.emp_name, e2.emp_name mgr_name
FROM Emp e1, Emp e2
WHERE e1.mgr = e2.emp_no
WITH CHECK OPTION;
Disallowed
Create and Manage Views in Oracle
INSERT
INSERT INTO Emp_dept (emp_name, emp_no,
dept_no) VALUES ('TOM', 119, 40)
Allowed
INSERT INTO Emp_dept (emp_name, emp_no,
dept_no) VALUES ('TOM', 110, 77)
Disallowed
The statement below can help identify which columns in a view are updateable
SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'EMP_DEPT_VIEW'
References
https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch11.
htm
https://docs.oracle.com/cd/E10405_01/appdev.120/e10379/ss_oracle
_compared.htm
http://www.oracle-dbaonline.com/sql/create_and_manage_views.htm