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;