Oracle Functions

  1. Functions are named pl/sql block.
  2. Function is used to solve particular task.
  3. Function must return a value.
  4. Function is having two parts: function specification and body.

Syntax:
CREATE OR REPLACE FUNCTION FUNCTION_NAME(parameters) RETURN DATATYPE
IS/AS
Variable/Cursor declaration/Exception
BEGIN
Statements;
return expression;
EXCEPTION
Statements;
END;

Functions execution:
Method 1: Using select statement: When a function having all in parameters or when a function does not have parameters, those functions are executed using SELECT statement.

Syntax: SELECT FUNCTION_NAME(parameters) FROM dual;

Method 2:Using anonymous block:
BEGIN variable_name:=function_name(parameters);
END;
/

Example:

CREATE OR REPLACE FUNCTION f1(a varchar2) return varchar2
IS
BEGIN
RETURN a;
END;

Execution:
Method 1:
SELECT f1(‘Hello’) from dual;

Method 2:
sql>DECLARE
x varchar2(10);
BEGIN
X:=F1(‘Welcome’);
dbmsoutput.put_line(x);
END;
/

DML statements in functions:

  • In Oracle, we can also use DML statements in user defined function.But, we are not allowed to execute these functions using SELECT statement.
  • These functions are executed through anonymous blocks. If we want to overcome this problem, we must use automous transactions in functions.

Example:

CREATE OR REPLACE FUNCTION f2(p_ename number) RETURN NUMBER
IS
p_count number(10);
pragma autonomous_transaction;
BEGIN
DELETE FROM emp WHERE empno=p_empno;
p_count:=sql%ROWCOUNT;
commit;
RETURN p-count;
END;
/

Execution: SELECT F2(1) FROM DUAL;

Powered by k2schools