The difference between a stored procedure and a function

Different points:
1, different identifiers. The identifier of the function is the functions, and the procedure is: PROCEDURE.
2, the function has a return value, and must return, and the procedure does not return a value.
3, the procedure has no return value type, cannot assign the result directly to the variable; the function has a return value type, and when called, the return value must be assigned to the variable, except in select.
4. The function can be used directly in the SELECT statement, and the procedure cannot, for example, assume that an existing function fun_getavg () returns the absolute value of the number type. Then select Fun_getavg (col_a) from table is possible.

Same point:
Both can have a reference

Essentially no difference. Just a function like: Only one variable can be returned with a limit. A stored procedure can return more than one. Functions can be embedded in SQL and can be called in Select, and stored procedures do not work. The essence of execution is the same.
There are many function limitations, such as the inability to use temporary tables and only table variables. There are also some functions that are not available, and so on. and stored procedures are relatively less restrictive
1. In general, the function of the stored procedure implementation is a bit more complex, and the function implementation of the function is relatively strong.
2. Parameters can be returned for stored procedures, and functions can only return values or table objects.
3. The stored procedure is typically performed as a separate part (exec execution), and the function can be invoked as part of a query statement (select Call), since the function can return a Table object, so it can be located in the query statement after the FROM keyword.
4. When the stored procedure and function are executed, SQL Manager will go to the procedure cache to fetch the corresponding query statement, and if there is no corresponding query in the procedure cache, SQL Manager compiles the stored procedures and functions.
The Procedure cache holds the execution plan (execution plan), executes the execution plan in the Procedure cache when it is compiled, and then SQL Server will follow each execution Plan's actual situation to consider whether or not to save the plan in the cache, the criteria for judging is the frequency at which the execution plan might be used, and secondly, the cost of generating the plan, which is the time it takes to compile. The plan saved in the cache will not be compiled the next time it executes.

Stored procedures and functions are intended to be a collection of SQL statements that can be executed repeatedly to manipulate the database.

The difference is the notation and invocation.

notation: The parameter list of the stored procedure can have input parameters, output parameters, input and output parameters;

The parameter list of a function has only input parameters and has return < return value type, no length description >.

On the return value:

The return value of the stored procedure, which can have multiple values,

The return value of the function, with only one value.

On Call mode:

Stored procedures are called as follows:

1), exec < process name >;

2), execute < process name >;

3), called directly in the PL/SQL statement block.

Functions are called as follows:

Called directly in a PL/SQL statement block.

Specifically divided into:

----Call Function Add_three_numbers

----1. Position notation Call function

BEGIN

Dbms_output.put_line (Add_three_numbers (2,4,5));

END;

----2. Named notation Call function

BEGIN

Dbms_output.put_line (Add_three_numbers (b=>3, a=>4,c=>2));

END;

----3. Mixed use positional notation and named notation call functions

BEGIN

Dbms_output.put_line (Add_three_numbers (3, b=>4,c=>2));

END;

----4. Exclude representations

BEGIN

Dbms_output.put_line (Add_three_numbers (12,c=>2));

END;

----5. SQL invocation notation--Mixed notation

SELECT add_three_numbers (3, b=>4,c=>2) from DUAL;

----1. The function accepts 3 optional parameters, returns 3 digits and

CREATE OR REPLACE FUNCTION add_three_numbers

(

A number:=0, b number:=0, C number:=0

)

RETURN number is

BEGIN

RETURN A+b+c;

END;

　　　　　　

Stored procedures:

Basic syntax:

CREATE PROCEDURE < procedure name > (< parameter list, ignore > without parameters)

As|is

Variable declaration, initialization

Begin

Business Processing, Logic code

exception

Exception capture, fault tolerant processing

End < procedure name >;

Parameters:< Parameter name > in|out|in out < parameter type, no length description >, such as: V_name VARCHAR2

In: Enter parameter

Out: Parameter

In-Out: entry and Exit parameters

Note: As|is represents as or is

Call Syntax:

1), exec < process name >;

2), execute < process name >;

3), called directly in the PL/SQL statement block.

Cases:

Create or Replace procedure Up_wap (v_param1 in Out varchar2,v_param2 on out varchar2)

Is

V_temp varchar2 (20);

Begin

Dbms_output.put_line (' pre-swap parameter 1: ' | | v_param1| | ' parameter 2: ' | | V_PARAM2);

v_temp:=v_param1;

V_PARAM1:=V_PARAM2;

V_param2:=v_temp;

Dbms_output.put_line (' post-swap parameter 1: ' | | v_param1| | ' parameter 2: ' | | V_PARAM2);

exception

When others then Dbms_output.put_line (' There are a error when the procedure Up_wap executing! ');

End Up_wap;

/

-- call a stored procedure

Declare

V_PARAM1 varchar2 (): = ' param1 ';

V_param2 varchar2 (): = ' param2 ';

Begin

Up_wap (v_param1 = v_param1,v_param2 = v_param2);

End

/

Custom Functions (function)

Basic syntax:

Create function < function name > (< parameter list, ignore > without parameters)

Return < returned value type, no length description >

As|is

Variable declaration, initialization

Begin

Business Processing, Logic code

Return < returned value >;

exception

Exception capture, fault tolerant processing

End < function name >;

Parameters: in Parameter

Note: Only the type of the entry parameter.

The passing of parameters in stored procedures and custom functions (incoming and outbound) cannot be matched using%type or%rowtype, and null values cannot be used, but the stored procedure can return null values.

Cases:

Create function Uf_select_name_by_id_test (v_id in number)

return VARCHAR2

Is

V_name T_test.t_name%type;

Begin

Select T_name into V_name from T_test where t_id=v_id;

return v_name;

exception

When others then Dbms_output.put_line (' Error ');

End Uf_select_name_by_id_test;

/

Select Uf_select_name_by_id_test (1) name from dual; --Select called

DECLARE --pl/sql statement block call

V_name varchar2 (20);

Begin

V_name:=uf_select_name_by_id_test (1);

Dbms_output.put_line (' name = ' | | V_name);

End

/

The difference between a stored procedure and a function