Oracle function creation and invocation

The syntax for creating a function is as follows:

CREATE [OR REPLACE] FUNCTION function_name
[(argment [{in | Out | In Out}] Type,
argment [{in | Out | In Out}] Type]
RETURN Return_type
{is | As}
< type. Description of the variable >
BEGIN
Function_body
EXCEPTION
Other statements
END;

Cases:

1 CREATE OR REPLACE FUNCTIONGet_salary (2Dept_no Number, Emp_count out Number)3  　　 RETURN  Number 4  　　  is5V_sum Number;6 BEGIN7  　　 SELECT SUM(SAL),Count(*) intoV_sum, Emp_count8  　　  fromEmpWHEREDeptno=Dept_no;9  　　 RETURNv_sum;Ten  　　 EXCEPTION One  　　　　  whenNo_data_found Then  ADbms_output. Put_Line ('The data you need doesn't exist!'); -  　　　　  whenOTHERS Then  -Dbms_output. Put_Line (SQLCODE||'---'||sqlerrm); the ENDGet_salary;

Calling function methods
The parameters defined by the function declaration are called formal parameters, and the arguments passed to the function when the application is called are called actual parameters. When an application calls a function, it can pass parameters to the function in the following three ways:

The first parameter passing format is called positional notation, in the form of:
Cases:

1 DECLARE2V_num Number;3V_sum Number;4 BEGIN5V_sum:=Get_salary ( -, v_num);6Dbms_output. Put_Line ('Total salary of unit 30th:'||V_sum||', number of persons:'||v_num);7 END;

The second type of parameter passing format is called name notation, in the following format:
Cases:

1 DECLARE2V_num Number;3V_sum Number;4 BEGIN5V_sum:=Get_salary (Emp_count=V_num, Dept_no=  -);6Dbms_output. Put_Line ('Total salary of unit 30th:'||V_sum||', number of persons:'||v_num);7 END;

The third parameter passing format is called mixed notation:
Cases:

1 DECLARE 2  　　 Var VARCHAR2( +);3 BEGIN4  　　 Var:=Demo_fun ('User1', -, sex= 'male');5Dbms_output. Put_Line (var);6  　　 Var:=Demo_fun ('User2', age=  +, sex= 'male');7Dbms_output. Put_Line (var);8  　　 Var:=Demo_fun ('User3', sex= 'female', age=  -);9Dbms_output. Put_Line (var);Ten END;

Parameter Default value
You can specify a default value for an input parameter using the Defaults keyword when declaring a function argument in the Create OR REPLACE function statement.
Cases:

1 CREATE OR REPLACE FUNCTIONDemo_fun (2NameVARCHAR2, VageINTEGER,3SexVARCHAR2 DEFAULT 'male')4  　　 RETURN VARCHAR2 5  is6V_varVARCHAR2( +);7 BEGIN8V_var:=Name||':'||To_char (age)||'years,'||sex;9  　　 RETURNV_var;Ten END;

After a function that has a default value is created, the function uses the default value of the parameter when it is called, if no actual parameter value is supplied for the parameter with the default value.
But when the caller provides the actual arguments for the default arguments, the function uses the actual parameter values.
When you create a function, you can only set default values for input parameters, and you cannot set default values for input/output parameters.
Cases:

1 DECLARE 2  　　 Var VARCHAR( +);3 BEGIN4  　　 Var:=Demo_fun ('User1', -);5Dbms_output. Put_Line (var);6  　　 Var:=Demo_fun ('User2', age=  +);7Dbms_output. Put_Line (var);8 　　 Var:=Demo_fun ('User3', sex= 'female', age=  -);9Dbms_output. Put_Line (var);Ten END;

You can use the drop statement to delete a function:
DROP FUNCTION function_name;

Authorizing execution to the relevant user or role
Grant Syntax:
GRANT System_privilege | Role
to user | Role | public [with ADMIN OPTION]

GRANT Object_privilege | All on Schema.object
to user | Role | public [with GRANT OPTION]
Cases:

GRANT EXECUTE  on  to  Public  with GRANT OPTION

Procedure-related permissions:
CREATE any PROCEDURE
DROP any PROCEDURE

Data dictionary related to process
User_source, User_procedures, user_errors

Oracle function creation and invocation