Procedures & Functions

"A procedures or function is a group or set of SQL and PL/SQL statements that perform a
specific task."
A function and procedure is a named PL/SQL Block which is similar . 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.

Procedures:
A procudure is a named PL/SQL block which performs one or more specific task. This is
similar to a procedure in other programming languages. 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 or 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.
We can pass parameters to procedures in three ways :

Parameters Description
IN type These types of parameters are used to send values to stored procedures.
These types of parameters are used to get values from stored procedures. This is
OUT type
similar to a return type in functions.
These types of parameters are used to send values and get values from stored
IN OUT type
procedures.

A procedure may or may not return any value.

Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name (<Argument> {IN, OUT, IN OUT}
<Datatype>,…)
IS
Declaration section<variable, constant> ;
BEGIN
Execution section
EXCEPTION
Exception section
END

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.
The syntax within the brackets [ ] indicate they are optional. By using CREATE OR
REPLACE together the procedure is created if no other procedure with the same name exists
or the existing procedure is replaced with the current code.

How to execute a Procedure?

There are two ways to execute a procedure :

  From the SQL prompt : EXECUTE [or EXEC] procedure_name;
 Within another procedure – simply use the procedure name : procedure_name;

Example:

create table named emp have two column id and salary with number datatype.
CREATE OR REPLACE PROCEDURE p1(id IN NUMBER, sal IN NUMBER)
AS
BEGIN
INSERT INTO emp VALUES(id, sal);
DBMD_OUTPUT.PUT_LINE('VALUE INSERTED.');
END;
/

Output:

Run SQL Command Line

SQL>set serveroutput on
SQL>start D://pr.sql
Procedure created.

SQL>exec p1(5,4);
VALUE INSERTED.
PL/SQL procudere successfully completed.

SQL>select * from emp;

ID SALARY
----- --------
2 5000

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.

Syntax:
CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype; {IS, AS}
Declaration_section <variable,constant> ;
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;

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.
The execution and exception section both should return a value which is of the datatype
defined in the header section.

How to execute a Function?

A function can be executed in the following ways.

 As a part of a SELECT statement : SELECT emp_details_func FROM dual;

 In a PL/SQL Statements like, : dbms_output.put_line(emp_details_func);

This line displays the value returned by the function .

Example:
create or replace function getsal (no IN number) return number
is
sal number(5);
begin
select salary into sal from emp where id=no;
return sal;
end;
/

Output:

Run SQL Command Line

SQL>select * from emp;
ID SALARY
----- --------
2 5000

SQL>start D://fun.sql
Function created.

SQL>select getsal(2) from dual;

GETSAL(2)
---------
5000

In the example we are retrieving the ‘salary’ of employee with id 2 to variable ‘sal’.
The return type of the function is number.

Destroying procedure and function :

Syntax:
DROP PROCEDURE/FUNCTION PROCEDURE/FUNCTION_NAME;

Procedures VS Functions:

 A function MUST return a value

 A procedure cannot return a value
  Procedures and functions can both return data in OUT and IN OUT parameters
 The return statement in a function returns control to the calling program and returns
the results of the function
 The return statement of a procedure returns control to the calling program and cannot
return a value
 Functions can be called from SQL, procedure cannot
 Functions are considered expressions, procedure are not

Transaction Scope:

A transaction begins whenever the first SQL statement (particularily DML commands
INSERT, UPDATE, DELETE, SELECT) is encountered and ends when a COMMIT or ROLLBACK
command is executed.

Using COMMIT

Commit command is executed after every DML command as they are not auto saved or
commited like DDL commands. This way, the commit command permanently changes the
data in the database.

Following is the syntax:

Commit;

NOTE: By default, automatic commit for DML commands is off. The automatic commit
for DML commands can be set by using the following command:

set autocommit on;

-- and to turn it off
set autocommit off;

Using ROLLBACK

Rollback means undo. Whenever rollback command is executed, it ends the transaction and
undoes all the changes made during the transaction. Rollback can be applied to those
transactions which are not committed.

The rollback command will have no affect if it is executed after the commit command
because in that case the commit command will make the changes done in the transaction
permanent.

Following is the syntax:

Rollback [to savepoint <savepointname >];

where,

savepoint is an optional parameter and is used to rollback a transaction partly upto a certain
specified point.
savepointname is the name given to the savepoint created during the transaction and is user-
defined.

Using SAVEPOINT

For longer transactions, savepoint is quite useful as it divides longer transactions into smaller
parts and marks certain points of a transaction as checkpoints.

