CREATE PROCEDURE dbo.importdata
@userID varchar(100)
ASinsert into dbo.ZCSJ
(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(dbo.ZCSJINPUT.SFZHM)) SFZHM, --身份证
rtrim(ltrim(dbo.ZCSJINPUT.XM)) XM,
rtrim(ltrim(dbo.ZCSJINPUT.XB)) XB, --性别
rtrim(ltrim(dbo.ZCSJINPUT.CSNY)) CSNY,
QX.ID AS QXID,
XZ.ID AS XZID,
dbo.XLZL.ID AS XLZLID,
rtrim(ltrim(dbo.ZCSJINPUT.XW)) XW,
rtrim(ltrim(dbo.ZCSJINPUT.BYXX)) BYXX,
rtrim(ltrim(dbo.ZCSJINPUT.SXZY)) SXZY,
dbo.ZY.ID AS ZYID,
rtrim(ltrim(dbo.ZCSJINPUT.GZGW)) GZGW,
rtrim(ltrim(dbo.ZCSJINPUT.GZDW)) GZDW,
rtrim(ltrim(dbo.ZCSJINPUT.XZZW)) XZZW,
dbo.JSZW.ID AS JSZWID,
rtrim(ltrim(dbo.ZCSJINPUT.QDSJ)) QDSJ, --日期
dbo.QDFS.ID AS QDFSID,
rtrim(ltrim(dbo.ZCSJINPUT.ZSBH)) ZSBH, --编号
rtrim(ltrim(dbo.ZCSJINPUT.PWHMC)) PWHMC,
rtrim(ltrim(dbo.ZCSJINPUT.JXJYJL)) JXJYJL,
rtrim(ltrim(dbo.ZCSJINPUT.ZCJL)) ZCJL,
rtrim(ltrim(dbo.ZCSJINPUT.ZSYXQ)) ZSYXQ,
rtrim(ltrim(dbo.ZCSJINPUT.QXZCYY)) QXZCYY,
rtrim(ltrim(dbo.ZCSJINPUT.GZJLJL)) GZJLJL
FROM dbo.ZCSJINPUT INNER JOIN
dbo.JSZW ON rtrim(ltrim(dbo.ZCSJINPUT.XRJSZW)) = dbo.JSZW.ZWMC INNER JOIN
dbo.DW XZ ON XZ.ParentID is not null and rtrim(ltrim(dbo.ZCSJINPUT.XZ)) = XZ.MC INNER JOIN
dbo.DW QX ON QX.ParentID is null and rtrim(ltrim(dbo.ZCSJINPUT.QX)) = QX.MC INNER JOIN
dbo.XLZL ON rtrim(ltrim(dbo.ZCSJINPUT.XL)) = dbo.XLZL.XLZLMC INNER JOIN
dbo.QDFS ON rtrim(ltrim(dbo.ZCSJINPUT.ZCQDFS)) = dbo.QDFS.QDFS INNER JOIN
dbo.ZY ON rtrim(ltrim(dbo.ZCSJINPUT.CSZY)) = dbo.ZY.ZYMC
where rtrim(ltrim(dbo.ZCSJINPUT.XB)) in ('男','女')
and not Exists (select dbo.ZCSJ.SFZHM from dbo.ZCSJ where dbo.ZCSJ.SFZHM=rtrim(ltrim(dbo.ZCSJINPUT.SFZHM)))
and dbo.SFZ_YZ(rtrim(ltrim(dbo.ZCSJINPUT.SFZHM)))=1
and IMPORTUSER=@userIDdelete
FROM dbo.ZCSJINPUT
where
Exists (select ZWMC from dbo.JSZW where ZWMC=rtrim(ltrim(dbo.ZCSJINPUT.XRJSZW)))
and Exists (select MC from dbo.DW where DW.ParentID is not null and MC=rtrim(ltrim(dbo.ZCSJINPUT.XZ)))
and Exists (select MC from dbo.DW where DW.ParentID is null and MC=rtrim(ltrim(dbo.ZCSJINPUT.QX)))
and Exists (select XLZLMC from dbo.XLZL where XLZLMC=rtrim(ltrim(dbo.ZCSJINPUT.XL)))
and Exists (select QDFS from dbo.QDFS where QDFS=rtrim(ltrim(dbo.ZCSJINPUT.ZCQDFS)))
and Exists (select ZYMC from dbo.ZY where ZYMC=rtrim(ltrim(dbo.ZCSJINPUT.CSZY)))
and rtrim(ltrim(dbo.ZCSJINPUT.XB)) in ('男','女')
and dbo.SFZ_YZ(rtrim(ltrim(dbo.ZCSJINPUT.SFZHM)))=1
and IMPORTUSER=@userID
select 1
GO上面这个是SQL中的存储过程,我把它修改为 ORACLE的存储过程如下 就动了一个声明的变量userid 系统就对这个变量的声明报了错。
CREATE PROCEDURE importdata
userID VARCHAR2
ASinsert into ZCSJ
(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.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;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;
select 1
GO请指点下 谢谢了~!
@userID varchar(100)
ASinsert into dbo.ZCSJ
(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(dbo.ZCSJINPUT.SFZHM)) SFZHM, --身份证
rtrim(ltrim(dbo.ZCSJINPUT.XM)) XM,
rtrim(ltrim(dbo.ZCSJINPUT.XB)) XB, --性别
rtrim(ltrim(dbo.ZCSJINPUT.CSNY)) CSNY,
QX.ID AS QXID,
XZ.ID AS XZID,
dbo.XLZL.ID AS XLZLID,
rtrim(ltrim(dbo.ZCSJINPUT.XW)) XW,
rtrim(ltrim(dbo.ZCSJINPUT.BYXX)) BYXX,
rtrim(ltrim(dbo.ZCSJINPUT.SXZY)) SXZY,
dbo.ZY.ID AS ZYID,
rtrim(ltrim(dbo.ZCSJINPUT.GZGW)) GZGW,
rtrim(ltrim(dbo.ZCSJINPUT.GZDW)) GZDW,
rtrim(ltrim(dbo.ZCSJINPUT.XZZW)) XZZW,
dbo.JSZW.ID AS JSZWID,
rtrim(ltrim(dbo.ZCSJINPUT.QDSJ)) QDSJ, --日期
dbo.QDFS.ID AS QDFSID,
rtrim(ltrim(dbo.ZCSJINPUT.ZSBH)) ZSBH, --编号
rtrim(ltrim(dbo.ZCSJINPUT.PWHMC)) PWHMC,
rtrim(ltrim(dbo.ZCSJINPUT.JXJYJL)) JXJYJL,
rtrim(ltrim(dbo.ZCSJINPUT.ZCJL)) ZCJL,
rtrim(ltrim(dbo.ZCSJINPUT.ZSYXQ)) ZSYXQ,
rtrim(ltrim(dbo.ZCSJINPUT.QXZCYY)) QXZCYY,
rtrim(ltrim(dbo.ZCSJINPUT.GZJLJL)) GZJLJL
FROM dbo.ZCSJINPUT INNER JOIN
dbo.JSZW ON rtrim(ltrim(dbo.ZCSJINPUT.XRJSZW)) = dbo.JSZW.ZWMC INNER JOIN
dbo.DW XZ ON XZ.ParentID is not null and rtrim(ltrim(dbo.ZCSJINPUT.XZ)) = XZ.MC INNER JOIN
dbo.DW QX ON QX.ParentID is null and rtrim(ltrim(dbo.ZCSJINPUT.QX)) = QX.MC INNER JOIN
dbo.XLZL ON rtrim(ltrim(dbo.ZCSJINPUT.XL)) = dbo.XLZL.XLZLMC INNER JOIN
dbo.QDFS ON rtrim(ltrim(dbo.ZCSJINPUT.ZCQDFS)) = dbo.QDFS.QDFS INNER JOIN
dbo.ZY ON rtrim(ltrim(dbo.ZCSJINPUT.CSZY)) = dbo.ZY.ZYMC
where rtrim(ltrim(dbo.ZCSJINPUT.XB)) in ('男','女')
and not Exists (select dbo.ZCSJ.SFZHM from dbo.ZCSJ where dbo.ZCSJ.SFZHM=rtrim(ltrim(dbo.ZCSJINPUT.SFZHM)))
and dbo.SFZ_YZ(rtrim(ltrim(dbo.ZCSJINPUT.SFZHM)))=1
and IMPORTUSER=@userIDdelete
FROM dbo.ZCSJINPUT
where
Exists (select ZWMC from dbo.JSZW where ZWMC=rtrim(ltrim(dbo.ZCSJINPUT.XRJSZW)))
and Exists (select MC from dbo.DW where DW.ParentID is not null and MC=rtrim(ltrim(dbo.ZCSJINPUT.XZ)))
and Exists (select MC from dbo.DW where DW.ParentID is null and MC=rtrim(ltrim(dbo.ZCSJINPUT.QX)))
and Exists (select XLZLMC from dbo.XLZL where XLZLMC=rtrim(ltrim(dbo.ZCSJINPUT.XL)))
and Exists (select QDFS from dbo.QDFS where QDFS=rtrim(ltrim(dbo.ZCSJINPUT.ZCQDFS)))
and Exists (select ZYMC from dbo.ZY where ZYMC=rtrim(ltrim(dbo.ZCSJINPUT.CSZY)))
and rtrim(ltrim(dbo.ZCSJINPUT.XB)) in ('男','女')
and dbo.SFZ_YZ(rtrim(ltrim(dbo.ZCSJINPUT.SFZHM)))=1
and IMPORTUSER=@userID
select 1
GO上面这个是SQL中的存储过程,我把它修改为 ORACLE的存储过程如下 就动了一个声明的变量userid 系统就对这个变量的声明报了错。
CREATE PROCEDURE importdata
userID VARCHAR2
ASinsert into ZCSJ
(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.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;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;
select 1
GO请指点下 谢谢了~!
CREATE PROCEDURE importdata
is
userID VARCHAR2(200)
begininsert into ZCSJ
(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.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; 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; end importdata;看看,
但是我不清楚select 1的作用
CREATE PROCEDURE importdata(userID in VARCHAR2)
is
begin insert into ZCSJ
(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.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; 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; end importdata;
我多SQLServer不是很清楚,呵呵
在ORACLE函数赋值的时候 格式是:变量名:= 值;
在ORACLE存储过程赋值 格式是:变量名 = 值;为什么函数需要在变量名后面加:符号呢 而存储过程不需要加:符号呀???
userID VARCHAR2)
as
begin
insert into ZCSJ
(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.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; 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; end;
/
CREATE or replace PROCEDURE importdata
而不采用:
CREATE PROCEDURE importdata
,这样在重新修改函数的时候直接编译,而不要删除过程,再create,比较麻烦