Let’s get started now…
The topics which will be covered in this tutorial:
- Introduction of Function
- Creating Function in PL/SQL
- Calling a function in PL/SQL
- FAQS (Frequently Asked Questions)
Basic introduction of Function in PL/SQL
PL/SQL functions are not very different from PL/SQL Procedures. Only one difference is there that function is returning the values. So, what we learn in the previous article of PL/SQL Procedures, will also correct for function.
How to Create Functions in PL/SQL
We can create a PL/SQL function by using the CREATE FUNCTION statement. The syntax of the Creation of function is as follows −
CREATE [OR REPLACE] FUNCTION function_name (parameter_name [IN | OUT | IN OUT] datatype [, ...]) RETURN return_datatype IS BEGIN < body > END [function_name];
- Here, the function-name refers to the function’s name.
- Here, the [OR REPLACE] option allows us to modify the existing function.
- The parameter_name, as we already know which passes in the function. It can be input or output type or both IN OUT.
- The PL/SQL functions are must contain a return statement which returns the datatype.
- The body contains the executable part where we are doing the queries and other kinds of stuff.
- A Function can also return the value through OUT parameters other than using RETURN.
CREATE OR REPLACE FUNCTION functionExample RETURN number IS vtotalno number() := 0; BEGIN SELECT count(*) into vtotalno FROM virtualtable; RETURN vtotalno; END; /
How to Call a Function in PL/SQL
When we are creating a function at that time, we know what the purpose of the function is. Where we are going to use that whether in standalone or somewhere in a procedure.
To call a function in the to declare block, we need to pass the necessary parameters among function_name, and if the function returns a value, we can store the value. The below- mentioned program calls the function example from an unnamed block −
DECLARE vnumber number(10); vtotalcost number(5); BEGIN SELECT t.cost into vtotalcost FROM virtualtable t; vnumber := totalempollyee(); if(vnumber > vtotalcost ) then dbms_output.put_line('Total no. of empollyee: ' || vnumber); end if; dbms_output.put_line('Total cost. of empollyee: ' || vtotalcost ); END; /
To call a function in the to a procedure, we need to pass the necessary parameters through the function name, and if the function returns a value, then we can store the value. The below- mentioned example calls the function example −
CREATE OR REPLACE PROCEDURE printContact( vcustomerid IN NUMBER default NULL; ) IS r_contact varchar2:=null; vnumber number:=0; BEGIN SELECT t.contactno INTO r_contact FROM contacts t WHERE t.customer_id = vcustomerid; if( r_contact = null) then vnumber := totalCustomers(); end if; EXCEPTION WHEN OTHERS THEN dbms_output.put_line( SQLERRM ); END;
- Basic Introduction of Function in PL/SQL
- How to Create a Function in PL/SQL
- How to Call a Function in PL/SQL
- Calling a function in declare block
- Calling a function in a procedure.
What are the characteristics of Function?
A function can allow passing one or more than one parameter or no parameter.
What is a translate function in Oracle?
A translate function in PL/SQL executes a character-wise replacement of a string.
Can we use UPDATE FOR instead of rowid in SQL?
Yes, we can use UPDATE FOR for updating or inserting a row or value manually in the SQL query.
What is the Key difference between Function and Procedure?
A FUNCTION must always have a return statement which returns a value while a PROCEDURE can return one or more than values through parameters or can not return any.
In the above, we have discussed the Function in PL/SQL with suitable examples; also, we have mentioned some Faqs related to Oracle PL/SQL.
I hope you guys are like this one. For any query, please feel free to contact us.
Thank you 🙂