It is useful when we want to rollback a particular part of a transaction instead of applying

rollback to unwanted parts of a transaction or the complete transaction.

For example, if a complete transaction has 8 DML statements, and we create a savepoint after
4 statements, then if, for some reason after the execution of 6th statement we want to rollback
uptil the 4th statement, then we can easily do that and the transaction can again be executed
starting from the 4th statement.

Following is the syntax:

Savepoint <savepointname>;

Time for an Example!

Below we have a PL/SQL program to demonstrate the execution of a transaction,

set serveroutput on;

DECLARE
rollno student.sno%type;
snm student.sname%type;
s_age student.age%type;
s_cr student.course%type;
BEGIN
rollno := &sno;
snm := '&sname';
s_age := &age;
s_cr := '&course';
INSERT into student values(rollno,snm,age,course);
dbms_output.put_line('One record inserted');
COMMIT;
END;

In the above code of PL/SQL block, there is a table called STUDENT in the database with
columns sno as number, sname as varchar2, age as number and course as varchar2.

In the code, we have executed an INSERT statement and then used the COMMIT statement to
commit or permanently save the changes into the database.

Instead of the COMMIT statement, if we use the ROLLBACK statement there, then even though
the INSERT statement executed successfully, still, after the execution of the PL/SQL block if
you will check the Student table in the database, you will not find the new student entry
because we executed the ROLLBACK statement and it rolled back the changes.
PL/SQL Code Example with Savepoint and Rollback

Let's add two insert statement in the above code and put a savepoint in between them and
then use the ROLLBACK command to revert back changes of one insert statement.

set serveroutput on;

DECLARE
rollno student.sno%type;
snm student.sname%type;
s_age student.age%type;
s_cr student.course%type;
BEGIN
rollno := &sno;
snm := '&sname';
s_age := &age;
s_cr := '&course';
INSERT into student values(rollno,snm,age,course);
dbms_output.put_line('One record inserted');
COMMIT;
-- adding savepoint
SAVEPOINT savehere;
-- second time asking user for input
rollno := &sno;
snm := '&sname';
s_age := &age;
s_cr := '&course';
INSERT into student values(rollno,snm,age,course);
dbms_output.put_line('One record inserted');
ROLLBACK [TO SAVEPOINT savehere];
END;
After execution of the above code, we will have one entry created in the Student table, while the
second entry will be rolled back.

Calling Subroutines

Calling notation is a way of providing values to the parameters of a subroutine such as

PL/SQL function or a stored procedure.

Types of Calling Notations for Subroutines

In Oracle PL/SQL there are 3 types of calling notations. These are:

1. Positional Notation
2. Named Notation and
3. Mixed calling notation

Positional Calling Notations

Positional notation is the most common calling notation which you can see in almost every
computer programming language. In positional notation we have to specify the value for each
formal parameter in a sequential manner. This means that you have to provide the values for
the formal parameters in the same order as they were declared in the procedure or in the
function.

In positional notation the datatype and the position of the actual parameter must match with
the formal parameter.

Example: Positional Notation for calling PL/SQL Subroutines.

CREATE OR REPLACE PROCEDURE emp_sal
(dep_id NUMBER, sal_raise NUMBER)
IS
BEGIN
UPDATE employees
SET salary = salary * sal_raise
WHERE department_id = dep_id;

DBMS_OUTPUT.PUT_LINE ('salary updated successfully');

END;
/

This is the same example which we did in PL/SQL Tutorial 42 on how to create stored
procedure with parameters albeit some minor changes. Now if we use positional calling
notation then we have to supply the values to both the parameters of the above procedure in
the same manner in which they are declared.

Stored Procedure call using positional notation in Oracle

Database
EXECUTE emp_sal(40,2);

In this simple procedure call, the value 40 is corresponding to the formal parameter dep_id
and value 2 is corresponding to the parameter sal_raise.

Named Calling Notations

Named calling notation lets you pass values to the formal parameters using their names. This
will in turn let you assign values to only required or say mandatory parameters.

This calling notation is useful when you have a subroutine with parameters where some of
those parameters are mandatory and some are optional and you want to pass the values to
only the mandatory ones.
Association Operator

In order to assign values to the formal parameters using their names we use association
operator. It is a combination of equal to (=) sign and greater than (>) sign. We write the name
of the formal parameter to the left hand side of the operator and the value which you want to
assign to the right hand side of the operator.

Example of Named Calling Notation for calling a PL/SQL Subroutines

