Oracle Function

Oracle 函数(function): 就是返回一个特定值的代码块，可以直接在查询语句中使用。

使用场景：
1、在一个查询字段中要显示特定格式的值(例如：将用户的所有电话号码以逗号分割拼接在一起)
2、在查询结果中要显示统计结果(总金额、总数量、平均值...)，而又不要分组时

 

语法来源：http://docs.oracle.com/cd/B19306_01/server.102/b14200/img_text/create_function.htm

 

 

 

语法：
CREATE [ OR REPLACE ] FUNCTION [ schema. ]function
  [ (argument [ IN | OUT | IN OUT ]
     [ NOCOPY ] datatype
       [, argument [ IN | OUT | IN OUT ]
          [ NOCOPY ] datatype
       ]...
    )
  ]
  RETURN datatype
  [ { invoker_rights_clause
    | DETERMINISTIC
    | parallel_enable_clause
    }
      [ invoker_rights_clause
      | DETERMINISTIC
      | parallel_enable_clause
      ]...
  ]
  { { AGGREGATE | PIPELINED }
    USING [ schema. ]implementation_type
  | [ PIPELINED ]
    { IS | AS }
    { pl/sql_function_body | call_spec }
  } ;

 

 

 

例子1：
根据订单号获取订单总金额，假设订单明细表中有如下字段：
订单号(order_no)、商品编号、数量（number）、价格(price)

--in标识参数为输入参数
create or replace function fn_get_amount(order_no in varchar2)
  return number is
  amount number(6, 2);
begin
  select sum(oi.number * oi.price)
    into amount
    from order_item oi
   where oi.order_no = order_no;
  return(amount); --此处括号可以省略以空格分割，return amount;
end fn_get_amount;

--测试
select fn_get_amount(3525) from dual;

 

 

例子2：
--out标识参数为输出参数
根据订单号获取订单总金额和总数量，因为函数只能返回一个值，所以可以用输出参数

create or replace function fn_get_amount(order_no     in varchar2,
                                         total_num    out varchar2)
  return number is
  amount number(6, 2);
begin
  select sum(oi.number * oi.price), sum(oi.number)
    into amount, total_num
    from order_item oi
   where oi.order_no = order_no;
  return amount;
end fn_get_amount;

--调用测试
declare
  v_total_num 	 varchar2(20);
  v_amount       number(6, 2);
begin
  v_amount := fn_get_amount(3525, v_total_num);
  dbms_output.put_line('v_total_num:' || v_deliver_type);
  dbms_output.put_line('v_amount:' || v_amount);
end;