Oracle PL/SQL Fundamentals

What is PL/SQL ?

PL/SQL is Oracle's procedural language extension to SQL
Oracle database allows us to create tables, objects, execute all the familiar SQL statements, like INSERT, UPDATE, and DELETE. -also offers PL/SQL a very powerful, and flexible procedural language, which gives us the ability to tie these SQL statements together.
It gives us the ability to write procedural logic, to create loops, perform conditional executions, and provides us with a whole lot of other powerful constructs, which gives us a lot of fine grain control in executing these statements.
It helps us define the business logic more effectively, and allows us to create reusable units of work, which I think is one of the greatest benefits of using it.
Oracle database is one of Word's largest relational databases used by thousands of companies across the globe. -PL/SQLused extensively in database applications, and other Oracle tools like Oracle Forms Oracle Reports Oracle Application
it can help your application become more modular. It is a feature rich language with procedural capabilities, and error handling mechanisms, giving you a fine grain control of your business logic execution.
It also supports object oriented programming.

View in Oracle PL/SQL

An Oracle VIEW, in essence, is a virtual table that does not physically exist. Rather, it is created by a query joining one or more tables. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

CREATE VIEW employee_view
AS
SELECT * FROM employee
WHERE salary>20000';

Complex View :

Complex views can be constructed on more than one base table. In particular, complex views can contain: join conditions,a group by clause,a order by clause

CREATE VIEW sample_complex_view AS
  SELECT emp.empno, emp.ename, emp.job, emp.deptno, dept.dname, dept.loc
  FROM emp, dept
 WHERE emp.deptno = dept.deptno;

Aggregate Functions :

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. e.g. group function “SUM” will help you find the total marks, even if the database stores only individual subject marks.

Group functions supported are SUM,AVG,Count,MIN,MAX

Group by Clause :

retrieve the summarized result set from the database using the SQL query that involves grouping of column values done by considering more than one column as grouping criteria.

SELECT employee_id, AVG(salary)
FROM employee
GROUP BY employee_id;

Having Clause :

The HAVING clause sets conditions on the GROUP BY clause similar to the way that WHERE interacts with SELECT. acts as filter to group. we cannot use WHERE in Group By Clause therefore we use HAVING instead. The HAVING clause comes after the GROUP BY clause and before the ORDER BY clause.

SELECT employee_id, Count(*)
FROM employee
GROUP BY employee_id
HAVING Count(*)> 5;

DATA MANIPULATION LANGUAGE

DELETE : delete a single record or multiple records from a table in Oracle (WHERE can)

DELETE FROM customers
WHERE last_name = 'Smith';

INSERT : used to insert a single record or multiple records into a table in Oracle.

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(5000, 'Apple');

UPDATE : update existing records in a table in an Oracle database

UPDATE customers
SET last_name = 'Anderson'
WHERE customer_id = 5000;

MERGE : allows you to perform multiple INSERT, UPDATE, and DELETE operations in a single statement

MERGE INTO employees e
    USING hr_records h
    ON (e.id = h.emp_id)
  WHEN MATCHED THEN
    UPDATE SET e.address = h.address
  WHEN NOT MATCHED THEN
    INSERT (id, address)
    VALUES (h.emp_id, h.address);

DATA DEFINITION LANGUAGE

DROP : allows you to remove or delete a table from the Oracle database.

DROP TABLE customers;

TRUNCATE : used to remove all records from a table in Oracle. It performs the same function as a DELETE statement without a WHERE clause.

TRUNCATE TABLE customers;

CREATE : to create and define a table

