Storage functions for Oracle objects

Storage Functions

Create or replace function Name of function (name in type, name out type, ...) return Data Type is

result variable data type ;

begin

return ( result variable );

end[ Name of function ];

The difference between stored procedures and stored functions

In general, the difference between a procedure and a function is that a function can have a return value, whereas a procedure does not return a value.

but procedures and functions can be outspecifies one or more output parameters. We can use outparameters, which are implemented in procedures and functions to return multiple values. In general, there is a return value with a stored function, multiple return values with stored procedures. Example: Using a stored function to query a specified employee's annual salary

1Create or Replace function Empincome (ENOinchEmp.empno%type)returnNumber is2  3Psal emp.sal%type;4  5Pcomm emp.comm%type;6  7 begin8  9   SelectT.sal into Psal fromEMP TwhereT.empno =Eno;Ten   One   returnPsal * A+ NVL (Pcomm,0); A   -End

Use stored procedures to replace the example above

1  2Create or Replace procedure EMPINCOMEP (EnoinchEmp.empno%type, income outNumber is3  4Psal emp.sal%type;5  6Pcomm emp.comm%type;7  8 begin9  Ten   SelectT.sal, T.comm into Psal, Pcomm fromEMP TwhereT.empno =Eno; One   AIncome: = psal* A+NVL (Pcomm,0); -   -End EMPINCOMEP;

Call:

1  2 Declare 3  4   income number; 5  6 begin 7  8   EMPINCOMEP (7369, income); 9  Ten   dbms_output.put_line (income);  One   the end;

Storage functions for Oracle objects