Oracle数据库PL/SQL学习笔记——函数定义

create or replace function add_three_numbers
(a number:=0,b number :=0,c number :=0)
return number is
begin
   return a+b-c;
end;
--位置表示法
begin
   dbms_output.put_line(add_three_numbers(3,4,5));
end;
--命名表示法
begin
   dbms_output.put_line(add_three_numbers(c =>5,b=>4,a=>3));
end;
--混合表达法（第一个参数使用位置表达法直接传值，后两个参数使用命名表达法赋值）
begin
   dbms_output.put_line(add_three_numbers(3,c=>5,b=>4));
end;
--排除表达法（没有提供的第二个参数b用null代替）
begin
   dbms_output.put_line(add_three_numbers(3,c=>5));
end;
/*sql中调用函数时也能使用调用表示法*/
select add_three_numbers(3,c=>5,b=>4) from dual;

--管道函数的使用（速度更快）
create or replace function Pipelined_numbers
return numbers
pipelined is
  list numbers := numbers(0,1,2,3,4,5,6,7,8,9);
begin
   for i in 1..list.last loop
      pipe row(list(i));          --管道函数的使用
   end loop;
   return;
end;   

--例：在包规范中定义了记录结构体、记录结构体的集合以及返回集合类型的函数：
--定义包pipelined
create or replace package pipelined is
  type user_record is record         --在包中定义记录record
  (name varchar2(30),
   age number
  );  
  type user_collection is table of user_record;
  function pf return user_collection pipelined;   --函数声明,返回的类型要指明所在的包pipelined
end pipelined;
--在包主体实现pf函数（当函数返回集合时，pipelined子句提供了改进的性能）
create or replace package body pipelined is     --注意包的主体声明中是   package body
 function pf
   return user_collection pipelined is
      counter number := 1;
      userlist user_collection := user_collection();  --初始化为空的集合
    cursor c is
       select t.name,t.age  from tb_user t;
begin
  for i in c loop
    userlist.extend;   --extend为一个元素分配空间，然后值被指派给被索引元素的组件
    userlist(counter).name := i.name;
    userlist(counter).age := i.age;
    pipe row(userlist(counter));--在函数中，PIPE ROW 语句被用来返回该集合的单个元素，该函数必须以一个空的 RETURN 语句结束，以表明它已经完成。
    counter := counter +1;
  end loop;
   return;
 end pf;
end pipelined;
--在包体中的函数调用            包名.函数名
select pipelined.pf from dual;        --返回记录结构体的行为聚集表 ， collection集合

create or replace type strings as table of varchar2(30);  --创建用户级别的type
create or replace function getnames
  (par_name varchar2) return strings
   result_cache relies_on(tb_user) is               --  result_cache 声明缓存结果，relies_on(tb_user)确保了对底层表的任何改变都会使缓存无效，确保了缓存是动态的
 counter number := 1;   --oracle数据库中集合从1开始遍历
 return_value strings := strings();
 cursor name_cur (v_name varchar2) is
    select name from tb_user where upper(name) like '%'||upper(v_name)||'%';
 begin
    for i in name_cur(par_name) loop
       return_value.extend;
       return_value(counter):=i.name;
       counter := counter + 1;
    end loop;
    return return_value;
 end getnames; 
 create or replace function getnames
  (par_name varchar2) return strings
   result_cache is               --  可省略relies_on(tb_user)提高查询性能，
 counter number := 1;   --oracle数据库中集合从1开始遍历
 return_value strings := strings();
 cursor name_cur (v_name varchar2) is
    select name from tb_user where upper(name) like '%'||upper(v_name)||'%';
 begin
    for i in name_cur(par_name) loop
       return_value.extend;
       return_value(counter):=i.name;
       counter := counter + 1;
    end loop;
    return return_value;
 end getnames;
--测试(第二次执行，耗时0s，说明缓存成功)
declare
 list strings;
begin 
  list := getnames('a');
  for i in 1..list.last loop
     dbms_output.put_line('list('||i||'):['||list(i)||']');
  end loop;
end;

--利用此函数能给另外一个系统游标赋值
create or replace function get_full_names
 return sys_refcursor is
   names sys_refcursor;    --变量声明
begin
   open names for
    select name,age from tb_user;
   return names;
end;

--递归函数
--例：返回任意数的阶乘值
create or replace function factorial
(n binary_double) return binary_double is
begin
   if n<=1 then
      return 1;
   else
      return n * factorial(n-1);
   end if;
end factorial;
select factorial(5) from dual;--120

create or replace function counting
(number_in in out number) return varchar2 is         --传入的参数经过函数处理后返回给调用者
  type numbers is table of varchar2(5);
  ordinal numbers := numbers('one','two','three','four','five');
  retval varchar2(9) := 'not found';
begin
   if number_in is null then
      number_in := 1;
   end if;
   if number_in < 4 then
      retval := ordinal(number_in);
      number_in := number_in +1;
   else
      retval := ordinal(number_in);
   end if;
   return retval;
end;
--测试
declare
  counter number :=1;
begin
   for i in 1..5 loop
     dbms_output.put_line('counter['||counter||']');
     dbms_output.put_line('counter['||counting(counter)||']');  
   end loop;
end;  
--out模式下的引用传递
create or replace function counting2
(
  number_out out number
) return varchar2 is
  type numbers is table of varchar2(5);
  ordinal numbers := numbers('one','two','three','four','five');
  retval varchar2(9):= 'not found';
begin
   if number_out is null then       --不管输入的number_out的值是多少，由于是out类型的，所以始终是null
      number_out := 1;
   end if;
   if number_out < 4 then
       retval := ordinal(number_out);
       number_out:=number_out+1;
   else
      retval := ordinal(number_out);
   end if;
   return retval;
end;
--测试
declare
 counter number :=1;
begin
   for i in 1..5 loop
     dbms_output.put('counter['||counter||']');
     dbms_output.put_line('['||counting2(counter)||']');
   end loop;
end;