CREATE TABLE departments
( department_id number(10) NOT NULL,
  department_name varchar2(50) NOT NULL,
  CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

ALTER : to add, modify, or drop/delete columns in a table. The Oracle ALTER TABLE statement is also used to rename a table.

ALTER TABLE table_name
  ADD column_name column_definition;
--------------------------------------
ALTER TABLE table_name
  RENAME TO new_table_name;
--------------------------------------
ALTER TABLE table_name
  DROP COLUMN column_name;
--------------------------------------
ALTER TABLE customers
  MODIFY (customer_name varchar2(100) NOT NULL,
          city varchar2(75) DEFAULT 'Seattle' NOT NULL);
--------------------------------------

Joins:

JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.

INNER JOIN : Oracle INNER JOINS return all rows from multiple tables where the join condition is met

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

LEFT OUTER JOIN : Retuns all rows from the Left table and matching rows from the Right table.the result will NULL from right side, if there is no match

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

RIGHT OUTER JOIN : Retuns all rows from the right table and matching rows from the left table. the result will NULL from left side, if there is no match

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

FULL OUTER JOIN : A full join returns all the rows from the joined table, whether they are matched or not. you can say full join combines the functions of LEFT and RIGHT JOIN Full join is type of outer join thats why it also refered as Full Outer join

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

CROSS JOIN: Specify all rows of 1st table with all rows of 2nd table.

SELECT * FROM CITIES CROSS JOIN FLIGHTS

SELF JOIN : A self join is a join in which a table is joined with itself. .two rows from the same table combine to form a resultant row .

SELECT  a.name, b.age, a.SALARY  
FROM CUSTOMERS a, CUSTOMERS b  
WHERE a.SALARY < b.SALARY;

NATURAL JOIN: compare all common columns in both table and return all match record

SELECT * FROM COUNTRIES NATURAL JOIN CITIES

DDL,DML,DCL,TCL

DDL :

DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.

CREATE – to create database and its objects like (table, index, views, store procedure, function and triggers).
ALTER – alters the structure of the existing database.
DROP – delete objects from the database.
TRUNCATE – remove all records from a table; also, all spaces allocated for the records are removed.
COMMENT – add comments to the data dictionary.
RENAME – rename an object.

DML

DML is short name of Data Manipulation Language which deals with data manipulation, and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE etc, and it is used to store, modify, retrieve, delete and update data in database.

SELECT – retrieve data from one or more tables.
INSERT – insert data into a table.
UPDATE – updates existing data within a table.
DELETE – delete all records from a table.
MERGE – UPSERT operation (insert or update)
CALL – call a PL/SQL or Java subprogram.
EXPLAIN PLAN – interpretation of the data access path.
LOCK TABLE – concurrency control.

DCL

DCL is short name of Data Control Language which includes commands such as GRANT, and mostly concerned with rights, permissions and other controls of the database system.

GRANT – allow users access privileges to database.
REVOKE – withdraw users access privileges given by using the GRANT command.

TCL

TCL is short name of Transaction Control Language which deals with transaction within a database.

COMMIT – commits a transaction.
ROLLBACK – rollback a transaction in case of any error occurs.
SAVEPOINT – a point inside a transaction that allows rollback state to what it was at the time of the savepoint.
SET TRANSACTION – specify characteristics for the transaction.

Triggers:

AFTER DELETE OR INSERT OR UPDATE ON employees ...

-- trigger Fired  When 

DELETE FROM employees WHERE ...;
INSERT INTO employees VALUES ( ... );
INSERT INTO employees SELECT ... FROM ... ;
UPDATE employees SET ... ;

Triggers are Stored Programs which are automatically execute or fired Before OR After occurence of some events. so what are the Events occured in Triggers triggers are the actions that you want to take on a database or table based on criteria.

1.Database ManipuLation statements D M L

DELETE, [what to do when existing data deleted]
INSERT [e.g. what to do everytime when data inserted or new row is added]
UPDATE [what to do when existing data updated]

2.Database Defination Statement D D L

CREATE
ALTER
DROP

3.Database Operation

LOGON or LOGFF [eg. what to do in the event when someone logs on unsuccessfully]
STARTUP or SHUTDOWN [e.g. what to do before Database is shutdown]
SERVERERROR

CREATE TRIGGER trigger_emp
AFTER UPDATE OF emp_salary ON employee
FOR EACH ROW 
BEGIN
INSERT INTO new_employee(id,salary,message) 
VALUES(:old.emp_id, :new.emp_salary, 'Record is updated');
END;


-- when it will trigger 

UPDATE employee SET emp_salary=10000 where emp_id=1;
-- result 1 row updated.
select * from new_employee;

Procedures

procedure is PLSQL Block which perform ONE or MORE Specific tasks.
Procedure is mainly created to perform one or more DML Operation over databse
It is not mandatory to return the value

Functions

The PL/SQL Function is very similar to PL/SQL Procedure, but a function must always return a value
Use RETURN to return the value
Return datatype is mandatory at the time of creation
Used mainly to perform some calculation

Package

Package is a schema object that contains definations for a group of related functionalities.

Package includes -variables,

constants,
cursors,
exception,
procedures and
functions etc.
Package is compiled and stored as a database object that can be used later.

why to use Package

you can easily manage
can hide implementation details.
improve application performance e.g. when package load in oracle it stored in temporary memory.
minimize unnecessory recomiple code.

components of package
package Specification : Package specification consists of as declaration of all the public variables, cursors, objects, procedures, functions and exception

Syntax of package Specification :

OR replace: optional ( modify existing package)

package Body : It Consist of the definition of all the elements that are present in the package specification.

Syntax of Package body:

Package program

Package Specification:

Package Body:

How to call an Oracle procedure/Function within a package?

1.insert_department procedure

2.delete_department Procedure

3.Add_number Function:

Cursors in PLSQL :

When an SQL statement is processed, Oracle creates a memory area known as context area.
A cursor is a pointer to this context area.
It contains all information needed for processing the statement.
In PL/SQL, the context area is controlled by Cursor.
A cursor contains information on a select statement and the rows of data accessed by it.
Cursor use for processing Row-by-Row
Cursor is a pointer to private SQL area that stores information about processing a Specific SELECT or DML statement.

There are two types of cursors

Implicit cursor
Explicit cursor

Cursor Attributes:

Every explicit cursor and cursor variable has four attributes:

%FOUND,
%ISOPEN
%NOTFOUND, and
%ROWCOUNT. When appended to the cursor or cursor variable, these attributes return useful information about the execution of a data manipulation statement

The implicit cursor SQL has additional attributes

%BULK_ROWCOUNT and
%BULK_EXCEPTIONS

Implicit Cursor in Oracle PL/SQL

The implicit cursors are automatically generated by Oracle while an SQL statement is executed
every time we run any DML statement Implicit cursor do his activity.
this cursor is constructed and managed by an Implicit Cursor.
These are created by default to process the statements when DML statements like INSERT, UPDATE, DELETE etc. are executed.

Explicit Cursor in Oracle PL/SQL

The Explicit cursors are defined by the programmers to gain more control over the context area
These cursors should be defined in the declaration section of the PL/SQL block
It is created on a SELECT statement which returns more than one row.

Steps in Explicit Cursor

Declare the cursor to initialize in the memory
Open the cursor to allocate memory.
Fetch the cursor to retrieve data.
Close the cursor to release allocated memory.

1) Declare the cursor: It defines the cursor with a name and the associated SELECT statement.

