发布时间 : 星期六 文章简单java做excel解析并验证插入数据库更新完毕开始阅读0523186f011ca300a6c3901d
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;