从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;
解决方案 »
- 数据库删除存储过程
- 求一个建表的存储过程
- 求大神指点oracle挂死的问题ORA-00600: internal error code, arguments: [2023], [0],
- 有什么好的方法测试sql语句的运行时间??
- 新手请各为大哥大姐帮忙!!!
- oracle9i中如何使用DBA工具进行数据库备份和数据导入和导出
- oracle names server启动错误
- 在oracle8.1.6forNT里怎样启动和配置Oracle intelligent agent?TKS!!!
- 我的一道面试sql试题,我不是高手,我不会,是你会做?
- oracle 9.2.0在solaris安装问题
- 求2个表之间数据转换的问题
- 高手指点下 ,请进~~~~
declare
begin
exec importdata( 'sysadmin ');
end;
begin
importdata( 'sysadmin ');
end;
begin
importdata('sysadmin');
end;这样执行是可以的,但是系统提示 参数类型 不对, 然后我把过程的参数改成
CREATE OR REPLACE PROCEDURE IMPORTDATA(userID IN VARCHAR2)
is
begin就可以正常执行了!