Introduction to PL/SQL functions in oracle database

By Manish Sharma

PL/SQL Functions In Oracle Database

To anyone, who has ever studied programming languages like C, C++ or Java, the concept of functions isn’t new. Functions are nothing but a group of executable statements. Using Functions you can save yourself from re-writing the same programming logic again and again. So how can we define a function in Oracle PL/SQL?

What are PL/SQL functions in Oracle Database?

In Oracle Database we can define a PL/SQL function as a self-contained sub-program that is meant to do some specific well defined task. Functions are named PL/SQL block which means they can be stored into the database as a database object and can be reused. That is also the reason why some books refer to PL/SQL functions as stored functions.

Type of PL/SQL functions in Oracle Database

There are two types of PL/SQL functions in Oracle Database, these are

  1. Pass-by-Value Functions and
  2. Pass-by-Reference functions

In Oracle Database both types of functions should have to return some values and these values should be a valid SQL or PL/SQL datatype.

Syntax of PL/SQL Functions in Oracle Database

 (Parameter 1, Parameter 2…)
 RETURN datatype
  Declare variable, constant etc. here.
  Executable Statements
  Return (Return Value);
I have discussed the PL/SQL function’s syntax line by line in the video tutorial on my YouTube channel on the same topic. I suggest you to go and check out that tutorial once.

Function Execution Method

Depending on your creativity and programming skills, a PL/SQL function can be called by multiple ways. Here are some general ways of calling a PL/SQL function in Oracle Database

  1. You can use SQL*Plus utility of the Oracle Database to invoke a PL/SQL function that can be called from PL/SQL as procedural statement.
  2. An anonymous PL/SQL block can also be used to call a function.
  3. You can even call a function directly into a SELECT or DML statement.
Stay tuned we will discuss each of these execution methods of PL/SQL functions in the next tutorial.

Restrictions on calling a function

  1. A function that returns SQL datatype can be used inside SQL statement and a PL/SQL function that returns PL/SQL datatype only works inside PL/SQL blocks. An exception to this rule is that, you cannot call a function that contains a DML operation inside a SQL query. However you can call a function that performs a DML operation inside INSERT, UPDATE and DELETE.
  2. A function called from an UPDATE or DELETE statement on a table cannot query (SELECT) or perform transaction (DMLs) on the same table.
  3. A function called from SQL expressions cannot contain the TCL (COMMIT or ROLLBACK) command or the DDL (CREATE or ALTER) command
That’s it for this tutorial on Introduction to PL/SQL Functions in Oracle Database. Do make sure to check out the next tutorial where I demonstrate the creation of PL/SQL function using a very simple example.

Also do make sure to share this blog on your social media as I am giving away RebellionRider’s merchandise to one of the randomly selected winner every month. For further updates follow me on my Facebook and Twitter.
Thanks for reading & have a great day!

SQL Script and Presentation used

You can DOWNLOAD SQL script and presentation used in the Video and in this article.

NO SQL Script used in Video and in this article

Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE

  • Manish Sharma Oracle Rebellion Rider
  • Manish Sharma Oracle certified SQL expert
  • Manish Sharma oracle certified associate
  • Manish Sharma oracle certified professional
  • View Manish Sharma's profile on LinkedIn

         View Manish Sharma's profile on LinkedIn