Examples of stored functions and stored procedures in oracle

This article mainly introduces the examples of stored functions and stored procedures in oracle, which has a certain reference value. Interested friends can refer to it. I hope you will gain a lot 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_pide 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.

Thank you for reading this article carefully. I hope the article "examples of stored functions and stored procedures in oracle" shared by the editor will be helpful to you. At the same time, I also hope that you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!