Overview
ORACLE provides the ability to store PL/SQL programs in a database, and can run it from anywhere. This is called a stored procedure or function. Procedures and functions are collectively known as PL/SQL subroutines, which are named PL/SQL blocks that are stored in the database and exchanged with their callers through input, output parameters, or input/output parameters. The only difference between a procedure and a function is that the function always returns data to the caller, and the procedure does not return data.
first, the storage function
1. Create a function
inline functions
CREATE [OR REPLACE] FUNCTIONfunction_name[(argment [{in | in Out}]type,argment[{in | Out | in Out}]Type][AUTHID Definer | Current_User]RETURNreturn_type{ is | as }<Type. Description of the variable>BEGINFunction_bodyexception Other StatementsEND; Description:1)OR REPLACEOptional. With it, you can either create a new function or replace a function with the same name without conflicting2The function name is followed by an optional parameter list that containsinch, out orinchOut tag. The parameters are separated by commas.inchThe parameter token indicates that the value passed to the function does not change in the execution of the function; An out tag indicates that a value is evaluated in a function and passed to the calling statement by that parameter; inchthe out tag indicates that the value passed to the function can vary and be passed to the calling statement. If the tag is omitted, the argument is implicitlyinch. 3Because the function needs to return a value,RETURN
Simple no parameter function:
CREATE OR REPLACE FUNCTION Hello RETURN VARCHAR2 is -- variables/record types/cursors required during function use BEGIN RETURN ' Hello world! ' ; END;
DECLARE VARCHAR2 (+); BEGIN v_return:= hello (); Dbms_output.put_line (V_return); END;
PL/SQL Lightweight version (quad)--storage function/stored procedure