--创建zs表
create table zs(
zs_id number(4),
zs_name char(4),
tel number(20)
);
insert into zs values(1,'新华',02228345432);
insert into zs (zs_id,zs_name,tel) values (2,'飞跃',null);
insert into zs values(3,'蓝天',02066622834);
--创建SP表
create table sp(
sp_id char(6),
sp_name char(6),
price number(10),
zs_id number(4)
);
insert into sp values('R001','微波炉',350,2);
insert into sp values('J001','电冰箱',2000,1);
insert into sp values('J002','电视机',3000,1);
insert into sp values('J003','洗衣机',1300,3);
--创建XS表
create table xs(
idno number(4),
sp_id char(6),
numb number(4),
xs_date date
);alter session set nls_date_format='yyyy-mm-dd';insert into xs values(1,'J001',5,'2005-1-15');
insert into xs values(2,'R001',10,'2005-1-15');
insert into xs values(3,'J001',5,'2005-3-3');
insert into xs values(4,'J002',6,'2005-6-20');
insert into xs values(5,'J003',8,'2005-6-22');
insert into xs values(6,'J002',10,'2005-10-20');这道题要求--使用游标变量,查询所有的商品名,单价,制造商,在查询每种商品的销售数量,请问这应该怎么写,新手,请教
create table zs(
zs_id number(4),
zs_name char(4),
tel number(20)
);
insert into zs values(1,'新华',02228345432);
insert into zs (zs_id,zs_name,tel) values (2,'飞跃',null);
insert into zs values(3,'蓝天',02066622834);
--创建SP表
create table sp(
sp_id char(6),
sp_name char(6),
price number(10),
zs_id number(4)
);
insert into sp values('R001','微波炉',350,2);
insert into sp values('J001','电冰箱',2000,1);
insert into sp values('J002','电视机',3000,1);
insert into sp values('J003','洗衣机',1300,3);
--创建XS表
create table xs(
idno number(4),
sp_id char(6),
numb number(4),
xs_date date
);alter session set nls_date_format='yyyy-mm-dd';insert into xs values(1,'J001',5,'2005-1-15');
insert into xs values(2,'R001',10,'2005-1-15');
insert into xs values(3,'J001',5,'2005-3-3');
insert into xs values(4,'J002',6,'2005-6-20');
insert into xs values(5,'J003',8,'2005-6-22');
insert into xs values(6,'J002',10,'2005-10-20');这道题要求--使用游标变量,查询所有的商品名,单价,制造商,在查询每种商品的销售数量,请问这应该怎么写,新手,请教
2 BEGIN
3 OPEN o FOR 'SELECT sp.sp_name, sp.price, zs.zs_name, SUM(xs.numb) numb
4 FROM sp, zs, xs
5 WHERE sp.zs_id = zs.zs_id
6 AND sp.sp_id = xs.sp_id
7 GROUP BY sp.sp_name, sp.price, zs.zs_name';
8 END;
9 /过程已创建。SQL> var o refcursor;
SQL> exec p_info(:o);PL/SQL 过程已成功完成。SQL> print o;SP_NAM PRICE ZS_N NUMB
------ ---------- ---- ----------
电冰箱 2000 新华 10
电视机 3000 新华 16
微波炉 350 飞跃 10
洗衣机 1300 蓝天 8SQL>
SQL> set serveroutput on;
SQL>
SQL> declare
2 cursor goods_info is
3 select sp.sp_name g_name,
4 sp.price g_price,
5 zs.zs_name z_name,
6 sum(xs.numb) num
7 from sp, zs, xs
8 where sp.zs_id = zs.zs_id
9 and sp.sp_id = xs.sp_id
10 group by sp.sp_name, sp.price, zs.zs_name;
11 begin
12 for v_g in goods_info
13 loop
14 dbms_output.put_line(
15 'goods_name = '||v_g.g_name||
16 ',goods_price = '||v_g.g_price||
17 ',Manufacturer = '||v_g.z_name||
18 ',Sales_num = '||v_g.num
19 );
20 end loop;
21 end;
22 /goods_name = 电冰箱,goods_price = 2000,Manufacturer = 新华,Sales_num = 10
goods_name = 电视机,goods_price = 3000,Manufacturer = 新华,Sales_num = 16
goods_name = 微波炉,goods_price = 350,Manufacturer = 飞跃,Sales_num = 10
goods_name = 洗衣机,goods_price = 1300,Manufacturer = 蓝天,Sales_num = 8PL/SQL procedure successfully completed