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
- Pass-by-Value Functions and
- 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
- 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.
- An anonymous PL/SQL block can also be used to call a function.
- 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
- 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.
- A function called from an UPDATE or DELETE statement on a table cannot query (SELECT) or perform transaction (DMLs) on the same table.
- 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!