函数:
create or replace function avg_pric(v_ctgry in varchar2,v_pric in out number) return number is
Result number;
v_qnty number;
begin
if v_pric is null then
v_pric := 0;
end if;
select avg(productprice),min(quantity) into v_pric,v_qnty from productinfo where category=v_ctgry and productprice>v_pric;
return(v_qnty);
exception
when no_data_found then
dbms_output.put_line('没有对应的数据!');
when too_many_rows then
dbms_output.put_line('对应数据过多,请确认!');
end avg_pric;
调用函数:
declare
v_ctgry varchar2(10) := '';
v_pric number := 30;
v_qnty varchar(20);
begin
v_qnty := avg_pric(v_ctgry,v_pric);
dbms_output.put_line('平均价格:' || v_pric);
dbms_output.put_line('最低的产品数量是:' || v_qnty);
end;
/
输出结果:
平均价格:
最低的产品数量是:
返回值为空,求解。感谢!