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.

Types 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

CREATE [OR REPLACE] FUNCTION function_name
(Parameter 1, Parameter 2…)
RETURN datatype
IS
	Declare variable, constant etc.  
BEGIN
	Executable Statements
	Return (Return Value);
END;

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!

RELATED ARTICLESMORE FROM AUTHOR