Oracle PL/SQL

1. Module 1 – 20 Questions
2. Module 2 – 30 Questions (include the topics from Module 1)
3. Module 3 – 40 Questions (include the topics from Module 1,2)
4. Hands -on Assessment
5. APEX certification
6. My Competency

Syllabus :SQL, PL/SQL, OCI, Case study, APEX

Day 1
PL/SQL
❖ Procedural/Programming Language with the extension of SQL
❖ It contains SQL & Non SQL queries as a PL/SQL block
❖ All SQL queries in PL/SQL block can be executed by Database engine. Non-SQL statements
will be executed by PL/SQL engine
❖ Non - SQL statement include conditional, looping, calculations etc.,
❖ There is a connection between the 2 consecutive statements in the PL/SQL block. If a block
contains 100 lines of code, there is an error in 10th line then the entire block will not be
executed
❖ DCL statements are not allowed in the PL/SQL
❖ DDL statements cannot be used directly in PL/SQL blocks.

Pros:

➢ Network traffic will be less if we keep the multiple SQL/Non SQL statements inside the
PL/SQL
➢ Provides security
➢ Nesting of a block is allowed
➢ It support Object oriented Programming language like Polymorphism (Function
Overloading)
➢ Reduces the compilation time
➢ It can be called from any front end application like Java, .net, oracle forms, oracle
reports

Building blocks/Components of the PL/SQL:

1. Declare (optional)
2. Begin (Mandatory)
3. Exception (Optional)
4. End (Mandatory)
Types:

1. Unnamed/Anonymous block
2. Named block
a. Stored Sub programs -> Procedures , Functions
b. Packages -> collection of procedures, functions, variables
c. Triggers

Named vs UnNamed block:

❖ Named block can be stored in the database, but unnamed block cannot be stored
❖ Named blocks can be compiled once, executed many times. But Unnamed block compile and
execute every time
❖ Named block can be called from any front end application, but we cannot call the unnamed
block from any front end application

UnNamed block:

1. To display Welcome using the PL/SQL block

DBMS_OUTPUT -> Predefined Package

PUT_LINE -> predefined procedure inside the DBMS_OUTPUT package

PUT_LINE is something equivalent to printf statement in C program

printf(“WELCOME\n”);

2. To print multiple statements:

a. Multiple PUT_LINE statements
 b. Using Concatenation Operator

|| -> concatenation operator

- - => used for single line comment
/* */ => multi line comments
Variables:
❖ Variables can be declared in the DECLARE section
❖ They are said to be local variables.
❖ If we are not assigning any value to the local variable, it takes NULL by default.
:= Is used to assign the value to the variable.
Nesting of a block:

Outer block variable is used in the inner block:

Try to display the value of inner block in outer block—> error

BOTH inner and outer block variable has the same name:
To display the outer block variable inside the inner block
BOOLEAN DATA TYPE: (PL/SQL DATA TYPE)

The above block will give compilation error, because boolean values cannot be printed. It can be
used only as a condition
Conditional Statements (IF , ELSE , ELSIF)

Conditional construct:

IF condition:

Nested IF condition:
OR

IF - ELSIF - ELSE
LOOPS:

1. Simple loop (infinite loop)

2. While loop
3. FOR loop

EXIT is used to come out of the loop, which is mandatory for simple loop.
CONTINUE is used to skip the checked value and continue the remaining ones.

1. To display hello 5 times

Simple Loop:
WHILE LOOP:

OR
FOR LOOP:

❖ Its a fastest loop

❖ No need to declare & initialise the loop variable
❖ Automatically increased or decreased by one
❖ No need to check the condition for loop

-> Autoincremented by 1 in the above example

For autodecrementation by 1,
Note: Don't declare a local variable as that of a loop variable. We cannot modify the loop
variable.

Day 2
Substitution variable:
SELECT STATEMENT IN PL/SQL BLOCK:

1. TO DISPLAY THE FIRST_NAME & SALARY OF A PARTICULAR EMPLOYEE

2. For invalid condition, we will exception

 3. When we try to fetch more than one row, leads to exception

4. If datatype is wrong or size mismatch for a variable leads to an exception

To resolve the above error,

%TYPE => will automatically assign the datatype & size of a column to the given variable
 5. To display the entire row of a particular employee.

There are 11 columns in [Link] table. To display the entire row, we have to create 11
variables for each column. To avoid this , we can use %ROWTYPE

BIND VARIABLE/HOST VARIABLE:

➢ Variable created in the SQL command prompt environment
➢ valid till the session ends
➢ we cannot use the bind variable inside the named block.
➢ By default BIND variable has NULL
 DATA TYPES OF PL/SQL

1. SCALAR/PRIMITIVE => all SQL datatypes , BOOLEAN, PLS_INTEGER ,

BINARY_INTEGER
2. COMPOSITE DATATYPE
a. %TYPE,%ROWTYPE -> Cursor attributes
b. RECORDS
c. COLLECTIONS
Cursor -> temporary memory area opened for DML,DRL(SELECT) operations

RECORD:

❖ Set of columns/variables can create a record

❖ Its Composite data type of PL/SQL
❖ Its similar to structure in C program
❖ We can reuse it later
❖ Memory is well organised in Records
 Structure example in C:

Struct emp
{
Int empid;
Char name[30];
Int salary;
};

Struct emp e1;

[Link]=101;
strcpy([Link],’Parvathy’);
[Link]=3400;

Records in PL/SQL:

Collections:

1. Associative Array/ Index by table/PLSQL table

2. Varray
3. Nested table

Associative array:
❖ Its like an array concept in programming
❖ Collection of elements under one name
❖ Its unbounded array
❖ Data in associative array will be SPARSE is nature -> data may be continuously or may not be
stored
❖ Created, used only inside the PL/SQL block
❖ We need an index to access the values. The indexes may or may not be continuous
 a. Create an associative array which has only the country list

