存储过程练习

客户定制业务ICD平台有数据表结构如下：

录音文件信息表

字段名类型中文名缺省值描述SerialNo VARCHAR2(20)流水号业务流水号

FilePath VARCHAR2(200)录音文件路

径录音文件路径和文件名

Partid Varchar2(4)分区字段格式为MMDD

StaffNo VARCHAR2(10)业务代表工

号录音的业务代表工号

RecordTimeDATE录音时间记录录音时

间

说明：记录录音文件的文件信息。每个业务可产生一个或多个录音文件。

索引：

Ix_RecordFile_SerialNo (SerialNo)

数据量：约800万

按每天2万个业务需要记录录音文件，每个业务需要记录4个录

音文件估算，保存三个月数据约800万数据量，由于此表数据

量不断累计，数据库任务定时删除3个月以前数据。

1、写一个存储过程，向表中随机插入1000条记录

SerialNo：使用序列方式，自增长。

Filepath：使用随机插入6个字母

Partid：使用随机4位数字

StaffNo：从YTCZ060001……. YTCZ060020 中随机抽取

RecordTime：从2006年8月4日之前的6个月中随机抽取。

2、写一个程序块，循环调用500次此存储过程，保证数据表中存储

50万条记录。

3、查找FilePath相同的行，并删除FilePath相同的重复记录（保存一

条时间最近记录）

4、写一个存储过程，删除3个月前的数据。

5、写一个触发器，每月末最后一天中午12点整，系统提示执行“删

除3个月前数据”的存储过程。

答：

CREATE TABLE RecordFile (

SerialNo VARCHAR2(20),

FilePath VARCHAR2(200),

Partid VARCHAR2(4),

StaffNo VARCHAR2(10),

RecordTime DATE)

CREATE INDEX Ix_RecordFile_SerialNo ON recordfile(SerialNo) PCTFREE 30 TABLESPACE mytbs

create sequence seq_RecordFile

start with 0

minvalue 0;

create or replace procedure add_RecordFile is

begin

for i in 1..1000 loop

insert into RecordFile values

(seq_RecordFile.nextval,

dbms_random.string('u',6),

trunc(dbms_random.value(1000,9999)),

'YTCZ0'||trunc(dbms_random.value(60001,60020)),

to_date('2006-08-04','yyyy-mm-dd')-dbms_random.value(0,181));

end loop;

commit;

end add_RecordFile;

begin

for i in 1..500 loop

add_RecordFile;

end loop;

end;

--------------3、查找FilePath相同的行，并删除FilePath相同的重复记录（保存一条时间最近记录）-----------

delete from t_voice_records maint where

maint.filepath in (select filepath from t_voice_records group by filepath having count(filepath)>1)

--------exists (select filepath from t_voice_records where maint.filepath=filepath group by filepath having ----count(filepath)>1)

and

maint.RecordTime<(select max(RecordTime) from t_voice_records where filepath= maint.filepath);

commit;

--------------------------4、写一个存储过程，删除3个月前的数据----------------------------------

create or replace procedure p_delete_3mothbf

as

begin

execute immediate 'create table temp as select * from t_voice_records where RecordTime>add_months(sysdate,-3)';

execute immediate 'truncate table t_voice_records';

execute immediate 'insert into t_voice_records select * from temp';

execute immediate 'drop table temp';

end;