-
Copyrights 2010 - Online Training Oracle Applications. All Rights Reserved.
|
PL/ SQL:
Function is a PL/SQL block which must and should return single value. Syntax: Create or replace function <Function_name> ex1: Create a function which accepts two numbers and display its sum. create or replace function add_num_f1 ( a number, b number) c :=a+b; To invoke a function from a pl/Sql block: declare n := add_num_f1(20,40); dbms_output.put_line('The sum is '||n); We can invoke functions from select stmt: Functions can be invoked as part of an expression: select 100 + add_num_f1(50,10) from dual; Ex2: create a function which accepts sal and returns tax value ( 10% of sal is tax). create or replace function cal_tax ( a number) return a*10/100; Note: A function can return a value using return statement. Ex 3: Have a look at the following function:
create or replace function add_num_f2 ( a number, b number) insert into dept values (50,'HR','HYDERABAD') c :=a+b; The above function gets created. The above function can be invoked from the pl/SQL block declare n := add_num_f2(20,40); dbms_output.put_line('The sum is '||n);
But, we cannot invoke the above function using select stmt. ex: select add_num_f2(30,50) from dual; -- will give us error. Note: So, functions with dml commands cannot be invoked from select stmt. ------------------------ TO see the list of all the functions select object_name from user_objects ---------------------- To drop a function drop function <function_name>; ex: drop function add_num_f2; ----------------------- Functions are mainly used for calculation purposes.
|