oracle function 函数

oracle function的几个实例，只限入门，高手路过

 

-- ********the simplest example of function
-- tip: can use sql to directly call the function with in module parameter
create or replace function get_customer_name return varchar2 is
  v_user varchar2(100);
begin
  select customer.KHQC into v_user from t_khxx customer ;
  return v_user;
end;

declare
  v1 varchar2(100);
begin
  v1 := get_customer_name;
  --set serveroutput on
  dbms_output.put_line(v1);
end;

select get_customer_name from dual;------directly call it
--******************the end




-- ********the example of funtion with parameter that can't follow length
-- tip: can use sql to directly call the function with in module parameter

create or replace function get_finance_name(financetype in varchar2)
  return nvarchar2 as
  v_name t_mast.dmnr%type;-- tip:using %type define val instead of 'nvarchar2(40)';
begin
  select mast.dmnr
    into v_name
    from t_mast mast
   where mast.dmlb = '0114'
     and upper(mast.dmbh) = upper(financetype);
  return v_name;
end;

declare
  v1 varchar2(30) := 'rd';
  v2 varchar2(30) := '';
begin
  v2 := get_finance_name(v1);
  select get_finance_name(v1) into v2 from dual; 
  dbms_output.put_line(v2);
end;

select get_finance_name('rd') from dual; -- directly call function in sql

-- *****************the end






-- ********the example of function with parameters with out module that can't follow by length
-- tip: can't use sql to directly call the function with out module parameter

create or replace function get_department(deptname       in varchar2,
                                          deptid         out varchar2,
                                          parentdeptname out varchar2)
  return varchar2 as
  v_id     varchar2(100);
begin
  select dept.bmjc, dept.id
    into parentdeptname,v_id
    from t_bm dept -- when need two or more returns, using parameter with out module
   where dept.bmmc = deptname;
   deptid := v_id;
   return v_id;
end;

declare
  v1 varchar2(100);
  v2 varchar2(100);
  v3 varchar2(100);
  v4 varchar2(100);
begin
  v1 := '有限公司';
  v4 := get_department(v1, v2, v3);
  
end;
-- **************the end