sql简单的存储过程和多表查询提问

Question

表一：iii
商品 收款

A001 4000

A001 10000
A002 5000

A003 10000

表二：yyy
商品 id 总价
A001 001 15400
A002 002 12500
A003 003 11000
A004 003 10800

表三：uuu
id name
001 李一
002 李四
003 王三
004 王六

写一个输入商品就显示name的自定义函数；
要求查询李一和王三的商品、name的数据；
写一个输入商品，那么如果收款超过总款50%则print商品，否则print不足的存储过程；
以yyy为主表查询商品、name、已交金额、未交金额；

Answer 1

create table iii as select 'A001' sp, 4000 sk from dual
union all
select 'A001' sp, 10000 sk from dual
union all
select 'A002' sp, 5000 sk from dual
union all
select 'A003' sp, 10000 sk from dual;

create table yyy as select 'A001' sp, '001' id, 15400 zj from dual
union all
select 'A002' sp, '002' id, 12500 zj from dual
union all
select 'A003' sp, '003' id, 11000 zj from dual
union all
select 'A004' sp, '003' id, 10800 zj from dual;

create table uuu as select '001' id, '李一' name from dual
union all
select '002' sp, '李四' sk from dual
union all
select '003' sp, '王三' sk from dual
union all
select '004' sp, '王六' sk from dual;

create or replace function getName(spname varchar2) return varchar2 is
Result varchar2(20);
begin
select a.name into Result from uuu a, yyy b where a.id = b.id and b.sp = spname;
return(Result);
end getName;

create or replace procedure printInfo(spName in varchar2) is

zj number := 0;
sk number := 0;

begin
select nvl((select zj from yyy where sp = spName), 0) into zj from dual;
if zj = 0 then dbms_output.put_line('未找到该商品');
else
select nvl((select sk from iii where sp = spName), 0) into sk from dual;
if sk/zj > 0.5 then --这里根据实际情况，改为>=
dbms_output.put_line(spName);
else
dbms_output.put_line('商品不足');
end if;
end if;
end printInfo;

SELECT sp 商品,
(select name from uuu where uuu.id = yyy.id) name,
nvl((select sum(sk) from iii where iii.sp = yyy.sp), 0) 已交,
zj - nvl((select sum(sk) from iii where iii.sp = yyy.sp), 0) 未交
FROM yyy;