Oracle Learning (10) user-defined functions

A user-defined function is a block of code stored in a database that can return a value to the calling program. There are 3 types of arguments for a function:

(1) in Parameter type: Represents an argument entered into a function that can only be used to pass a value and cannot be assigned a value.

(2) out parameter type: Indicates that the parameter is assigned in the function and can be passed to the function call program, which can only be used for assignment and not for passing values.

(3) in out parameter type: Indicates that the parameter can either pass a value or be assigned a value.

1. Creation of functions

Syntax format:

    create [or replace] function functionname  
    (  
        parameterName1 mode1 dataType1,  
        parameterName2 mode2 dataType2 ,  
        ...  
    )  
    return returndatatype  
    is/as
    begin
        Function_body return  
        expression end  
    functionname;-- To end the declaration of a function, you can also write it directly without the function name.  
-where Mode1, Mode2 represents the parameter type, and datatype represents the data type of the parameter. Returndatatype represents the return value type.

Example 1:

Create or Replace function Explainparameter--defines a function named Explainparameter  
(Inparam in  
    char,-  The parameter type is defined as the in parameter type and can only be used for assignment  
    outparam out char,  --The Out  parameter type, which can only be used to pass the value Inandoutparam in out  
    char  Out parameter type, both assignable and value-     return CHAR--  represents a function that returns a type of char as--representing the part of the      function body  
    Returnchar char;        --  declaring a zero variable, which is optional, declares the return variable to the Returnchar variable of type char

.  
begin
    Inparam: = ' Hello world ';   -This is wrong, in-type arguments can only be used to pass values, cannot be assigned  
    outparam: = ' Hello World ';--this is true, an out type of argument may be used to be assigned  
    inandoutparam: = ' Hello World '; --This is correct, in out parameters can be used to transfer values, but also can be assigned  
    inandoutparam: = Outparam;--This is wrong, out parameters can not be used to transfer value return  
    (Returnchar); Return Returnchar, or do not have parentheses, write back directly to Returnchar.  
End Explainparameter--ends the Explainparameter function, indicating that the Explainparameter function declaration ends, or it can

write the end directly, without the function name.

Example 2:

CREATE OR REPLACE 
FUNCTION testfunc (num1 in number, num2 in number) return number as  
num3 number
    ;  
    NUM4 number;  
    NUM5 number;  
BEGIN
    num3: = Num1 + num2;  
    NUM4: = Num1 * NUM2;  
    NUM5: = num3 * NUM4;  
    return NUM5;  
End;

2. Call to function

The calling method of a custom function is the same as the calling method of the system built-in function, which can be called directly in the SELECT statement or in a function, as follows:

Select TestFunc (1,2) from TableName;

Num: = TestFunc (1,2);

3. Deletion of functions

The deletion of a custom function is similar to the deletion of a table, and the syntax format is as follows:

Drop function [schema.] functionname;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/