plsql经典测试题--练习
今天的学习是熟悉PLSQL⼯具，上⽹下载了⼀份"plsqlDeveloper8.0中⽂⽤户⼿册.pdf"，不知道为什么，⾥⾯的内容，我⾃有那么⼀点耐⼼看完前⾯的⼀些操作，后⾯的内容太多，也看不明⽩。
偶然，在百度⽂库中看到了“PLSQL经典测试题”，其链接如下：
花了⼀天的时间，就只解决了这么2道PLSQL题⽬，真杯具，不过，我也就这⽔平了。
在第⼆道题⽬中，测试了下“数据同步⽅法⼀‘，还是存在⼀些代码的编译错误，对我来说，oracle编辑有些陌⽣，但还是硬着头⽪将代码调试出来了。
⾃潮点：
to_date('2010-9-9')，需要百度后，才确认是：to_date('2010-9-9','yyyy-mm-dd');
确认后：
alter table tb_01
modify name varchar(40);
alter table Account
add Pay_sum numeric(11,2);
今⽇的收获：
今天的代码调试如下（如原⽂有些出⼊）：
drop table Account;
create table Account (
acct_No int ,
Date_Created date,
Amount numeric(11,2)
);
insert into Account values(1000,to_date('2010-1-14','yyyy-mm-dd'),900 );
insert into Account values(1001,to_date('2010-4-15','yyyy-mm-dd'),500 );
insert into Account values(1002,to_date('2010-6-20','yyyy-mm-dd'),2000 );
insert into Account values(1003,to_date('2010-8-4','yyyy-mm-dd'),3000 );
insert into Account values(1004,to_date('2010-10-10','yyyy-mm-dd'),4400 );
select * from Account;
commit;
drop table Payment;
create table Payment (
Acct_No int,
Serial_No smallint,
Payment numeric(11,2),
Date_paid date
);
insert into Payment values (1000,1,450,to_date('2010-1-30','yyyy-mm-dd'));
insert into Payment values (1000,2,450,to_date('2010-2-22','yyyy-mm-dd'));
insert into Payment values (1002,1,300,to_date('2010-7-20','yyyy-mm-dd'));
insert into Payment values (1002,2,300,to_date('2010-8-20','yyyy-mm-dd'));
insert into Payment values (1002,3,300,to_date('2010-9-30','yyyy-mm-dd'));
insert into Payment values (1003,1,1000,to_date('2010-9-9','yyyy-mm-dd'));
select * from Payment;
--delete from Payment where acct_no =1003
commit;
-
-current_date: 2010-10-19
--select b.Acct_No, sum(b.Payment) Pay_sum, max(b.Date_paid) Last_act_date from Payment b group by b.Acct_No
/*
select a.acct_no,sum(b.Payment) Pay_sum, max(b.Date_paid) Last_act_date from Account a
left join Payment b
on a.acct_no = b.acct_no
group by a.Acct_No
sqldeveloper安装步骤
order by a.acct_no;
*/
/*
select a.acct_no
,(case when sum(b.Payment) is null then 0 else sum(b.Payment) end) Pay_sum
,(case when max(b.Date_paid) is null then max(a.date_created) else max(b.Date_paid) end) Last_act_date
from Account a
left join Payment b
on a.acct_no = b.acct_no
group by a.Acct_No
order by a.acct_no;
*/
--修改Account表结构，添加Pay_sum和 Last_act_date
select * from Account;
alter table Account
add Pay_sum numeric(11,2);
alter table Account
add Last_act_date date;
--创建存储过程，没弄明⽩为什么要设定两个输⼊参数：aSucc  和aReason？
create or replace procedure Account_datasync_Pro(
aSucc  out integer,
aReason out varchar2
)
is
vSql varchar2(2000);
vCur sys_refcursor;
vAcct_No varchar2(20);
vPay_sum integer;
vLast_act_date date;
begin
vSql :=  'select a.acct_no
,(case when sum(b.Payment) is null then 0 else sum(b.Payment) end) Pay_sum
,(case when max(b.Date_paid) is null then max(a.date_created) else max(b.Date_paid) end) Last_act_date
from Account a
left join Payment b
on a.acct_no = b.acct_no
group by a.Acct_No';
open vCur for vSql;
loop
fetch vCur into vAcct_No,vPay_sum,vLast_act_date;
exit when vCur%notfound;
vSql := 'update Account a set a.Pay_sum =:Pay_sum,a.Last_act_date =: Last_act_date where a.Acct_no = : Acct_No';  execute immediate vSql using vPay_sum,vLast_act_date,vAcct_no;
end loop;
close vCur;
exception
when others
then
dbms_output.put_line(sqlerrm);
end;
--执⾏该存储过程后，查询下数据
select * from Account;
select a.Acct_No
, a.Last_act_date
,(case when sysdate - a.Last_act_date <= 30 then a.Amount - Pay_sum end) "<=30days"
,(case when sysdate - a.Last_act_date > 30 then a.Amount - Pay_sum end) ">30days"
from Account a
where a.Amount - Pay_sum <> 0;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。