Functions of Notations:
A function is a subprogram which computes a value. The Functions and procedures are structured alike, except which functions have a RETURN clause. Functions are created using the given syntax:
CREATE OR REPLACE FUNCTION <FUNCTIONNAME>[parameter list] RETURN datatype IS # for create and replace the function#
PL/SQL Statements.
Return <value/variable>;
END;
An instance of selecting the department information is displays below:
Example
CREATE OR REPLACE FUNCTION seldept(dno IN NUMBER) RETURN VARCHAR2 IS# for create and replace the function#
M_dNAME VARCHAR2(20);
BEGIN
SELECT DNAME INTO M_DNAME FROM DEPT WHERE DEPTNO=DNO;
RETURN M_DNAME;
END;
Displays the corresponding department name.
Function created.
As like Procedures, Functions cannot be called using EXEC statement. They must be called by using Select Statement. The subsequent display this:
SELECT seledept(10) FROM DUAL;
Generally a function is used to compute specific values. They are not meant to perform any DML operations on the table. For instance, if a function tries to insert a record onto a table, an Oracle throws an error. The instance describes this:
CREATE OR REPLACE FUNCTION INSDEPT(DNO NUMBER,NAME VARCHAR2) RETURN NUMBER AS
BEGIN
INSERT INTO DEPT(DEPTNO,DNAME) VALUES(DNO,NAME);
RETURN 1;
END;
Function created.
select insdept(23,'purchase') from dual;
select insdept(23,'purchase') from dual
*
ERROR at line 1:
ORA-06571: Function INSDEPT does not guarantee not to update database
The function has to satisfy some purity stages for performing any DML operations. Purity Level determines to what extent the function does not do any alterations to the database object. The four stages of purity levels are display in the following table.
Depending on the purity level, a function is restricted to the subsequent restrictions:
1. Function callable from a SELECT statement must not perform any operation to any tables.
2. A Functions can take only IN parameter and not OUT and IN OUT.
3. Return type of a function must be a database type.