What is the difference between stored functions and stored procedures in oracle

In this issue, the editor will bring you about the difference between stored functions and stored procedures in oracle. The article is rich in content and analyzes and describes it from a professional point of view. I hope you can get something after reading this article.

One: stored procedure: simply speaking, it is a named pl/sql block.

Grammatical structure:

Create or replace stored procedure name (parameter list) is-define variable begin-pl/sql end

Case study:

Create or replace procedure add_ (an int,b int) is c int; begin dbms_output.put_line (c); end

Call stored procedure

Declare begin add_ (126.34); end

There are three types of parameters for stored procedures:

Input parameters (default) in

Output parameter out

Input and output parameter in out

Function definition

Grammatical structure:

Create or replace function function name (argument list) return type is begin end

Case study:

Create or replace function F1 (N1 dec,n2 dec) return dec is r dec (19Magin2); begin rsaw return r; exception when zero_divide then dbms_output.put_line ('divisor cannot be 0'); return 0; end

The difference and relationship between stored procedures and stored functions:

Similarities: 1. The creation syntax is similar, and both can carry multiple incoming and outgoing parameters.

two。 All are compiled at one time and run many times

Differences: 1. Procedure for stored procedure definition keywords and function for function definition

two。 You cannot return a value with return in a stored procedure, but you can in a function, and there must be a return return in the function.

3. The mode of execution is slightly different, and there are two ways to execute stored procedures (1. Use execute;2. Use begin and end) functions except

Two ways of stored procedures can also be used as expressions, such as putting them in select (select F1 () from dual;)

Summary: if there is only one return value, use a stored function, otherwise, generally use a stored procedure.

These are the differences between stored functions and stored procedures in oracle shared by Xiaobian. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.