真实面试题--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

这是我写的一条SQL语句，已经近似的实现了你说的功能，希望对你有用
select c.province, ta.fundcode,
       decode(sign(age-20),-1,'小于等于20',0,'小于等于20',decode(sign(age-40),-1,'大于20小于等于40',0,'大于20小于等于40',1,'41以上'))  年龄段,
       count(decode(sign(age-20),-1,'小于等于20',0,'小于等于20',decode(sign(age-40),-1,'大于20小于等于40',0,'大于20小于等于40',1,'41以上')))该年龄段人数 ,
        sum(ta.curshare)
from customers c join ta_share ta on c.taaccoid=ta.taaccoid
                 join products p on p.fundcode=ta.fundcode
                 join province pv on pv.id=c.province
group by c.province,ta.fundcode,decode(sign(age-20),-1,'小于等于20',0,'小于等于20',decode(sign(age-40),-1,'大于20小于等于40',0,'大于20小于等于40',1,'41以上')) 
order by c.province,ta.fundcode下面是查询结果:
    PROVINCE FUNDCODE 年龄段           该年龄段人数 SUM(TA.CURSHARE)
1    1          1      大于20小于等于40      2             2460.24
2    1          1      小于等于20            3             2935.36
3    1          2      大于20小于等于40      5             2805.6
4    1          2      小于等于20            2             3690.24
5    1          3      大于20小于等于40      2             4250.24
6    1          3      小于等于20            2             6690.24
7    2          1      大于20小于等于40      1             2125.12
8    2          2      小于等于20            3             2835.36
9    2          3      小于等于20            2             4440.24
10    2          4      大于20小于等于40      5             5535.6
11    2          4      小于等于20            2             4190.24
12    3          1      大于20小于等于40      5             13725.6
13    3          1      小于等于20            3             6785.36
14    3          2      大于20小于等于40      1             2845.12
15    3          2      小于等于20            1             2095.12
16    3          3      小于等于20            1             2095.12
17    3          4      大于20小于等于40      1             2125.12
18    3          4      小于等于20            1             2095.12

Answer 2

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));
create table PROVINCE --省份表
(id number(8) primary key,name varchar2(100));
create table TA_SHARE --基金份额表
(taaccoid char(12),fundcode char(6),curshare number(16,2));
create table PRODUCTS --基金产品表
(fundcode char(6),fundname varchar2(100));insert into customers values (1, '111000051346', '李惠萍', 19780304, '430111197803043000', 2, 32);  
insert into customers values (2, '111000051347', '李abcd', 19780304, '430111197803043000', 1, 32);  
insert into customers values (3, '111000051348', 'aaaa', 19780304, '430111197803043000', 1, 56); 
insert into customers values (4, '111000051349', 'bbbb', 19780304, '430111197803043000', 2, 15);insert into province values (1, '北京市');
insert into province values (2, '上海市'); insert into ta_share values ('111000051346', '110001', 1234);
insert into ta_share values ('111000051346', '110002', 5201);
insert into ta_share values ('111000051347', '110001', 1000);
insert into ta_share values ('111000051348', '110001', 23);
insert into ta_share values ('111000051348', '110002', 11);
insert into ta_share values ('111000051349', '110001', 44);insert into products values ('110001','产品1');
insert into products values ('110002','产品2');commit;create index idx_customer_tacct on CUSTOMERS(taaccoid);
create index idx_customer_PROVINCE on CUSTOMERS(PROVINCE);
create index idx_ta_share_tacct on ta_share(taaccoid);
CREATE index idx_products_fcode on products(fundcode);select PV.NAME, PS.FUNDNAME,
count(case when ct.age between 1 and 20 then 1 else null end) "1~20岁",
count(case when ct.age between 21 and 40 then 1 else null end) "21~40岁",
count(case when ct.age > 41 then 1 else null end) "41岁以上",
sum(ts.curshare)
from CUSTOMERS ct, PROVINCE pv, TA_SHARE ts, PRODUCTS ps
where pv.id = ct.province
and ct.taaccoid = TS.TAACCOID
and TS.FUNDCODE = PS.FUNDCODE
group by PV.NAME, PS.FUNDNAME
order by 1, 2;NAME       FUNDNAME        1~20岁     21~40岁      41岁以上 SUM(TS.CURSHARE)
---------- ---------- ---------- ---------- ---------- ----------------
上海市        产品1                 1          1          0             1278
上海市        产品2                 0          1          0             5201
北京市        产品1                 0          1          1             1023
北京市        产品2                 0          0          1               11

