How to use Functions in Oracle

1. Definition

Function: accepts 0 or more input parameters, has a return value, and the data type of the returned value is defined when the function is created.

function Example 1

CREATE OR REPLACE FUNCTION Tax (p_empno in number)

Return number is

V_sal number;

V_returnvalue number;

BEGIN

SELECT Sal into V_sal

From Emp1 WHERE empno=p_empno;

V_returnvalue: = v_sal*0.08;

return v_returnvalue;

End tax;

Return income tax for employees

Call function

DECLARE

V_tax number;

BEGIN

V_tax: = Tax (&empno);

Dbms_output. Put_Line (' TAX is ' | | V_tax);

End;

function Example 2

To create a function:

CREATE OR REPLACE FUNCTION get_salary (

P_dept_no number,

P_num out number)

Return number is

V_sum number;

BEGIN

SELECT SUM (SAL), COUNT (*) into V_sum, P_num

from EMP WHERE deptno=p_dept_no;

return v_sum;

End Get_salary;

/

Called in the Pl/sql block:

DECLARE

V_NUM2 number;

V_sum number;

BEGIN

V_sum: =get_salary (&&DEPTNO,V_NUM2);

Dbms_output. Put_Line (&deptno| | ' Department sum salary is ' | |    v_sum| | ' Employee: ' | | V_NUM2);

End;

/

2. Delete function

We can remove the unwanted function from the drop function command as follows:

DROP FUNCTION [user.] Function_name;

Author: Oracle Small-mixed son

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/