可能需要用到cursor,但又是掌握的不够透彻,悲剧啊。所以还请大家能够帮帮忙,看看这个语句怎么写,个人感觉有点复杂,要用嵌套循环来产生每个单独客户的账户订单。先有3个数据表
1. charges 表
Purchase A/C Product Purchase
Number No. Code Qty Date
---------- --- ------- ------ ---------
101 12 H123 3 04-JUN-09
102 12 P227 1 04-JUN-09
103 12 B127 5 04-JUN-09
104 12 B010 4 04-JUN-09
105 16 P227 2 04-JUN-09
106 17 C129 5 04-JUN-09
107 17 H140 1 04-JUN-092. account表
ID NAME STREET SUBURB POST PHONE
-- -------------------- -------------------- --------------- ---- ---------------
12 Smyth, R 2a Low Rd Lawnceston 7250 6412 3456
15 Jonas, S 45 Upper Main St Hoburt 7009 0417 123 456
16 Weston, J. K. 32 Marine Parade Burney 7418 6422 1234
17 Chen-Smith, Z. Back St Knewnham 7251 0418 592 123
19 Zhou, J. 26 Beauty View Sandie Bay 7002 6422 4321
21 Smyth-Jones, S. 1 Lower Main Road Andtherest 7253 0418 592 3213.product表
CODE DESCRIPTION STOCK_ON_HAND REORDER_LEVEL MAX_STOCK COST_PRICE SELLING_PRICE G
---- ------------------------------ ------------- ------------- ---------- ---------- ------------- -
H123 Fuse Wire 17 15 25 1.5 2.95 G
H124 Knife 11 10 20 8.5 14.95 G
B126 Pineapple 11 10 15 2.5 4.95 P
B127 Bread (White) - 1kg 77 75 100 1.5 4.95 P
B136 Baked Beans - 500g 27 20 35 2.5 5.95 N
B147 Chocolate Bar - 150g 102 100 150 1.75 2.95 N
C129 Coca Cola - 375ml 253 250 400 1.95 2.95 N
C140 Coca Cola - 1 ltr 60 50 75 2.5 3.75 N
P227 Hair Brush 14 12 15 5.5 10.95 G
H129 Tap washer 14 5 20 .75 1.25 G
H120 Torch Battery - 6v 3 2 4 4.5 8.5 G
P130 Razor 10 5 20 5.5 7.45 G
H140 Shoe Polish (Black) - 20g 5 4 10 4.5 9.95 G
B010 Bread (White) - 500g 88 75 100 1.25 2.8 P
B020 Bread (Multi grain) - 1 kg 79 75 100 2.5 5 P
B030 Hamburger Meat - 250g 32 30 60 3.5 4.95 P
B040 Hamburger Bun 41 30 60 .65 .95 P
C010 Coca Cola - 2.5 ltr 270 250 400 3.5 4.95 N
C020 Diet Coca Cola - 500 ml 83 50 75 1.9 2.95 N题目要求不绕,要求列出每个客户(包括未购物)的购物信息
结果有example
tatement Dated : 16/MAY/09
Account Number : 12
Smyth, R,
2a Low Rd,
Lawnceston,
Tasmania,7250.------- ------------ --- ----- --------
Product Description Qty Unit Extended
Code Price Price
------- ------------ --- ----- --------H123 Fuse Wire 3 2.95 8.85
P227 Hair Brush 1 10.95 10.95
B127 Bread (White) 5 4.95 24.75
=======
TOTAL 44.55
=======
表头居中显示日期,换行显示订单号
显示客户信息
接着就是具体显示订单的信息了,包括产品code,description,qty,单价,总价
还得用computer 计算一下所有商品总价。格式化这一块显示的时候有的问题,自己想的有些乱了,能帮忙做下的真是感激不尽了,谢谢大家。
1. charges 表
Purchase A/C Product Purchase
Number No. Code Qty Date
---------- --- ------- ------ ---------
101 12 H123 3 04-JUN-09
102 12 P227 1 04-JUN-09
103 12 B127 5 04-JUN-09
104 12 B010 4 04-JUN-09
105 16 P227 2 04-JUN-09
106 17 C129 5 04-JUN-09
107 17 H140 1 04-JUN-092. account表
ID NAME STREET SUBURB POST PHONE
-- -------------------- -------------------- --------------- ---- ---------------
12 Smyth, R 2a Low Rd Lawnceston 7250 6412 3456
15 Jonas, S 45 Upper Main St Hoburt 7009 0417 123 456
16 Weston, J. K. 32 Marine Parade Burney 7418 6422 1234
17 Chen-Smith, Z. Back St Knewnham 7251 0418 592 123
19 Zhou, J. 26 Beauty View Sandie Bay 7002 6422 4321
21 Smyth-Jones, S. 1 Lower Main Road Andtherest 7253 0418 592 3213.product表
CODE DESCRIPTION STOCK_ON_HAND REORDER_LEVEL MAX_STOCK COST_PRICE SELLING_PRICE G
---- ------------------------------ ------------- ------------- ---------- ---------- ------------- -
H123 Fuse Wire 17 15 25 1.5 2.95 G
H124 Knife 11 10 20 8.5 14.95 G
B126 Pineapple 11 10 15 2.5 4.95 P
B127 Bread (White) - 1kg 77 75 100 1.5 4.95 P
B136 Baked Beans - 500g 27 20 35 2.5 5.95 N
B147 Chocolate Bar - 150g 102 100 150 1.75 2.95 N
C129 Coca Cola - 375ml 253 250 400 1.95 2.95 N
C140 Coca Cola - 1 ltr 60 50 75 2.5 3.75 N
P227 Hair Brush 14 12 15 5.5 10.95 G
H129 Tap washer 14 5 20 .75 1.25 G
H120 Torch Battery - 6v 3 2 4 4.5 8.5 G
P130 Razor 10 5 20 5.5 7.45 G
H140 Shoe Polish (Black) - 20g 5 4 10 4.5 9.95 G
B010 Bread (White) - 500g 88 75 100 1.25 2.8 P
B020 Bread (Multi grain) - 1 kg 79 75 100 2.5 5 P
B030 Hamburger Meat - 250g 32 30 60 3.5 4.95 P
B040 Hamburger Bun 41 30 60 .65 .95 P
C010 Coca Cola - 2.5 ltr 270 250 400 3.5 4.95 N
C020 Diet Coca Cola - 500 ml 83 50 75 1.9 2.95 N题目要求不绕,要求列出每个客户(包括未购物)的购物信息
结果有example
tatement Dated : 16/MAY/09
Account Number : 12
Smyth, R,
2a Low Rd,
Lawnceston,
Tasmania,7250.------- ------------ --- ----- --------
Product Description Qty Unit Extended
Code Price Price
------- ------------ --- ----- --------H123 Fuse Wire 3 2.95 8.85
P227 Hair Brush 1 10.95 10.95
B127 Bread (White) 5 4.95 24.75
=======
TOTAL 44.55
=======
表头居中显示日期,换行显示订单号
显示客户信息
接着就是具体显示订单的信息了,包括产品code,description,qty,单价,总价
还得用computer 计算一下所有商品总价。格式化这一块显示的时候有的问题,自己想的有些乱了,能帮忙做下的真是感激不尽了,谢谢大家。
解决方案 »
- 求助一SQL。。为什么我写的三表连接中会有重复数据?GROUP BY是不行的。。
- oracle分组问题
- 请教SQL语言如何实现这一算法~period_start_time
- 如何在数据库中插入特殊字符
- 库名叫ldj 在C:\oracle\ora81\database 和 C:\oracle\admin\ldj\pfile
- 怎样查看一个以建表的详细信息
- 关于Oracle数据类型的问题
- 模式和用户的关系?有没有子数据库的概念?
- 在linux下用proc/c++往oracle里写汉字的问题(汉字给拆了,不知如何解决)
- 简单问题:在Oracle8.1.5版本中如何进入的Enterprise Manager Console?
- 在EM中,普通用户不能登录吗
- 格式化输出 客户订单
(
pur_num integer primary key,
account_ID varchar(20) not null,
pur_code varchar(4),
pur_qua integer,
pur_date date
);
create sequence purchase_order_number increment by 1 start with 100 nomaxvalue nocycle cache 10;insert into charges values(purchase_order_number.NEXTVAL, 12,'H123', 3, sysdate);
insert into charges values(purchase_order_number.NEXTVAL, 12,'P227', 1, sysdate);
insert into charges values(purchase_order_number.NEXTVAL, 12,'B127', 5, sysdate);
insert into charges values(purchase_order_number.NEXTVAL, 12,'B010', 4, sysdate);
insert into charges values(purchase_order_number.NEXTVAL, 16,'P227', 2, sysdate);
insert into charges values(purchase_order_number.NEXTVAL, 17,'C129', 5, sysdate);
insert into charges values(purchase_order_number.NEXTVAL, 17,'H140', 1, sysdate);
2. account表drop table account cascade constraints;
create table account(
id char(2) primary key,
name varchar(20) not null,
street varchar(20),
suburb varchar(15) not null,
postcode char(4) not null,
phone varchar(15));insert into account values('12','Smyth, R','2a Low Rd','Lawnceston','7250','6412 3456');
insert into account values('15','Jonas, S','45 Upper Main St','Hoburt','7009','0417 123 456');
insert into account values('16','Weston, J. K.','32 Marine Parade','Burney','7418','6422 1234');
insert into account values('17','Chen-Smith, Z.','Back St','Knewnham','7251','0418 592 123');
insert into account values('19','Zhou, J.','26 Beauty View','Sandie Bay','7002','6422 4321');
insert into account values('21','Smyth-Jones, S.','1 Lower Main Road','Andtherest','7253','0418 592 321');
3. product表drop table product cascade constraints;
create table product(
code char(4) primary key,
description varchar(30) not null,
stock_on_hand integer,
reorder_level integer,
max_stock integer,
cost_price number,
selling_price number,
group_code varchar(1));insert into product values('H123','Fuse Wire',17,15,25,1.5,2.95,'G');
insert into product values('H124','Knife',11,10,20,8.5,14.95,'G');
insert into product values('B126','Pineapple',11,10,15,2.5,4.95,'P');
insert into product values('B127','Bread (White) - 1kg',77,75,100,1.5,4.95,'P');
insert into product values('B136','Baked Beans - 500g',27,20,35,2.5,5.95,'N');
insert into product values('B147','Chocolate Bar - 150g',102,100,150,1.75,2.95,'N');
insert into product values('C129','Coca Cola - 375ml',253,250,400,1.95,2.95,'N');
insert into product values('C140','Coca Cola - 1 ltr',60,50,75,2.5,3.75,'N');
insert into product values('P227','Hair Brush',14,12,15,5.5,10.95,'G');
insert into product values('H129','Tap washer',14,5,20,.75,1.25,'G');
insert into product values('H120','Torch Battery - 6v',3,2,4,4.5,8.5,'G');
insert into product values('P130','Razor',10,5,20,5.5,7.45,'G');
insert into product values('H140','Shoe Polish (Black) - 20g',5,4,10,4.5,9.95,'G');
insert into product values('B010','Bread (White) - 500g',88,75,100,1.25,2.80,'P');
insert into product values('B020','Bread (Multi grain) - 1 kg',79,75,100,2.5,5,'P');
insert into product values('B030','Hamburger Meat - 250g',32,30,60,3.5,4.95,'P');
insert into product values('B040','Hamburger Bun',41,30,60,.65,.95,'P');
insert into product values('C010','Coca Cola - 2.5 ltr',270,250,400,3.5,4.95,'N');
insert into product values('C020','Diet Coca Cola - 500 ml',83,50,75,1.9,2.95,'N');
create table product(
code char(4) primary key,
description varchar(30) not null,
stock_on_hand integer,
reorder_level integer,
max_stock integer,
cost_price number,
selling_price number,
group_code varchar(1));insert into product values('H123','Fuse Wire',17,15,25,1.5,2.95,'G');
insert into product values('H124','Knife',11,10,20,8.5,14.95,'G');
insert into product values('B126','Pineapple',11,10,15,2.5,4.95,'P');
insert into product values('B127','Bread (White) - 1kg',77,75,100,1.5,4.95,'P');
insert into product values('B136','Baked Beans - 500g',27,20,35,2.5,5.95,'N');
insert into product values('B147','Chocolate Bar - 150g',102,100,150,1.75,2.95,'N');
insert into product values('C129','Coca Cola - 375ml',253,250,400,1.95,2.95,'N');
insert into product values('C140','Coca Cola - 1 ltr',60,50,75,2.5,3.75,'N');
insert into product values('P227','Hair Brush',14,12,15,5.5,10.95,'G');
insert into product values('H129','Tap washer',14,5,20,.75,1.25,'G');
insert into product values('H120','Torch Battery - 6v',3,2,4,4.5,8.5,'G');
insert into product values('P130','Razor',10,5,20,5.5,7.45,'G');
insert into product values('H140','Shoe Polish (Black) - 20g',5,4,10,4.5,9.95,'G');
insert into product values('B010','Bread (White) - 500g',88,75,100,1.25,2.80,'P');
insert into product values('B020','Bread (Multi grain) - 1 kg',79,75,100,2.5,5,'P');
insert into product values('B030','Hamburger Meat - 250g',32,30,60,3.5,4.95,'P');
insert into product values('B040','Hamburger Bun',41,30,60,.65,.95,'P');
insert into product values('C010','Coca Cola - 2.5 ltr',270,250,400,3.5,4.95,'N');
insert into product values('C020','Diet Coca Cola - 500 ml',83,50,75,1.9,2.95,'N');
create or replace package RefCursor is
type t_RefCursor is ref cursor;
end RefCursor;create or replace procedure report
as
v_cur RefCursor.t_Refcursor;
v_acc varchar2(20)
v_purdate date;
v_str varchar2(100);
v_str1 varchar2(1000);
cursor bh_list is select distinct account_ID,pur_date from charges;
begin
open bh_list;
fetch bh_list into v_acc,v_purdate;
while bh_list %found loop
dbms_output.put_line('tatement Dated : '||to_char(v_purdate,'dd/mon/yy'));
dbms_output.put_line('Account Number : '||v_acc);
select name||chr(10)||street||chr(10)||suburb||chr(10)||post into v_str from account where id=v_acc;
dbms_output.put_line(v_str);
dbms_output.put_line('------- ------------ --- ----- --------');
dbms_output.put_line('Product Description Qty Unit Extended');
dbms_output.put_line(' Code Price Price');
dbms_output.put_line('------- ------------ --- ----- --------');
open v_cur for 'select CODE||chr(9)||DESCRIPTION||chr(9)||pur_qua||chr(9)||SELLING_PRICE||chr(9)||pur_qua*SELLING_PRICE
from charges a,product b where a.pur_code=b.code and a.account_id='''||v_acc||''' and a.pur_date='''||
v_purdate||'''';
fetch v_cur into v_str1;
while v_str1 %found loop
dbms_output.put_line(v_str1);
fetch v_cur into v_str1;
end loop;
close v_cur;
dbms_output.put_line(' =======');
dbms_output.put_line(' TOTAL 44.55');
dbms_output.put_line(' =======');
dbms_output.put_line(chr(10)||chr(10)||chr(10));
fetch bh_list into v_acc,v_purdate;
end loop;
close bh_list;
end;
/exec report;
运行的时候
不运行那个exec report
Warning: Procedure created with compilation errors.
整体运行 出错代码Warning: Package created with compilation errors.SQL>
SQL> exec report;
BEGIN report; END; *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'REPORT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
有点不会更改,兄弟很厉害!
create or replace package RefCursor is
type t_RefCursor is ref cursor;
end RefCursor;
/create or replace procedure report
as
v_cur RefCursor.t_Refcursor;
v_acc varchar2(20);
v_purdate date;
v_str varchar2(100);
v_str1 varchar2(1000);
cursor bh_list is select distinct account_ID,pur_date from charges;
v_cost number;
begin
open bh_list;
fetch bh_list into v_acc,v_purdate;
while bh_list %found loop
dbms_output.put_line('tatement Dated : '||to_char(v_purdate,'dd/mon/yy'));
dbms_output.put_line('Account Number : '||v_acc);
select name||chr(10)||street||chr(10)||suburb||chr(10)||postcode into v_str from account where id=v_acc;
dbms_output.put_line(v_str);
dbms_output.put_line('------- ------------ --- ----- --------');
dbms_output.put_line('Product Description Qty Unit Extended');
dbms_output.put_line(' Code Price Price');
dbms_output.put_line('------- ------------ --- ----- --------');
open v_cur for 'select CODE||chr(9)||DESCRIPTION||chr(9)||pur_qua||chr(9)||SELLING_PRICE||chr(9)||pur_qua*SELLING_PRICE
from charges a,product b where a.pur_code=b.code and a.account_id='''||v_acc||''' and to_char (a.pur_date,''yyyymmdd'')='''||
to_char(v_purdate,'yyyymmdd')||'''';
fetch v_cur into v_str1;
while v_cur %found loop
dbms_output.put_line(v_str1);
fetch v_cur into v_str1;
end loop;
close v_cur;
select sum(pur_qua*SELLING_PRICE) into v_cost from charges a,product b where a.pur_code=b.code and a.account_id=v_acc and to_char (a.pur_date,'yyyymmdd')=to_char(v_purdate,'yyyymmdd');
dbms_output.put_line(' =======');
dbms_output.put_line(' TOTAL '||v_cost);
dbms_output.put_line(' =======');
dbms_output.put_line(chr(10)||chr(10)||chr(10));
fetch bh_list into v_acc,v_purdate;
end loop;
close bh_list;
end;
/exec report;
tatement Dated : 05/6月 /09
Account Number : 12
Smyth, R
2a Low Rd
Lawnceston
7250
------- ------------ --- ----- --------
Product Description Qty Unit Extended
Code Price Price
------- ------------ --- ----- --------
H123 Fuse Wire 3 2.95 8.85
B127 Bread (White) - 1kg 5 4.95 24.75
P227 Hair Brush 1 10.95 10.95
B010 Bread (White) - 500g 4 2.8 11.2
=======
TOTAL 55.75
=======tatement Dated : 05/6月 /09
Account Number : 16
Weston, J. K.
32 Marine Parade
Burney
7418
------- ------------ --- ----- --------
Product Description Qty Unit Extended
Code Price Price
------- ------------ --- ----- --------
P227 Hair Brush 2 10.95 21.9
=======
TOTAL 21.9
=======tatement Dated : 05/6月 /09
Account Number : 17
Chen-Smith, Z.
Back St
Knewnham
7251
------- ------------ --- ----- --------
Product Description Qty Unit Extended
Code Price Price
------- ------------ --- ----- --------
C129 Coca Cola - 375ml 5 2.95 14.75
H140 Shoe Polish (Black) - 20g 1 9.95 9.95
=======
TOTAL 24.7
=======
PL/SQL 过程已成功完成。