Shulou(Shulou.com)06/01 Report--

The function is mainly used to manipulate all kinds of data and return the corresponding operation results. A user-defined function is a block of code stored in a database that can return values to the caller.

The syntax of the function:

Create [or replace] function function_name [in datatype,out datatype, in out datatype]

Return datatype

Is | as

…… ..

Function:

1. The function can be called in the following parts of the sql:

Select, where, having, connect by, start with, order by, group by, insert in values, update in set.

2. Within the function, the value is returned through the return statement. The function body can have multiple return statements, but only one can be executed. An error occurs when the return statement is not executed at the end of the function.

In addition, the return statement can also be used in a stored procedure, in which case it has no parameters, and when the return statement without parameters is executed, it immediately ends and jumps out of the stored procedure, passes the current value of the formal parameter in the form of out,in out to the argument, and returns control to the calling environment.

3. There is no difference between is and as in function and storage, and the cursor is is.

4. The called function meets the following restrictions:

1) only server-side functions can be called, that is, they cannot be part of a block.

2) the function can only have input parameters, not out\ in out.

3) the function can only use the standard data types supported by sql, not the data types unique to PL/SQL.

4) DML statements cannot be included in the function.

5. There are three types of parameters of the function:

1). In parameter type: indicates the parameter input to the function. This parameter can only be used to pass values and cannot be assigned.

Eg:

Create or replace function f_test1 (h in varchar2) return varchar2 as begin-dbms_output.enable (buffer_size= > null); dbms_output.put_line (h); return h; end;select f_test1 (4) from dual

2). Out parameter type: indicates that the parameter is assigned in the function and can be passed to the function calling program. This parameter can only be used for assignment, not for passing values.

Eg:

Create or replace function f_test3 (h varchar2,b out varchar2) return varchar2 as begin bazaar dbms_output.put_line 10;-dbms_output.put_line (b); return h; end;. Call .declarev _ 1 varchar2 (10); vault2 varchar2 (10); begin v_1:=f_test3 (10); dbms_output.put_line (vault 1); dbms_output.put_line (vault2); end

3). In out parameter type: indicates that the parameter can be either passed or assigned.

Create or replace function f_test2 (h in out varchar2) return varchar2 as begin hburetor 2; dbms_output.enable (buffer_size= > null); dbms_output.put_line (h); return h; end;declarev_1 varchar2 (10): = '4potential begin v_1:=f_test2 (vault 1); end

(note: word definition functions with out parameters cannot be used in SQL statements, only in PL/SQL programs)

Example section:

1.function calls function

Create or replace function f_test4 (h varchar2) return varchar2asg varchar2 (10); f varchar2 (10); j varchar2 (10); begin g:=f_test3 (h varchar2 f); return j; end; select f_test4 (10) from dual

two。 Take the name of the right holder:

Create or replace function f_get_qlr (v_slbh varchar2) return varchar2

As

V_qlr varchar2 (50)

Begin

For i in (select q.* from dj_qlrgl gl inner join dj_qlr Q on gl.qlrid=q.qlrid where gl.qlrlx=' obligee 'and gl.slbh=v_slbh) loop

V_qlr:=v_qlr | | i.qlrmc | |','

End loop

Return substr (vandals qlr _ 0pr _ instr (vandals qlr _ r',',-1)-1)

-return v_qlr

End

By wolihaito 2018.02.20

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.