oracle数据库中我有两张表
订单表:order
订单号 应收金额
000123 23.00
订单详情表orderdetal
商品名称 商品数量 订单号
发光八面玲珑 2 000123
柏木 1 000123 在查询时, 应该这样显示........... 订单号 应收金额 商品详情
000123 23.00 发光八面玲珑x2,柏木x1
请问用存储过程怎么做,在此跪谢!!!!!!
订单表:order
订单号 应收金额
000123 23.00
订单详情表orderdetal
商品名称 商品数量 订单号
发光八面玲珑 2 000123
柏木 1 000123 在查询时, 应该这样显示........... 订单号 应收金额 商品详情
000123 23.00 发光八面玲珑x2,柏木x1
请问用存储过程怎么做,在此跪谢!!!!!!
begin
OPEN mycur for
SELECT t1.订单号,t2.应收金额,t2.商品名称 FROM ORDER T1 LEFT JOIN orderdetal T2 ON T1.订单号=T2.订单号;
end PRO1;
订单表:order
订单号 应收金额
[color=#FF0000]000123 23.00
订单详情表orderdetal
商品名称 商品数量 订单号
发光八面玲珑 2 000123
柏木 1 000123 在查询时, 应该这样显示........... 订单号 应收金额 商品详情
000123 23.00 发光八面玲珑x2,柏木x1
请问用存储… 订单详情表orderdetal中的两条记录是用一个订单号
create procedure my_procedure
as
begin
declare @priousID varchar(20);
declare @currentID varchar(20);
declare @detail varchar(20);
declare @message varchar(20);
declare @money varchar(20);
CurSor cur is
select order .订单号 ,A.商品详情,order.应收金额
from order,
(select 订单号,orderdetal .商品名称 ||'*'|| rtrim(cast(orderdetal .商品数量 as varchar(10)) as 商品详情)
from
orderdetal) AS A
where A.订单号=order.订单号
open cur;loop
fetch cur into @currentID ,@detail,@money;
@priousID= @currentID;
exit when cur%notfound;
if @priousID<> @currentID then
begin
insert into T(ID ,detal,money)
values(@priousID,@message,@money);
@priousID = @currentID;
@message = null;
end
elsif @priousID == @currentID then
begin
@message =@message||@detail;
end
select * from T;
end loop;end
SQL> select * from torder;OID OMONEY
------ ----------
000123 23SQL> select * from torderdetail;ONAME ONUMBER OID
-------------------- ---------- ------
八面玲珑 2 000123
四面埋伏 1 000123SQL> select a.oid,a.omoney,b.name from
2 torder a,
3 (select oid,wmsys.wm_concat(oname||'×'||onumber) name from torderdetail gr
oup by oid) b
4 where a.oid=b.oid;OID OMONEY NAME
------ ---------- ------------------------------
000123 23 八面玲珑×2,四面埋伏×1
FID varchar2(20),
FMoney numeric
);insert into torder(FID,Fmoney)
values('000123',23);
commit;create table torderdetail(
FID varchar2(20),
FName varchar2(40),
FNumber integer
);insert into torderdetail values('000123','坐落卡斯多夫',2);
insert into torderdetail values('000123','阿瑟多幅',1);commit;create or replace function forderdetail(
AID varchar2
)
return varchar2
as
resultStr varchar2(200);
begin
for mycur in (select fname,fnumber from torderdetail where fid = AID) loop
resultStr := resultStr || ',' || mycur.fname ||'*'||mycur.fnumber;
end loop; return resultstr;
exception
when others then
return 'exception';
end;
create or replace function forderdetail(
AID varchar2
)
return varchar2
as
resultStr varchar2(200);
begin
for mycur in (select fname,fnumber from torderdetail where fid = AID) loop
resultStr := resultStr || ',' || mycur.fname ||'*'||mycur.fnumber;
end loop; return resultstr;
exception
when others then
return 'exception';
end;
select FID,forderdetail(FID) from torder