ORACLE_FUNCTION

FUNCTION:

　　DEFINE：函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写成一个函数。

1.basic syntax

create [or replace] function fun_name [(parameter1[,parameter2])...] return data_type is|as

　　[inner_variable]

begin

plsql_sentences;

[exception]

[dowith_sentences;]

end [fun_name];

2.Example Usage

　　The sql query below is calculate average in the specify department.

 

create or replace function get_avg_pay(num_deptno number) return number is
num_avg_pay number;
begin
     select avg(sal) into num_avg_pay from emp where deptno=num_deptno;
     return(round(num_avg_pay,2));
excepton
    when no_data_found then
         dbms_output.put_line('This is departmetn not exists');     
         return(0);
end;
/

set serveroutput on
declare
    avg_pay number;
begin
    avg_pay:=get_avg_pay(10);
    dbms_output.put_line('average wage is:'||avg_pay);
end;
/

 

 

 3.Delete Function

　　BAISE SYNTAX:

select * from user_objects where object_type='FUNCTION';

drop functon fun_name;