Oracle Custom Functions

One, custom function creation syntax format:Create [or replace] functionFunction_name/*Function Name*/(/*parameter Definition section*/parameter_name1 model1 dataType1, parameter_name2 model2 dataType2, Parameter_name3 model3 da     TaType3, ...) returnReturn_datatype/*defining return value types*/     is/ as    [local_declarations]/*declaring temporary variables*/    BEGINfunction_body/*function Body Part*/           returnScalar_expression/*Return Statement*/    ENDfunction_name; Parsing:or Replace: Overwrite, optional function_name: function namereturnpose: Specifies the return type of the function, cannot specify size local_declarations: local variable declaration, optional function_body: pl to be executed-There are 3 types of SQL statement parameters (if not noted, the default type of the parameter is in)inch: For read-only mode, in a function, the value of the parameter can only be referenced and cannot be changed out: write-only mode, can only be assigned, cannot be referencedinchOut: Readable and writable Example 1:--Query organization name by agency number    CREATE OR REPLACE FUNCTIONGet_org_name (p_org_idinch  Number)RETURN VARCHAR2  isV_org_nameVARCHAR2(4000); BEGIN         SELECTOrg_name intoV_org_name fromsys_orgWHEREorg_id=p_org_id; RETURN(V_org_name); ENDGet_org_name;

the calling method of the custom function call custom function is the same as the    call method of the system built-in function, can be called directly in the SELECT statement, or can be called    in the function as follows:Select get_org_name ( from sys_org;     str :=get_org_name (+); Delete Custom Function Delete    method similar to table delete, syntax format is as follows:     Dropfunction[schema. ]functionname;

Oracle Custom Functions