Definition of Oracle Functions

First, the function

Functions are stored as database objects in an Oracle database, and functions are called PL/SQL subroutines. Oracle processing uses system-provided functions, and users can define their own functions. A function is usually called as an expression to call or a parameter of a stored procedure that has a return value. Typically used to return a specific data. The function syntax is as follows:

Create or Replace function name (
Parameter name test Type,
Parameter name test type
)
Return data type
Is
Custom variable name data type
Begin
Processing statements;
return custom variable name;
exception
exception handling statements;
End

Functions are similar to stored procedures, except that a function must have a return value.

Second, examples

1. Functions without parameters

Create or Replace function test return varchar2is begin       return ' Hello World '; end test;--function call begin   DBMS_OUTPUT.PU T_line (Test ()); end

2. Functions with Input parameters

Create or Replace function get_name (  v_id number) return Varchar2is--is similar to declare v_name varchar2  (a);     Begin  Select username to v_name from person where id = v_id;  Return v_name;end get_name;--function call begin    Dbms_output.put_line (get_name (1)); end;

3, with input and output functions

Create or Replace function Get_perons_info (    f_id number,    f_age out number) return Varchar2is    v_name VARCHAR2 (50); --Must have a declaration length    of begin select username, age to v_name, f_age from person where id = f_id;    Return v_name;end get_perons_info;--function call declare    v_age number;    V_name varchar2 (255); begin    V_name: = Get_perons_info (1, v_age);    Dbms_output.put_line (' Name: ' | | v_name| | ' Age: ' | | V_age); end;

4. Functions with input and output parameters

Create or Replace function Get_person_info2 (    f_id in Out number    ) return varchar2is    v_name varchar2 (50); Begin    Select username, age to v_name, f_id from person where id = f_id;    Return v_name;end get_person_info2;--function call declare    v_id number    ;    V_name VARCHAR2 (a); begin    V_ID: = 1;    V_name: = Get_person_info2 (v_id);    Dbms_output.put_line (' Name: ' | | v_name| | ' Age: ' | | V_ID); end;

5. function return cursor

Create or Replace function Get_person_all  return sys_refcursoris    p_cursor sys_refcursor;begin    Open P_ Cursor for      select * from person  ;       return p_cursor;  Exception when others and then           dbms_output. Put_Line (' Get information error '); end Get_person_all; --Function call declare    c_cursor sys_refcursor;    R_person person%rowtype;begin  C_cursor: = Get_person_all ();  --2, open Cursor--open  c_cursor;--This does not require a cursor to be opened here, because the cursor returned when the stored procedure was called has opened the  --3, fetched the data  loop    fetch C_cursor into     R_person;    Exit when C_cursor%notfound; --when there is no data, exit    dbms_output.put_line (' ID: ' | | r_person.id);    Dbms_output.put_line (' Username: ' | | R_person.username);    Dbms_output.put_line (' Age: ' | | R_person.age);   End Loop; End

Third, function other commands

Recompile function

Delete a function

drop function name;

View the specified function

SELECT * from dba_objects where object_name = ' function name (uppercase letters) ' and object_type = ' functions ';

Definition of Oracle Functions