PL/SQL Custom functions

Restrictions on calling a function from an SQL expression

In order to invoke a function from an SQL expression, a user-defined function must:

is a storage function

Accept only in functions

Receives only the SQL data type that is affected, not the. PL data type

The return data type is a valid SQL data type, not a PL-SQL special type

A function called from an SQL expression cannot contain a DML statement

A function that is called from a update/delete statement on a table T, the contents of the function cannot be contained in a DML on the same table T

Function contents cannot query the same table from a function called in an UPDATE or DELETE statement on a table T

A function called from an SQL statement cannot contain a statement that ends a thing

Calling a subroutine that violates a previous level of constraint is not allowed in a function

Custom functions

function function: Enter work number, return salary

Create or Replace function get_sal

(p_id in Employees.employee_id%type)

return number

Is

V_salary employees.salary%type:=0;

Begin

Select salary into V_salary from employees where employee_id=p_id;

return v_salary;

End Get_sal;

/

Select Get_sal (employee_id) from employees;

Tax function

Create or Replace function tax (p_value in number)

Return number is

Begin

return (p_value*0.08);

End tax;

/

Select Employee_id,last_name,salary,tax (Salary) from employees where department_id=100;

Delete a function

DROP FUNCTION function_name

Show errors can display compilation errors (if any)

Show wage rank function

Create or Replace function F_grade (V_eno in Employees.employee_id%type)

Return VARCHAR2 is

V_sal Employees.salary%type;

V_result VARCHAR2 (50);

Begin

Select salary into V_sal from employees where Employee_id=v_eno;

Case

When V_sal>0 and v_sal<2000 then

v_result:= ' Little case ';

When v_sal>2000 and v_sal<5000 then

V_result:= ' Medium case ';

When v_sal>5000 Then

v_result:= ' big case ';

Else

v_result:= ' no case ';

End case;

return v_result;

End F_grade;

/

PL/SQL Custom functions