Working With Functions In Oracle
PLSQL FUNCTION:
A function is a named PLSQL block that can accept parameters and be invoked.
FEATURES:
- You can use a function to compute a value
- A function must return a value to the calling environment
- Like a procedure, a function have
- a header
- a declarative part
- an executable part
- and an optional exception handling part
- A function must have a RETURN clause in the header and at least one RETURN statement in the executable section.
- Function can be stored in the database as a schema object for repeated execution. And these are called stored function.
- Functions promote reusability and maintainability. When validated they can be used in any number of applications. If the processing requirement change, only the function needs to be updated.
- A Function is called as a part of SQL expression or as part of PLSQL expression. When called from an SQL expression, a function must obey specific rules to control side effect. And when called in PLSQL expression, the function identifier acts like a variable whose value depends on the parameter passed to it.
Example:
--Working With Functions In Oracle: Example
create or replace function get_emp_sal(p_empid employees.employee_id%type)
return number
is
v_sal employees.salary%type := 0;
begin
begin
select salary
into v_sal
from employees
where employee_id = p_empid;
exception
when others then
v_sal := 0;
end;
dbms_output.put_line('Salary = '||v_sal);
return v_sal;
exception
when others then
dbms_output.put_line('Error - '||sqlerrm);
return null;
end;
/ Executing a Function:
- Invoke the function as part of a PLSQL expression
- Create a variable to hold the returned value
- Execute the function. The variable will be populated by the value returned by the RETURN statement
declare
v_sal employees.salary%type := 0;
begin
v_sal := get_emp_sal(100);
dbms_output.put_line('Value of v_sal = '||v_sal);
end;
/
Output:
Salary = 24000
Value of v_sal = 24000
PL/SQL procedure successfully completed.
Execution of a Function with Bind Variable:
variable g_sal number;
exec :g_sal := get_emp_sal(100);
print :g_sal;
Output:
Salary = 24000
PL/SQL procedure successfully completed.
G_SAL
----------
24000
Advantages of Function:
- Allow calculations that are too complex, awkward and not possible with SQL
- Functions are stored in DB. So it increases data independence by processing complex data analysis within SQL server, rather than by retrieving data into an application.
- We can perform functions in the query rather than in the application. So its increase efficiency of queries
- Function can manipulate character strings
Locations to Call User Defined Functions:
- Select list of a SELECT command
- Condition of the WHERE and HAVING clause
- CONNECT BY, START WITH, ORDER BY, GROUP BY
- VALUES clause of the INSERT command
- SET clause of the UPDATE command
Condition for Calling Functions From SQL Expressions:
To be callable from SQL expressions, a user-defined function must:
- Be a stored function
- You must own or have the EXECUTE privilege on the function to call it from SQL
- Accept only IN parameter
- Accept only valid SQL data types, not PLSQL specific types, as parameters
- Return data types that are valid SQL data types, not PLSQL specific types such as BOOLEAN, RECORD, or TABLE.
Restrictions on Calling Functions from SQL Expression:
- When Functions are called from SQL expressions cannot contain DML statements.
- When Functions are called from UPDATE/DELETE statements on a table T cannot contain DML on the same table T.
- Functions which are called from a DML statement on a table T cannot query the same table.
- Functions called from SQL statements cannot contain statements that end the transactions.
- When called from a SELECT, INSERT, UPDATE and DELETE statement, the function cannot execute
- SQL transaction control statements – COMMIT/ROLLBACK
- Session control statements – such as SET ROLE
- System control statements – such as ALTER SYSTEM
- Also cannot execute DDL statements – such as CREATE/ALTER/DROP/MODIFY
- When called from a SELECT, INSERT, UPDATE and DELETE statement, the function cannot execute
- Calls to subprogram that break the previous restriction are not allowed in the function.
Removing a Function:
- All the privileges granted on a function are revoked when the function is dropped
- The CREATE or REPLACE syntax is equivalent to dropping a function and recreating it. Privileges granted on the function remain the same when this syntax is used.
Benefits of Stored Procedure and Functions:
- Improved performance
- Avoid reparsing for multiple users by exploiting the shared SQL area
- Avoid PLSQL parsing at runtime by parsing at compile time
- Reduce the no of calls to the database and decrease network traffic.
- PLSQL functions are compiled once and stored in executable form, so function calls are efficient.
- Since the stored functions executed in the database server, the entire block of code can be sent to the database at one time. So a single call over the network can start a large job. This can drastically reduce network traffic between the database and an application, and improve response time.
- Easy Maintenance
- We can Modify routines online without interfering with other users
- Modify one routine to affect multiple applications
- Modify one routine to eliminate duplicate testing
- Improved data security and integrity
- Control indirect access to database objects from nonprivileged users with security privileges.
- Stored function move application code from client to the server where you can
- protect it from tampering
- hide internal details
- restrict user access
- Stored function move application code from client to the server where you can
- Control indirect access to database objects from nonprivileged users with security privileges.
- Improve Code Clarity
- By using appropriate identifier names to describe the actions of the routine, you can reduce the need for comments and enhance clarity.
Modes of Parameters:
IN:
This is the default mode, means you send the copy of the actual parameter. And if you don't specify any explicit mode then its IN mode. It is read only parameter. When you specify a formal parameter as read only you cannot change it during the execution of the function. You can assign a default value to the parameter by assigning a value to it and making the parameter optional. You can use IN parameter when you want to define a pass-by-value function.
OUT:
OUT means you send a reference but NULL as initial value. A formal parameter is write only. When you set a formal parameter as write only no initial physical size is allocated to the variable. You allocate the initial physical size and value inside the function. You can't assign a default value to the OUT parameter, which makes the OUT mode formal parameter as optional. If you try you will receive a message -> PLS-00230 error. The error says that an OUT and IN/OUT parameter cannot have a default value.
And similarly, you cannot pass a literal as an actual parameter to an OUT mode variable as that would block writing the OUT variable. If you try you will receive an error -> ORA-06577 from SQL and PLS-00363 from PLSQL. SQL error states that the OUT parameter is not bind variable. PLSQL error states that the expression cannot be an assignment target.
IN/OUT:
The IN OUT mode means that you send a reference and a starting value. A formal parameter is read-write.
When you set a formal parameter as read-write. When you set a formal parameter as read-write, the actual parameter provides the physical size of the actual parameter.
While you change the actual contents of the variable inside the function, you cannot change or exceed the actual parameter's allocated size.
The IN OUT mode restrictions on default values and literal values are like those of the OUT parameterrs.
NOCOPY:
By default, oracle programs send the copies of all parameters to functions when they call them.
When the function completes successfully they COPY OUT and IN OUT mode parameters back into the external variable.
In this way oracle guarantees the content of external variables are unchanged before a function completes successfully.
This eliminates the possibility of writing partial result sets because an error terminates the function.
When an exception thrown by the function, you have an opportunity to attempt recovery or write variable to log files.
You can override the default behavior of passing copies of variables when calling functions for local transactions. This means you pass by reference and not by copy of data. You override copy behavior by using NOCOPY hint. Exception is calling function by DB link or external function call.
The NOCOPY hint does not override the copy rule when:
- Actual parameter is an element of an associative array. NOCOPY hint works when you pass a complete associative array.
- The actual parameter is a not null constraint
- The actual parameter is constrained by scale.
- Actual parameter is an implicitly defined record structure. And it means you used either the %ROWTYPE or %TYPE anchor.
- An actual parameter is an implicitly defined record structure from a for loop, which fails because the native index has restricted scope to the loop structure.
- An actual parameter require implicit type casting
You can define a Function by either of the two ways:
- Definer rights model
- Invoker rights model
Definer Rights Model:
This is default option. Works only with local data reside in the same schema. It automatically sets AUTHID to DEFINER.
Invoker Rights Model:
You can define a function to write to current user's schema.
It can be done by setting AUTHID to CURRENT_USER. It allow independent users to write on local data who has the execute privilege on the function.
Calling a Function:
When calling a procedure you can write the actual parameter by using:
- Positional Notation
- Named Notation
- Mixed Notation
| Positional Notation | Named Notation | Mixed Notation |
|---|---|---|
| You can specify the same parameter in the same order as they are declared in the procedure | Specify the name of each parameter along with its value | You specify the first parameter with positional notation, then switched to named notation for last parameters |
| You must change your code if the procedure's parameter list changed | An arrow serves as the association operator. The order of parameters is not significant | Use this notation to call procedures that have some required parameters, followed by some optional parameters |
Prior to 11g you can use both positional and named notation in PLSQL but could not use named notation in SQL. And 11g rectified this issue and you can use named and mixed notation in SQL too.
Examples:
--Working With Functions In Oracle: Example
create or replace function fun_show_emp(p_empid employees.employee_id%type default 100,
p_jobid employees.job_id%type default 'AD_PRES',
p_deptid employees.department_id%type default 90)
return number
is
v_sal employees.salary%type := 0;
begin
begin
select salary
into v_sal
from employees
where employee_id = p_empid
and job_id = p_jobid
and department_id = p_deptid;
exception
when others then
v_sal := 0;
end;
return v_sal;
exception
when others then
dbms_output.put_line('Error - '||sqlerrm);
end;
/ Calling with Positional Notation:
begin
dbms_output.put_line('Salary = '||fun_show_emp(100, 'AD_PRES', 90));
end;
/
Output:
Salary = 24000
PL/SQL procedure successfully completed.
Calling with Named Notation:
begin
dbms_output.put_line('Salary = '||fun_show_emp(p_jobid => 'AD_PRES', p_empid => 100, p_deptid => 90));
end;
/
Output:
Salary = 24000
PL/SQL procedure successfully completed.
Calling Mixed Notation:
begin
dbms_output.put_line('Salary = '||fun_show_emp(100, p_deptid => 90, p_jobid => 'AD_PRES'));
end;
/
Output:
Salary = 24000
PL/SQL procedure successfully completed.
You can also exclude one or more parameters when parameters are optional, i.e. parameters have default values.
Here in the above example all the parameters have default values. So the function can be called as follows:
begin
dbms_output.put_line('Salary = '||fun_show_emp);
end;
/
Output:
Salary = 24000
PL/SQL procedure successfully completed.
Calling from SQL:
Before 11g we have only one choice, i.e. positional parameters are allowed in function call from SQL.
But in 11g SQL allow Named and Mixed notation as well.
Named Notation in SQL:
select fun_show_emp(p_jobid => 'AD_PRES', p_empid => 100, p_deptid => 90) salary
from dual;
Output:
SALARY
24000
Mixed Notation in SQL:
select fun_show_emp(100, p_deptid => 90, p_jobid => 'AD_PRES') salary
from dual;
Output:
SALARY
24000
With default values:
select fun_show_emp salary
from dual;
Output:
SALARY
24000
PRAGMA RESTRICT REFERENCE:
Pragma signifies that the statement is a pragma, i.e. compiler directive. Pragmas are processed at compile time, not at runtime. They pass information to the compiler.
To be callable from SQL statement, a stored function must obey certain purity rules which control side effect. The fewer side effect a function has, the better it can be optimize within a query, particular when PARALLEL_ENABLE or DETERMINISTIC hints are used. The same rule that apply to the function is also applicable for the function it calls.
If any SQL statement inside the function body violets the rule, you get an error at runtime (when the statement is parsed). To check the violation of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES.
The restrict reference pragma asserts that a user defined function does not read or write database tables or package variable.
Functions that read or write database tables or package variables are difficult to optimize. Because any call to the function might produce different result or encounter errors.
RNDS:
RNDS asserts that the function reads no database state (does not query any database tables).
RNPS:
RNPS asserts that subprogram reads no package state (does not reference he values of packaged variables).
TRUST:
TRUST asserts that the subprogram can be trusted not to violate one or more rules. This value is needed for function written in Java, C that are called from plsql. And plsql does not verify them at runtime.
WNDS:
WNDS asserts that the subprogram writes no database state (does not modify database tables).
WNPS:
WNPS asserts that the function writes no package state (does not change the values of packaged variable).
Examples of Pragma Restrict References:
WNDS:
create or replace package pkg_emp_sal
is
function fun_update_emp_sal(p_empid employees.employee_id%type)
return employees.salary%type;
end;
/
create or replace package body pkg_emp_sal
is
function fun_update_emp_sal(p_empid employees.employee_id%type)
return employees.salary%type
is
v_sal employees.salary%type := 0;
begin
update employees
set salary = salary * 1.05
where employee_id = p_empid
returning salary into v_sal;
commit;
return v_sal;
end;
end;
/
On compilation:
Package PKG_EMP_SAL compiled
Package Body PKG_EMP_SAL compiled
Calling:
select pkg_emp_sal.fun_update_emp_sal(100) from dual;
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "HR.PKG_EMP_SAL", line 8
14551. 00000 - "cannot perform a DML operation inside a query "
*Cause: DML operation like insert, update, delete or select-for-update
cannot be performed inside a query or under a PDML slave.
*Action: Ensure that the offending DML operation is not performed or
use an autonomous transaction to perform the DML operation within
the query or PDML slave.
Using Pragma Restrict References:
create or replace package pkg_emp_sal
is
function fun_update_emp_sal(p_empid employees.employee_id%type)
return employees.salary%type;
pragma restrict_references(fun_update_emp_sal, WNDS);
end;
/
Package PKG_EMP_SAL compiled
--Working With Functions In Oracle: Example of Function inside a package
create or replace package body pkg_emp_sal
is
function fun_update_emp_sal(p_empid employees.employee_id%type)
return employees.salary%type
is
v_sal employees.salary%type := 0;
begin
update employees
set salary = salary * 1.05
where employee_id = p_empid
returning salary into v_sal;
commit;
return v_sal;
end;
end;
/ Package Body PKG_EMP_SAL compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
3/3 PLS-00452: Subprogram 'FUN_UPDATE_EMP_SAL' violates its associated pragma
Errors: check compiler log
When using PRAGMA Restrict_references, error occurred at compile time. And it will help developer to re-write his code based on compilation error.
Using Default keyword:
When using default keyword instead of a function name, then it will be applicable for all the subprograms inside the package.
create or replace package pkg_emp_sal
is
function fun_update_emp_sal(p_empid employees.employee_id%type)
return employees.salary%type;
function fun_update_emp_comm(p_empid employees.employee_id%type)
return employees.commission_pct%type;
pragma restrict_references(default, WNDS);
end;
/
--Working With Functions In Oracle: Example of Function inside a package
create or replace package body pkg_emp_sal
is
function fun_update_emp_sal(p_empid employees.employee_id%type)
return employees.salary%type
is
v_sal employees.salary%type := 0;
begin
update employees
set salary = salary * 1.05
where employee_id = p_empid
returning salary into v_sal;
commit;
return v_sal;
end fun_update_emp_sal;
function fun_update_emp_comm(p_empid employees.employee_id%type)
return employees.commission_pct%type
is
v_comm employees.commission_pct%type := 0;
begin
update employees
set commission_pct = .05
where employee_id = p_empid
returning commission_pct into v_comm;
commit;
return v_comm;
end fun_update_emp_comm;
end;
/ Package Body PKG_EMP_SAL compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
3/3 PLS-00452: Subprogram 'FUN_UPDATE_EMP_SAL' violates its associated pragma
15/3 PLS-00452: Subprogram 'FUN_UPDATE_EMP_COMM' violates its associated pragma
Errors: check compiler log
WNPS:
--Working With Functions In Oracle: Example of WNPS create or replace package pkg_emp_sal is function fun_update_emp_sal(p_empid employees.employee_id%type) return employees.salary%type; v_sal employees.salary%type := 0; pragma restrict_references(fun_update_emp_sal, WNPS); end; /
Package PKG_EMP_SAL compiled
--Working With Functions In Oracle: Example of WNPS
create or replace package body pkg_emp_sal
is
function fun_update_emp_sal(p_empid employees.employee_id%type)
return employees.salary%type
is
begin
update employees
set salary = salary * 1.05
where employee_id = p_empid
returning salary into v_sal;
commit;
return v_sal;
end fun_update_emp_sal;
end;
/ Package Body PKG_EMP_SAL compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
3/3 PLS-00452: Subprogram 'FUN_UPDATE_EMP_SAL' violates its associated pragma
Errors: check compiler log
RNDS:
--Working With Functions In Oracle: Example of RNDS create or replace package pkg_emp_sal is function fun_get_emp_sal(p_empid employees.employee_id%type) return employees.salary%type; v_sal employees.salary%type := 0; pragma restrict_references(fun_get_emp_sal, RNDS); end; /
Package PKG_EMP_SAL compiled
--Working With Functions In Oracle: Example of RNDS
create or replace package body pkg_emp_sal
is
function fun_get_emp_sal(p_empid employees.employee_id%type)
return employees.salary%type
is
begin
begin
select salary
into v_sal
from employees
where employee_id = p_empid;
exception
when others then
v_sal := 0;
end;
return v_sal;
end fun_get_emp_sal;
end;
/ Package Body PKG_EMP_SAL compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
3/3 PLS-00452: Subprogram 'FUN_GET_EMP_SAL' violates its associated pragma
Errors: check compiler log
RNPS:
--Working With Functions In Oracle: Example of RNPS create or replace package pkg_emp_sal is function fun_update_emp_sal(p_empid employees.employee_id%type) return employees.salary%type; v_sal employees.salary%type := 0; v_deptno employees.department_id%type := 90; pragma restrict_references(fun_update_emp_sal, RNPS); end; /
--Working With Functions In Oracle: Example of RNPS
create or replace package body pkg_emp_sal
is
function fun_update_emp_sal(p_empid employees.employee_id%type)
return employees.salary%type
is
begin
update employees
set salary = salary * 1.05
where employee_id = p_empid
and department_id = v_deptno
returning salary into v_sal;
commit;
return v_sal;
end fun_update_emp_sal;
end;
/ Package PKG_EMP_SAL compiled
Package Body PKG_EMP_SAL compiled
LINE/COL ERROR
--------- -------------------------------------------------------------
3/3 PLS-00452: Subprogram 'FUN_UPDATE_EMP_SAL' violates its associated pragma
Errors: check compiler log
Creating Function in PLSQL examples:
There are various ways in plsql through which function can be created like:
- Stand-alone function in DB
- Creating Function inside a package
- Function in a PLSQL block
- Function inside a Procedure's or Function's declaration part
Example of creating Function in SQL:
- Using WITH clause
Stand-alone Function in DB:
--Working With Functions In Oracle: Example of Standalone Function in DB
create or replace function fun_get_emp_info(p_empid employees.employee_id%type)
return varchar2
is
v_empid employees.employee_id%type := null;
v_name employees.last_name%type := null;
v_jobid employees.job_id%type := null;
v_sal employees.salary%type := null;
begin
begin
select employee_id,
last_name,
job_id,
salary
into v_empid,
v_name,
v_jobid,
v_sal
from employees
where employee_id = p_empid;
exception
when others then
v_empid := null;
v_name := null;
v_jobid := null;
v_sal := null;
end;
return ('Employee_id = '||v_empid||' Name = '||v_name||' Job = '||v_jobid||' Salary = '||v_sal);
exception
when others then
dbms_output.put_line('Error -> '||sqlerrm);
end;
/ Calling:
select fun_get_emp_info(100) emp_info from dual;
EMP_INFO
Employee_id = 100 Name = King Job = AD_PRES Salary = 24000
Creating Function inside a Package:
--Working With Functions In Oracle: Example of Function inside a package create or replace package pkg_emp_info is v_empid employees.employee_id%type := null; v_name employees.last_name%type := null; v_jobid employees.job_id%type := null; v_sal employees.salary%type := null; function fun_get_emp_info(p_empid employees.employee_id%type) return varchar2; end pkg_emp_info; /
--Working With Functions In Oracle: Example of Function inside a package
create or replace package body pkg_emp_info
is
function fun_get_emp_info(p_empid employees.employee_id%type)
return varchar2
is
begin
begin
select employee_id,
last_name,
job_id,
salary
into v_empid,
v_name,
v_jobid,
v_sal
from employees
where employee_id = p_empid;
exception
when others then
v_empid := null;
v_name := null;
v_jobid := null;
v_sal := null;
end;
return ('Employee_id = '||v_empid||' Name = '||v_name||' Job = '||v_jobid||' Salary = '||v_sal);
exception
when others then
dbms_output.put_line('Error -> '||sqlerrm);
end fun_get_emp_info;
end pkg_emp_info;
/ Calling:
select pkg_emp_info.fun_get_emp_info(100) emp_info from dual;
EMP_INFO
Employee_id = 100 Name = King Job = AD_PRES Salary = 24000
Function in a PLSQL Block:
--Working With Functions In Oracle: Example of Function inside a PLSQL block
set serveroutput on;
declare
v_info varchar2(4000) := null;
v_empid employees.employee_id%type := null;
v_name employees.last_name%type := null;
v_jobid employees.job_id%type := null;
v_sal employees.salary%type := null;
function fun_get_emp_info(p_empid employees.employee_id%type)
return varchar2
is
begin
begin
select employee_id,
last_name,
job_id,
salary
into v_empid,
v_name,
v_jobid,
v_sal
from employees
where employee_id = p_empid;
exception
when others then
v_empid := null;
v_name := null;
v_jobid := null;
v_sal := null;
end;
return ('Employee_id = '||v_empid||' Name = '||v_name||' Job = '||v_jobid||' Salary = '||v_sal);
exception
when others then
dbms_output.put_line('Error -> '||sqlerrm);
end fun_get_emp_info;
begin
v_info := fun_get_emp_info(100);
dbms_output.put_line('Employee Info -> '||v_info);
end;
/ Output:
Employee Info -> Employee_id = 100 Name = King Job = AD_PRES Salary = 24000
PL/SQL procedure successfully completed.
Function inside a Procedure or another Function’s declaration part:
--Working With Functions In Oracle: Example of Function inside a Procedure's declaration part
create or replace procedure proc_get_emp_info(p_empid employees.employee_id%type)
is
v_info varchar2(4000) := null;
v_empid employees.employee_id%type := null;
v_name employees.last_name%type := null;
v_jobid employees.job_id%type := null;
v_sal employees.salary%type := null;
function fun_get_emp_info(p_empid employees.employee_id%type)
return varchar2
is
begin
begin
select employee_id,
last_name,
job_id,
salary
into v_empid,
v_name,
v_jobid,
v_sal
from employees
where employee_id = p_empid;
exception
when others then
v_empid := null;
v_name := null;
v_jobid := null;
v_sal := null;
end;
return ('Employee_id = '||v_empid||' Name = '||v_name||' Job = '||v_jobid||' Salary = '||v_sal);
exception
when others then
dbms_output.put_line('Error -> '||sqlerrm);
end fun_get_emp_info;
begin
v_info := fun_get_emp_info(p_empid);
dbms_output.put_line('Employee Info -> '||v_info);
end proc_get_emp_info;
/ Calling:
exec proc_get_emp_info(100);
Employee Info -> Employee_id = 100 Name = King Job = AD_PRES Salary = 24000
PL/SQL procedure successfully completed.
PLSQL Function inside SQL using WITH Clause:
--Working With Functions In Oracle: Example of Function inside WITH clause
with
function get_emp_details(p_empid employees.employee_id%type)
return varchar2
is
v_empid employees.employee_id%type := null;
v_name employees.last_name%type := null;
v_jobid employees.job_id%type := null;
v_sal employees.salary%type := null;
begin
begin
select employee_id,
last_name,
job_id,
salary
into v_empid,
v_name,
v_jobid,
v_sal
from employees
where employee_id = p_empid;
exception
when others then
v_empid := null;
v_name := null;
v_jobid := null;
v_sal := null;
end;
return ('Employee_id = '||v_empid||' Name = '||v_name||' Job = '||v_jobid||' Salary = '||v_sal);
exception
when others then
return('Error -> '||sqlerrm);
end get_emp_details;
select get_emp_details(employee_id) emp_details
from employees
where department_id = 30;
RELATED TOPICS: