oracle如何验证导入完整,oracle验证批量导入存储过程-CSDN博客

1、存储过程

create or replace procedure pro_Test_Admin_Table(testadmintable in varchar2,resultValue out number) is

/**

功能：对一个批量添加的验证, 返回结果： 1 ：成功 0：失败 -1 :出现异常

作者：王聘群

时间：2012-06-01

**/

v_sql varchar2(4000);

v_count number;

begin

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''编号不能为空,请确认!'' where t.fal is null and t.id is null';

execute immediate v_sql;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''编号已经存在,请确认!'' where t.fal is null and exists(select 1 from admin_table a where a.id=t.id)';

execute immediate v_sql;

commit;

v_sql :='update '||testadmintable||' t3 set t3.fal=''失败'',t3.mark=''存在重复记录，请确认!'' where t3.rowid in (select t0.rowid from test_admin_table t0 ,

(select t1.id,min(t1.rowid) row_id from test_admin_table t1

where exists(select 1 id from test_admin_table t0 where t0.id=t1.id group by t0.id having 2<= count(t0.id)) group by t1.id) t2

where t0.id = t2.id and t0.rowid<>t2.row_id)';

execute immediate v_sql;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''名称不能为空,请确认!'' where t.fal is null and t.name is null';

execute immediate v_sql;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''年龄不能为空,请确认!'' where t.fal is null and t.age is null';

execute immediate v_sql;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''年龄必须为数字,请确认!'' where t.fal is null and 0 = is_number(t.age)';

execute immediate v_sql;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''年龄必须为整型数字,请确认!'' where t.fal is null and INSTR(t.age,''.'')<>0';

execute immediate v_sql;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''年龄必须为数字,并且大于0小于150,请确认!'' where t.fal is null and ((select to_number(t.age) from dual) < 1 or (select to_number(t.age) from dual) > 150)';

execute immediate v_sql;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''工资不能为空,请确认!'' where t.fal is null and t.salary is null';

execute immediate v_sql;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''工资必须为数字,请确认!'' where t.fal is null and 0=is_number(t.salary)';

execute immediate v_sql;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''出生日期不能为空,请确认!'' where t.fal is null and t.brithday is null';

execute immediate v_sql;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''失败!'',t.mark=''出生日期必须为时间类型，并且格式为:年-月-日,请确认!'' where t.fal is null and 0=is_date(t.brithday,''yyyy-mm-dd'')';

execute immediate v_sql;

commit;

v_sql := 'select count(1) from '||testadmintable||' t where t.fal is not null';

execute immediate v_sql into v_count;

commit;

v_sql := 'update '||testadmintable||' t set t.fal=''成功'', t.mark=''验证通过!'' where t.fal is null';

execute immediate v_sql;

commit;

if v_count > 0 then

resultValue := 0;

else

resultValue := 1;

end if;

exception

when others then

resultValue := -1;

end;

2、验证结果表

-- Create table

create table TEST_ADMIN_TABLE

(

ID VARCHAR2(1000),

NAME VARCHAR2(100),

AGE VARCHAR2(1000),

SALARY VARCHAR2(1000),

BRITHDAY VARCHAR2(100),

FAL VARCHAR2(1000),

MARK VARCHAR2(1000)

)

3、验证结果信息

1 1 张* 24 2500 2012-05-23 失败! 编号已经存在,请确认! AAANQaAABAAAPC6AAA

2 2 余** 23 2530 2012-05-01 成功验证通过! AAANQaAABAAAPC6AAB

3 叶*20 2730 2012-06-20 失败! 编号不能为空,请确认! AAANQaAABAAAPC6AAC

4 3 25 5000 2012-01-20 失败! 名称不能为空,请确认! AAANQaAABAAAPC6AAD

5 4 周** 24 xx 2012-03-20 失败! 工资必须为数字,请确认! AAANQaAABAAAPC6AAE

6 5 王挺* 20 6000 2012-03-20ysm 失败! 出生日期必须为时间类型，并且格式为:年-月-日,请确认! AAANQaAABAAAPC6AAF

7 2 余** 23 2530 2012-05-01 失败存在重复记录，请确认! AAANQaAABAAAPC6AAG

8 6 刘** 24 5600 2012-05-05 成功验证通过! AAANQaAABAAAPC6AAH

9 7 黄* xsa 5621 2012-01-28 失败! 年龄必须为数字,请确认! AAANQaAABAAAPC6AAI

10 2 余** 23 2530 2012-05-01 失败存在重复记录，请确认! AAANQaAABAAAPC6AAJ

11 8 王** 20 4600 2012-02-21 成功验证通过! AAANQaAABAAAPC6AAK

12 6 刘** 24 5600 2012-05-05 失败存在重复记录，请确认! AAANQaAABAAAPC6AAL

13 9 小纬子 5200 2012-05-13 失败! 年龄不能为空,请确认! AAANQaAABAAAPC6AAM

14 10 小顺子 23 2012-06-28 失败! 工资不能为空,请确认! AAANQaAABAAAPC6AAN

15 11 小燕子 20 4500 失败! 出生日期不能为空,请确认! AAANQaAABAAAPC6AAO

16 12 小川子 24.5 4500 2012-05-23 失败! 年龄必须为整型数字,请确认! AAANQaAABAAAPC6AAP

17 13 小叶子 24 6300 2012-14-20 10:20:10 失败! 出生日期必须为时间类型，并且格式为:年-月-日,请确认! AAANQaAABAAAPC6AAQ

18 14 小君子 0 5630 2012-05-23 失败! 年龄必须为数字,并且大于0小于150,请确认! AAANQaAABAAAPC6AAR

19 15 小川川 400 6800 2012-05-05 失败! 年龄必须为数字,并且大于0小于150,请确认! AAANQaAABAAAPC6AAS