Oracle SQL Types of Database Languages Used to
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_name 1 data_type(size) , column_name 2 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_name 1 = ‘x’ where column_name 1 = ‘y’; • TCL – Transaction Control Language; commands that manage changes made by DML statements Ex: COMMIT, ROLLBACK • Ex: DELETE from table_name WHERE column_name 1 = 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 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 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 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 B 2 B/ A 2 A 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 A transaction can be defined as a encountered. When a series of SQL group of operations or tasks that queries that modify records have to should be treated as a single unit. For be run as a group, oracle SQL makes instance, a collection of SQL queries If you use explicit BEGIN TRAN / changes in memory only until a modifying records that all must be COMMIT TRAN commands, you can COMMIT command is issued. After updated at the same time, where (for group these together as an explicit the commit, the next command instance) a failure to update any transaction—a set of statements that issued is treated as a new transaction single records among the set should must fail or succeed together. and the process begins again. result in none of the records being updated.
• 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, ‘ 1 B’, 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 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) FROM DUAL RPAD(expr 1, n, expr 2) Returns expr 1, right padded to length n characters with expr 2, 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 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 FROM Emp_dept_view WHERE emp_name = 'SMITH' Allowed CREATE VIEW emp_emp AS SELECT e 1. emp_name, e 2. emp_no, e 1. dept_no FROM Emp e 1, Emp e 2 WHERE e 1. emp_no = e 2. emp_no; Disallowed CREATE VIEW Emp_mgr AS SELECT e 1. emp_name, e 2. emp_name mgr_name FROM Emp e 1, Emp e 2 WHERE e 1. mgr = e 2. emp_no WITH CHECK OPTION; Disallowed
Create and Manage Views in Oracle 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/A 57673_01/DOC/server/doc/SCN 73/ch 11. htm https: //docs. oracle. com/cd/E 10405_01/appdev. 120/e 10379/ss_oracle _compared. htm http: //www. oracle-dbaonline. com/sql/create_and_manage_views. htm
- Slides: 27