从insert into --> 复制到最下面的commit; 在PL/SQL中单独执行是正确的,但是直接使用 exec importdate sysadmin 系统就提示 无效的SQL语句,请指点!!!!
is
userID VARCHAR2(200);
begin
insert into ZCSJ
(ID,
SFZHM,
XM,
XB,
CSNY,
QX,
XZ,
XL,
XW,
BYXX,
SXZY,
CSZY,
GZGW,
GZDW,
XZZW,
XRJSZW,
QDSJ,
ZCQDFS,
ZSBH,
PWHMC,
JXJYJL,
ZCJL,
ZSYXQ,
QXZCYY,
GZJLJL)SELECT rtrim(ltrim(ZCSJINPUT.ID)) ID,
rtrim(ltrim(ZCSJINPUT.SFZHM)) SFZHM, --身份证
rtrim(ltrim(ZCSJINPUT.XM)) XM,
rtrim(ltrim(ZCSJINPUT.XB)) XB, --性别
rtrim(ltrim(ZCSJINPUT.CSNY)) CSNY,
QX.ID QXID,
XZ.ID XZID,
XLZL.ID XLZLID,
rtrim(ltrim(ZCSJINPUT.XW)) XW,
rtrim(ltrim(ZCSJINPUT.BYXX)) BYXX,
rtrim(ltrim(ZCSJINPUT.SXZY)) SXZY,
ZY.ID ZYID,
rtrim(ltrim(ZCSJINPUT.GZGW)) GZGW,
rtrim(ltrim(ZCSJINPUT.GZDW)) GZDW,
rtrim(ltrim(ZCSJINPUT.XZZW)) XZZW,
JSZW.ID JSZWID,
rtrim(ltrim(ZCSJINPUT.QDSJ)) QDSJ, --日期
QDFS.ID QDFSID,
rtrim(ltrim(ZCSJINPUT.ZSBH)) ZSBH, --编号
rtrim(ltrim(ZCSJINPUT.PWHMC)) PWHMC,
rtrim(ltrim(ZCSJINPUT.JXJYJL)) JXJYJL,
rtrim(ltrim(ZCSJINPUT.ZCJL)) ZCJL,
rtrim(ltrim(ZCSJINPUT.ZSYXQ)) ZSYXQ,
rtrim(ltrim(ZCSJINPUT.QXZCYY)) QXZCYY,
rtrim(ltrim(ZCSJINPUT.GZJLJL)) GZJLJL
FROM ZCSJINPUT
INNER JOIN JSZW ON rtrim(ltrim(ZCSJINPUT.XRJSZW)) = JSZW.ZWMC
INNER JOIN DW XZ ON XZ.ParentID is not null
and rtrim(ltrim(ZCSJINPUT.XZ)) = XZ.MC
INNER JOIN DW QX ON QX.ParentID is null
and rtrim(ltrim(ZCSJINPUT.QX)) = QX.MC
INNER JOIN XLZL ON rtrim(ltrim(ZCSJINPUT.XL)) = XLZL.XLZLMC
INNER JOIN QDFS ON rtrim(ltrim(ZCSJINPUT.ZCQDFS)) = QDFS.QDFS
INNER JOIN ZY ON rtrim(ltrim(ZCSJINPUT.CSZY)) = ZY.ZYMC
where rtrim(ltrim(ZCSJINPUT.XB)) in ('男', '女')
and not Exists
(select ZCSJ.SFZHM from ZCSJ where ZCSJ.SFZHM = rtrim(ltrim(ZCSJINPUT.SFZHM)))
and SFZ_YZ(rtrim(ltrim(ZCSJINPUT.SFZHM))) = 1
and IMPORTUSER = userID;
COMMIT;
delete FROM ZCSJINPUT
where Exists
(select ZWMC from JSZW where ZWMC = rtrim(ltrim(ZCSJINPUT.XRJSZW)))
and Exists (select MC
from DW
where DW.ParentID is not null
and MC = rtrim(ltrim(ZCSJINPUT.XZ)))
and Exists (select MC
from DW
where DW.ParentID is null
and MC = rtrim(ltrim(ZCSJINPUT.QX)))
and Exists
(select XLZLMC from XLZL where XLZLMC = rtrim(ltrim(ZCSJINPUT.XL)))
and Exists
(select QDFS from QDFS where QDFS = rtrim(ltrim(ZCSJINPUT.ZCQDFS)))
and Exists
(select ZYMC from ZY where ZYMC = rtrim(ltrim(ZCSJINPUT.CSZY)))
and rtrim(ltrim(ZCSJINPUT.XB)) in ('男', '女')
and SFZ_YZ(rtrim(ltrim(ZCSJINPUT.SFZHM))) = 1
and IMPORTUSER = userID;
COMMIT;end importdata;
is
userID VARCHAR2(200);
begin
insert into ZCSJ
(ID,
SFZHM,
XM,
XB,
CSNY,
QX,
XZ,
XL,
XW,
BYXX,
SXZY,
CSZY,
GZGW,
GZDW,
XZZW,
XRJSZW,
QDSJ,
ZCQDFS,
ZSBH,
PWHMC,
JXJYJL,
ZCJL,
ZSYXQ,
QXZCYY,
GZJLJL)SELECT rtrim(ltrim(ZCSJINPUT.ID)) ID,
rtrim(ltrim(ZCSJINPUT.SFZHM)) SFZHM, --身份证
rtrim(ltrim(ZCSJINPUT.XM)) XM,
rtrim(ltrim(ZCSJINPUT.XB)) XB, --性别
rtrim(ltrim(ZCSJINPUT.CSNY)) CSNY,
QX.ID QXID,
XZ.ID XZID,
XLZL.ID XLZLID,
rtrim(ltrim(ZCSJINPUT.XW)) XW,
rtrim(ltrim(ZCSJINPUT.BYXX)) BYXX,
rtrim(ltrim(ZCSJINPUT.SXZY)) SXZY,
ZY.ID ZYID,
rtrim(ltrim(ZCSJINPUT.GZGW)) GZGW,
rtrim(ltrim(ZCSJINPUT.GZDW)) GZDW,
rtrim(ltrim(ZCSJINPUT.XZZW)) XZZW,
JSZW.ID JSZWID,
rtrim(ltrim(ZCSJINPUT.QDSJ)) QDSJ, --日期
QDFS.ID QDFSID,
rtrim(ltrim(ZCSJINPUT.ZSBH)) ZSBH, --编号
rtrim(ltrim(ZCSJINPUT.PWHMC)) PWHMC,
rtrim(ltrim(ZCSJINPUT.JXJYJL)) JXJYJL,
rtrim(ltrim(ZCSJINPUT.ZCJL)) ZCJL,
rtrim(ltrim(ZCSJINPUT.ZSYXQ)) ZSYXQ,
rtrim(ltrim(ZCSJINPUT.QXZCYY)) QXZCYY,
rtrim(ltrim(ZCSJINPUT.GZJLJL)) GZJLJL
FROM ZCSJINPUT
INNER JOIN JSZW ON rtrim(ltrim(ZCSJINPUT.XRJSZW)) = JSZW.ZWMC
INNER JOIN DW XZ ON XZ.ParentID is not null
and rtrim(ltrim(ZCSJINPUT.XZ)) = XZ.MC
INNER JOIN DW QX ON QX.ParentID is null
and rtrim(ltrim(ZCSJINPUT.QX)) = QX.MC
INNER JOIN XLZL ON rtrim(ltrim(ZCSJINPUT.XL)) = XLZL.XLZLMC
INNER JOIN QDFS ON rtrim(ltrim(ZCSJINPUT.ZCQDFS)) = QDFS.QDFS
INNER JOIN ZY ON rtrim(ltrim(ZCSJINPUT.CSZY)) = ZY.ZYMC
where rtrim(ltrim(ZCSJINPUT.XB)) in ('男', '女')
and not Exists
(select ZCSJ.SFZHM from ZCSJ where ZCSJ.SFZHM = rtrim(ltrim(ZCSJINPUT.SFZHM)))
and SFZ_YZ(rtrim(ltrim(ZCSJINPUT.SFZHM))) = 1
and IMPORTUSER = userID;
COMMIT;
delete FROM ZCSJINPUT
where Exists
(select ZWMC from JSZW where ZWMC = rtrim(ltrim(ZCSJINPUT.XRJSZW)))
and Exists (select MC
from DW
where DW.ParentID is not null
and MC = rtrim(ltrim(ZCSJINPUT.XZ)))
and Exists (select MC
from DW
where DW.ParentID is null
and MC = rtrim(ltrim(ZCSJINPUT.QX)))
and Exists
(select XLZLMC from XLZL where XLZLMC = rtrim(ltrim(ZCSJINPUT.XL)))
and Exists
(select QDFS from QDFS where QDFS = rtrim(ltrim(ZCSJINPUT.ZCQDFS)))
and Exists
(select ZYMC from ZY where ZYMC = rtrim(ltrim(ZCSJINPUT.CSZY)))
and rtrim(ltrim(ZCSJINPUT.XB)) in ('男', '女')
and SFZ_YZ(rtrim(ltrim(ZCSJINPUT.SFZHM))) = 1
and IMPORTUSER = userID;
COMMIT;end importdata;
declare
begin
exec importdata( 'sysadmin ');
end;
begin
importdata( 'sysadmin ');
end;
begin
importdata('sysadmin');
end;这样执行是可以的,但是系统提示 参数类型 不对, 然后我把过程的参数改成
CREATE OR REPLACE PROCEDURE IMPORTDATA(userID IN VARCHAR2)
is
begin就可以正常执行了!