PL/SQL tutorial 38: Introduction to PL/SQL functions in Oracle Database

PL/SQL tutorial 38: Introduction to PL/SQL functions in Oracle Database

What’s up internet? Welcome back once again, I am Manish from Till so far we have seen several examples
of anonymous PL/SQL blocks as well as named PL/SQL blocks such as database triggers. In today’s PL/SQL tutorial we are going
to learn the concepts of another named PL/SQL block. This is also the most requested one on my
social media. It is none other than PL/SQL Functions in
Oracle Database. Yes, this video is an introduction to PL/SQL
functions in Oracle Database. Also if you don’t know what are anonymous
and named PL/SQL blocks then I suggest you to checkout my PL/SQL tutorial 1 where I explained
the concepts of Blocks in detail. Find the link on the right hand side corner
of the screen as well as in the description. That being said let’s move ahead and start
today’s tutorial and learn what are PL/SQL functions as well as how to create them. So what are functions? We can define a 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. Actually in Oracle PL/SQL there are two types
of Subroutines or say sub-programs. These are
1. PL/SQL functions and
2. PL/SQL Procedures. The concepts of Functions and Procedures are
very closely related to each other. Where both have some similarities, they have
some differences too. We will save PL/SQL procedure for future videos
and for the time being concentrate on PL/SQL functions only. Let’s take a quick look at the syntax of
PL/SQL Functions. CREATE [OR REPLACE] FUNCTION function_name
(Parameter 1, Parameter 2…) RETURN datatype
IS Declare variable, constant etc. BEGIN
Executable Statements Return (Return Value);
END; The syntax of PL/SQL function is divided into
two parts. First is the header of the function and second
is the execution part of the function. The header consists of the signature of the
PL/SQL Function and Execution part holds all the executable statements as well as defines
the working of your PL/SQL Function. Let’s take a detailed look at this syntax. The keyword CREATE marks the beginning of
the syntax, followed by another keyword FUNCTION which tells the compiler what kind of object
we want to create. In between CREATE or FUNCTION we have an additional
clause OR REPLACE, this clause can help you in modifying the definition of an already
created function. Anyways, next we have to specify the function
name. This is completely user defined and could
be anything which follows the oracle database nomenclature rules. Followed by function name we have the parameters. A function can either have zero parameters
or n number of parameters. You just have to make sure to separate all
the parameters from each other using comma (,) and enclose them inside the parenthesis. Followed by the parameter list we have to
specify the value which the function will return using the RETURN keyword. With PL/SQL functions it is mandatory to specify
the Return value. In order to specify the return value we use
RETURN keyword followed by the datatype in the header of the function. Yes, you only have to write the datatype of
the value which you want your function to return. Then we have keyword IS. IS keyword indicates
the ending of the header of the function. Now in case you want to declare a variable
or a constant then you can do so right after the IS keyword or right before the starting
of the execution section. That too without the use of DECLARE keyword. Then we have the execution section of our
PL/SQL function. Similar to the anonymous PL/SQL block, the
keyword BEGIN marks the beginning of the execution section and the keyword END marks the ending. In between these two keywords you can write
the executable statement and define the working of the function. Do not forget to define the return statement
in the execution section. This will specify the actual values which
will be returned by your function and the datatype of this actual value must match with
the datatype that you have specified in the header of the function. That’s all you have to take care of. And that’s it about the syntax of PL/SQL
function in oracle database. I have done an excellent blog post on the
same topic. There I have discussed PL/SQL functions in
detail. If you want to read and make notes for your
college exams or Oracle database certification then do check that out. Link is in the description. That’s it for this PL/SQL tutorial on Introduction
to PL/SQL functions in Oracle database. Do make sure to subscribe as I am going to
show you the live demonstration of how to create PL/SQL Functions in Oracle database
in the next tutorial with the help of a very easy example. Also if you like this video then give it a
thumbs up. You can also help others in learning as well
as support me and my channel by sharing this video on your social media with your friends. Thanks for watching this is Manish from

Daniel Ostrander

Related Posts

14 thoughts on “PL/SQL tutorial 38: Introduction to PL/SQL functions in Oracle Database

  1. Shahrukh Qureshi says:

    awesome bro ………….. big fan of urs and plz complete pl/sql tutorials fast

  2. Ravi Bhardwaj says:

    great work man!!

  3. Preeti Walia says:

    Hi Manish thank you very much for such a interactive videos they are great platform to learn Pl/SQL and i have gone through all of them. It will be great if you will help us with exception handling in Pl/SQL.

  4. Deepika Jadhav says:

    Nice Explanation of Functions…Please add more videos related to Functions and Stored Procedures and there combined usage in packages..:)

  5. Emanuel Vald says:

    Manish thanks for upload your tutorials with subtitles!. I would like you upload a tutorial about collections PL SQL (Associative arrays, nested table and Varrays) and another one for Ref Cursors.

  6. Horps says:

    Thanks, you the man

  7. Abhishek Mishra says:

    thanks for plsql can u please upload videos for form builder and report builder

  8. Lansah Ibraheema says:

    i need triggers before and after updating

  9. Rishikesh Patahk says:

    Hi Manish can we insert update and delete data from function in oracle if yes please let us know how..

  10. hiren patel says:

    what about "IN " "OUT" AND "INOUT" option with paramiter

  11. Alekhya says:

    I watch this for fun.

  12. Pooja Sahu says:

    i want to in Hindi

  13. SMC says:

    One of my favorite YouTube programming creators I’ve found in a long time. Keep up the great work!

  14. kranti CAT says:

    thnx manish bhai thanks a lot…..

Leave a Reply

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