CREATE OR REPLACE FUNCTION add_num
(var_1 NUMBER, var_2 NUMBER DEFAULT 0, var_3 NUMBER ) RETURN NUMBER
IS
BEGIN
RETURN var_1 + var_2 + var_3;
END;
/

The above function has 3 parameters. Among these 3 parameters 2 are mandatory and 1 is
optional with a default value 0.

You can call this function using positional notation. But it has a restriction which you have to
fulfill and that is that you have to supply values to all the formal parameters in the same order
in which they are declared and the datatype of formal and actual parameters must match.

So if you want to omit the optional parameter and want to use their default value or you just
forgot the order of the parameter in which they were declared! Then it will be slightly
difficult for you to call the above subroutine using positional notation. In such a scenario you
can take advantage of Named Calling Notation. This calling notation will provide you the
desired flexibility in calling your subroutines.

PL/SQL Function call using Named Calling Notation in

Oracle Database
DECLARE
var_result NUMBER;
BEGIN
var_result := add_num(var_3 => 5, var_1 =>2);
DBMS_OUTPUT.put_line('Result ->' || var_result);
END;

I have explained the PL/SQL Function call in detail in the Video Tutorial on this same topic
on my YouTube channel.

Mixed Calling Notation for calling PL/SQL Subroutines

As the name suggests in mixed calling notation you can call subroutines using the
combination of named as well as positional calling notations. Mixed calling notation is very
helpful where the parameter list is defined with all mandatory parameters first and optional
parameters next.
Example of Mixed calling notation for calling PL/SQL subroutines

Here is the anonymous block in which we are calling the same function add_num ( ) which
we coded when doing named calling notation.

DECLARE
var_result NUMBER;
BEGIN
var_result := add_num(var_1 => 10, 30 ,var_3 =>19);
DBMS_OUTPUT.put_line('Result ->' || var_result);
END;

That’s how we use mixed calling notation for calling PL/SQL Subroutines.

Exclusionary notation in PL/SQL

1. Using Parameters for Exclusion

Approach:

 Use parameters to define the conditions under which certain operations should not
execute.
 Pass these parameters explicitly or set default values to exclude specific logic.

Example: Exclude Specific Employee IDs

CREATE OR REPLACE PROCEDURE update_salaries (

exclude_emp_id IN NUMBER DEFAULT NULL

) IS

BEGIN

UPDATE employees

SET salary = salary * 1.1 -- 10% increment

WHERE employee_id != exclude_emp_id; -- Exclude specific ID

END;

/ 2. Exclusion with Conditional Logic

Approach:

 Use IF statements, CASE, or NOT operators within the procedure or function to exclude
specific cases.
Example: Skip Processing for Certain Conditions

CREATE OR REPLACE FUNCTION calculate_bonus (

emp_id IN NUMBER,

salary IN NUMBER

) RETURN NUMBER IS

bonus NUMBER;

BEGIN

IF salary > 100000 THEN

-- Exclude high-salary employees

RETURN 0;

ELSE

bonus := salary * 0.1; -- 10% bonus for others

RETURN bonus;

END IF;

END;

3. Exclusion via Cursor Filtering

Approach:

 When working with cursors, use the WHERE clause to exclude specific rows or
conditions.

Example: Exclude Specific Departments

CREATE OR REPLACE PROCEDURE process_employees IS

CURSOR emp_cursor IS

SELECT employee_id, department_id

FROM employees
 WHERE department_id NOT IN (10, 20); -- Exclude departments 10 and 20

BEGIN

FOR emp IN emp_cursor LOOP

-- Process employee

DBMS_OUTPUT.PUT_LINE('Processing Employee ID: ' || emp.employee_id);

END LOOP;

END;

4. Using Exceptions for Exclusion

Approach:

 Handle cases explicitly by raising exceptions to skip certain operations or conditions.

Example: Exclude Invalid Data

CREATE OR REPLACE PROCEDURE update_employee_status (

emp_id IN NUMBER

) IS

e_invalid_id EXCEPTION;

BEGIN

IF emp_id NOT IN (SELECT employee_id FROM employees) THEN

RAISE e_invalid_id; -- Exclude non-existing IDs

END IF;

-- Update status for valid IDs

UPDATE employees

SET status = 'ACTIVE'

WHERE employee_id = emp_id;

EXCEPTION

WHEN e_invalid_id THEN

DBMS_OUTPUT.PUT_LINE('Invalid Employee ID: ' || emp_id);

END;