CURSOR name IS
SELECT statement;

2) Open the cursor: It is used to allocate memory for the cursor and make it easy to fetch the rows returned by the SQL statements into it.

OPEN cursor_name;

3) Fetch the cursor: It is used to access one row at a time. You can fetch rows from the above—opened cursor as follows:

FETCH cursor_name INTO variable_list;

4) Close the cursor: It is used to release the allocated memory. The following syntax is used to close the above-opened cursors.

Close cursor_name;

Exceptions in Oracle PL/SQL

An error occurs during the program execution is called Exception in PL/SQL. There are two types of exceptions:

Pre-defined Exceptions
User-defined Exceptions

Pre-defined Exceptions

There are many pre-defined exception in PL/SQL which are executed when an database rule is violated by the program. With PL/SQL, a mechanism called exception handling lets you "bulletproof" your program so that it can continue operating in the presence of errors.

most commonly used Exceptions are

NO_DATA_FOUND : It is raised when a select into statement returns no rows.
PROGRAM_ERROR : It is raised when PL/SQL has an internal problem.
STORAGE_ERROR : It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS : It is raised when a SELECT INTO statement returns more than one row.
ZERO_DIVIDE: It is raised when an attempt is made to divide a number by zero.

NO_DATA_FOUND Exception Example:

Raising Exception :

All the predefined exceptions are raised implicitly whenever the error occurs.
But the user-defined exceptions needs to be raised explicitly.
This can be achieved using the keyword ‘RAISE’.
This can be used in any of the ways mentioned below.

Syntax for raising an exception :

DECLARE
 exception_name EXCEPTION;
BEGIN
 IF condition THEN
     RAISE exception_name ;
 END IF;
EXCEPTION
 WHEN exception_name THEN 
 statement; 
END;

USER-DEFINED Exception :

