Functions and Stored Procedures


Just a general question

What are advantages/disadvantages of using Functions and Stored Procedures in a SQL query - just been asked?
Mark WilsonBI DeveloperAsked:
Who is Participating?
Pawan KumarDatabase ExpertCommented:
Ganesh GuruduSenior ConsultantCommented:
Parameters in Procedure and Functions

Pass the parameters to Procedures and Functions in PL/SQL
In PL/SQL, we can pass parameters to procedures and functions in three ways.

1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.

NOTE: If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter.

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

General Syntax to create a function is

CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype;  
Return return_variable;  
exception section  
Return return_variable;  
1)      Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2)      The execution and exception section both should return a value which is of the datatype defined in the header section
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.