5,889
社区成员
发帖
与我相关
我的任务
分享
--用户表
create table Users
( uid int primary key not null,
uname varchar (30)
);
--商品表
create table goods
(
gid int primary key not null,
goods_name varchar (200)
);
--商品属性表
create table goods_attribute
(
aid int primary key not null,
gid int,
model_name varchar (200) ,
price decimal(10,2)
);
--用户商品订单表
create table OrderInfo
( oid int primary key not null,
uid int ,
gid int
);
insert into Users values(1, '张三');
insert into Users values(2, '李四');
insert into Users values(3, '王五');
insert into goods values(1, '华为手机');
insert into goods values(2, '联想电脑');
insert into goods values(3, '比亚迪汽车');
insert into goods values(4, '汤臣别墅');
insert into goods values(5, '佳能相机');
insert into goods_attribute values(1, 1,'N8888',1888);
insert into goods_attribute values(2, 2,'L1688',4680);
insert into goods_attribute values(3, 3,'E6SF',88888);
insert into goods_attribute values(4, 4,'豪华',8888888);
insert into goods_attribute values(5, 5,'600D',4888);
insert into orderinfo values(1,1,1);
insert into orderinfo values(2,1,2);
insert into orderinfo values(3,1,3);
insert into orderinfo values(4,1,4);
insert into orderinfo values(5,2,1);
CREATE PROCEDURE DB2ADMIN.GETUSERGOODS()
RESULT SETS 1
LANGUAGE SQL
P: BEGIN
DECLARE end_state INT DEFAULT 0;
DECLARE good_id_it INT DEFAULT 0;
DECLARE good_id VARCHAR(10);
DECLARE sql_str VARCHAR(10000);
DECLARE stmt STATEMENT;
DECLARE rscur CURSOR WITH RETURN TO CALLER FOR stmt;
DECLARE cycur CURSOR FOR SELECT gid FROM goods;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_state=1;
SET sql_str='SELECT u.uid,u.uname';
OPEN cycur;
FETCH cycur INTO good_id_it;
WHILE end_state=0 DO
SET good_id=RTRIM(CAST(good_id_it AS CHAR(10)));
SET sql_str=sql_str||',MAX((CASE g.gid WHEN '||good_id||' THEN g.goods_name ELSE NULL END)) AS goods_name'||good_id||',MAX((CASE ga.gid WHEN '||good_id||' THEN ga.model_name ELSE NULL END)) AS model_name'||good_id||',MAX((CASE ga.gid WHEN '||good_id||' THEN ga.price ELSE NULL END)) AS price'||good_id;
FETCH cycur INTO good_id_it;
END WHILE;
CLOSE cycur;
SET sql_str=sql_str||' FROM Users u LEFT JOIN orderinfo o ON u.uid=o.uid LEFT JOIN goods g ON o.gid=g.gid LEFT JOIN goods_attribute ga ON g.gid=ga.gid GROUP BY u.uid,u.uname';
PREPARE stmt FROM sql_str;
OPEN rscur;
END P
---------------------------------------------------
CALL DB2ADMIN.GETUSERGOODS();