Sub Programs
Functions & Procedures
By
Parteek Bhatia
Assistant Professor
Dept of Comp Sc & Engg
Thapar University
Patiala
Subprograms
Subprograms are named PL/SQL blocks that can
take parameters and can be invoked. Subprograms
allow decomposition of a program into logical units.
PL/SQL has two types of subprograms:
Procedures
Functions
They can be further classified as:
Local subprograms
Stored Subprograms
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Local and Stored Subprograms
Local Subprograms
Such procedures and functions are local to the
PL/SQL module, which contain it. They can be
created in the declarative section of PL/SQL
module, local to the module. The local module can
be called anywhere in the module’s execution
section.
Stored Subprograms
A stored procedure or function is a named PL/SQL
code block that have been compiled and stored in
one of the Oracle engine’s system tables.They are
invoked or called by any the PL/SQL block that
appear within an application.
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Difference between Procedure and Function
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Local Procedure
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Arguments
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Consider a procedure that accepts two numbers and
return addition, subtraction, multiplication and division
of two numbers or in other words a procedure to return
multiple values through arguments.
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Stored Procedure
Note:
If an error occurs during compilation of the procedure or function an invalid
procedure or function is created. These errors can be viewed using the
select statement:
SELECT *FROM USER_ERRORS;
Or
Show errors;
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
A Stored procedure that accepts two numbers
and return addition, subtraction, multiplication
and division of two numbers or in other words a
stored procedure to return multiple values
through arguments.
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
A PL/SQL code to call the procedure PROCESS
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
A stored procedure fire_employee to delete
employee on the basis of employee number.
CREATE PROCEDURE fire_employee (emp_id NUMBER)
AS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END;
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
A PL/SQL code to call the procedure fire_employee
DECLARE
e number;
BEGIN
e:=&empno;
fire_employee(e);
END;
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Local Function
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
A PL/SQL code that calls a function to add two
numbers.
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Stored Function
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
A Stored function that accepts two numbers and return
addition of passed values.
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
A PL/SQL code to call the function ADDN
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
A Stored function that accepts department number
and return total salary of that department.
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
A PL/SQL code to call the function SALARY
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Dropping a Procedure/ Function
Syntax:
DROP PROCEDURE procedure_name;
DROP FUNCTION function_name;
Examples:
DROP PROCEDURE process;
DROP FUNCTION addn;
Workshop on Advanced Databases,
Chitkara Univ, By Parteek Bhatia
Thanks
Lets Implement it in Lab Session
