Mastering SQL for Data Science: A Comprehensive Guide
Day-12
Stored Functions
& Package
Stored Function
Syntax:
Create or replace function
<function_name>[(argument1)[in|out|inout]data
type, (argument2)[in|out|inout]datatype……….)]
return datatype
Is
[<local
variable declaration>]
Begin
Executable
statements;
End;
/
As with a procedure, a
function is made up of two parts, the declaration and the body. The declaration
begins with the keyword function and ends with return statement. The body
begins with the keyword is and ends with the keyword end.
The difference between aprocedure and function is the return value. A function has the return
declaration as well as a return function within the body of the function that
returns a value.
E.g:
create or replace
function fun1(n
in number)
return varchar2 is
nm employee.name%type;
begin
select name
into nm
from employee
where no=n;
return nm;
end;
/
--call block
declare
n employee.no%type;
nm
employee.name%type;
begin
n:=&n;
nm:=fun1(n);
dbms_output.put_line(n||' '||nm);
end;
/
v Package:
A package is an
oracle object, which holds other objects within it. Objects commonly held
within packages are procedures, functions, variables, constants, cursors and exceptions.
It is a way of creating generic, encapsulations, re-usable code.
A package once
written and debugged is compiled and stored in oracle’s system tables held in
and oracle database. All users who have executed permissions on the oracle
database can use the package.
Packages can
contain PL/SQL block of code, which have been written to perform some process
internally on their own. These are also subprograms of the package but these
subprograms are not standards.
ü Use of package:
Packages offer the following advantages;
1. Packages enable the organization of the commercial applications into efficient modules. Each package is easily understood and the interfaces between packages are simple, clear and well defined.
2. Packages allow granting of privileges efficiently.
3. A package’s public variables and cursors persist
for the duration of the session. Therefore, cursors and the procedures that
execute in this environment can share them.
4. Packages enable the overloading of procedures and
functions when required.
5. Packages improve performance by loading multiple
objects into memory at once.
6. Package promotes code reuse through the use of
libraries that contain stored procedures, functions there by reducing redundant
coding.
ü Package Specification:
The package specification contains;
·
Name of the package
·
Name of the data types of any arguments
·
This declaration is local to the database and
global to the package.
This means that
procedures, function, variables, cursors, exceptions and other objects declared
in a package are accessible from anywhere in the package. Therefore all the
information a package needs, to execute a stored program, is contained in the
package specification.
Syntax:
Create or replace package <package_name> as
Function
<function_name>(arguments) return data type;
Procedure
<procedure_name> (arguments)
End <package_name>;
/
E.g:
create or replace package pack1 as
function
f_emp1(n in number) return varchar2;
procedure
p_emp1(n in number, nm out varchar2,c out varchar2,s out number);
end pack1;
/
--Package body
create or replace
package body pack1 is
function f_emp1(n in number)return varchar2 is
nm a_emp.name%type;
begin
select name
into nm
from a_emp
where no=n;
return nm;
end;
procedure p_emp1(n in number,nm out varchar2,c out varchar2,
s out number) is
begin
select name,city,sal
into nm,c,s
from a_emp
where no=n;
end;
end pack1;
/
--function call block
declare
n a_emp.no%type;
nm a_emp.name%type;
begin
n:=&n;
nm:=pack1.f_emp1(n);
dbms_output.put_line(n||' '||nm);
end;
/
--procedure call block
declare
n a_emp.no%type;
nm a_emp.name%type;
c a_emp.city%type;
s a_emp.sal%type;
begin
n:=&n;
pack1.p_emp1(n,nm,c,s);
dbms_output.put_line(n||' '||nm||' '||c||' '||s);
end;
/
0 Comments