DECLARE
TYPE MY_ARR IS TABLE OF VARCHAR2(40) INDEX BY PLS_INTEGER;
MY_OBJ MY_ARR;
X PLS_INTEGER;
BEGIN
MY_OBJ(-77):='INDIA';
MY_OBJ(0):='US';
MY_OBJ(9):='JAPAN';
MY_OBJ(2):='CHINA';
MY_OBJ(-23):='RUSSIA';
-- TO PRINT SPECIFIC VALUE
DBMS_OUTPUT.PUT_LINE(MY_OBJ(2));
-- TO PRINT THE TOTAL NO OF ELEMENTS
DBMS_OUTPUT.PUT_LINE('TOTAL COUNT ='||MY_OBJ.COUNT);
-- TO PRINT THE FIRST INDEX
DBMS_OUTPUT.PUT_LINE('FIRST INDEX ='||MY_OBJ.FIRST);
-- TO PRINT THE VALUE IN THE FIRST INDEX
DBMS_OUTPUT.PUT_LINE('FIRST INDEX VALUE ='||MY_OBJ(MY_OBJ.FIRST));
-- TO PRINT ALL THE ELEMENTS
X:=MY_OBJ.FIRST;
WHILE X IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(MY_OBJ(X));
X:=MY_OBJ.NEXT(X);
END LOOP;
END;
EXISTS FUNCTION, OVERWRITE OF DATA:

DECLARE
TYPE MY_ARR IS TABLE OF VARCHAR2(40) INDEX BY PLS_INTEGER;
MY_OBJ MY_ARR;
X PLS_INTEGER;
BEGIN
MY_OBJ(-77):='INDIA';
MY_OBJ(0):='US';
MY_OBJ(9):='JAPAN';
MY_OBJ(-77):='CHINA';
MY_OBJ(-23):='RUSSIA';
-- TO PRINT SPECIFIC VALUE, CHECK THE INDEX AND THEN PRINT
IF(MY_OBJ.EXISTS(2)) THEN
DBMS_OUTPUT.PUT_LINE(MY_OBJ(2));
ELSE
DBMS_OUTPUT.PUT_LINE('NO SUCH DATA');
END IF;
-- TO PRINT THE TOTAL NO OF ELEMENTS
DBMS_OUTPUT.PUT_LINE('TOTAL COUNT ='||MY_OBJ.COUNT);
-- TO PRINT THE FIRST INDEX
DBMS_OUTPUT.PUT_LINE('FIRST INDEX ='||MY_OBJ.FIRST);
-- TO PRINT THE VALUE IN THE FIRST INDEX
DBMS_OUTPUT.PUT_LINE('FIRST INDEX VALUE ='||MY_OBJ(MY_OBJ.FIRST));
-- TO PRINT ALL THE ELEMENTS
X:=MY_OBJ.FIRST;
WHILE X IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(MY_OBJ(X));
X:=MY_OBJ.NEXT(X);
END LOOP;
END;

b. To have an array for the population of a countries

DECLARE
TYPE MY_ARR IS TABLE OF NUMBER INDEX BY VARCHAR2(40);
MY_OBJ MY_ARR;
X VARCHAR2(40);
BEGIN
MY_OBJ('INDIA'):=65000;
MY_OBJ('US'):=32000;
MY_OBJ('JAPAN'):=28000;
MY_OBJ('CHINA'):=62000;
MY_OBJ('RUSSIA'):=45000;
-- TO PRINT SPECIFIC VALUE, CHECK THE INDEX AND THEN PRINT
IF(MY_OBJ.EXISTS('US')) THEN
 DBMS_OUTPUT.PUT_LINE(MY_OBJ('US'));
ELSE
DBMS_OUTPUT.PUT_LINE('NO SUCH DATA');
END IF;
-- TO PRINT THE TOTAL NO OF ELEMENTS
DBMS_OUTPUT.PUT_LINE('TOTAL COUNT ='||MY_OBJ.COUNT);
-- TO PRINT THE FIRST INDEX
DBMS_OUTPUT.PUT_LINE('FIRST INDEX ='||MY_OBJ.FIRST);
-- TO PRINT THE VALUE IN THE FIRST INDEX
DBMS_OUTPUT.PUT_LINE('FIRST INDEX VALUE ='||MY_OBJ(MY_OBJ.FIRST));
-- TO PRINT ALL THE ELEMENTS
X:=MY_OBJ.FIRST;
WHILE X IS NOT NULL
LOOP
DBMS_OUTPUT.PUT_LINE(MY_OBJ(X));
X:=MY_OBJ.NEXT(X);
END LOOP;
END;

Create an associative array having more than one column & perform the DELETE operation

DECLARE
TYPE MY_REC IS RECORD(NAME VARCHAR2(40),MARKS NUMBER);
TYPE MY_ARR IS TABLE OF MY_REC INDEX BY BINARY_INTEGER;
MY_OBJ MY_ARR;

BEGIN
MY_OBJ(0).NAME:='ARAV';
MY_OBJ(0).MARKS:=77;
MY_OBJ(8).NAME:='HARSHA';
MY_OBJ(8).MARKS:=45;
MY_OBJ(-12).NAME:='JANE';
MY_OBJ(-12).MARKS:=88;
-- TO PRINT THE FIRST INDEX
DBMS_OUTPUT.PUT_LINE('FIRST INDEX ='||MY_OBJ.FIRST);
-- TO PRINT THE VALUE IN THE FIRST INDEX
DBMS_OUTPUT.PUT_LINE('FIRST INDEX VALUE ='||MY_OBJ(MY_OBJ.FIRST).NAME);
-- TO DELETE THE SPECIFIC DATA
MY_OBJ.DELETE(8);
-- TO PRINT THE TOTAL NO OF ELEMENTS
DBMS_OUTPUT.PUT_LINE('TOTAL COUNT ='||MY_OBJ.COUNT);

END;
OR

