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

[PANMEIMEI]

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

[qin_phoenix]

估计是面试单位遇到难题了。

原来我，心理黑暗了一下下。

[zhaaacn]

这是我写的一条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

[ngx20080110]

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

[zhangwonderful]

[Quote=引用 3 楼 ngx20080110 的回复:]
SQL code

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 PROVIN……
[/Quote]
支持此方法

[forgetsam]

[Quote=引用 1 楼 qin_phoenix 的回复:]
估计是面试单位遇到难题了。

原来我，心理黑暗了一下下。
[/Quote]

搞笑，这么基本的东西怎么会在单位实际工作中遇到，哪有这么简单的业务和结构这么简单的表给你做。

基本的表连接，利用decode分段，行转列，使用存储过程，传出动态游标，建立索引，注意SQL规范不使索引失效，把这么多知识点揉到一起，典型的考试题，只不过像小学生的应用题一样，给你造个场景而已

[kyle7788]

[Quote=引用 3 楼 ngx20080110 的回复:]
SQL code

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 PROVIN……
[/Quote]
UP UP UP

[reality123456]

学习了 3楼的不错

[yjwcwrkks]

procedure sp_liu2(v_fundcode char,v_province in INTEGER,v_states out int,RC1 in out t_ref,RC2 in out t_ref) is

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

[yjwcwrkks]

[Quote=引用 8 楼 yjwcwrkks 的回复:]
procedure sp_liu2(v_fundcode char,v_province in INTEGER,v_states out int,RC1 in out t_ref,RC2 in out t_ref) is

v integer;
BEGIN
if v_fundcode is null and v_province is null then OPEN RC1 FOR SE……
[/Quote]
create or replace package PACK_LIU is
TYPE t_ref IS ref CURSOR;
建个包，定义游标类型。

[yjwcwrkks]

[Quote=引用 9 楼 yjwcwrkks 的回复:]
引用 8 楼 yjwcwrkks 的回复:
procedure sp_liu2(v_fundcode char,v_province in INTEGER,v_states out int,RC1 in out t_ref,RC2 in out t_ref) is

v integer;
BEGIN
if v_fundcode is null and v_province is null……
[/Quote]
create or replace procedure SP_liu1
AS

RC1 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;
测试代码