Stored procedures and functions (autonomous)
Non-stored procedures and functions (non-autonomous)
PL/SQL
Subprograms: Procedures and Functions
Ines BAKLOUTI
ines.baklouti@esprit.tn
Ecole Supérieure Privée d’Ingénierie et de Technologies
Stored procedures and functions (autonomous)
Non-stored procedures and functions (non-autonomous)
Outline
1 Stored procedures and functions(autonomous)
Stored procedures
Stored functions
2 Non-stored procedures and functions (non-autonomous)
Non-stored procedures
Non-stored functions
2/18
Stored procedures and functions (autonomous)
Non-stored procedures and functions (non-autonomous)
Introduction
Functions and procedures are subprograms that are considered as named
PL/SQL blocks.
Stored Functions and procedures are autonomous subprograms PL/SQL
that are compiled and stored in the data dictionnary using a CREATE
OR REPLACE FUNCTION/PROCEDURE statement.
A non-autonomous function or procedure can be declared in a PL/SQL
block,a subprogram, or a package without using the CREATE OR
REPLACE clause.
3/18
Stored procedures and functions (autonomous) stored procedures
Non-stored procedures and functions (non-autonomous) stored functions
Outline
1 Stored procedures and functions autonomous
Stored procedures
Stored functions
4/18
Stored procedures and functions (autonomous) Stored procedures
Non-stored procedures and functions (non-autonomous) stored functions
Stored procedures and functions (autonomous)
An autonomous or stored procedure (or function) is a PL/SQL subprogram that is
stored in an Oracle database and called by a user, directly or indirectly.
ADVANTAGES
Efficiency: Minimizes the use of SQL queries on the client side by
replacing them with server-side procedure calls that integrate
multiple SQL statements.
Reusability : A stored procedure can be used in various situations (SQL,
triggers, applications).
Portability : A stored procedure is independent of the operating
system version or compilers. is independent of the operating system
version or compilers.
Maintainability :By calling the same procedure from multiple tools
(SQL*PLUS, application, other stored procedure), we reduce the
maintenance cost of this centralized procedure.
5/18
Stored procedures and functions (autonomous) stored procedures
Non-stored procedures and functions (non-autonomous) stored functions
Stored procedures
Syntaxe
CREATE [OR REPLACE] PROCEDURE procedure name
[(argument1 [passing mode1] type1, ..., [argumentN [passing modeN] typeN])] IS
–The optional declarative section, without using the keyword
DECLARE [decclaring local variables]
BEGIN
–the mandatory executable
Section
[exception section]
END [procedure-name];
For a procedure, there are three modes of passing parameters:
IN : input (by default)
OUT : output
IN OUT : Input and Output
6/18
Stored procedures and functions (autonomous) stored procedures
Non-stored procedures and functions (non-autonomous) stored functions
Stored procedures
Example
CREATE OR REPLACE PROCEDURE add dept (dept id IN
departments.department id%TYPE, dept name IN
departments.department name%TYPE, nbre OUT number)
IS
BEGIN
insert into departments(department id,department name)
values(dept id,dept name);
commit;
select count(*) into nbre from departments;
dbms output.put line(’Le nombre de département est : ’||nbre);
END;
7/18
Stored procedures and functions (autonomous) stored procedures
Non-stored procedures and functions (non-autonomous) stored functions
Stored procedures
Calling stored procedures
Example
DECLARE
nb number;
BEGIN
add dept(300,’IT’,nb);
end;
8/18
Stored procedures and functions (autonomous) stored procedures
Non-stored procedures and functions (non-autonomous) stored functions
Stored functions
Syntaxe
CREATE [OR REPLACE] FUNCTION function name
[[(argument1 [passing mode1] type1, ..., [argumentN [passing modeN] typeN])]]
RETURN return type IS
–The optional declarative section, without using the keyword DECLARE
[declaring local variables]
BEGIN
–The mandatory executable
section. [exception section ]
END [function name];
All parameters of a function are in IN mode (in this case, we
are not required to write the mode)
9/18
Stored procedures and functions (autonomous) stored procedures
Non-stored procedures and functions (non-autonomous) stored functions
Stored functions
Example 1
CREATE OR REPLACE FUNCTION fn check sal (empno employees.employee id%TYPE)
RETURN Boolean IS
dept id employees.department id%TYPE;
sal employees.salary%TYPE;
avg sal employees.salary%TYPE;
BEGIN
SELECT salary,department id INTO sal,dept id FROM employees WHERE employee id=empno;
SELECT avg(salary) INTO avg sal FROM employees WHERE department id=dept id;
IF sal > avg sal THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
10/18
Stored procedures and functions (autonomous) stored procedures
Non-stored procedures and functions (non-autonomous) stored functions
Stored functions
Example 2
CREATE OR REPLACE FUNCTION fn dept name (deptno number)
RETURN varchar IS
dname varchar(30);
BEGIN
select department name into dname from departments where
department id=deptno;
RETURN dname;
END;
11/18
Stored procedures and functions (autonomous) stored procedures
Non-stored procedures and functions (non-autonomous) stored functions
Stored functions
Calling stored functions
Example 1
BEGIN
IF (fn check sal(124)) THEN
DBMS OUTPUT.PUT LINE(’Salary > average’);
ELSE
DBMS OUTPUT.PUT LINE(’Salary < average’);
END IF;
END;
Example 2
SELECT department id No dept, fn dept name(department id)
Nom dept,last name Nom, first name Prenom FROM employees order by
department id;
12/18
Stored procedures and functions (autonomous) stored procedures
Non-stored procedures and functions (non-autonomous) stored functions
Remarks
When creating any object such as a table, procedure or
function, the corresponding entries are created in the user
objects table. You can examine the content of the user
objects table by executing the following command:
SELECT object name,object type FROM user objects;
The source code of a stored procedure or function is stored in the
user source table. You can examine the source code of the
procedure by executing the following command:
SELECT * FROM user source WHERE name=’FN DEPT NAME’;
Use the DESCRIBE command to examine the arguments and data type
returned by a function or procedure.
Example : DESCRIBE fn check sal;
13/18
Stored procedures and functions (autonomous)
Non-stored procedures and functions (non-autonomous)
Outline
2 Non-stored procedures and functions (non-autonomous)
Non-stored procedures
Non-stored functions
14/18
Stored procedures and functions (autonomous) Non stored procedures
Non-stored procedures and functions (non-autonomous) Non stored functions
Non Stored Procedures
15/18
Stored procedures and functions (autonomous) Non stored procedures
Non-stored procedures and functions (non-autonomous) Non stored functions
Non Stored Procedures
Les proc´
16/18
Stored procedures and functions (autonomous) Non stored procedures
Non-stored procedures and functions (non-autonomous) Non stored functions
Non Stored functions
18/18
Stored procedures and functions (autonomous) Non stored procedures
Non-stored procedures and functions (non-autonomous) Non stored functions
Non Stored functions
18/18
