Detailed explanation of stored functions and stored procedures in Oracle

In Oracle, functions and stored procedures are often used. There are many similarities in their syntax, but they also have their differences. This article mainly introduces the difference between stored functions and stored procedures in Oracle. It is very good and has reference value. Friends who need it can refer to it. I hope it can help everyone.

I just finished learning functions and stored procedures during this period, so let me give myself a summary:

1: Stored procedures: Simply put, they are named pl/sql blocks.

Grammar structure:

create or replace 存储过程名(参数列表)
  is
   --定义变量
  begin
   --pl/sql
  end;

Case:

create or replace procedure add_(a int,b int)
  is 
   c int;
  begin
   c:=a+b;
   dbms_output.put_line(c);
  end;

Call Stored procedure

 declare
  begin 
   add_(12,34);
  end;

Three types of parameters for stored procedures:

Input parameters (default) in

Output parameters out

Input and output parameters in out

Function definition

Syntax structure:

 create or replace function 函数名(参数列表) return 类型
   is
   begin
   end;

Case:

##

create or replace function f1(n1 dec,n2 dec) return dec
   is
    r dec(19,2);
   begin
    r:=n1/n2;
    return r;
   exception
    when zero_pide then
     dbms_output.put_line('除数不能为0');
     return 0;
   end;

The difference and connection between stored procedures and stored functions:

Same points: 1. The syntax structure of creation is similar, both can carry multiple passes Incoming parameters and outgoing parameters;

2. They are compiled once and run multiple times;

Differences: 1. The stored procedure definition keyword uses procedure, and the function definition uses function;

2. You cannot use return to return a value in a stored procedure, but you can use it in a function, and there must be a return return in the function;

3. The execution method is slightly different. There are two ways to execute a stored procedure. (1. Use execute; 2. Use begin and end) In addition to the two ways of storing procedures, the function can also be used as an expression, for example, placed in select (select f1() from dual;)

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

Related recommendations:

How to use the decode function in oracle

How to change the data file location of the table space in Oracle

Detailed explanation of Oracle's common function Trunc

The above is the detailed content of Detailed explanation of stored functions and stored procedures in Oracle. For more information, please follow other related articles on the PHP Chinese website!