Oracle PL/SQL Fundamentals
16 min read
Table of contents
- What is PL/SQL ?
- View in Oracle PL/SQL
- Aggregate Functions :
- DATA MANIPULATION LANGUAGE
- DATA DEFINITION LANGUAGE
- How to call an Oracle procedure/Function within a package?
- Cursors in PLSQL :
- Implicit Cursor in Oracle PL/SQL
- Explicit Cursor in Oracle PL/SQL
- Exceptions in Oracle PL/SQL
- Raising Exception :
- USER-DEFINED Exception :
- Primary and Foreign Key Constraints
- What is an Anonymous Block?
- PL/SQL Commonly Used Datatypes :
- Overview of PLSQL Concepts
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 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 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 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 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 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.
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
- 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]
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;
- 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
- 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 is a schema object that contains definations for a group of related functionalities.
Package includes -variables,
- procedures and
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:
How to call an Oracle procedure/Function within a package?
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
Every explicit cursor and cursor variable has four attributes:
- %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
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.
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.
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
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