Answer 3

搞笑，这么基本的东西怎么会在单位实际工作中遇到，哪有这么简单的业务和结构这么简单的表给你做。基本的表连接，利用decode分段，行转列，使用存储过程，传出动态游标，建立索引，注意SQL规范不使索引失效，把这么多知识点揉到一起，典型的考试题，只不过像小学生的应用题一样，给你造个场景而已

Answer 4

procedure sp_liu2(v_fundcode char,v_province in INTEGER,v_states out int,RC1 in out t_ref,RC2 in out t_ref) isv integer;
BEGIN
  if v_fundcode is null and v_province is null then OPEN RC1 FOR SELECT 99991 AS errorcode, '传入基金产品和省不能为空!' AS errormsg FROM DUAL;
  v_states :=0; 
  else
    begin
      select count(PV.ID) into v from CUSTOMERS ct, PROVINCE pv, TA_SHARE ts, PRODUCTS ps
      where pv.id = ct.province
            and ct.taaccoid = TS.TAACCOID
            and TS.FUNDCODE = PS.FUNDCODE
            AND TS.FUNDCODE=v_fundcode
            AND pv.id=v_province
      group by PV.NAME, PS.FUNDNAME;
      if v=0 then
        Open RC1 FOR SELECT 99991 AS errorcode, '基金产品和省不存在！' AS errormsg FROM DUAL;
        v_states :=0; 
        else
           begin
               Open RC1 FOR SELECT 0 AS errorcode, '查询省数据成功！' AS errormsg FROM DUAL;
               OPEN RC2 FOR
              select PV.NAME, PS.FUNDNAME,
              count(case when ct.age between 1 and 20 then 1 else null end) age1_10,
              count(case when ct.age between 21 and 40 then 1 else null end) age21_40,
              count(case when ct.age > 41 then 1 else null end) age_40,
              sum(ts.curshare) AS curshare
              from CUSTOMERS ct, PROVINCE pv, TA_SHARE ts, PRODUCTS ps
              where pv.id = ct.province
              and ct.taaccoid = TS.TAACCOID
              and TS.FUNDCODE = PS.FUNDCODE
              AND pv.id=v_province
              AND TS.FUNDCODE=v_fundcode
              group by PV.NAME, PS.FUNDNAME;
              v_states :=1; 
            end;
        end if;
     end;
  end if; EXCEPTION
   WHEN OTHERS THEN
      OPEN RC1 FOR SELECT 99991 AS errorcode, '查询错误!' AS errormsg FROM DUAL;
      v_states :=0; 
end sp_liu2;

Answer 5

create or replace package PACK_LIU is
  TYPE t_ref IS ref CURSOR;
建个包，定义游标类型。

Answer 6

create or replace procedure SP_liu1
ASRC1 SYS_REFCURSOR;
RC2 SYS_REFCURSOR;  v_name VARCHAR2(20);
  fundname VARCHAR2(20);
  age1_10  NUMBER;
  age21_40 NUMBER;
  age_40   NUMBER;
  curshare NUMBER;
  v_states INT;
errorcode integer;
errormsg varchar2(50);
begin
         PACK_LIU.sp_liu2('110002',1,v_states,RC1,RC2);
         dbms_output.put_line('1成功0失败: '||to_char(v_states));
       LOOP
        FETCH RC1 INTO errorcode,errormsg;
          EXIT WHEN RC1%NOTFOUND;
          DBMS_OUTPUT.PUT_LINE(errorcode);
          DBMS_OUTPUt.PUT_LINE(errormsg);
       end loop;
       DBMS_OUTPUT.PUT_LINE(errorcode);
       if errorcode<> 99991 THEN
      LOOP
        FETCH RC2 INTO v_name,fundname,age1_10,age21_40,age_40,curshare;
        EXIT WHEN RC2%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name);
        DBMS_OUTPUT.PUT_LINE(fundname);
        DBMS_OUTPUt.PUT_LINE(age1_10);
        DBMS_OUTPUt.PUT_LINE(age21_40);
        DBMS_OUTPUt.PUT_LINE(age_40);
        DBMS_OUTPUt.PUT_LINE(curshare);
      end loop;
      
      end if;
      
end SP_liu1;
测试代码