A user-defined function is a code block stored in a database. It can return the value to the calling program. The call is like a system function, such as a max (value) function. value is called a parameter. There are three types of function parameters. In parameter type: indicates the parameter input to the function. Out parameter type: indicates that the parameter is assigned a value in the function and can be passed to the function calling program. In out parameter type: indicates that a parameter can be either passed or assigned a value. 1. Syntax format: Create or replace function function_name/** // * function name */
(
Parameter_name1, mode1 datatype1,/** // * Parameter definition section */
Parameter_name2, mode2 datatype2,
Parameter_name3, mode3 datatype3
...
)
Return return_datatype/** // * defines the return value type */
Is/
Begin
Function_body/** // * function body part */
Return scalar_expression/** // * return statement */
End function_name;
Description: function_name: User-Defined Function Name. The function name must comply with the rules defined by the identifier. For its owner, the name is unique in the database. Parameter: User-defined parameter. You can define one or more parameters. Mode: parameter type. Datatype: Data Type of user-defined parameters. Return_type: indicates the data type returned by the user. The function returns the value of the scalar_expression expression. The function_body function is composed of PL/SQL statements. 2. Sample Function Code: Create or replace function t01001_count
Return number
Is
Count_t01001 number;
Begin
Select count (*) into count_t01001 from t01001;
Return (count_t01001 );
End t01001_count; -- Remember to score the number.
Call:
Declare
I number;
Begin
I: = t010010000count ();
Dbms_output.put_line (to_char (I ));
End; -- Remember to score the number
Note: (1) if the function does not have a parameter, no parentheses should be placed after the function name; (2) Remember to write the function name after the end when creating the function;
