函数
create or replace function f_productCard( str_in in varchar2 )--分类字段
return varchar2
is
str_list varchar2(4000) default null;--连接后字符串
str varchar2(20) default null;--连接符号
strVar1 varchar2(40) default null ; --订单号
strVar2 varchar2(40) default null ; --卡号
begin
for x in ( select t.orderno,t.agric_card_no from v_send_product1 t where t.orderno = str_in ) loop
if(strVar1=x.orderno and strVar2=x.agric_card_no) then
str_list :=str_list;
else
str_list :=str_list||str||x.agric_card_no;
end if ;
str := ' | ';
strVar1 :=x.orderno;
strVar2 :=x.agric_card_no ;
end loop;
return str_list;
end ;
存储过程
create or replace procedure pro_FarmproductRanking(v1 In char,v2 In char,saletime_cur Out sale_pack.sale_cur,v3 In Char) is
Begin
Open saletime_cur For
select rownum as id,a."ORDER_ID",a."PRODUCTNAME",a."QUANTITY",a."FEE",null as STATICE_TIME from (
select rank() over
( order by sum(a.quantity) desc) order_id,--排名
b.productname,--产品名称
sum(a.quantity) quantity,--成交总数量
sum(a.quantity*a.price) fee--成交总金额
from product b,--产品
order_item a,--订单详细
sales_order c
where a.productid=b.productid(+) And c.orderid=a.orderid and c.orderstatus=6 and to_char(c.updatetime,'yyyymmdd')>=v1 And to_char(c.updatetime,'yyyymmdd')<=v2 And b.productname Like v3
and b.producttypeid='11'
group by b.productname) a
Union All
select null as id,null as order_id,'合计'as productname,sum(quantity),sum(fee),null as statice_time from
(
select rownum as id,a."ORDER_ID",a."PRODUCTNAME",a."QUANTITY",a."FEE",null as STATICE_TIME from (
select rank() over
( order by sum(a.quantity) desc) order_id,--排名
b.productname,--产品名称
sum(a.quantity) quantity,--成交总数量
sum(a.quantity*a.price) fee--成交总金额
from order_item a,--订单详细
product b,--产品
sales_order c
where a.productid=b.productid(+) And c.orderid=a.orderid and c.orderstatus=6 and to_char(c.updatetime,'yyyymmdd')>=v1 And to_char(c.updatetime,'yyyymmdd')<=v2 And b.productname Like v3
and b.producttypeid='11'
group by b.productname) a
)b;
end pro_FarmproductRanking ;
动态REF游标声明:
CREATE OR REPLACE PACKAGE sale_pack AS
TYPE sale_cur IS REF CURSOR;
end sale_pack;