Differences between Oracle procedures and functions: oracle process functions

Differences between Oracle procedures and functions: oracle process 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 

What is the difference between oracle functions and stored procedures? It is best to elaborate on them.

1. The difference between return values. A function has one return value, while a stored procedure returns a value through a parameter. There may be multiple or none
2. Differences in calling. A function can be called directly in a query statement, and a stored procedure must be called independently.
A function is generally used to calculate and return a computing result, while a stored procedure is generally used to perform specific data operations (such as modifying, inserting a database table, or executing some DDL statements)

Different stored procedures of oracle Functions

The function has a return value, but the process does not,

Simply put
Function test_f (parameter in varchar2) return varchar2;
Procdrue test_p (parameter in varchar2 );

Declare
Result varchar2;
Begin
Result: = test_f; -- the function must use variables to load the returned values.
Test_p; -- no process can be called directly.
End;