Mastering SQL for Data Science: A Comprehensive Guide Day-12
Mastering SQL for Data Science: A Comprehensive Guide Day-12
Stored Functions & Stored Package
Stored Function
Syntax:
Create or replace function <function_name>[(argument1)[in|out|inout] datatype, (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 a procedure 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;
/