DECLARE
TYPE MY_REC IS RECORD(NAME VARCHAR2(40),MARKS NUMBER);
TYPE MY_ARR IS TABLE OF MY_REC INDEX BY BINARY_INTEGER;
MY_OBJ MY_ARR;

BEGIN
MY_OBJ(0).NAME:='ARAV';
MY_OBJ(0).MARKS:=77;
MY_OBJ(8).NAME:='HARSHA';
MY_OBJ(8).MARKS:=45;
MY_OBJ(-12).NAME:='JANE';
MY_OBJ(-12).MARKS:=88;
-- TO PRINT THE FIRST INDEX
DBMS_OUTPUT.PUT_LINE('FIRST INDEX ='||MY_OBJ.FIRST);
-- TO PRINT THE VALUE IN THE FIRST INDEX
DBMS_OUTPUT.PUT_LINE('FIRST INDEX VALUE ='||
MY_OBJ(MY_OBJ.FIRST).NAME||' '||MY_OBJ(MY_OBJ.FIRST).MARKS);
-- TO DELETE THE SPECIFIC DATA
MY_OBJ.DELETE(8);
-- TO PRINT THE TOTAL NO OF ELEMENTS
DBMS_OUTPUT.PUT_LINE('TOTAL COUNT ='||MY_OBJ.COUNT);

END;

VARRAY:

❖ Variable array which exactly the array concept in programming language

❖ Fixed in total no of elements
❖ Data is collectively stored ie., they are dense in nature
❖ We can create varray in both inside the PL/SQL block or outside it(in SQL Prompt also )
❖ Index always starts with 1

In other programming language, int a[10] -> we can store 10 integer elements starting with index zero
In varray -> it can have numbers, characters, date, starting with index one

Examples:
 OR

OR
Partially filled varray:

To resolve the above issue,

OR
Inside SQLPlus (Oracle Linux) / outside the PL/SQL block
<Separate Creation of VARRAY Type Variable>

Nested table:

❖ Table within another table

❖ Index starting with 1
❖ It has no limit of data
❖ It can be created inside the PL/SQL block or in SQL command prompt as well
Scenario:

CREATE TABLE COUNTRY_DETAILS_NEW

(COUNTRY_ID NUMBER CONSTRAINT PK_CID PRIMARY KEY, COUNTRY_NAME
VARCHAR2(40));

CREATE TABLE STATE_DETAILS

(STATE_ID NUMBER,STATE_NAME VARCHAR2(40),CID NUMBER,
CONSTRAINT PK_STATE_ID PRIMARY KEY(STATE_ID),
CONSTRAINT FK_COUNTRY_ID FOREIGN KEY(CID) REFERENCES
COUNTRY_DETAILS_NEW(COUNTRY_ID)
ON DELETE SET NULL);

INSERT INTO COUNTRY_DETAILS_NEW VALUES(1,'INDIA');

INSERT INTO COUNTRY_DETAILS_NEW VALUES(2,'US');

INSERT INTO STATE_DETAILS VALUES(1,'MAHARASHTRA',1);

INSERT INTO STATE_DETAILS VALUES(2,'KARNATAKA',1);
INSERT INTO STATE_DETAILS VALUES(3,'TAMILNADU',1);

Another options:

1. First create an object for the foreign key /repeated values

2. Create a nested table for the above one

3. Create the main table which has one of the column as nested table column
 Day 3
Cursor
A temporary memory area which is automatically opened for all DML operations. For Select
statement we have to open the cursor area explicitly.

Types of Cursor :
❖ Implicit
❖ Explicit

Implicit Cursor vs Explicit Cursor

A. Implicit for all 3 DML operations(INSERT, UPDATE, and DELETE). Explicit for Select
Statement.
B. Implicit cursor has their name SQL. Explicit cursor is created by the user and they can have
any name.
C. Implicit cursor area will be opened , fetch all the rows , and perform the DML automatically.
For explicit cursors ,all the functions can be done by the user.

Steps for using the cursor

1. Open the cursor area

2. Fetch the first row from the cursor area
3. Perform the operation/s
4. Repeat the Step 2 to Step 3 until all rows are fetched.
5. Close the cursor area

Cursor Attributes
● %ISOPEN : Returns True if the cursor area is open.
● %FOUND : Returns True if you are able to find the record.
● %NOTFOUND : Returns True if you are NOT able to find the record.
● %ROWCOUNT : Returns the total number of rows affected by DML OR SELECT statement.
Implicit Cursor
Demo :

Explicit Cursor
1. To display first_name of employees who are working in department no 90.

Errored Code : Variable Can’t Hold More than One Values

Solution :
Simple Loop

While Loop

For Loop
Fastest and Required less syntaxes to write as OPEN , FETCH , CLOSE cursors are done
automatically

(NO explicit Cursors NECESSARY for SELECT statements)

Same Result for Every Loop

Parametrized Cursor

Parametrized Cursor in for loop

For Update , Wait & Nowait

To reduce the race condition and bring consistency in the changes to the tuples in the database we
can use For Update clause with wait and Nowait clause.

Wait X : Wait for X seconds to check where any another session doing changes in the same sets of
data, if fails returns an error.

Nowait : Doesn’t wait and tries to update, if fails returns an error.

Scenario:
If we want to update the salary of employees:
a ) for odd no : salary + 1000.
b ) for even no : salary + 2000;
Upon applying COMMIT(CHANGE ACCEPTED) or ROLLBACK(CHANGE REJECTED) to any of
those sessions cause to release the lock and current session’s changes can be reflected.
Up to this , all of the cursors are Static Cursors.

Exception

Runtime errors are called Exceptions in Oracle. Exception can be handled in Exception Block.
If it is not handled, it will lead to abnormal termination , which affects the performance of the
PL/SQL Block.

Types of Exception

➢ Pre-Defined Exception
➢ Non-Predefined Exception
➢ User-Defined Exception

Pre-Defined Exception :