PL/SQL facilitates their users to define their own exceptions according to the need of the program.
A user-defined exception can be raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

DECLARE
<exception_name> EXCEPTION; 
BEGIN
<Execution block>
EXCEPTION
WHEN <exception_name> THEN 
<Handler>
END;

Primary and Foreign Key Constraints

Primary Key	Foreign Key
A primary key is a column or a set of columns that uniquely identify a row in a table.	A foreign key is a field (or collection of fields) in a table whose value is required to match the value of the primary key for a second table.
A primary key should be short, stable and simple.	Usually a foreign key is in a table that is different from the table whose primary key it is required to match. A table can have multiple foreign keys.
The primary key cannot accept null values.	Foreign key can accept.
We can have only one primary key in a table.	We can have more than one foreign key in a table.

There are Three Type of Blocks in PL/SQL

Anonymous Blocks
Procedure(Named) : may or may not return value ACTION Hello task
Function(Named): not return written value CALCULATION Repeat multiple time.

What is an Anonymous Block?

-Its is a block without name.

Basic unit of PLSQL
It allows us to combine together a bunch of SQL statements, and run them as a single block of code.
Think of the anonymous block is like the piece of paper with custom instructions. Unlike the prebuilt modes, which we can call by name, and anyone can understand, and get to them, these are known only to me, or to whomsoever I pass on to.
Having a good understanding of anonymous block will provide us a good foundation to understand the other program units, like the procedures and functions.

Structure of Anonymous Blocks

The anonymous block consists of three sections the declaration section, that starts with the DECLARE keyword, and ends at the BEGIN keyword. you declare all the variables to be used within your block. You can declare variables, which can be based on simple datatypes, variables based on advanced data types, cursors, etc.

In Java, or C# background, we used to put the datatype first, followed by the name of the variable. But in PL/SQL, the order is reversed.

PL/SQL all lines have to end with a semicolon character. This is with the exception of the DECLARE, BEGIN, and EXCEPTION keywords. You still need to put a semicolon after the END keyword to mark the end of the anonymous block.

Next we have the execution section which starts with the BEGIN keyword.This is a main body of the anonymous block, and consists of executable lines of code like SQL statements, numerical calculations, etc.
that the assignment operator is a colon equal to sign(:=) which is kind of unusual. In Java or C#, it is the equals operator. In PL/SQL, the equals operator is a comparison operator. Most of the other operators, like greater than, less than, etc., are the same with other languages
The last section is the exception, or error handling section. Here we can handle different kinds of errors. WHEN OTHERS is a catchall exception handler, which traps any exception if there is no specific exception handler defined for that error in the exception block. The anonymous block ends with the END keyword. Of these sections, only the execution section is required. The declaration and exception sections are optional.

So we can just have a block which starts with a BEGIN, and ends with an END keyword.

Comments in Anonymous Blocks

Good documentation in the code is always very helpful for the maintenance of the code, and comments help with that.

There are two kinds of comments you can put in PL/SQL.

single line comment. These can go on the line by their own, or can also be put in line with code towards the end that start with two dashes.
multi-line comments: which start with a / and end with a / . it is also very helpful to have a proper indentation of your code to improve readability.

PL/SQL is not case sensitive except for the character literals, which we will talk about later. the BEGIN keyword in upper, lower or mixed case, but I like to always have DECLARE, BEGIN, EXCEPTION, and END keywords in uppercase, variable declarations and executable statements in lowercase. It is up to you, and your team, to decide on the naming conventions, and cases, and spacing your code.

PL/SQL Commonly Used Datatypes :

1. scalar type : No internal components.

2. Composite type : Internal components that can be manipulated individually, such as the elements of an array, record, or table.

3. Reference type : Holds values, called pointers, that designate other program items. These types include REF CURSORS and REFs to object types.

4. Other Datatypes: The LOB (large object) datatypes BFILE, BLOB, CLOB, and NCLOB let you store blocks of unstructured data, such as text, graphic images, video clips, and sound waveforms. LOBs allow efficient, random, piece-wise access to the data. BLOB, CLOB, and NCLOB are from 8 to 128 terabytes in size. The size of a BFILE is system dependent, but cannot exceed four gigabytes (4GB - 1 bytes).

Overview of PLSQL Concepts

![plsql fundamentals.png]