oracle跨用户数据导入案例

CREATE
OR REPLACE PROCEDURE OMGEMP_PER2USER IS
BEGIN
--delete cap_user data for liuk-2016-01-11
DELETE
FROM
CAP_USER C1
WHERE
C1.OPERATOR_ID >= 100000 ;
--insert cap_user data for liuk-2016-01-11
INSERT INTO CAP_USER C1 (
C1.OPERATOR_ID,
C1.TENANT_ID,
C1.USER_ID,
C1. PASSWORD,
C1.INVALDATE,
C1.USER_NAME,
C1.AUTHMODE,
C1.STATUS,
C1.UNLOCKTIME,
C1.MENUTYPE,
C1.LASTLOGIN,
C1.ERRCOUNT,
C1.STARTDATE,
C1.ENDDATE,
C1.VALIDTIME,
C1.MACCODE,
C1.IPADDRESS,
C1.EMAIL,
C1.CREATEUSER,
C1.CREATETIME
) SELECT
T1.PERSON_ID,
--OPERATOR_ID
'default',
--TENANT_ID
T1.PERSON_ID,
--USER_ID
'k2xvHUmCHWw=',
--PASSWORD
'',
--INVALDATE
T1.PERSON_NAME,
--USER_NAME
'local',
--AUTHMODE
'1',
--STATUS
TO_DATE ('19-JUN-01', 'dd-mon-yy'),
--UNLOCKTIME
'default',
--MENUTYPE
TO_DATE ('19-JUN-01', 'dd-mon-yy'),
--LASTLOGIN
'',
--ERRCOUNT
TO_DATE ('19-JUN-01', 'dd-mon-yy'),
--STARTDATE
'',
--ENDDATE
'',
'',
'',
'',
'sysadmin',
--CREATEUSER
TO_DATE ('19-JUN-01', 'dd-mon-yy') --CREATETIME
FROM
TCIMS.PERSON T1
WHERE
T1.login_id IS NOT NULL ;

--delete cap_user data for liuk-2016-01-11
DELETE
FROM
ORG_EMPLOYEE O1
WHERE
O1.EMPID >= 100000 ;

--inser person_info data 2016/01/11 for liuk
INSERT INTO ORG_EMPLOYEE O1 (
O1.EMPID,
O1.EMPCODE,
O1.OPERATORID,
O1.USERID,
O1.EMPNAME,
O1.REALNAME,
O1.GENDER,
O1.BIRTHDATE,
O1.POSITION,
O1.EMPSTATUS,
O1.CARDTYPE,
O1.CARDNO,
O1.INDATE,
O1.OUTDATE,
O1.OTEL,
O1.OADDRESS,
O1.OZIPCODE,
O1.OEMAIL,
O1.FAXNO,
O1.MOBILENO,
O1.QQ,
O1.HTEL,
O1.HADDRESS,
O1.HZIPCODE,
O1.PEMAIL,
O1.PARTY,
O1. DEGREE,
O1.SORTNO,
O1.MAJOR,
O1.SPECIALTY,
O1.WORKEXP,
O1.REGDATE,
O1.CREATETIME,
O1.LASTMODYTIME,
O1.ORGIDLIST,
O1.ORGID,
O1.REMARK,
O1.TENANT_ID,
O1.APP_ID,
O1.WEIBO
) SELECT
T1.PERSON_ID,
--EMPID
T1.PERSON_ID,
--EMPCODE
T1.PERSON_ID,
--OPERATORID
T1.PERSON_ID,
--USERID
T2.PERSON_NAME,
--EMPNAME
T2.PERSON_NAME,
--REALNAME
'',
--GENDER
'',
--BIRTHDATE
'',
--POSITION
'',
--EMPSTATUS
'',
--CARDTYPE
'',
--CARDNO
'',
--INDATE
'',
--OUTDATE
'',
--OTEL
'',
--OADDRESS
'',
--OZIPCODE
'',
--OEMAIL
'',
--FAXNO
T1.INFO_VALUE,
--MOBILENO
'',
--QQ
'',
--HTEL
'',
--HADDRESS
'',
--HZIPCODE
'',
--PEMAIL
'',
--PARTY
'',
--DEGREE
'',
--SORTNO
'',
--MAJOR
'',
--SPECIALTY
'',
--WORKEXP
'',
--REGDATE
TO_DATE ('19-JUN-01', 'dd-mon-yy'),
--CREATETIME
TO_DATE ('19-JUN-01', 'dd-mon-yy'),
--LASTMODYTIME
'',
--ORGIDLIST
'',
--ORGID
'',
--REMARK
'default',
--TENANT_ID
'',
--APP_ID
'' --WEIBO
FROM
TCIMS.PERSON_INFO T1,
TCIMS.PERSON T2
WHERE
T1.info_name = '移动电话'
AND T1.priority = 0
AND T1.PERSON_ID = T2.PERSON_ID
AND LENGTH (T1.INFO_VALUE) < 12 ; EXCEPTION --记录异常日志：
WHEN OTHERS THEN
ROLLBACK ; COMMIT ; RETURN ;
END ;

--本案例仅供个人学习使用，不喜勿喷！

--如果报编译错误或者权限问题，需要给用户授权：

grant all on primeton.cap_user to tcims

--注：两个用户都相互执行一次即可执行存储过程