Difference between Procedure and Function ?
- A procedure is a named PL/SQL block which performs one or more specific task.This is similar to a procedure in other programming languages.
- A procedure has a header and a body.
- The header consists of the name of the procedure and the parameters or variables passed to the procedure.
- The body consists declaration section, execution section and exception section similar to a general PL/SQL Block. A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.
We can pass parameters to procedures in three ways
| Parameters |
Description |
| IN type |
These types of parameters are used to send values to stored procedures |
| OUT type |
These types of parameters are used to get values from stored procedures. This is similar to a return type in functions. |
| IN OUT type |
These types of parameters are used to send values and get values from stored procedures. |
Syntax
[pastacode lang=”markdown” manual=”CREATE%20%5BOR%20REPLACE%5D%20PROCEDURE%20procedure_name%20(%3CArgument%3E%20%7BIN%2C%20OUT%2C%20IN%20OUT%7D%20%20%20%20%3CDatatype%3E%2C%E2%80%A6)%20%20%0AIS%20%0A%20%20Declaration%20section%3Cvariable%2C%20constant%3E%20%3B%20%0ABEGIN%20%0A%20%20Execution%20section%20%0AEXCEPTION%20%0A%20%20Exception%20section%20%20%0AEND” message=”” highlight=”” provider=”manual”/]
- 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.
Syntax
[pastacode lang=”markdown” manual=”CREATE%20%5BOR%20REPLACE%5D%20FUNCTION%20function_name%20%5Bparameters%5D%20%20%0A%20RETURN%20return_datatype%3B%20%20%7BIS%2C%20AS%7D%20%0A%20Declaration_section%20%3Cvariable%2Cconstant%3E%20%3B%0A%20BEGIN%20%20%20%0A%20%20%20%20Execution_section%20%0A%20%20%20%20Return%20return_variable%3B%20%20%20%0A%20EXCEPTION%20%0A%20%20%20exception%20section%20%20%20%0A%20%20%20%20Return%20return_variable%3B%20%20%0A%20END%3B” message=”” highlight=”” provider=”manual”/]
| Procedure |
Function |
| Used mainly to a execute certain process. |
Used mainly to perform some calculation. |
| Cannot call in SELECT statement. |
A Function that contains no DML statements can be called in SELECT statement. |
| Use OUT parameter to return the value. |
Use RETURN to return the value. |
| It is not mandatory to return the value. |
It is mandatory to return the value. |
| RETURN will simply exit the control from subprogram. |
RETURN will exit the control from subprogram and also returns the value. |
| Return datatype will not be specified at the time of creation. |
Return datatype is mandatory at the time of creation. |
