Oracle storage functions

The storage function is also a named PLSQL block.

--Note: the stored procedure has no return value, but the stored function has a return value.

The difference between stored procedure and stored function
 Generally speaking, the difference between a procedure and a function is that a function can have a return value;
The procedure did not return a value.

But procedures and functions can specify one or more output parameters through out.

We can use out parameters to return multiple values in procedures and functions.

create or replace function Function name (parameter1 in|out Parameter type,parameter2 in|out Parameter type)

return data type

is|as
       --Defining variables
begin
       --Procedural body
       return(Outcome variable);
end[Function name];

--Create a storage function
create or replace function fun_001 
return varchar2 
as
begin
 return 'Create a storage function';
end;

Calling function

--One way:stay select Calling storage function in statement
select fun_001 from dual;

--Mode two:stayPLSQLCalling storage function in block
declare
  --Declare a variable to receive the return value of the storage function
  p_temp varchar2(50);

begin
  --Assign the return value of a storage function to a variable
  p_temp := fun_001;
  dbms_output.put_line(p_temp);
end;

--Query the annual salary of an employee
create or replace function fun_emp_year_sal(eno in number)
return number
as
   --Receiving annual salary
   yearsal number;
begin
   select round((sal *12)+nvl(comm,0),2) into yearsal from emp where empno = eno;
   --Must have return value
   return yearsal;
end;

--Calling function
declare
   ysal number;
begin
   ysal := fun_emp_year_sal(7369);
   dbms_output.put_line('The annual salary is:'||ysal);
end;
--The return value of a storage function can only have one value. If you want to return multiple values, you need to rely on the output parameters.

--Using stored procedure to query employee's annual salary
--Writing stored procedures
create or replace procedure pro_emp_year_sal(eno number,totalsal out number)
as

begin
    -- Query annual salary
    select round((sal *12)+nvl(comm,0),2) into totalsal from emp where empno = eno;

end;

--Call stored procedure
declare
   --Define variable receive value
   dd number;
begin
   pro_emp_year_sal(7369,dd);
   dbms_output.put_line('Stored procedure query annual salary:'||dd);
end;

Delete function

drop functioin function name;

The difference between stored procedure and stored function

--1) Return value: stored procedure has no return value; stored function has return value;

--2) Usage: storage functions are generally used in SQL statements; storage functions can be written in select statements, but not stored procedures
 --The stored procedure is generally used alone;

--3) Purpose: store functions to realize calculation and return calculation results, while stored procedures are generally used to achieve some specific functions;

--Storage function: query department name according to department number
create or replace function fun_find_dname_by_dno(dno number)
return varchar2
as
   ddname varchar2(200);
begin
   select dname into ddname from dept where deptno = dno;
   return ddname;
end;

--stay select Using functions in statements
select e.* ,fun_find_dname_by_dno(e.deptno) Department name,fun_emp_year_sal(e.empno)  Annual salary
from emp e;