真实面试题--Oracle存储过程

[clear]

create table CUSTOMERS --客户表
(id number(8) primary key,taaccoid char(12),name varchar2(100),birthdate number(8),certno varchar2(20),province number(8),age number(4));
1 111000051346 李惠萍 19780304 430111197803043000 2 32
...

create table PROVINCE --省份表
(id number(8) primary key,name varchar2(100));
1 北京市
...

create table TA_SHARE --基金份额表
(taaccoid char(12),fundcode char(6),curshare number(16,2));
111000051346 110002 5228.48

create table PRODUCTS --基金产品表
(fundcode char(6),fundname varchar2(100));
110001 产品1
...


建立存储过程，输入参数：I_FUNDCODE --基金产品,I_PROVINCE --省份
输出参数：O_STATUS --态值,RT_CURSOR --返回游标
返回如下表格的数据:

省份 基金产品 1~20岁（个） 21~40岁（个） 41岁以上（个） 份额





分别在以上表上建立合理的索引，要求存储过程中写出的SQL必须不能对CRM_CUSTOMERS和CRM_TA_SHARE进行全表扫描

[clear]

我这么解决的。
先创建一个view，然后在view基础上查询。当然最后将这些sql转成存储过程。


create or replace view tes_view t as
select pro.name province,cc.fundname fundname,cc.age age,cc.curshare curshare from province pro,
( select cu.province province,sp.fundname fundname,cu.age age,sp.curshare curshare
from customers cu join (
select pr.fundname fundname,ts.curshare curshare,ts.taaccoid taaccoid from ta_share ts join products pr on ts.fundcode=pr.funcode
) sp on cu.taaccoid=sp.taaccoid
) cc where pro.id=cc.province;




然后sql语句

select province,--省份
fundname,-- 基金名称
count(decode(sign(age - 20),-1,'小于20',0,'等于20')) as 不大于20,-- 小于等20岁的个数
count(decode(sign(age - 20),-1,'小于20',0,'不大于20',1,sign(age - 40),-1,'大于20小于40',0,'等于40',1,'大于40'))-count(decode(sign(age - 20),-1,'小于20',0,'等于20'))-count(decode(sign(age-40),1,'大于40')) as 不大于40大于20,-- 总数-小于20岁的-大于40岁的
count(decode(sign(age-40),1,'大于40')) as 大于40,--大于40岁的
sum(curshare) 总份额 --份额
from tes_view s
group by s.province,
s.fundname,
decode(sign(age - 20),
-1,
'小于20',
0,
'不大于20',
1,
sign(age - 40),
-1,
'大于20小于40',
0,
'等于40',
1,
'大于40');

[clear]

上面图片传错了。

[clear]

-- customer create table customers( id number(8), taaccoid varchar2(12), name varchar2(100), province number(8), age number(4) ); -- province create table province( id number(8), name varchar2(100) ); -- 基金份额 create table ta_share( taaccoid varchar2(12), fundcode varchar2(12), curshare number(16) ); -- 基金产品表 create table products( funcode varchar2(12), fundname varchar2(100) ); insert into customers values(1,'111000011111','tom',1,23); insert into customers values(2,'111000088888','tony',1,27); insert into customers values(3,'111000022222','smith',1,19); insert into customers values(4,'111000055555','join',1,34); insert into customers values(5,'111000033333','jack',1,32); insert into customers values(6,'111000044444','obama',1,43); insert into customers values(7,'111000066666','washington',1,54); insert into customers values(8,'111000077777','george',1,50); insert into customers values(9,'111000099999','lincoin',1,18); insert into province values(1,'seattle'); insert into province values(2,'losangeles'); insert into province values(3,'florida'); insert into ta_share values('111000011111','001',1000); insert into ta_share values('111000088888','004',2000); insert into ta_share values('111000022222','004',3000); insert into ta_share values('111000055555','001',4000); insert into ta_share values('111000033333','004',3000); insert into ta_share values('111000044444','002',2000); insert into ta_share values('111000066666','002',1000); insert into ta_share values('111000077777','003',5000); insert into ta_share values('111000099999','002',1000); insert into products values('001','great wall'); insert into products values('002','strong grow up'); insert into products values('003','new world'); insert into products values('004','lose money in china'); select * from products; select * from ta_share; select * from province; select * from customers; drop table ta_share;