Differences between Oracle procedures and functions

Oracle process and function are expected to be more familiar. The following describes the differences between Oracle process and function. We hope that you can have a deeper understanding of Oracle process and function.

Oracle processes and functions are stored in the database in the form of compilation. functions can have either no parameter or multiple parameters and return a value. The process has zero or multiple parameters and no return value. Functions and processes can receive or return zero or multiple values through the parameter list. The main difference between a function and a process is not the return value, but the call method. The Oracle process is called as an independent execution statement:

 
 

  
  
pay_involume(invoice_nbr,30,due_date); 
 
 

The function is called using a valid expression:

 
 

  
  
order_volumn:=open_orders(SYSDATE,30); 
 
 

The syntax of the creation process is as follows:

 
 

  
  
CREATE [ OR REPLACE] PROCEDURE [schema.]procedure_name  
  
  
[parameter_lister]  
  
  
{AS|IS}  
  
  
declaration_section  
  
  
BEGIN  
  
  
executable_section  
  
  
[EXCEPTION  
  
  
exception_section]  
  
  
END [procedure_name]   
 
 

The syntax for each parameter is as follows:

 
 

  
  
paramter_name mode datatype [(:=|DEFAULT) value] 
 
 

There are three modes: IN, OUT, And INOUT.

IN indicates that during the call process, the actual parameter value is passed to the process. The formal parameter is considered read-only. When the process ends, the control will return to the control environment, the actual parameter value does not change.

When an out api is called, the actual parameter values are ignored. In the process, parameters can only be assigned values, but cannot be read from them, after the process ends, the content of the formal parameter is assigned to the actual parameter.

The INOUT mode is a combination of IN and OUT. The value of the actual parameter IN the Oracle process is passed to the form parameter, and the value of the situation parameter is readable and writable. After the process ends, the value of the situation parameter is assigned to the actual parameter.

The syntax for creating a function is basically the same as that for a process. The only difference is that a function has a RETUREN clause.

 
 

  
  
CREATE [ OR REPLACE] FINCTION [schema.]function_name  
  
  
[parameter_list]  
  
  
RETURN returning_datatype  
  
  
{AS|IS}  
  
  
declaration_section  
  
  
BEGIN  
  
  
executable_section  
  
  
[EXCEPTION]  
  
  
exception_section  
  
  
END [procedure_name]   
 
 

Some functions must have multiple return statements.

You can call single-row and group functions in creating a function. For example:

 
 

  
  
CREATE OR REPLACE FUNCTION my_sin(DegreesIn IN NUMBER)  
  
  
RETURN NUMBER  
  
  
IS   
  
  
pi NUMBER=ACOS(-1);  
  
  
RadiansPerDegree NUMBER;  
  
  
 
  
  
BEGIN  
  
  
RadiansPerDegree=pi/180;  
  
  
RETURN(SIN(DegreesIn*RadiansPerDegree));  
  
  
END   
 
 

 

Use of Oracle to_char Functions

Implementation of oracle function return table

Learn about the Oracle FBI Index

How to uninstall an Oracle database in Windows

Multiple table Connection Methods in Oracle