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

Question

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进行全表扫描

Answer 1

我这么解决的。
先创建一个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');

Answer 2

-- 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;