● These exceptions are already defined in the ORACLE SERVER with unique error codes and
error messages.
● It is automatically invoked and handled by the ORACLE SERVER.
● It is already stored in th database.
● SQLCODE - A predefined pseudocolumn which gives the unique error codes.
● SQLERRM - A predefined pseudocolumn which gives the unique error messages.

Example :
To resolve this problem, we can write an exception block

Not necessary to give SQLCODE , SQLERRM everytime. We need at least one statement after
the WHEN clause.

We will get an exception in the above example if we don’t handle the exception correctly.
 Day 4
Non Predefined Exception
1. Its an exception created by the user
2. It can be invoked and handled by the oracle server automatically.
3. We can access the predefined exceptions indirectly through the non-predefined exceptions

More than one handlers for one exception raises Compilation Error. In the above example ,
MY_EXP already became a NO_DATA_FOUND exception , so to give again NO_DATA_FOUND
separately raises the Compilation error.
User Defined Exception

● It’s an exception which can be created , invoked and handled by the user.
● This exception is used for Logical Checking of data.

The above block will work fine if the age is between 20 to 60. If not , the it will throw the error. To
resolve this, we can use two methods
A. IF-ELSE Block

B. User Defined Exception

All Three Exceptions in one program
(Only One Error will be thrown at a time)
Nesting of exceptions

DECLARE
FNAME empl.FIRST_NAME%TYPE;

BEGIN
SELECT FIRST_NAME
INTO FNAME
FROM empl
WHERE EMPLOYEE_ID = 100;
DBMS_OUTPUT.PUT_LINE(FNAME);

DECLARE
SAL [Link]%TYPE;

