Introduction
PL/SQL is procedural language.
Syntax of PL/SQl Block:
The Declaration section (optional).
The Execution section (mandatory).
The Exception Handling (or Error) section (optional).
Note-: Every statement in the above three sections must end with a semicolon ; . PL/SQL blocks can be nested within other PL/SQL blocks. Comments can be used to document code.
Sample PL/SQL Block
DECLARE
Variable declaration
BEGIN
Program Execution
EXCEPTION
Exception handling
END;
PL/SQL Placeholders
Placeholders are temporary storage area. PL/SQL Placeholders can be any of Variables, Constants and Records.
Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block.
Depending on the kind of data you want to store, you can define placeholders with a name and a datatype. Few of the datatypes used to define placeholders are as given below: Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile
General Syntax to declare a variable is:
variable_name datatype [NOT NULL := value ];
Example
DECLARE
salary number(4);
dept varchar2(10) NOT NULL := “HR Dept”;
Variable Declaration
1) We can directly assign values to variables.
The General Syntax is:
variable_name:= value;
2) We can assign values to variables directly from the database columns by using a SELECT.. INTO statement. The General Syntax is:
SELECT column_name
INTO variable_name
FROM table_name
[WHERE condition];
Example
DECLARE var_salary number(6);
var_emp_id number(6) := 1116;
BEGIN
SELECT salary INTO var_salary FROM employee WHERE emp_id = var_emp_id;
END;
Constant
General Syntax to declare a constant is:
constant_name CONSTANT datatype := VALUE;
Note:-You must assign a value to a constant at the time you declare it. If you do not assign a value to a constant while declaring it and try to assign a value in the execution section, you will get a error. If you execute the below Pl/SQL block you will get error.
Example
DECLARE salary_increase CONSTANT number (3) := 10;
DECLARE salary_increase CONSTANT number(3);
BEGIN
salary_increase := 100; --Error will be shown
dbms_output.put_line (salary_increase);
END;
Records
Records are composite datatypes, which means it is a combination of different scalar datatypes like char, varchar, number etc. Each scalar data types in the record holds a value. A record can be visualized as a row of data. It can contain all the contents of a row.
The General Syntax to define a composite datatype is:
TYPE record_type_name IS RECORD (first_col_name column_datatype, second_col_name column_datatype, ...);
Example
DECLARE
TYPE employee_type IS RECORD
(employee_id number(5), employee_first_name varchar2(25), employee_last_name employee.last_name%type, employee_dept employee.dept%type);
Syntax’s to declare Record
Following syntax is used to declare a record based on a user-defined type:
record_name record_type_name;
2. If all the fields of a record are based on the columns of a table, we can declare the record as follows:
record_name table_name%ROWTYPE;
How to put Value
The General Syntax to assign a value to a column within a record directly is:
record_name.col_name := value;
If you used %ROWTYPE to declare a record, you can assign values as shown:
record_name.column_name := value;
To assign values to each field of a record from the database table
SELECT col1, col2 INTO record_name.col_name1, record_name.col_name2 FROM table_name [WHERE clause];
To assign a value to all fields in the record from a database table.
SELECT * INTO record_name FROM table_name [WHERE clause];
Conditional Statements in PL/SQL
IF condition 1
THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF
IF condition 1
THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF;
IF condition1
THEN
ELSE
IF condition2 THEN
statement1;
END IF;
ELSIF condition3 THEN
statement2;
END IF;
Iterative Statements in PL/SQL
There are three types of loops in PL/SQL:
• Simple Loop
LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;
• While Loop
WHILE <condition>
LOOP statements;
END LOOP;
• For Loop
FOR counter IN val1..val2
LOOP statements;
END LOOP;
val1 - Start integer value.
val2 - End integer value.
Cursors
A cursor is a temporary work area created in the system memory when a SQL statement is executed.
This temporary work area is used to store the data retrieved from the database, and manipulate this data.
A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.
Types of Cursors
1.Implicit cursors:-These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.
2.Explicit cursors:-They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
Implicit Cursors
When you execute DML statements like DELETE, INSERT, UPDATE and SELECT statements, implicit statements are created to process these statements.
Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.
Explicit Cursors
Explicit Cursors-:An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row
1) Declaring a Cursor in the Declaration Section:
DECLARE CURSOR emp_cur IS SELECT * FROM emp_tbl WHERE salary > 5000;
2) Accessing the records in the cursor:
These are the three steps in accessing the cursor.
Open the cursor.
Fetch the records in the cursor one at a time.
Close the cursor.
Stepwise
General Syntax to open a cursor is:
OPEN cursor_name;
General Syntax to fetch records from a cursor is:
FETCH cursor_name INTO record_name;
OR
FETCH cursor_name INTO variable_list;
General Syntax to close a cursor is:
CLOSE cursor_name;
Procedures
A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task.
A procedure has a header and a body. The header consists of the name of the procedure and the parameters or variables passed to the procedure. The body consists of declaration section, execution section and exception section similar to a general PL/SQL Block.
A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.
Note-: IS - marks the beginning of the body of the procedure and is similar to DECLARE in anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.
PL/SQL Functions
A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.
General Syntax to create a function is
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;
1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2) The execution and exception section both should return a value which is of the datatype defined in the header section.
Parameters in Procedure and Functions
In PL/SQL, we can pass parameters to procedures and functions in three ways.
1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.
NOTE: If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter.
General syntax to pass a IN parameter is
CREATE [OR REPLACE] PROCEDURE procedure_name (
param_name1 IN datatype, param_name12 IN datatype ... )
General syntax to create an OUT parameter is
CREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)
General syntax to create an IN OUT parameter is
CREATE [OR REPLACE] PROCEDURE proc3 (param_name IN OUT datatype)
Example of IN OUT parameter
Exception Handling
General Syntax:
DELCARE
Declaration section
BEGIN
DECLARE
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
EXCEPTION
Exception section
END;
Types of Exception
There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions
Example of Unnamed Exception
Example of User Defined Exception
DECLARE
huge_quantity EXCEPTION;
CURSOR product_quantity is
SELECT p.product_name as name, sum(o.total_units) as units
FROM order_tems o, product p
WHERE o.product_id = p.product_id;
quantity order_tems.total_units%type;
up_limit CONSTANT order_tems.total_units%type := 20;
message VARCHAR2(50);
BEGIN
FOR product_rec in product_quantity LOOP
quantity := product_rec.units;
IF quantity > up_limit THEN
message := 'The number of units of product ' || product_rec.name ||
' is more than 20. Special discounts should be provided.
Rest of the records are skipped. '
RAISE huge_quantity;
ELSIF quantity < up_limit THEN
v_message:= 'The number of unit is below the discount limit.';
END IF;
dbms_output.put_line (message);
END LOOP;
EXCEPTION
WHEN huge_quantity THEN
dbms_output.put_line (message);
END;
/
TRIGGER
A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.
Syntax for Creating a Trigger:
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;
Types of PL/SQL Triggers
There are two types of triggers based on the which level it is triggered.
1) Row level trigger - An event is triggered for each row upated, inserted or deleted.
2) Statement level trigger - An event is triggered for each sql statement executed.
1) BEFORE UPDATE, Statement Level: This trigger will insert a record into the table 'product_check' before a sql update statement is executed, at the statement level.
CREATE or REPLACE TRIGGER Before_Update_Stat_product
BEFORE
UPDATE ON product
Begin
INSERT INTO product_check
Values('Before update, statement level',sysdate);
END;
/
2) BEFORE UPDATE, Row Level: This trigger will insert a record into the table 'product_check' before each row is updated.
CREATE or REPLACE TRIGGER Before_Upddate_Row_product
BEFORE
UPDATE ON product
FOR EACH ROW
BEGIN
INSERT INTO product_check
Values('Before update row level',sysdate);
END;
View
A view is a predefined query on one or more tables.
Retrieving information from a view is done in the same manner as retrieving from a table.
With some views you can also perform DML operations (delete, insert, update) on the base tables.
Views don't store data, they only access rows in the base tables.
user_tables, user_sequences, and user_indexes are all views.
View Only allows a user to retrieve data.
view can hide the underlying base tables.
By writing complex queries as a view, we can hide complexity from an end user.
View only allows a user to access certain rows in the base tables.
Example-:
CREATE VIEW my_view AS SELECT * FROM employee WHERE id < 5;
Syntax-:
CREATE [OR REPLACE] VIEW [{FORCE | NOFORCE}] VIEW view_name
[(alias_name[, alias_name...])] AS subquery
[WITH {CHECK OPTION | READ ONLY} CONSTRAINT constraint_name];
Index
An index for a database table is similar in concept to a book index.
When a row is added to the table, additional time is required to update the index for the new row.
Oracle database automatically creates an index for the primary key of a table and for columns included in a unique constraint.
You create an index using CREATE INDEX, which has the following simplified syntax:
CREATE [UNIQUE] INDEX index_name ON table_name(column_name[, column_name...]) TABLESPACE table_space;
Diagram Dataflow
