create or replace function 函数名称
          (argument1 [mode1] datatype1,argument2 [mode2] datatype2,...)
return datatype
is|as
PL/SQL Block;
--------------------------------------------------------------
create or replace function get_sal(eno number)
return number
as
 v_sal scott.emp.sal%type;
begin
 select sal into v_sal from scott.emp where empno=eno;
 return v_sal;
end;
/
--------------------------------------------------------------
select get_sal(7788) from dual;
--------------------------------------------------------------
declare
  f_sal scott.emp.sal%type;
begin
  f_sal:=get_sal(&eno);
  dbms_output.put_line(f_sal);
end;
/
--------------------------------------------------------------

1、建立无参数的函数(直接引用函数名)
create or replace function cur_datetime
return varchar2
is
begin
     return to_char(sysdate,'yyyy-mm-dd');
end;
/
begin
  dbms_output.put_line(cur_datetime);
end;

2、建立带有输入参数的函数(要为输入参数提供数据值)
create or replace function get_sal(name varchar2)
return number
is
  v_sal scott.emp.sal%type;
begin
     select sal into v_sal from scott.emp where upper(ename)=upper(name);
     return v_sal;
end;
/
begin
  dbms_output.put_line('工资：'||get_sal('&name'));
end;

select get_sal('&name') 工资 from dual;

3、建立带有输出参数的函数(需要使用变量接收输出结果)
create or replace function get_info
  (eno number,title out varchar2)
return varchar2
as
  name scott.emp.ename%type;
begin
     select ename,job into name,title from scott.emp where empno=eno;
     return name;
end;
/
declare
       v_name scott.emp.ename%type;
       v_job scott.emp.job%type;
begin
  v_name:=get_info(&eno,v_job);
  dbms_output.put_line('姓名：'||v_name||'，岗位：'||v_job);
end;

4、建立带有输入输出参数的函数
create or replace function get_upd_info
  (eno number,sal_chg in out number)
return varchar2
as
  name scott.emp.ename%type;
begin
     update scott.emp set sal=sal+sal_chg where empno=eno
     returning ename,sal into name,sal_chg;
     return name;
end;
/
declare
       v_empno scott.emp.empno%type;
       v_name scott.emp.ename%type;
       v_salchg scott.emp.sal%type;
begin
  v_empno:=&eno;
  v_salchg:=&incre;
  v_name:=get_upd_info(v_empno,v_salchg);
  dbms_output.put_line('姓名：'||v_name||'，新工资：'||v_salchg);
end;

5、使用记录类型作为返回类型
create or replace function get_info(eno number)
return scott.emp%rowtype
is
  emp_record scott.emp%rowtype;
begin
     select * into emp_record from scott.emp where empno=eno;
     return emp_record;
end;
/
declare
       emp_record scott.emp%rowtype;
begin
     emp_record:=get_info(&eno);
     dbms_output.put_line('姓名：'||emp_record.ename||'，部门号：'||emp_record.deptno);
end;

6、使用集合类型作为返回类型
create or replace type ename_table_type is table of varchar2(10);
/
create or replace function get_name(dno number)
return ename_table_type
is
  ename_table ename_table_type;
begin
     select ename bulk collect into ename_table from scott.emp where deptno=dno;
     return ename_table;
end;
/
declare
       ename_table ename_table_type;
begin
     ename_table:=get_name(&dno);
     for i in 1..ename_table.count loop
         dbms_output.put_line('姓名：'||ename_table(i));
     end loop;
end;

7、管理函数
删除函数
drop function get_name;

显示无效函数
select object_name from user_objects where status='INVALID' and object_type='FUNCTION';

编译函数
alter function get_name compile;

查看函数代码
select text from user_source where name='GET_NAME';

例
create or replace function valid_id(eno number)
return boolean
is
  temp int;
begin
     select count(*) into temp from scott.emp where empno=eno;
     if temp<>0 then
        return true;
     else
         return false;
     end if;
end;
/
create or replace function get_sal(eno number,name out varchar2)
return number
is
  v_sal scott.emp.sal%type;
begin
     if valid_id(eno) then
        select ename,sal into name,v_sal from scott.emp where empno=eno;
        return v_sal;
     else
         raise_application_error(-20010,'该雇员不存在');
     end if;
end;
/
declare
  eno scott.emp.empno%type;
  name scott.emp.ename%type;
  v_sal scott.emp.sal%type;
begin
     v_sal:=get_sal(&eno,name);
     dbms_output.put_line(name||','||v_sal);
end;
/

例
create or replace function product
(
  a number,
  b number
)
return number as c number;
begin
  c:=a*b;
  return c;
end;