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

Stored Function

 

A function, like a procedure is a set of PL/SQL statements that form a subprogram. The subprogram is designed and cleared to perform a specific operation on data. A function takes zero or more input parameters and returns just one output value.

    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:

PLSQL 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 varchar2s 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;

/

Leave a Reply

Your email address will not be published. Required fields are marked *