Oracle's functions

This theme green/color

End of function Be sure to return a value that matches the declaration using the return statement

--Syntax:

Create[or Replace] function< function name > [(parameter list)]

Return data type

Is|as (is or as is fully equivalent)

[local variable declaration]

Begin

PL/SQL function body

end[< function name;]

--Function without parameters

Create or Replace function GetCount
return number
As v_num number;

Begin
Select COUNT (*) into v_num from V_emp;
return v_num;
End

--Call Function 1
Select GetCount () from dual;
--Call Function 2 PLSQL statement block
Declare
Num number;
Begin
Num: = GetCount ();
Dbms_output.put_line (num);
End

--functions with an in parameter, in default, can be called using the SELECT statement and the PLSQL statement block

Create or Replace function GetName (v_name varchar2)
return VARCHAR2
As
V_person V_emp%rowtype;
V_str varchar2 (100);
Begin
SELECT * into V_person from v_emp where ename = V_name;
V_str: = ' current person is ' | | v_person.ename| | ' wages are ' | | V_person.sal;
return v_str;
End

--Invoke Function SELECT statement

Select Getpersonbyname (' SMITH ') from dual;

--Call Function PLSQL statement block

Declare
A_name VARCHAR2 (50);
Begin
A_name: = GetName (' SMITH ');
Dbms_output.put_line (A_name);
End

--function functions with out parameters with out parameters can only be called using the PLSQL statement block

Create or Replace function getsal (p_name varchar2,e_sal out number)
return VARCHAR2
As
V_st varchar2 (100);
Begin
Select Sal into E_sal from V_emp where ename=p_name;
V_st: = p_name| | ' Open every month ' | | e_sal| | ' Yuan ';
return v_st;
End

--Call Function PLSQL statement block

Declare
V_str varchar2 (20);
V_sal number;
Begin
V_STR: = Getsal (' SMITH ', v_sal);
Dbms_output.put_line (V_STR);
Dbms_output.put_line (v_sal);
End

--a function with an out parameter also has an out parameter function that can only be called by the PLSQL statement block

Create or Replace function swap (num1 in out number,num2)
return VARCHAR2
As
Temp number;
Begin
Temp: = NUM1;
NUM1: = num2;
NUM2: = temp;
Return ' ABC ';
End

--Call function

Declare
NUM1 number: = 10;
NUM2 number: = 20;
V_str varchar2 (20);
Begin
Dbms_output.put_line (num1| | ' ========== ' | | NUM2);
V_STR: = Swap (NUM1,NUM2);
Dbms_output.put_line (num1| | ' ========== ' | | NUM2);
End

Oracle's functions