oracle基础案例（1）

select * from o_mdm_ac_rel   --账号与客户介质表
select * from o_dd_mst       --活期存款主文件
select * from o_trace_log    --业务流水
（一个账户存在多个账号,一个客户存在一个账号信息）
--1.交易流水中找出账户为101214673的卡号交易用子查询的方式

select * from o_trace_log where ac_id=101214673;

select * from o_trace_log where ac_no in
 (select ac_no from o_mdm_ac_rel where ac_id=101214673)

--2.在活期账户主文件中找出徐耀华的账户用exists实现
select d.* from o_dd_mst d where exists
   (select * from o_mdm_ac_rel m where m.name='徐耀华' and d.ac_id=m.ac_id)

--3.当天没有进行交易，且账户余额超过1000元的账户有多少人？用not exists实现
select  count(*) from o_dd_mst m where not exists
  (select  ac_id from o_trace_log t where t.ac_id=m.ac_id  )
and bal>1000
 
--4.开户日期最早的第5人到第10人的数据
select s.* from 
 (select rownum rn,e.* from
 (select d.* from o_dd_mst d where length(opn_date)=8 order by d.opn_date) e)s
where rn between 5 and 10;

--5.找出交易流水中名字长度大于4位的数据
select * from o_trace_log where length(name)>4;

--6.查询过程中，把前三位为承德市的数据替换为河北省承德市，只能用函数实现。

select replace(name,'承德市','河北省承德市'),concat('河北省',name)
from o_mdm_ac_rel  where name like '承德市%';


--7.活期账户主文件中，每一个机构下的当日平均余额，要求展示开户机构编码，

--平均余额1（小数位2位不进位），平均余额2（只展示整数，百位以上位有数据）

select trunc(avg(bal),2) 平均余额1,trunc(avg(bal),-2) 平均余额2,
opn_br_no from o_dd_mst group by opn_br_no;


--8.写出以个plsql块，定义变量分别为100011359,100150515，通过if－then的方法，
--判别账户是对公账户还是对私账户，并打印出结果（客户名称XXX的账户XXX为对公账户）。

ac_type 1-4 对公  5-6  对私

select * from o_mdm_ac_rel   --账号与客户介质表
select * from o_dd_mst       --活期存款主文件
select * from o_trace_log    --业务流水
declare
  name1 o_mdm_ac_rel.name%type;
  ac_id1 o_mdm_ac_rel.ac_id%type;
  ac_type1 o_mdm_ac_rel.ac_type%type;
begin
  select name.as_id,ac_type into name1,ac_id1,ac_type1 from o_mdm_ac_rel m
  where ac_id='&id';
 if ac_type1=5 or as_type1=6 then
   dbms_output.put_line('客户名称' ||m.name||'的账户' ||m.ac_id1||'为对私账户');
 else
   dbms_output.put_line('客户名称' ||m.name||'的账户' ||m.ac_id1||'为对私账户');
 end if;
end;
 
--9.对于以下三个账户设置变量100930256、100784563、101464960，查询三个账户
--在交易流水表中的金额最大的一笔交易进行判别，通过case－when的方法，
--如果金额小于5万则显示无须授权操作，如果金额小于5万且金额大于5万的90%，

--显示接近授权操作，如果金额大于等于5万显示该笔需要授权操作。  

declare 
 max_amt o_trace_log.amt%type;
 cursor a is select ac_id,max(amt) max_amt from o_trace_log
  where ac_id='100930256' or ac_id='100784563' or ac_id='101464960'
  group by ac_id;
begin
 for b in a loop
  case 
   when b.max_amt<5000 and b.max_amt>4500 then dbms_output.put_line('接近授权');
   when b.max_amt>=5000 then dbms_output.put_line('需要授权操作');
   when b.max_amt<5000 then dbms_output.put_line('无授权操作');
  end case;
 end loop;
end;    --编写case -when 结构时，其中一定要有成立的条件，
        --或者直接在case—when结构中加相应的else

--10.使用loop－end loop 查询13082001机构下于2015年建立的
--每个账户计算当天需要支付的利息（可以剔除余额为0），

--进行打印，并打印本次支付利息的总金额。（配合num进行循环） 

declare
ac_id1 o_mdm_ac_rel.ac_id%type;
lixi1 number(20);
cursor a is select ac_id,sum(bal*0.0072/365) lixi from o_dd_mst
where ac_id in(select m.ac_id from o_mdm_ac_rel where 
    opn_br=13082001 and substr(beg_date,1,4)=2015) group by ac_id;
begin
open a;
loop
fetch a into ac_id1,lixi1;
dbms_output.put_line('账户为'||ac_id1||'的需要付利息'||lixi1);
exit when a%notfound;
end loop;
close a;
end;

--11、使用while 循环，打印13020201下2015年1月活期账户的

--客户姓名，客户编号、客户的生日。

declare 
cursor bbb is select name,AC_ID,substr(id_on,7,8) from o_mdm_ac_rel where OPN_BR_NO=13020201
and substr(beg_date,1,6)=201501;
name1 o_mdm_ac_rel.name%type;
ac_id1 o_mdm_ac_rel.ac_id%type;
id_no1 o_mdm_ac_rel.id_no%type;
begin
oppn bbb;
fetch bbb into name1,ac_id1,id_no1;
while bbb%found loop
dbms_output.put_line('客户姓名'||name1||',客户编号'||ac_id1||',客户生日'||id_no1);
fetch bbb into name1,ac_id1,id_no1;
end loop;
close bbb;
end;

--12、使用for循环，显示交易流水表中，当天累计交易金额前50名的帐户

--及其交易总金额，（增减标志进行判别0是出帐1是入账，区分对待）；

declare
 cursor ccc is select * from (select a.*,rownum r from
  (select ac_id,sum(amt) sum_amt from o_trace_log 
    group by ac_id order by sum_amt desc)a) where r<=50;
begin
 for ddd in ccc loop
   dbms_output.put_line('账号'||ddd.ac_id||',交易总金额'||ddd.sum_amt);
 end loop;
end;

--13、1995年之前开户的客户编码，客户名称及该客户的账户信息，卡号信息

--必须保证游标使用两种。

select * from o_dd_mst       --日期 OPN_DATE--活期存款主文件  

declare
cursor c_yb is  select * from 
   (select d.cif_no ,m.name ,m.ac_id ,m.ac_no 
   from o_dd_mst d left join o_mdm_ac_rel m on d.ac_id=m.ac_id
    where opn_date<1995);
begin
  for a in c_yb loop
  dbms_output.put_line(a.cif_no ||'：客户编号  '||a.name||
             '：客户姓名  '||a.ac_id ||'：账号id  '||a.ac_no ||'：卡号信息');
  end loop;
end;
         
--14、创建一个函数，通过传入卡号，获取客户号

--应用在交易流水中，查询数据时，可以使用该函数获取客户编码。

create or replace function fc_yh(f_ac_no o_mdm_ac_rel.ac_no%type)
return number       
is 
  k_cif_no o_dd_mst.cif_no%type;
begin
  select d.cif_no into k_cif_no from o_mdm_ac_rel m,o_dd_mst d 
  where m.ac_id=d.ac_id and ac_no=f_ac_no; 
return k_cif_no;  
end; 
   select fc_yh(ac_no) from o_mdm_ac_rel