In my previous article I have given the details about the procedures and how to execute the parameterized procedures in detail. In this article I would like to throw light on what are user defined functions in PL/SQL and the key interview question asked in PL/SQL interviews i.e. the difference between stored procedures and functions with examples. The procedures and functions are basic building blocks of PL SQL. There is always a question in interview about difference between procedure and function with examples.
This article contains :
- What are functions in PL/SQL with example?
- What are procedures in PL/SQL with example?
- Difference between procedure and function with example.
What are PLSQL functions with example?
The PL SQL functions are nothing but the named PL/SQL blocks which will used to return the specified value. The functions always returns the value.
There are two types of PL SQL functions :
- In Built functions : These are built in functions by Oracle SQL or PL SQL engine.
- User Defined Functions : These are basic building blocks which will return the value created by developers.
The functions are stored in the Schema as database objects.
You can call the function as expression or it is used to provide the parameter value.
Create function function_name
Real life example :
You can create a function to return the salary of employee.
create or replace function F_get_sal
select salary into v_sal
What are procedures with real life example?
I have already given the details about the procedure and execution of the procedure. In this section I just want to throw light on what is mean by procedure.
The PL/SQL procedure is basic building block which is used to perform any action.
Just like a function it also can be stored as database object named procedure.
The key features of procedure is reusability and maintainability.
There are three types of parameters which you can use to call the procedures in PLSQL i.e. IN,OUT,INOUT
You can execute procedure in anonymous block or using exec keyword.
What is difference between procedure and function :
|Procedures are basic PL SQL blocks to perform a specific action.||Functions are blocks used mainly to perform the computations.|
|Procedures will not return the value||Functions must return the value. When you are writing functions make sure that you can write the return statement.|
|Procedures always executes as PL SQL statement||Functions executes as part of expression|
|It does not contain return clause in header section||It must contain return clause in header|
|We can pass the values using IN OUT IN OUT parameters||Function must return a single value|
|Procedures can not be executed in Select statement||Functions can execute or call using select statement but it must not contain Out or IN OUT parameters.|
|Example : CREATE OR REPLACE PROCEDURE P_UPDATE_SAL (P_EMP_ID IN NUMBER, P_AMOUNT IN NUMBER)|
IS –here you define variables — n number;
UPDATE employees set salary=salary+P_AMOUNT where employee_id=P_EMP_ID;
WHEN OTHERS THEN
|Example : create or replace function P_get_sal (p_emp_id number)|
return number is v_sal number;
select salary into v_sal from employees where employee_id=p_emp_id; return v_sal;
These are few difference between procedures and function in PL SQL. I hope you get clear idea about difference between Procedure and function in PL SQL with real examples. If you like this article or if you have any concerns with the same kindly comment in comments section.