简单java做excel解析并验证插入数据库

DATE_OF_BIRTH DATE,

POLITICAL VARCHAR2(30), EMAIL_ADDRESS VARCHAR2(255),

COMPANY_ID NUMBER(10) NOT NULL, CREATE_BY NUMBER(10), LAST_UPDATE_DATE DATE,

LAST_UPDATED_BY NUMBER(15) )

create table EMP_COMPANY (

COMPANY_ID NUMBER(10) PRIMARY KEY, COMPANY_NAME VARCHAR2(20), CREATE_BY NUMBER(10), LAST_UPDATE_DATE DATE,

LAST_UPDATED_BY NUMBER(15) )

CREATE table emp_log (

ERROR_ID NUMBER not null,--序列 ERROR_TEXT VARCHAR2(512), DB_COLUMN VARCHAR2(256), LAST_UPDATE_DATE DATE,

LAST_UPDATED_BY NUMBER(15) )

create table EMP_TEMP (

FULL_NAME VARCHAR2(255) , NATIONAL_IDENTIFIER VARCHAR2(30), SEX VARCHAR2(30), DATE_OF_BIRTH VARCHAR2(30), POLITICAL VARCHAR2(30), EMAIL_ADDRESS VARCHAR2(255), COMPANY_ID VARCHAR2(30) , CREATE_BY VARCHAR2(30), LAST_UPDATE_DATE VARCHAR2(30), LAST_UPDATED_BY VARCHAR2(30) )

13.存储过程

create or replace procedure modifyEmp is fullName varchar2(255);

nationalIdentifier varchar2(30); sex varchar2(30); dateOfBrith date; politi varchar2(30);

emailAddress varchar(255); companyId number(10); nationalCount number; companyCount number; owner number; cou number; couName number; brith varchar2(30); cursor c is select

FULL_NAME ,NATIONAL_IDENTIFIER,SEX,to_date(DATE_OF_BIRTH,'yyyy-mm-dd') ,POLITICAL,EMAIL_ADDRESS,to_number(COMPANY_ID) from emp_temp; begin

select user_id into owner from all_users where username=(select user from dual); open c; loop

fetch c into fullName,nationalIdentifier,sex,dateOfBrith,politi,emailAddress,companyId; exit when c%notfound;

select count(fullName) into couName from emp_basic where FULL_NAME=fullName; if length(fullName)=0 then

insert into emp_log values(emp_seq.nextval,'用户名不能为空','FULL_NAME',sysdate,owner); else

if couName>0 then

insert into emp_log values(emp_seq.nextval,'用户名不能重复','FULL_NAME',sysdate,owner); else

insert into emp_basic values

(fullName,'123','m',sysdate,'d','qq',1,owner,sysdate,owner); end if; end if;

select count(nationalIdentifier) into nationalCount from emp_temp where FULL_NAME=fullName; if nationalCount<1 or length(nationalIdentifier)=0 then insert into emp_log values(emp_seq.nextval,'身份证不能为空','NATIONAL_IDENTIFIER',sysdate,owner); elsif nationalCount=1 then

if length(nationalIdentifier)<>18 then

insert into emp_log values(emp_seq.nextval,'身份证长度不为18位','NATIONAL_IDENTIFIER',sysdate,owner); else

update emp_basic set NATIONAL_IDENTIFIER=nationalIdentifier where FULL_NAME=fullname;

select substr(nationalIdentifier,7,8) into brith from emp_temp where FULL_NAME=fullName;

if to_date(brith,'yyyymmdd')<>dateOfBrith then

insert into emp_log values(emp_seq.nextval,'生日填写不正确

','DATE_OF_BRITH',sysdate,owner); else

update emp_basic set DATE_OF_BIRTH =dateOfBrith where FULL_NAME=fullname; end if; end if; end if;

select count(companyId) into companyCount from emp_temp where FULL_NAME=fullName; if companyCount<1 or length(companyId)=0 then

insert into emp_log values(emp_seq.nextval,'公司ID不能为空','COMPANY_ID',sysdate,owner); else

select count(companyId) into cou from emp_company where COMPANY_ID=companyId; if cou<1 then

insert into emp_log values(emp_seq.nextval,'公司ID不存在','COMPANY_ID',sysdate,owner); else

update emp_basic set COMPANY_ID=companyId where FULL_NAME=fullName; end if; end if;

update emp_basic set SEX=sex where FULL_NAME=fullname;

update emp_basic set POLITICAL=politi where FULL_NAME=fullname; update emp_basic set EMAIL_ADDRESS=emailAddress; end loop; close c; commit; end;