Differences and linkages between functions and stored procedures in Oracle "reproduced Bamboo Lek Pinellia"

Differences and linkages between functions and stored procedures in Oracle "reproduced Bamboo Lek Pinellia"

In Oracle, functions and stored procedures are often used, and there are many similarities in their syntax, but they also have their own characteristics. Just finished with the functions and stored procedures, let's share with you some of the differences you've summarized about functions and stored procedures.

One, stored procedures

1. Definition

A stored procedure is a subroutine stored in a database that provides all user program calls, and the keyword defining the stored procedure is procedure.

2. Create a stored procedure

create [or Replace] procedure stored procedure name

[(parameter 1 type, parameter 2 out type ...)]

As

Variable name type;

Begin

Program Code Body

End

Example one: No reference, no return

Create or replace procedure P1--or replace means that when the stored procedure is created, if the storage name exists, replace the original stored procedure, re-create-no argument list, do not need to write () Asbegin  dbms_output.put _line (' Hello World '); end;--execute stored procedure 1set serveroutput on;begin  p1 () end;--Execute stored procedure 2set serveroutput on;execute p1 ();

Example two: There is a reference to return

Create or Replace procedure P2 (name in varchar2,age int,msg out varchar2)--The argument list, when declaring a variable type, remember that you cannot write the size, write only the type name, For example, the declaration of the name variable in the parameter list-the parameter list, the input parameter is represented by in, the output parameter is out, and the default is the input parameter when not written. ------------input parameters cannot carry the value out, the output parameter cannot carry the value comes in, when wants to carry the value to come in, wants to carry the value to go out, may use in Outasbegin  msg:= ' name ' | | name| | ', age ' | | Age;  --When assigning a value in addition to the use of: =, you can also use into to achieve  --the upper face sentence equivalent to select ' Name ' | | name| | ', age ' | | The age to MSG from dual;end;--executes the stored procedure set serveroutput on;declare  msg varchar2 (); Begin  P2 (' Zhang San ', 23,msg);  Dbms_output.put_line (msg); end;

Example three: in parameter list with in out parameter

Create or Replace procedure P3 (msg in out VARCHAR2)--When you want to carry values in and want to carry values out, you can use in Outasbegin  dbms_output.put_line (msg);- -The output is the value carried in  msg:= ' I am the value carried out from the stored procedure '; end;--execute stored procedure set serveroutput on;declare  msg varchar2 (100): = ' I am the value from the carry in '; Begin  P3 (msg);  Dbms_output.put_line (msg); end;

Example four: Defining parameters in a stored procedure

Create or replace procedure P4as  --The parameter list defined in the stored procedure  name varchar; begin  Name: = ' Hello World ';  Dbms_output.put_line (name); end;---Execute stored procedure set serveroutput On;execute P4 ();

Summary: 1. The keyword for creating the stored procedure is procedure.

2. Parameters in the parameter list can be modified with In,out,in out, the parameter type must not be write size. There can be multiple input and output parameters in the list.

3. The parameter list defined in the stored procedure does not need to be declared with declare, and it is necessary to take the size of the write size when declaring the parameter type.

4.as can be replaced with IS.

5. Calling a procedure with an output parameter must declare the variable to receive the output parameter value.

6. There are two ways to execute a stored procedure, one is to use execute and the other to wrap with begin and end.

Although there are many advantages to a stored procedure, it is not possible to return a value using return. We can use a function when we need to return a value using return.

Second, storage function

1. The function is similar to the structure of a stored procedure, but the function must have a return clause that returns the value of the function.

Create or Replace function F1return varchar--must have a return value, and you do not need to increase the small as MSG varchar when declaring a return value type  ; begin   Msg: = ' Hello World ' ';   Return msg;end;--performs function mode 1select F1 () from dual;--performs function mode 2set serveroutput on;begin   dbms_output.put_line (F1 ()); end;

Three, the difference between stored procedure and storage function and the relation

　　Same point: 1. Creating a syntax structure is similar, you can carry multiple incoming and outgoing parameters.

2. It is compiled and executed multiple times.

　　different points: 1. Stored procedure Definition Keywords with procedure, function definitions with functions.

2. You cannot return a value with return in a stored procedure, but it is possible in a function, and a return clause must be in the function.

3. The execution is slightly different, there are two ways to execute the stored procedure (1. Using EXECUTE2. Using begin and end), the function can be used as an expression in addition to the two methods of the stored procedure, such as in select (select F1 () Form dual;).

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

Differences and linkages between functions and stored procedures in Oracle "reproduced Bamboo Lek Pinellia"