-----------------------------------------------------
-- Export file for user JXHEALTH --
-- Created by Administrator on 2007-1-25, 11:08:20 --
-----------------------------------------------------
spool pro.log
prompt
prompt Creating function TO_PID18
prompt ==========================
prompt
create or replace function jxhealth.to_pid18(pid15 in char) return char is
TYPE array_17_number IS VARRAY(17) OF NUMBER;
TYPE array_11_char IS VARRAY(11) OF char;
result varchar2(18);
v_check_number integer := 0;
v_check_char char(1);
v_factor array_17_number := array_17_number(7,
9,
10,
5,
8,
4,
2,
1,
6,
3,
7,
9,
10,
5,
8,
4,
2);
v_mod array_11_char := array_11_char('1',
'0',
'X',
'9',
'8',
'7',
'6',
'5',
'4',
'3',
'2');
begin
if (length(pid15) = 18) then
return pid15;
elsif (length(pid15) = 15) then
result := substr(pid15, 1, 6) || '19' || substr(pid15, 7, 9);
FOR i IN 1 .. 17 LOOP
v_check_number := to_number(substr(result, i, 1)) * v_factor(i) +
v_check_number;
END LOOP;
v_check_number := mod(v_check_number, 11);
v_check_char := v_mod(v_check_number + 1);
result := result || v_check_char;
return result;
else
raise_application_error(-20001,'Length of pid should be 15 or 18!');
end if;
end to_pid18;
/
prompt
prompt Creating procedure PROC_ADD_USER
prompt ================================
prompt
create or replace procedure jxhealth.Proc_Add_User is
v_sqlerrm varchar2(500);
BEGIN
FOR i IN 1 .. 500 LOOP
insert into JXUSER
(USERID,
NAME,
PASSWORD,
PID,
GROUPID,
CONTACT,
EMAIL,
UNITCODE,
REMARK1,
REMARK2,
REMARK3,
REMARK4,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG,
UFLAG)
values
('testuser' || i,
'testuser' || i,
'F379EAF3C831B04DE153469D1BEC345E',
null,
'8888',
'666',
'6s6a@fd.com',
'1',
null,
null,
null,
'FDAAB4E0D287DB9AD6EBF507115C619A',
'admin',
to_date('07-11-2006 13:33:40', 'dd-mm-yyyy hh24:mi:ss'),
'admin',
to_date('07-11-2006 13:33:40', 'dd-mm-yyyy hh24:mi:ss'),
'0',
'1');
end loop;
--rollback;
COMMIT;
EXCEPTION
when others then
v_sqlerrm := substr(SQLERRM, 1, 300);
dbms_output.put_line('ERR=' || v_sqlerrm);
rollback;
END Proc_Add_User;
/
prompt
prompt Creating procedure PROC_INPUT_JXHEALTH
prompt ======================================
prompt
create or replace procedure jxhealth.Proc_Input_JXHEALTH is
--name_list MDSYS.Sdo_Addr_Array := MDSYS.Sdo_Addr_Array();
--pid_list MDSYS.:= MDSYS.SDO_STRING_ARRAY();
v_sqlerrm varchar2(500);
BEGIN
delete from individual;
DELETE FROM CONTACT;
delete from citizenuser;
--select xm into name_list from zxgrxx;
-- 80萬數據插入
insert into individual
(NAME,
PID,
SEX,
BIRTHDAY,
NATIVEPLACE,
NATION,
MARRIAGE,
EDUCATION,
BIRTHPLACE,
RELATION,
SALVATIONCARDID,
MARRIAGEDATE,
CITIZENCARDNO,
CONTACTDIVISION,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG,
NFLAG,
rid)
(select trim(t1.XM),
trim(t1.SFZH),
(case t1.XB
when '1' then
'01'
when '2' then
'02'
else
'03'
end),
to_date(to_char(t1.CSRQ, 'yyyy-mm-dd'), 'yyyy-mm-dd'),
(select t2.codename
from zx_codedetail t2
where t2.codetype = 'QXDM'
and t1.JG = t2.codeid),
t1.MZ,
--(select t2.codename from zx_codedetail t2 where t2.codetype = 'MZ' and t1.mz = t2.codeid),
(case t1.HYZK
when '1' then
'01'
when '2' then
'02'
when '3' then
'03'
when '4' then
'04'
else
'05'
end),
(case t1.WHCD
when '0' then
'00'
else
t1.WHCD
end),
(select t2.codename
from zx_codedetail t2
where t2.codetype = 'QXDM'
and trim(t1.CSD) = t2.codeid),
(case t1.YHZGX
when '1' then
'01'
when '02' then
'01'
when '2' then
'01'
when '3' then
'03'
else
t1.YHZGX
end),
t1.JZZBH,
t1.JHDJRQ,
'00000000',
'02',
'admin',
to_date('2006-11-25', 'yyyy-mm-dd'),
'admin',
to_date('2006-11-25', 'yyyy-mm-dd'),
'0',
'0',
SEQ_INDIVIDUAL.nextval
from zxgrxx t1);
INSERT INTO CONTACT
(RID,
BEGINTIME,
ENDTIME,
PID,
NAME,
CONTACTDIVISION,
PROVINCE,
CITY,
COUNTY,
STREET,
VILLAGE,
ADDRESS,
COMPANY,
ZIPCODE,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG,
GROUPS)
(SELECT SEQ_CONTACT.NEXTVAL,
CSRQ,
ADD_MONTHS(CSRQ, 1200),
trim(SFZH),
trim(XM),
'02',
'33',
'04',
SUBSTR(TCQH, LENGTH(TCQH) - 1, 2),
SUBSTR(SSXZBM, LENGTH(SSXZBM) - 1, 2),
SUBSTR(SSCBM, LENGTH(SSCBM) - 1, 2),
GAJTDZ,
--SZDW 因為現在目前工作單位是不可識別的編碼。所以導入的時候為空
'',
'314001',
'ADMIN',
sysdate(),
'ADMIN',
sysdate(),
'0',
SUBSTR(TRIM(SSZBM), 0, 2)
FROM ZXGRXX);
INSERT INTO CONTACT
(RID,
BEGINTIME,
ENDTIME,
PID,
NAME,
CONTACTDIVISION,
PROVINCE,
CITY,
COUNTY,
STREET,
VILLAGE,
ADDRESS,
COMPANY,
ZIPCODE,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG,
GROUPS)
(SELECT SEQ_CONTACT.NEXTVAL,
CSRQ,
ADD_MONTHS(CSRQ, 1200),
trim(SFZH),
trim(XM),
'03',
'33',
'04',
SUBSTR(TCQH, LENGTH(TCQH) - 1, 2),
SUBSTR(SSXZBM, LENGTH(SSXZBM) - 1, 2),
SUBSTR(SSCBM, LENGTH(SSCBM) - 1, 2),
GAJTDZ,
--SZDW 因為現在目前工作單位是不可識別的編碼。所以導入的時候為空
'',
'314001',
'ADMIN',
sysdate(),
'ADMIN',
sysdate(),
'0',
SUBSTR(TRIM(SSZBM), 0, 2)
FROM ZXGRXX);
--插入市民用戶表
insert into citizenuser
(pid,
name,
PASSWORD,
CITIZENCRADNO,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG)
(select trim(SFZH),
trim(XM),
'F379EAF3C831B04DE153469D1BEC345E',
'0000000000',
'ADMIN',
sysdate(),
'ADMIN',
sysdate(),
'0'
from ZXGRXX);
COMMIT;
EXCEPTION
when others then
v_sqlerrm := substr(SQLERRM, 1, 300);
dbms_output.put_line('ERR=' || v_sqlerrm);
rollback;
END Proc_Input_JXHEALTH;
/
prompt
prompt Creating procedure PROC_INPUT_JXHEALTH_FAMILY
prompt =============================================
prompt
create or replace procedure jxhealth.Proc_Input_JXHEALTH_Family is
v_sqlerrm varchar2(500);
begin
/* insert into Family
(FAMILYNO,
FAMILYRECNO,
CONTACTDIVISION,
FAMILYTYPE,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG,
REMARK1,
MASTERPID,
MASTERNAME)
select lpad(seq_family.nextval, 8, '0'),
TRIM(HKBH),
'01',
(CASE TRIM(JTLB)
WHEN '00' THEN
'01'
ELSE
'05'
END),
'ADMIN',
TO_DATE('2006-11-30', 'YYYY-MM-DD'),
'ADMIN',
TO_DATE('2006-11-30', 'YYYY-MM-DD'),
'0',
TRIM(HKBH) || TRIM(XM),
SFZH,
XM
from zxgrxx
where trim(YHZGX) = '02'; --與戶主關系為'01:本人'*/
--增加家庭信息：
/*
說明：
1、采用戶口編號作為區分不同家庭的依據，
但是由于公安數據中存在2個家庭使用同一個戶口編號的情況，
所以 使用 戶口編號+公安家庭地址 作為區分條件，
2、將公安家庭地址插入到Remark1字段中，在插入家庭成員時作為關聯字段
*/
insert into Family
(FAMILYNO,
FAMILYRECNO,
CONTACTDIVISION,
FAMILYTYPE,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG,
REMARK1,
MASTERPID,
MASTERNAME)
select lpad(seq_family.nextval, 8, '0'),
TRIM(HKBH),
'01',
(CASE TRIM(JTLB)
WHEN '00' THEN
'01'
ELSE
'05'
END),
'ADMIN',
TO_DATE('2006-11-30', 'YYYY-MM-DD'),
'ADMIN',
TO_DATE('2006-11-30', 'YYYY-MM-DD'),
'0',
TRIM(GAJTDZ),
TRIM(SFZH),
TRIM(XM)
from (SELECT HKBH,
MAX(JTLB) JTLB,
MAX(GAJTDZ) GAJTDZ,
SFZH,
MAX(XM) XM
FROM ZXGRXX Z
where (trim(Z.YHZGX) = '02' OR TRIM(Z.YHZGX) = '01')
GROUP BY Z.HKBH, Z.SFZH) A;
COMMIT;
EXCEPTION
when others then
v_sqlerrm := substr(SQLERRM, 1, 300);
dbms_output.put_line('ERR=' || v_sqlerrm);
rollback;
end Proc_Input_JXHEALTH_Family;
/
prompt
prompt Creating procedure PROC_INPUT_JXHEALTH_FMEMBER
prompt ==============================================
prompt
create or replace procedure jxhealth.Proc_Input_JXHEALTH_FMember is
v_sqlerrm varchar2(500);
begin
/* insert into FAMILYMEMBER
(RID,
FAMILYNO,
RELATIONCODE,
PID,
Name,
MEMBERCITIZENCARDNO,
LIVEDIVISION,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG,
MFLAG)
SELECT LPAD(seq_familymember.nextval, 8, 0),
F.familyno,
(case YHZGX
when '02' then
'01'
when '2 ' then --公安個人信息數據中存在'與戶主關系為'2 '的數據,而公安提供的對應共通編碼表中不存在,新增時轉換為'02:其他'
'02'
when '3 ' then --公安個人信息數據中存在'與戶主關系為'3 '的數據,而公安提供的對應共通編碼表中不存在,新增時轉換為'02:其他'
'02'
else
YHZGX
end), --與戶主關系:此處需要按照對應關系更新
TRIM(SFZH),
TRIM(XM),
'',
'01',
'ADMIN',
TO_DATE('2006-11-30', 'YYYY-MM-DD'),
'ADMIN',
TO_DATE('2006-11-30', 'YYYY-MM-DD'),
'0',
'0'
FROM ZXGRXX Z, family F
WHERE TRIM(Z.HKBH) = F.FAMILYRECNO;*/
--增加家庭成員
/*說明：采用戶口編號作為區分不同家庭的依據，
但是由于公安數據中存在2個家庭使用同一個戶口編號的情況，
所以 使用 戶口編號+公安家庭地址 作為區分條件*/
insert into FAMILYMEMBER
(RID,
FAMILYNO,
RELATIONCODE,
PID,
Name,
MEMBERCITIZENCARDNO,
LIVEDIVISION,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG,
MFLAG)
SELECT LPAD(seq_familymember.nextval, 8, 0),
F.familyno,
(case YHZGX
when '01' then
'01'
when '02' then
'01'
when '2 ' then --公安個人信息數據中存在'與戶主關系為'2 '的數據,而公安提供的對應共通編碼表中不存在,新增時轉換為'02:其他'
'02'
when '3 ' then --公安個人信息數據中存在'與戶主關系為'3 '的數據,而公安提供的對應共通編碼表中不存在,新增時轉換為'02:其他'
'02'
else
YHZGX
end), --與戶主關系
TRIM(SFZH),
TRIM(XM),
'000000',
'01',
'ADMIN',
TO_DATE('2006-11-30', 'YYYY-MM-DD'),
'ADMIN',
TO_DATE('2006-11-30', 'YYYY-MM-DD'),
'0',
'0'
FROM ZXGRXX Z
INNER JOIN FAMILY F ON TRIM(Z.HKBH) = F.FAMILYRECNO
AND TRIM(Z.GAJTDZ) = F.Remark1;
commit;
EXCEPTION
when others then
v_sqlerrm := substr(SQLERRM, 1, 300);
dbms_output.put_line('ERR=' || v_sqlerrm);
rollback;
end Proc_Input_JXHEALTH_FMember;
/
prompt
prompt Creating procedure PROC_INPUT_JXHEALTH_F_CONCACT
prompt ================================================
prompt
create or replace procedure jxhealth.Proc_Input_JXHEALTH_F_Concact is
v_sqlerrm varchar2(500);
begin
/* INSERT INTO CONTACT
(RID,
BEGINTIME,
ENDTIME,
FAMILYNO,
CONTACTDIVISION,
PROVINCE,
CITY,
COUNTY,
STREET,
VILLAGE,
ADDRESS,
COMPANY,
ZIPCODE,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG,
GROUPS)
(SELECT SEQ_CONTACT.NEXTVAL,
CSRQ,
ADD_MONTHS(CSRQ, 1200),
F.FAMILYNO,
'01',
'33',
'04',
SUBSTR(TCQH, LENGTH(TCQH) - 1, 2),
SUBSTR(SSXZBM, LENGTH(SSXZBM) - 1, 2),
SUBSTR(SSCBM, LENGTH(SSCBM) - 1, 2),
GAJTDZ,
--SZDW 因為現在目前工作單位是不可識別的編碼。所以導入的時候為空
'',
'314001',
'ADMIN',
TO_DATE('2006-11-25', 'YYYY-MM-DD'),
'ADMIN',
TO_DATE('2006-11-25', 'YYYY-MM-DD'),
'0',
SUBSTR(TRIM(SSZBM), 0, 2)
FROM ZXGRXX Z, FAMILY F
WHERE Z.YHZGX = '02'
AND F.MASTERPID = Z.SFZH
AND F.MASTERNAME = Z.XM);*/
--添加家庭聯系方式
INSERT INTO CONTACT
(RID,
BEGINTIME,
ENDTIME,
FAMILYNO,
CONTACTDIVISION,
PROVINCE,
CITY,
COUNTY,
STREET,
VILLAGE,
ADDRESS,
COMPANY,
ZIPCODE,
CREATEUID,
CREATTIME,
UPDATEUID,
UPDATETIME,
RFLAG,
GROUPS)
(SELECT SEQ_CONTACT.NEXTVAL,
C.BEGINTIME,
C.ENDTIME,
F.FAMILYNO,
'01',
C.PROVINCE,
C.CITY,
C.COUNTY,
C.STREET,
C.VILLAGE,
C.ADDRESS,
C.COMPANY,
C.ZIPCODE,
C.CREATEUID,
C.CREATTIME,
C.UPDATEUID,
C.UPDATETIME,
C.RFLAG,
C.GROUPS
FROM FAMILY F
INNER JOIN CONTACT C ON F.MASTERPID = TRIM(C.PID)
AND F.MASTERNAME = TRIM(C.NAME)
AND C.contactdivision = '02'
AND C.RFLAG = '0');
COMMIT;
EXCEPTION
when others then
v_sqlerrm := substr(SQLERRM, 1, 300);
dbms_output.put_line('ERR=' || v_sqlerrm);
rollback;
end Proc_Input_JXHEALTH_F_Concact;
/
prompt
prompt Creating procedure PROC_UPDATE_I_FAMIRECNO
prompt ==========================================
prompt
create or replace procedure jxhealth.Proc_Update_I_FamiRecNo is
v_sqlerrm varchar2(500);
begin
--更新個人信息表家庭編碼
UPDATE INDIVIDUAL I SET I.FAMILYRECNO = NULL;
UPDATE INDIVIDUAL I
SET I.FAMILYRECNO = (SELECT TRIM(Z.HKBH)
FROM ZXGRXX Z
WHERE Z.SFZH = I.PID
AND Z.XM = I.NAME),
I.RELATION = (SELECT TRIM(Z.YHZGX)
FROM ZXGRXX Z
WHERE Z.SFZH = I.PID
AND Z.XM = I.NAME);
COMMIT;
EXCEPTION
when others then
v_sqlerrm := substr(SQLERRM, 1, 300);
dbms_output.put_line('ERR=' || v_sqlerrm);
rollback;
end Proc_Update_I_FamiRecNo;
/
prompt
prompt Creating procedure PROC_UPDATE_JXHEALTH_CONTACT
prompt ===============================================
prompt
CREATE OR REPLACE PROCEDURE JXHEALTH.PROC_UPDATE_JXHEALTH_CONTACT is
v_sqlerrm VARCHAR2(500);
TYPE JT_RECORD_TYPE IS RECORD(
NAME JT_TZ_RY.XM%TYPE,
PID JT_TZ_RY.SFZH%TYPE,
STREET CHAR(2),
VILLAGE CHAR(2));
jt_record JT_RECORD_TYPE;
CURSOR cur_jt IS
SELECT J.XM, J.SFZH, SUBSTR(J.SAFECODE, 7, 2), SUBSTR(J.SAFECODE, 9, 2)
FROM JT_TZ_RY J;
BEGIN
OPEN cur_jt;
LOOP
FETCH cur_jt
INTO jt_record.NAME, jt_record.PID, jt_record.STREET, jt_record.VILLAGE;
EXIT WHEN cur_jt%NOTFOUND;
UPDATE CONTACT C
SET C.COUNTY = '11',
C.STREET = jt_record.STREET,
C.VILLAGE = jt_record.VILLAGE
WHERE C.PID = jt_record.PID
AND C.NAME = jt_record.NAME
AND C.Contactdivision<>'01';
IF (MOD(cur_jt%rowcount, 100) = 0) THEN
COMMIT;
END IF;
END LOOP;
CLOSE cur_jt;
COMMIT;
EXCEPTION
when others then
v_sqlerrm := substr(SQLERRM, 1, 300);
dbms_output.put_line('ERR=' || v_sqlerrm);
rollback;
end PROC_UPDATE_JXHEALTH_CONTACT;
/
prompt
prompt Creating procedure PROC_UPDATE_PID_TO_18
prompt ========================================
prompt
create or replace procedure jxhealth.PROC_UPDATE_PID_TO_18 is
v_sqlerr varchar2(300);
PROCEDURE UPDATE_PID(v_tablename in varchar2) IS
TYPE cursor_typ IS REF CURSOR;
cur cursor_typ;
v_sqlstr varchar2(200);
v_pid varchar2(15);
v_name varchar2(20);
v_new_pid varchar2(18);
BEGIN
--back up pid to remark1
v_sqlstr := 'update ' || v_tablename || ' t set t.remark1=t.pid where t.remark1 is null';
dbms_output.put_line(v_sqlstr);
execute immediate v_sqlstr;
--update pid
v_sqlstr := 'select t.name, t.pid from ' || v_tablename ||
' t where length(t.pid)=15';
open cur for v_sqlstr;
loop
fetch cur
into v_name, v_pid;
exit when cur%notfound;
v_new_pid := to_pid18(v_pid);
v_sqlstr := 'update ' || v_tablename || ' t set t.pid=''' ||
v_new_pid || ''' where t.pid=''' || v_pid ||
''' and t.name=''' || v_name || '''';
execute immediate v_sqlstr;
end loop;
close cur;
commit;
exception
when others then
v_sqlerr := substr(SQLERRM, 1, 300);
dbms_output.put_line('ERR=' || v_sqlerr);
rollback;
END;
begin
UPDATE_PID('tumors');
end PROC_UPDATE_PID_TO_18;
/
prompt
prompt Creating procedure UPDATE_COMTACT_FAMILY_11
prompt ===========================================
prompt
create or replace procedure jxhealth.UPDATE_COMTACT_FAMILY_11 is
v_sqlerrm VARCHAR2(500);
v_familyno family.familyno%type;
v_familyrecno family.familyrecno%type;
v_street contact.street%type;
v_village contact.village%type;
v_groups contact.groups%type;
v_pid individual.pid%type;
v_name individual.name%type;
CURSOR cur_fn IS
SELECT f.familyno, f.familyrecno, c.street, c.village, c.groups
from family f
join contact c on f.masterpid = c.pid
and f.mastername = c.name
where c.contactdivision = '02'
and c.county = '11'
and f.rflag = '0'
and c.rflag = '0';
CURSOR cur_fm(v_familyno family.familyno%type) IS
SELECT f.pid, f.name FROM familymember f WHERE f.familyno = v_familyno;
begin
OPEN cur_fn;
LOOP
FETCH cur_fn
INTO v_familyno, v_familyrecno, v_street, v_village, v_groups;
EXIT WHEN cur_fn%NOTFOUND;
UPDATE CONTACT C
SET C.COUNTY = '11',
c.street = v_street,
c.village = v_village,
c.groups = v_groups
WHERE c.contactdivision = '01'
and c.county is null
and c.familyno = v_familyno;
open cur_fm(v_familyno);
loop
fetch cur_fm
into v_pid, v_name;
exit when cur_fm%NOTFOUND;
update individual i
set i.familyrecno = v_familyrecno
where i.pid = v_pid
and i.name = v_name;
end loop;
close cur_fm;
if (mod(cur_fn%ROWCOUNT, 100) = 0) then
commit;
end if;
END LOOP;
close cur_fn;
commit;
EXCEPTION
when others then
v_sqlerrm := substr(SQLERRM, 1, 300);
dbms_output.put_line('ERR=' || v_sqlerrm);
rollback;
end UPDATE_COMTACT_FAMILY_11;
/
spool off