Oracle PL/SQL:Stored Functions

A PL/SQL function is a schema object that logically groups a set of SQL and other PL/SQL programming language statements together to perform a specific task.
or
pl/sql functions are a pl/sql block which is named and stored in oracle database.the main difference between a stored procedure and stored function is that a stored function should return at least one value but a stored procedure may or may not return a value.it is used like as built in functions.

pl/sql functions can be invoked from sql or pl/sql statements or from expressions functions are created in a user’s schema and stored in a
database for continued use.

You can execute a procedure or function interactively using an Oracle tool, such as SQL*Plus, or call it explicitly in the code of a database
application, such as an Oracle Forms or Precompiler application, or in the code of another procedure or trigger.

creating Functions

syntax is

CREATE [OR REPLACE ] FUNCTION function_name
[(Formal parameter1 [MODE] Data_type,
(Formal parameter2 [MODE] Data_type,
…..)] RETURN data_type
IS | AS

BEGIN

PL/SQL Block

RETURN return_variable;

END;

Parameter Modes

-You use parameter modes to define the behavior of formal parameters.

-          The three parameter modes, IN (the default), OUT, and IN OUT, can be used with any subprogram.

-          However, avoid using the OUT and IN OUT modes with functions. The purpose of a function is to take zero or more arguments and return a single value. It is poor programming practice to have a function return multiple values. Also, functions should be free from <em>side effects</em>, which change the values of variables not local to the subprogram.

The following table summarizes the information about parameter modes.

IN	OUT	IN OUT
the default	must be specified	must be specified
passes values to a subprogram	returns values to the caller	passes initial values to a subprogram; returns updated values to the caller
formal parameter acts like a constant	formal parameter acts like an uninitialized variable	formal parameter acts like an initialized variable
formal parameter cannot be assigned a value	formal parameter cannot be used in an expression; must be assigned a value	formal parameter should be assigned a value
actual parameter can be a constant, initialized variable, literal, or expression	actual parameter must be a variable	actual parameter must be a variable

Return Statment

RETURN data_type
>>Only type has to be specified and size specification is not allowed
>>Can be Scalar data type or composite datatype
>>Can Return only one value
>>should have only one return statment in the PL/SQL block

Example

CREATE OR REPLACE FUNCTION get_sal_emp(v_emp_id IN number)
RETURN number
IS
v_emp_sal number;
BEGIN

SELECT emp_sal into v_emp_sal
FROM emp WHERE emp_id=v_emp_id;

RETURN v_emp_sal;

END;

Invoking the Function

SQL:>execute:salary:=get_sal_emp(182312);

Removing the Function

DROP FUNCTION function_name;

example

DROP FUNCTION get_emp_sal;

It will remove the stored function get_emp_sal
It also remove the related data dictionary entries and contents

---