BEGIN
SELECT SALARY
INTO SAL
FROM empl
WHERE EMPLOYEE_ID = 101;
DBMS_OUTPUT.PUT_LINE(SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('INNER BLOCK EXCEPTION');
END;
DBMS_OUTPUT.PUT_LINE('INNER BLOCK OVER');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('OUTER BLOCK EXCEPTION');
END;

Scenario - 1 :Both the blocks will get executed , since there are no errors. In case we
get an exception in the INNER BLOCK

Scenario - 2 : We got an exception in the OUTER BLOCK , not in the inner block.

Scenario - 3 : We got an exception in the inner block, but it’s not handled in the INNER
BLOCK , it was handled in the OUTER BLOCK.

DECLARE
FNAME empl.FIRST_NAME%TYPE;
BEGIN
SELECT FIRST_NAME
INTO FNAME
FROM empl
WHERE EMPLOYEE_ID = 100;
DBMS_OUTPUT.PUT_LINE(FNAME);

DECLARE
SAL [Link]%TYPE;

BEGIN
SELECT SALARY
INTO SAL
FROM empl
WHERE EMPLOYEE_ID = 1010;
DBMS_OUTPUT.PUT_LINE(SAL);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('INNER BLOCK EXCEPTION');
END;
DBMS_OUTPUT.PUT_LINE('INNER BLOCK OVER');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('OUTER BLOCK EXCEPTION');
END;

Scenario - 4 : We got an INNER BLOCK exception. From INNER BLOCK exception , we

directly go to the OUTER BLOCK exception

DECLARE
FNAME empl.FIRST_NAME%TYPE;
BEGIN
SELECT FIRST_NAME
INTO FNAME
FROM empl
WHERE EMPLOYEE_ID = 100;
DBMS_OUTPUT.PUT_LINE(FNAME);

DECLARE
SAL [Link]%TYPE;

BEGIN
SELECT SALARY
INTO SAL
FROM empl
WHERE EMPLOYEE_ID = 1010;
DBMS_OUTPUT.PUT_LINE(SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('INNER BLOCK EXCEPTION');
RAISE; -- This is called Re-Raising the same exception
END;
DBMS_OUTPUT.PUT_LINE('INNER BLOCK OVER');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('OUTER BLOCK EXCEPTION');
END;

By Knowing the OUTER BLOCK EXCEPTION (different from Inner Block), we can RAISE that
error from inner block.

DECLARE
FNAME empl.FIRST_NAME%TYPE;

BEGIN
SELECT FIRST_NAME
INTO FNAME
 FROM empl
WHERE EMPLOYEE_ID = 100;
DBMS_OUTPUT.PUT_LINE(FNAME);

DECLARE
SAL [Link]%TYPE;

BEGIN
SELECT SALARY
INTO SAL
FROM empl
WHERE EMPLOYEE_ID = 1010;
DBMS_OUTPUT.PUT_LINE(SAL);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('INNER BLOCK EXCEPTION');
RAISE TOO_MANY_ROWS; -- This will Invoke Outer Block
Exception
END;
DBMS_OUTPUT.PUT_LINE('INNER BLOCK OVER');
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('OUTER BLOCK EXCEPTION');
END;

Raise is a statement which will invoke pre-defined , non-predefined and user-defined exception also.

Notes :

❖ If both Inner Block & Outer Block has no errors , no exception will be invoked

❖ If outer block is having exception , the control will automatically go to the outer block
exception , even the inner block is correct. If the exception is handled in the outer block ,it
is normal termination , otherwise it is abnormal termination.
 ❖ If the Outer block is correct but the inner block is having an exception , then the control
will go the inner block exception. If the exception is handled in the inner block, it won’t go to
the outer block exception. If its not handled , it will check into Outer Block Exception.

❖ If we want to go to the Outer Block exception From the Inner block directly , Use RAISE
statement.

❖ RAISE is a statement which will invoke all kinds of exceptions but its MANDATORY for
USER-DEFINED EXCEPTIONS.

Store ERROR CODES & MESSAGES in a Table

CREATE TABLE ERROR_DETAILS(

ECODE VARCHAR2(100),

EMESSAGE VARCHAR2(200)

);

DECLARE

FNAME empl.FIRST_NAME%TYPE;

e_code ERROR_DETAILS.ECODE%TYPE;

e_message ERROR_DETAILS.EMESSAGE%TYPE;

BEGIN

SELECT FIRST_NAME

INTO FNAME

FROM empl

WHERE EMPLOYEE_ID = 110100;

DBMS_OUTPUT.PUT_LINE(FNAME);

EXCEPTION

WHEN OTHERS THEN

e_code:=SQLCODE;

e_message:=SQLERRM;
 INSERT INTO ERROR_DETAILS VALUES(e_code,e_message);

END;

SELECT * FROM ERROR_DETAILS;

Named Block
● Can be stored in the database.
● It can be called from any frontend application.
● Compiled once , executed many times.

Types of Named Block

A. Stored Sub Programs

a. Procedures
b. Functions
B. Packages
C. Triggers

Stored Sub Programs

➔ Procedure Vs Functions

1. Procedures may or may not return a value. Function must return at least one value.
2. Procedures can’t be called as a part of SQL Queries. Functions can be called as a part
of SQL queries.

Parameters OR Arguments
3 types
1. IN - The procedure or function can be called through IN parameter. Its a Read-Only
Parameter.
2. OUT - procedure or function can return a value through OUT parameter. Default initial value is
NULL
 3. IN OUT - procedure or function can return a value through IN OUT parameter. This parameter
can accept values from User and modify the data in the same parameter.
E.g: In sum of n natural numbers , value of sum is initially 0 and it got updated in the
same variable. Its an example of IN OUT parameter.

Procedure WITHOUT parameters

CREATE OR REPLACE PROCEDURE EMP_PROC
AS
FNAME empl.FIRST_NAME%TYPE;

BEGIN
SELECT FIRST_NAME
INTO FNAME
FROM empl
WHERE EMPLOYEE_ID = &ID;
DBMS_OUTPUT.PUT_LINE(FNAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('invalid employee');
END;

EXEC EMP_PROC

Procedure WITH parameters

In the above example , we use the substitution variable to get the data from the user. But substitution
variable is a pre-compiler variable so it will ask only once for all named block. Don’t use a substitution
variable in the named block which is of no use.

To resolve this - try the below code -

CREATE OR REPLACE PROCEDURE EMP_PROC(ID IN NUMBER)

AS
FNAME empl.FIRST_NAME%TYPE;

BEGIN
SELECT FIRST_NAME
INTO FNAME
FROM empl
WHERE EMPLOYEE_ID = ID;
DBMS_OUTPUT.PUT_LINE(FNAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('invalid employee');
END;

EXEC EMP_PROC(105)
EXEC EMP_PROC(110)

Notes : IS keyword is mainly used for NORMAL or STANDALONE procedures/functions.

Whereas , AS keyword will be used in procedure/function which is created inside of package.

Procedure on a Cursor

CREATE OR REPLACE PROCEDURE PROC_CURSOR_EMP(ID IN NUMBER)

AS
CURSOR emp_cur IS SELECT FIRST_NAME,SALARY FROM empl WHERE EMPLOYEE_ID = ID;

BEGIN
UPDATE empl
SET SALARY = SALARY + 1000
WHERE EMPLOYEE_ID = ID;
COMMIT;
FOR it IN emp_cur
LOOP
DBMS_OUTPUT.PUT_LINE(it.FIRST_NAME||' '||[Link]);
END LOOP;
END;

EXEC PROC_UPDATE_EMP(100);
EXEC PROC_UPDATE_EMP(110);
Function Without Parameters

Create a Function
Using DUAL Table , PL/SQL Block & Bind Variable
Using Procedures

Another Example
Function With Parameters

In Parameter
A and B are IN Parameters by default and C is a local variable for ADD_FUNC.

Day 5
OUT Parameter
● Out parameter is used to return a value from the procedure or function.
● We can initialize the out parameters outside the block but it always takes NULL.
● The value can be modified only inside the Named blocks

Notes :
1. We can call functions/procedures from any other Named Block , Unnamed Block , Bind
Variables.
2. Named Block always starts with Create command.
Procedure with OUT parameters

CREATE OR REPLACE PROCEDURE EMP_PROC

(ID IN NUMBER , FNAME OUT VARCHAR2 , SAL OUT NUMBER)
AS
BEGIN
SELECT FIRST_NAME , SALARY
INTO FNAME , SAL
FROM empl
WHERE EMPLOYEE_ID = ID;
-- DBMS_OUTPUT.PUT_LINE(FNAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('invalid employee');
END;

DECLARE
X empl.FIRST_NAME%TYPE;
Y [Link]%TYPE;

BEGIN
 EMP_PROC(100,X,Y);
DBMS_OUTPUT.PUT_LINE(X||' '||Y);
END;

Calling Using Bind Variables

Procedure

We can use OUT or IN OUT parameters in Functions only if we try to return more than one values.
Function
Can’t Use SELECT statement for Functions/Procedures Having OUT parameters

Function with OUT parameters

CREATE OR REPLACE FUNCTION EMP_FUNC
(ID IN NUMBER , FNAME OUT VARCHAR2)
RETURN NUMBER
AS
SAL NUMBER;
BEGIN
SELECT FIRST_NAME , SALARY
INTO FNAME , SAL
FROM empl
WHERE EMPLOYEE_ID = ID;
-- DBMS_OUTPUT.PUT_LINE(FNAME);
RETURN SAL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('invalid employee');
END;

DECLARE
X empl.FIRST_NAME%TYPE;
Y [Link]%TYPE;

BEGIN
Y:=EMP_FUNC(100,X);
DBMS_OUTPUT.PUT_LINE(X||' '||Y);
END;
IN OUT parameter

Q.

1. factorial of a given number

2. sum of n natural numbers
 Factorial of Number by using Procedure

Factorial of Number by using Function

 Sum of n natural by using procedure

Sum of n natural by using function

CREATE OR REPLACE PROCEDURE SUM_N_PROC(N NUMBER ,R OUT NUMBER)
IS
BEGIN
R:=0;
FOR I IN 1..N
LOOP
R:=R+I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(R);
END;

DECLARE
A NUMBER:=10;
R NUMBER;
BEGIN
SUM_N_PROC(A,R);
END;

Local Sub Programs

● The procedure or function which is defined locally inside the another procedure or
function.
● This local subprogram can be called only within procedure or function body.

Notes : For the inner procedure , we don’t have to start with create or replace . Same goes for
nested functions , the inner function won’t start with create or replace.

Procedures
Functions
Procedure as local subprogram inside Function
Function as local subprogram inside Procedure

Named & Positional Parameters(procedures/functions)

The above one is an example of Positional parameters

Named parameters

When we try calling the above procedure like below, we will get an error.

To resolve this, we can use NAMED parameter.

Need to be considered:

Positional followed by 2 named is allowed

Default values for parameters:

Calling the procedure as follows:

Default in tables:
Packages:

❖ Collection of procedures, functions, variables

 ❖ The variables defined inside the package is said to be a global variable which can be
accessed by anywhere in any session
❖ Function overloading is possible only in packages
❖ All the members of the package will be loaded in the first time itself , which will reduce the
loading time.
❖ We cannot have parameters for the package . But package members can have parameters

Building blocks/Components:
1. Package Header /Specification (Mandatory) -> It contains all the prototype/declaration
of all the members (variables, procedures, functions
2. Package Body (Optional) -> It contains the definition/coding block of the members

Note:

➢ If we keep 1000 procedures, 1000 functions inside a package, it creates only 2 entries,
one for header and one for body
➢ If we drop the package header/specification, the package body also dropped
along with that.
➢ If we drop the package body, then the header/specification still remains the same.
It won't be dropped
➢ If we drop the table in which package is created, package become invalid

Demos:

Package contains a procedure:

Package contains both procedure & function:
One time only Procedure (procedure within begin and end statement)

❖ The codes given in the BEGIN and END block of Package body will be executed
only once (First time alone). This is called ONE TIME ONLY PROCEDURE

DEMO 1:

CREATE OR REPLACE PACKAGE MYPACK

IS
CURSOR emp_cur IS SELECT * FROM empl WHERE DEPARTMENT_ID=60;
FUNCTION FAC(N NUMBER)RETURN NUMBER;
END;

CREATE OR REPLACE PACKAGE BODY MYPACK

IS
FUNCTION FAC
(N NUMBER)
RETURN NUMBER
IS
C NUMBER:=1;
BEGIN
FOR I IN 2..N
LOOP
C:=C*I;
END LOOP;
RETURN C;
END FAC;
BEGIN
FOR it IN emp_cur
LOOP
DBMS_OUTPUT.PUT_LINE(it.FIRST_NAME||' '||it.LAST_NAME||' '||[Link]);
END LOOP;

END;

SELECT [Link](5) FROM DUAL

DEMO 2:
Variables in Packages:

❖ The variable which is inside a package header is said to be a global variable .

❖ This can be accessed by all the members of the packages. We can access this variable
outside the package also.
Purity Level Checking

RNDS
DEMO 2

create or replace package sam_pkg

is

x number:=100;

procedure demo_proc(ID in number);

function demo_func(a number, b number, c out number)

return number;

pragma restrict_REFERENCES(demo_proc, RNDS);

end;

create or replace package body sam_pkg

is procedure demo_proc (ID in number)

as

fname ACC_EMP.first_name%type;

BEGIN

dbms_output.put_line(x);

x:=500;

select first_name into fname from ACC_EMP where employee_id=ID;

dbms_output.put_line(fname);

end demo_proc;

function demo_func(a number, b number, c out number)

return number

as

begin

dbms_output.put_line(x);

c:=a+b;

return c;

end demo_func;

BEGIN

dbms_output.put_line('hello');

end;

/
PRAGMA RESTRICT_REFERENCES
PRAGMA RESTRICT_REFERENCES is used to check the purity level of the code.
We are restricting the accessing of variables & tables inside the members of the package.

Syntax:

PRAGMA RESTRICT_REFERENCES(procedure_name/function_name,purity_level)
 Purity level:

1. RNDS - Read No to Database State (SELECT is not allowed, but DML’s are allowed)
2. WNDS - Write No to Database State( Only select is allowed, no DML)
3. RNPS - Read No to Package State(Reading of Package variable is not allowed)
4. WNPS - Write No to Package State( Modifying the package variable is not
allowed)

RNPS

-- PACKAGE BODY

CREATE OR REPLACE PACKAGE BODY SAMPLE_PKG

IS
PROCEDURE DEMO_PROC
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('VALUE OF X INSIDE PROCEDURE '||X);
X:=500;
END DEMO_PROC;
FUNCTION ADD_FUNC
(A NUMBER,B NUMBER)
RETURN NUMBER
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('VALUE OF X INSIDE FUNCTION '||X);
RETURN A+B;
END ADD_FUNC;
END;
/
For ex, we have 10 procedures (P1,P2..P10) and 10 functions(F1, F2..F10) inside a package. Now
we have to restrict both DML, DRL(SELECT) statement for P1, restrict SELECT statement in F1

PRAGMA RESTRICT_REFERENCES(P1,RNDS,WNDS);
PRAGMA RESTRICT_REFERENCES(F1,RNDS);
PRAGMA RESTRICT_REFERENCES(P5,RNDS,RNPS,WNDS,WNPS) -> NO SELECT, NO DML,
NO READING OF A VARIABLE, NO MODIFICATION OF A VARIABLE

Day 6

Function Overloading (Polymorphism)

DATA DICTIONARIES: (PROCEDURE/FUNCTION/PACKAGE)
To see the content of procedure/function/package, use the below data dictionary

Triggers:
❖ Its an automatic firing event happen whenever there is a change in the database
❖ TCL statements(commit,rollback, savepoint) are not allowed in triggers
❖ We cannot create a trigger for select statement
❖ If we create a trigger for the table, and the table is dropped then the trigger is automatically
dropped.

Components:

1. Trigger Name
2. Trigger Type
3. Triggering Events -
INSERT/UPDATE/DELETE/CREATE/DROP/LOGON/LOGOFF/STARTUP/SHUTDOWN
4. Trigger Timings - BEFORE/AFTER
5. Trigger Body

TYPES:

1. DML - only for tables

2. DDL/SYSTEM - created only by DBA for the events
“CREATE/DROP/LOGON/LOGOFF/STARTUP/SHUTDOWN”
3. INSTEAD OF - only for views

DML TRIGGERS:
TYPES:
 1. STATEMENT LEVEL TRIGGER - this trigger will fire only once whether the DML operation is
success/failed
2. ROW LEVEL TRIGGER - this will be fired depending on the no of rows affected by DML
operations.

For example,
1. if 5 rows are affected, STATEMENT LEVEL trigger will fire only once, but ROW LEVEL trigger
will fire 5 times.
2. If no rows are affected, STATEMENT LEVEL trigger will fire only once, but ROW LEVEL
trigger will not fire

STATEMENT LEVEL TRIGGER

FOR ZERO ROWS,

Notes : DBA can only perform DDL triggers , not DML triggers

TO REMOVE A TRIGGER:
ROW LEVEL TRIGGERS:

FOR 3 ROWS,
FOR ZERO ROWS, ROW LEVEL TRIGGER WILL NOT FIRE

DEMO 2:
:OLD, :NEW -> Is applicable only in row level trigger

Scenarios:

1. Trigger should not be fired even though the particular event occurs
If we issue the update statement, the trigger will not fire

2. Stop firing all the triggers on a particular table

We can enable the triggers by using ENABLE instead of DISABLE keyword.

 3. We disabled/dropped all triggers on the table. We are creating 2 triggers of the same type,
same operation, same table,same timings, which trigger will get fired?

CREATE OR REPLACE TRIGGER T1

AFTER
UPDATE
ON
SAMPLE_EMP
BEGIN
DBMS_OUTPUT.PUT_LINE('AFTER STATEMENT LEVEL');
END;
/

CREATE OR REPLACE TRIGGER T2

BEFORE
UPDATE
ON
SAMPLE_EMP
BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE STATEMENT LEVEL');
END;
/

4. If we create 2 BEFORE STATEMENT LEVEL triggers, the one which created last will be fired
first.
CREATE OR REPLACE TRIGGER T1
AFTER
UPDATE
ON
SAMPLE_EMP
BEGIN
DBMS_OUTPUT.PUT_LINE('AFTER STATEMENT LEVEL');
END;
/

CREATE OR REPLACE TRIGGER T2

AFTER
UPDATE
ON
SAMPLE_EMP
BEGIN
DBMS_OUTPUT.PUT_LINE('AFTER STATEMENT LEVEL TRIGGER 2');
END;
/
UPDATE SAMPLE_EMP SET SALARY=SALARY+1000 WHERE DEPARTMENT_ID =60

5. CREATE 4 TRIGGERS (T1,T2,T3,T4)IN THE ORDER GIVEN BELOW:

a. After row level

 b. After statement
c. Before row level
d. Before statement

Identify the order of execution of the trigger:

➢ 3 ROWS AFFECTED
➢ ZERO ROWS AFFECTED

CREATE OR REPLACE TRIGGER AST

AFTER
UPDATE
ON
EMPL
BEGIN
DBMS_OUTPUT.PUT_LINE('AFTER UPDATE : STATEMENT LEVEL TRIGGER');
END;
/

CREATE OR REPLACE TRIGGER ARW

AFTER
UPDATE
ON
EMPL
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('AFTER UPDATE : ROW LEVEL TRIGGER');
END;
/

CREATE OR REPLACE TRIGGER BRW

BEFORE
UPDATE
ON
EMPL
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE UPDATE : ROW LEVEL TRIGGER');
END;
/

CREATE OR REPLACE TRIGGER BST

BEFORE
UPDATE
ON
EMPL
 BEGIN
DBMS_OUTPUT.PUT_LINE('BEFORE UPDATE : STATEMENT LEVEL TRIGGER');
END;
/

UPDATE EMPL SET SALARY=SALARY+1000 WHERE DEPARTMENT_ID = 60;

❖ BEFORE STATEMENT
❖ BEFORE ROW
❖ AFTER ROW
❖ BEFORE ROW
❖ AFTER ROW
❖ BEFORE ROW
❖ AFTER ROW
❖ AFTER STATEMENT

6. After disabling all the triggers, we are a creating the new trigger as below:
Recursive Error

It becomes infinite because we are using the update statement on the same table inside the trigger.

To resolve this,

Consider the above scenario with row level triggers

Mutative Error

We got mutation error, because of the below reason,

1. When we issue an outer update command on salary, the entire table got locked. When we
come inside the trigger block, we are trying to update the commission_pct column on a locked
table. This leads to a deadlock situation called mutation.
2. Mutation occurs only with row level not with statement level

Even we tried to create the trigger like below, still its the same.

Notes : To stop Mutation Error , We need Compound Trigger (Not in our syllabus)
OR
Simultaneous DML operations with one or more than one third party tables.
Solution : DML on particular columns

7. Preventing update on the table

To prevent the update, use the below code:

This is not possible in AFTER trigger
Questions:
Create a trigger on the EMP1 table such that whenever a record is deleted from EMP1, the
deleted record is automatically inserted into another table EMP_HISTORY.

CREATE TABLE EMP_HISTORY AS SELECT * FROM EMP1;

TRUNCATE TABLE EMP_HISTORY;

CREATE OR REPLACE TRIGGER RESERVE_EMP

BEFORE DELETE
ON
EMP1
FOR EACH ROW
BEGIN
INSERT INTO EMP_HISTORY
VALUES(:OLD.EMPLOYEE_ID , :OLD.FIRST_NAME , :OLD.LAST_NAME , :[Link], :[Link]
NE_NUMBER , :OLD.HIRE_DATE , :OLD.JOB_ID , :[Link] , :OLD.COMMISSION_PCT , :O
LD.MANAGER_ID , :OLD.DEPARTMENT_ID);
END;
/

SELECT * FROM EMP_HISTORY;

DELETE FROM EMP1 WHERE EMPLOYEE_ID = 102;
SELECT * FROM EMP1;
SELECT * FROM EMP_HISTORY;

Day 7
Consider the below scenario:
This trigger will be fired for all departments. But we want to fire the trigger only for department id 30.
This will be resolved by CONDITIONAL TRIGGERS

Conditional triggers:

❖ Trigger will be fired only on the specified condition

❖ This can be done by using WHEN clause
❖ This works for both row level as well as statement level trigger

Trigger is not fired for department no 70

But it will be fired for department no 30

Scenario 2:

1. Create a procedure which accepts the department id and print all the first name of the
employees who are working in that department
2. The above procedure must be called on Week end(saturday and sunday) automatically

Logon - DDL command

CREATE OR REPLACE TRIGGER show_details

AFTER LOGON ON DATABASE
WHEN(TO_CHAR(SYSDATE,'Dy') IN ('Sat','Sun','Fri'))
BEGIN
 show_emp_details(&did);
END;

CREATE OR REPLACE PROCEDURE show_emp_details (did EMPLOYEES.DEPARTMENT_ID%TYPE)

IS
BEGIN
FOR emp_cur IN (SELECT FIRST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID=did AND
did IS NOT NULL)
LOOP
DBMS_OUTPUT.PUT_LINE(emp_cur.FIRST_NAME);
END LOOP;
END;
/

(For Insufficient privileges , you can only do it in oraclelinux)

Scenario 3:

1. Create a procedure which accepts the department id and print all the first name of the
employees who are working in that department
2. The above procedure must be called whenever we tried the UPDATE on the
EMPLOYEES table on Week end(saturday and sunday) automatically
Scenario:

Create a trigger which works for all DML operations.(INSERT/UPDATE/DELETE)

DEMO 1:

DEMO 2:
INSERTING,UPDATING,DELETING - Conditional Predicates
Instead of triggers:

❖ It is created only for views

❖ It is used to deny the DML operation on the view

Consider the given where we can insert a row in the table through view:

We are going to stop inserting the record through view. This can be done by using INSTEAD OF
TRIGGER
 DDL/SYSTEM TRIGGERS

❖ It is created only by DBA for the DDL operation(CREATE, DROP), system

events(LOGON,LOGOFF,STARTUP,SHUTDOWN)
❖ DBA cannot create a DML triggers on their own table
BULK COLLECT:

To display the first name of all the employees who are working specific department using the below
logics
1. Cursor
2. Using select in FOR loop
3. BULK COLLECT

Method 2:

Method 3:
We can use BULK COLLECT in our PL/SQL COLLECTIONS i.e., Associative array, Nested table,
VARRAY

The above is an example of a nested [Link] same thing can be achieved by Associative array.

BULK Collect using Varray :

Dynamic Cursor:
❖ Default or normal cursor is static in nature. We can use dynamic cursor also
❖ REF CURSOR is the dynamic cursor

To display the department name from DEPARTMENTS table using Cursor

Using REF CURSOR:

DECLARE
TYPE DEPT_CUR IS REF CURSOR RETURN DEPARTMENTS%ROWTYPE;
TYPE EMP_REC IS RECORD (EID NUMBER,ENAME VARCHAR2(40),DNO NUMBER,SAL NUMBER);
DEPT_CUR_NEW DEPT_CUR;
D DEPARTMENTS%ROWTYPE;
E EMP_REC;
BEGIN
OPEN DEPT_CUR_NEW FOR SELECT * FROM DEPARTMENTS;
LOOP
FETCH DEPT_CUR_NEW INTO D;
EXIT WHEN DEPT_CUR_NEW%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(D.DEPARTMENT_NAME);
END LOOP;
CLOSE DEPT_CUR_NEW;
OPEN DEPT_CUR_NEW FOR SELECT EMPLOYEE_ID,FIRST_NAME,DEPARTMENT_ID,SALARY FROM
EMPLOYEES;
LOOP
FETCH DEPT_CUR_NEW INTO E;
EXIT WHEN DEPT_CUR_NEW%NOTFOUND;
DBMS_OUTPUT.PUT_LINE([Link]||' '||[Link]);
END LOOP;
CLOSE DEPT_CUR_NEW;
END;
/

But the below code will give an compilation error because the structure of DEPARTMENTS &
EMPLOYEES are different
DECLARE
TYPE DEPT_CUR IS REF CURSOR RETURN DEPARTMENTS%ROWTYPE;
DEPT_CUR_NEW DEPT_CUR;
D DEPARTMENTS%ROWTYPE;
E EMPLOYEES%ROWTYPE;
BEGIN
OPEN DEPT_CUR_NEW FOR SELECT * FROM DEPARTMENTS;
LOOP
FETCH DEPT_CUR_NEW INTO D;
EXIT WHEN DEPT_CUR_NEW%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(D.DEPARTMENT_NAME);
END LOOP;
CLOSE DEPT_CUR_NEW;
OPEN DEPT_CUR_NEW FOR SELECT * FROM EMPLOYEES;
LOOP
FETCH DEPT_CUR_NEW INTO E;
EXIT WHEN DEPT_CUR_NEW%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(E.FIRST_NAME||' '||[Link]);
END LOOP;
CLOSE DEPT_CUR_NEW;
END;
/

To resolve the above problem,

Notes:
 ❖ We need a RETURN in REF cursor if the multiple tables follow the same structure of columns.
❖ If tables are having different structure, we can use REF CURSOR without RETURN
clause
❖ If we have RETURN clause, it is said to be strongly typed REF CURSOR, otherwise it is
Weakly typed REF CURSOR.

REF CURSOR as a parameter / using SYS_REFCURSOR: