Skip to main content

Stored Function in Oracle Database and it’s benefit #Diksha Oracle Training Centre

              Stored Function in Oracle Database and it’s benefit.


Hello Everyone,

In my previous blogs we discussed about stored procedures and its attributes and benefits of using Stored Procedure in Databases.Today we will discuss about Stored Function. Stored Function is also named block like Stored Procedure a stored function is a set of PL/SQL statements you can call by name. Stored functions are very similar to procedures, except that a function returns a value. Stored functions must return a value whether condition is true or false, but in Stored procedure value should return only when condition is true.  Stored function only return one value, whereas in stored procedure if condition is true it can give you multiple record as well as multiple transaction. Stored Function can be used in Procedures, Packages as well as into select statement, but procedure can’t be called in select statement. Both Stored Procedures and functions are stored in a compact compiled form. When called, they are loaded and processed immediately. Subprograms take advantage of shared memory, so that only one copy of a subprogram is loaded into memory for execution by multiple users. Procedures and functions defined within a package are known as packaged subprogram.

Creation of Stored Function is Similar to Stored Procedure where we can create , modify , run and drop with SQL command similar to what we use in Stored Procedure , only difference in stored function is we need to put RETURN data type in CREATE statement while creating the Stored function . The value returned by the function can be used directly in the DBMS_OUTPUT.PUT_LINE Statement.

Before we go to Syntax and example of Stored function , Please check my previous blogs for Stored procedures to understand easily as in Stored function we use same arguments like IN , OUT and attributes like %TYPE , %ROWTPE , Cursor etc. All this important parts mentioned in below blogs.

Syntax for Stored Function is as below

CREATE OR REPLCE FUNCTION function_name

   RETURN datatype

   IS variable name data type

DECLARE

   <declarations section>

BEGIN

   <executable command(s)>

EXCEPTION

   <exception handling>

RETURN variable name

END;

 

Here you see it’s very similar block structure as we use in Stored Procedure only thing we need to include RETURN data type in CREATE statement while creating the Stored function . The value returned by the function can be used directly in the DBMS_OUTPUT.PUT_LINE Statement by Execute command or we can also call this in SQL statement:

Simple Stored Function Example where we pass emp_id and get the salary:

CREATE OR REPLACE FUNCTION get_salary(p_empid IN NUMBER)

   RETURN NUMBER

   IS v_salary NUMBER;

   BEGIN

      SELECT salary

      INTO v_salary

      FROM employee

      WHERE emp_id = p_empid;

      RETURN(v_salary);

    END;

/

 

Here the get_salary function returns the salary of a specified emp_id given by user.When you call the function, you must specify the argument account_no as we put it in IN parameter, The datatype of emp_id is NUMBER. The function returns the salary for particular employee id passed by user. The RETURN clause of the CREATE FUNCTION statement specifies the datatype of the return value to be NUMBER.

 

The function created above can be used in a SQL statement.

Example:

SELECT get_salary(10002) FROM DUAL;

This will give you one result as Function return only one value;

It will give you the salary for that particular employee id. This is the main benefits of Stored  function is that  we can it in select statement but Stored Procedure cannot be called in Select statement.

 

Another way to execute Stored Function

DECLARE

    l_salary NUMBER;

BEGIN

    l_salary := get_salary(2000);

    DBMS_OUTPUT.PUT_LINE('Salary of given employee id is '|| l_salary);

END;

This statement will also give you one result as Function return only one value.

Output of the above Stored Function is as below:



Removing Function we use Drop Statement as below

DROP FUNCTION get_salary;


Another Example of Stored Function:

Creating function  to get_address from employee and employee_details table against empid

 

Create or replace FUNCTION get_address(v_empid IN NUMBER)

   RETURN VARCHAR2

   IS address_details VARCHAR2(160);

   BEGIN

 SELECT a.name||' '|| a.address||' '|| b.city ||' '||b.state||' '||b.country

  INTO address_details

  FROM employee a , employee_details b

  WHERE a.emp_id = b.emp_id

   AND a.emp_id = v_empid;

      RETURN(address_details);

    END get_address;

/

 

In above stored function we pass v_empid  as IN Parameter ,We can call this function by using both select statements as well as call it from dbms_output.put_line  and also by using EXECUTE command.

SELECT get_address(2000) FROM DUAL;

This will give you only 1 output against the emp_id

Another way to execute this function

DECLARE

    l_adress varchar2(160);

BEGIN

    l_adress := get_address(2000);

    DBMS_OUTPUT.PUT_LINE('Details are '|| l_adress);

END;

This will also give you one result as function will give you only 1 result.

Output of above Stored Function is as below:


Removing Function we use Drop Statement as below

DROP FUNCTION get_address;

Now we are able to understand Stored Function must return a value and can be used in select statement or we can execute Stored Function by execute statement.


Note: We can call Stored Function in Oracle packages as we can use it for reusability as it stored in database same like stored Procedures.

·       The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values.

·        Functions can have only input parameters for it whereas Procedures can have input or output parameters.

·        Functions can be called from Procedure whereas Procedures cannot be called from a Function.

As you all understand both Stored Procedure and Stored function, Please try to write both stored Procedures and Functions as much as you can so that it’s easy for you to understand Oracle Package where we can use both Procedures and functions.

Please read this blog carefully and let me know if you have any doubts on creating stored function. Also in interview lots of question will be asked from Stored Procedures and Functions. So you can explain easily now with some practical examples as you seen some differences and similarities between Stored Procedure and Stored Function. In next Blog I will come up with some more examples and differences between them.

 

 Thanks

Comments

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger etc. SQL allows

Materialized View uses and Benefits in Database #DOTC_Mdb

Materialized View uses and Benefits in Database. Hello Everyone, Today we will discuss about Materialized view as it’s play important role in database. We already discussed about Simple Views and complex views in my previous blog. Before Materialized view go through my previous blog which related to simple view. https://dheeraj60.blogspot.com/2020/05/benefits-of-creating-and-using-view-in.html As we know View is not a database object and not like table which is stored in database, but view can be created from base table a view is an SQL statement that’s stored in the database. This statement, or view, has a name.A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table. In other word we can say View is a virtual/logical table which is basically used for security purpose. Let’s understand   about   Materialized view : A materialized view is a view that stores the results of the view’s query. Whenever you query the ma

Top 50 Interview Questions On SQL and PL/SQL #DOTC_Mdb

                    Top 50 Interview Questions On SQL and PL/SQL. Today we will Discuss Top 50 interview questions and answers of SQL and PL/SQL which is frequently asked in interview.     Question 1: What is SQL and Classification of SQL? Answer SQL is a Structure Query Language which is vastly used in RDBMS database like Oracle, Sybase, DB2 , Microsoft SQL server etc.   Classification of SQL is as below: DDL (Data Definition Language):  Commands are  create , alter , drop , truncate etc DML (Data Manipulation Language) : Commands are  insert,update and delete . TCL (Transaction Control Language ) : Commands are  Commit , Rollback and Save point. DCL (Data Control Language) : Commands are Grant , Revoke Question 2:    What is meant by Joins? What are the types of join? Answer Joins are basically used to extract/get data from multiple tables using some common columns or conditions and also by using alias to fulfill the condition.   There are various types of Joins as li