CREATE OR REPLACE PROCEDURE insert_update(
p_upcode in varchar2(150),
p_upname in varchar2(160),
p_ditem in number(10),
p_dcode in varchar2(150),
p_dname in varchar2(160),
p_dmaterial in varchar2(160),
p_dweight in number(15,3),
p_dtweight in number(15,3),
p_dquwantity in number(10),
p_dre in varchar2(150),
p_dother1 in varchar2(150),
p_productdwgcode in varchar2(150),
p_g1 in varchar2(50),
p_g2 in varchar2(50),
p_g3 in varchar2(50),
p_g4 in varchar2(50),
p_g5 in varchar2(50),
p_g6 in varchar2(50),
p_g7 in varchar2(50),
p_g8 in varchar2(50),
p_g9 in varchar2(50),
p_xialiao in varchar2(50),
p_dinge in varchar2(50),
p_lldw in varchar2(10),
p_d in varchar2(10),
p_invcost in number(15,3),
p_dinvcost in number(15,3),
p_cinvcode in varchar2(20),
p_invunit in varchar2(20),
p_yongyou in varchar2(20),
p_creator in varchar2(30),
p_createtime in date(7),
p_modified in varchar2(30),
p_modifytime in date(7)
)
as
num number;
begin
select count(*) into num from XTDETAMSALL_DETAIL_2 WHERE UPCODE=p_upcode,DITEM=p_ditem;
if num<>0 then
update XTDETAMSALL_DETAIL_2 set UPCODE=p_upcode,UPNAME=p_upname,DITEM=p_ditem,DCODE=p_dcode,DNAME=p_dname,DMATERIAL=p_dmaterial,DWEIGHT=p_dweight,DTWEIGHT=p_dtweight,DQUANTITY=p_dquwantity,DREMARK=p_dre,DOTHER1=p_dother1,PRODUCTDWGCODE=p_productdwgcode,G1=p_g1,G2=p_g2,G3=p_g3,G4=p_g4,G5=p_g5,G6=p_g6,G7=p_g7,G8=p_g8,G9=p_g9,XIALIAO=p_xialiao,DINGE=p_dinge,LLDW=p_lldw,DMARK=p_d,INVCOST=p_invcost,DINVCOST=p_dinvcost,CINVCODE=p_cinvcode,INVUNIT=p_invunit,YONGYOU=p_yongyou,MODIFIED=p_modified,MODIFYTIME=sysdate;
else
insert into XTDETAMSALL_DETAIL_2(UPCODE,UPNAME,DITEM,DCODE,DNAME,DMATERIAL,DWEIGHT,DTWEIGHT,DQUANTITY,DREMARK,DOTHER1,PRODUCTDWGCODE,G1,G2,G3,G4,G5,G6,G7,G8,G9,XIALIAO,DINGE,LLDW,DMARK,INVCOST,DINVCOST,CINVCODE,INVUNIT,YONGYOU,CREATOR,CREATETIME,MODIFIED,MODIFYTIME)values(p_upcode,p_upname,p_ditem,p_dcode,p_dname,p_dmaterial,p_dweight,p_dtweight,p_dquwantity,p_dre,p_dother1,p_productdwgcode,p_g1,p_g2,p_g3,p_g4,p_g5,p_g6,p_g7,p_g8,p_g9,p_xialiao,p_dinge,p_lldw,p_d,p_invcost,p_dinvcost,p_cinvcode,p_invunit,p_yongyou,p_creator,sysdate);
end if;end;
出现的错误是:行号=1 列号=38 错误文本=PLS-00103:出现符号“CREATE”在需要下列之一时:(;is with authid as cluster compress order using compiled wrappes external deterministic parallel_enable pipelined)
请各位高手帮忙看看这是为何?
p_upcode in varchar2(150),
p_upname in varchar2(160),
p_ditem in number(10),
p_dcode in varchar2(150),
p_dname in varchar2(160),
p_dmaterial in varchar2(160),
p_dweight in number(15,3),
p_dtweight in number(15,3),
p_dquwantity in number(10),
p_dre in varchar2(150),
p_dother1 in varchar2(150),
p_productdwgcode in varchar2(150),
p_g1 in varchar2(50),
p_g2 in varchar2(50),
p_g3 in varchar2(50),
p_g4 in varchar2(50),
p_g5 in varchar2(50),
p_g6 in varchar2(50),
p_g7 in varchar2(50),
p_g8 in varchar2(50),
p_g9 in varchar2(50),
p_xialiao in varchar2(50),
p_dinge in varchar2(50),
p_lldw in varchar2(10),
p_d in varchar2(10),
p_invcost in number(15,3),
p_dinvcost in number(15,3),
p_cinvcode in varchar2(20),
p_invunit in varchar2(20),
p_yongyou in varchar2(20),
p_creator in varchar2(30),
p_createtime in date(7),
p_modified in varchar2(30),
p_modifytime in date(7)
)
as
num number;
begin
select count(*) into num from XTDETAMSALL_DETAIL_2 WHERE UPCODE=p_upcode,DITEM=p_ditem;
if num<>0 then
update XTDETAMSALL_DETAIL_2 set UPCODE=p_upcode,UPNAME=p_upname,DITEM=p_ditem,DCODE=p_dcode,DNAME=p_dname,DMATERIAL=p_dmaterial,DWEIGHT=p_dweight,DTWEIGHT=p_dtweight,DQUANTITY=p_dquwantity,DREMARK=p_dre,DOTHER1=p_dother1,PRODUCTDWGCODE=p_productdwgcode,G1=p_g1,G2=p_g2,G3=p_g3,G4=p_g4,G5=p_g5,G6=p_g6,G7=p_g7,G8=p_g8,G9=p_g9,XIALIAO=p_xialiao,DINGE=p_dinge,LLDW=p_lldw,DMARK=p_d,INVCOST=p_invcost,DINVCOST=p_dinvcost,CINVCODE=p_cinvcode,INVUNIT=p_invunit,YONGYOU=p_yongyou,MODIFIED=p_modified,MODIFYTIME=sysdate;
else
insert into XTDETAMSALL_DETAIL_2(UPCODE,UPNAME,DITEM,DCODE,DNAME,DMATERIAL,DWEIGHT,DTWEIGHT,DQUANTITY,DREMARK,DOTHER1,PRODUCTDWGCODE,G1,G2,G3,G4,G5,G6,G7,G8,G9,XIALIAO,DINGE,LLDW,DMARK,INVCOST,DINVCOST,CINVCODE,INVUNIT,YONGYOU,CREATOR,CREATETIME,MODIFIED,MODIFYTIME)values(p_upcode,p_upname,p_ditem,p_dcode,p_dname,p_dmaterial,p_dweight,p_dtweight,p_dquwantity,p_dre,p_dother1,p_productdwgcode,p_g1,p_g2,p_g3,p_g4,p_g5,p_g6,p_g7,p_g8,p_g9,p_xialiao,p_dinge,p_lldw,p_d,p_invcost,p_dinvcost,p_cinvcode,p_invunit,p_yongyou,p_creator,sysdate);
end if;end;
出现的错误是:行号=1 列号=38 错误文本=PLS-00103:出现符号“CREATE”在需要下列之一时:(;is with authid as cluster compress order using compiled wrappes external deterministic parallel_enable pipelined)
请各位高手帮忙看看这是为何?
UPCODE VARCHAR2 150 否
UPNAME VARCHAR2 160 是
DITEM NUMBER 10 0 否
DCODE VARCHAR2 150 是
DNAME VARCHAR2 160 是
DMATERIAL VARCHAR2 150 是
DWEIGHT NUMBER 10 0 是
DTWEIGHT NUMBER 10 0 是
DQUANTITY NUMBER 10 0 是
DREMARK VARCHAR2 150 是
DOTHER1 VARCHAR2 150 是
PRODUCTDWGCODE VARCHAR2 150 是
G1 VARCHAR2 50 是
G2 VARCHAR2 50 是
G3 VARCHAR2 50 是
G4 VARCHAR2 50 是
G5 VARCHAR2 50 是
G6 VARCHAR2 50 是
G7 VARCHAR2 50 是
G8 VARCHAR2 50 是
G9 VARCHAR2 50 是
XIALIAO VARCHAR2 50 是
DINGE VARCHAR2 50 是
LLDW VARCHAR2 10 是
DMARK VARCHAR2 10 是
INVCOST NUMBER 15 2 是
DINVCOST NUMBER 15 2 是
CINVCODE VARCHAR2 20 是
INVUNIT VARCHAR2 20 是
YONGYOU VARCHAR2 20 是
CREATOR VARCHAR2 30 是
CREATTETIME VARCHAR2 40 是 sysdate
UPDATE VARCHAR2 30 是
UPDATETIME VARCHAR2 40 是
改为
select count(*) into num from XTDETAMSALL_DETAIL_2 WHERE UPCODE=p_upcode and DITEM=p_ditem;
CREATE OR REPLACE PROCEDURE insert_update(
p_upcode in varchar2(150),
p_upname in varchar2(160),
p_ditem in number(10),
p_dcode in varchar2(150),
p_dname in varchar2(160),
p_dmaterial in varchar2(160),
p_dweight in number(15,3),
p_dtweight in number(15,3),
p_dquwantity in number(10),
p_dre in varchar2(150),
p_dother1 in varchar2(150),
p_productdwgcode in varchar2(150),
p_g1 in varchar2(50),
p_g2 in varchar2(50),
p_g3 in varchar2(50),
p_g4 in varchar2(50),
p_g5 in varchar2(50),
p_g6 in varchar2(50),
p_g7 in varchar2(50),
p_g8 in varchar2(50),
p_g9 in varchar2(50),
p_xialiao in varchar2(50),
p_dinge in varchar2(50),
p_lldw in varchar2(10),
p_d in varchar2(10),
p_invcost in number(15,3),
p_dinvcost in number(15,3),
p_cinvcode in varchar2(20),
p_invunit in varchar2(20),
p_yongyou in varchar2(20),
p_creator in varchar2(30),
p_createtime in date(7),
p_modified in varchar2(30),
p_modifytime in date(7)
)
as
num number;
begin
select count(*) into num from XTDETAMSALL_DETAIL_2 WHERE UPCODE=p_upcode and DITEM=p_ditem;
if num<>0 then
update XTDETAMSALL_DETAIL_2 set UPCODE=p_upcode,UPNAME=p_upname,DITEM=p_ditem,DCODE=p_dcode,DNAME=p_dname,DMATERIAL=p_dmaterial,DWEIGHT=p_dweight,DTWEIGHT=p_dtweight,DQUANTITY=p_dquwantity,DREMARK=p_dre,DOTHER1=p_dother1,PRODUCTDWGCODE=p_productdwgcode,G1=p_g1,G2=p_g2,G3=p_g3,G4=p_g4,G5=p_g5,G6=p_g6,G7=p_g7,G8=p_g8,G9=p_g9,XIALIAO=p_xialiao,DINGE=p_dinge,LLDW=p_lldw,DMARK=p_d,INVCOST=p_invcost,DINVCOST=p_dinvcost,CINVCODE=p_cinvcode,INVUNIT=p_invunit,YONGYOU=p_yongyou,MODIFIED=p_modified,MODIFYTIME=sysdate;
else
insert into XTDETAMSALL_DETAIL_2(UPCODE,UPNAME,DITEM,DCODE,DNAME,DMATERIAL,DWEIGHT,DTWEIGHT,DQUANTITY,DREMARK,DOTHER1,PRODUCTDWGCODE,G1,G2,G3,G4,G5,G6,G7,G8,G9,XIALIAO,DINGE,LLDW,DMARK,INVCOST,DINVCOST,CINVCODE,INVUNIT,YONGYOU,CREATOR,CREATETIME,MODIFIED,MODIFYTIME)values(p_upcode,p_upname,p_ditem,p_dcode,p_dname,p_dmaterial,p_dweight,p_dtweight,p_dquwantity,p_dre,p_dother1,p_productdwgcode,p_g1,p_g2,p_g3,p_g4,p_g5,p_g6,p_g7,p_g8,p_g9,p_xialiao,p_dinge,p_lldw,p_d,p_invcost,p_dinvcost,p_cinvcode,p_invunit,p_yongyou,p_creator,sysdate);
end if;
commit ; -->这里加提交语句
end insert_update; -->存储过程名结束,这是比较正规的写法
这个where条件错误改了以后 但是 还是出现上述问题
使我没有传值的问题?
还是还有哪些问题没有发现呢?
这个where条件错误改了以后 但是 还是出现上述问题
使我没有传值的问题?
还是还有哪些问题没有发现呢?
楼主的这个存储过程可以使用merge into语句
CREATE OR REPLACE PROCEDURE insert_update(
p_upcode in varchar2,
p_upname in varchar2,
p_ditem in number,
p_dcode in varchar2,
p_dname in varchar2,
p_dmaterial in varchar2,
p_dweight in number,
p_dtweight in number,
p_dquwantity in number,
p_dre in varchar2,
p_dother1 in varchar2,
p_productdwgcode in varchar2,
p_g1 in varchar2,
p_g2 in varchar2,
p_g3 in varchar2,
p_g4 in varchar2,
p_g5 in varchar2,
p_g6 in varchar2,
p_g7 in varchar2,
p_g8 in varchar2,
p_g9 in varchar2,
p_xialiao in varchar2,
p_dinge in varchar2,
p_lldw in varchar2,
p_d in varchar2,
p_invcost in number,
p_dinvcost in number,
p_cinvcode in varchar2,
p_invunit in varchar2,
p_yongyou in varchar2,
p_creator in varchar2,
p_createtime in date,
p_modified in varchar2,
p_modifytime in date
)
is
begin
merge into XTDETAMSALL_DETAIL_2 t
using (select p_upcode,p_upname, p_ditem,p_dcode,p_dname,p_dmaterial,p_dweight,p_dtweight,
p_dquwantity,p_dre,p_dother1,p_productdwgcode,p_g1,p_g2,p_g3,p_g4,p_g5,p_g6,p_g7,p_g8,p_g9,
p_xialiao,p_dinge,p_lldw,p_d,p_invcost,p_dinvcost,p_cinvcode,p_invunit,p_yongyou,p_creator,
p_createtime,p_modified,p_modifytime,sysdate MODIFYTIME from dual) t1
on (t.UPCODE = t1.p_upcode AND t.DITEM=t1.p_ditem)
when matched then update set
t.UPNAME=t1.p_upname,t.DCODE=t1.p_dcode,t.DNAME=t1.p_dname,t.DMATERIAL=t1.p_dmaterial,
t.DWEIGHT=t1.p_dweight,t.DTWEIGHT=t1.p_dtweight,t.DQUANTITY=t1.p_dquwantity,t.DREMARK=t1.p_dre,t.DOTHER1=t1.p_dother1,
t.PRODUCTDWGCODE=t1.p_productdwgcode,
t.G1=t1.p_g1,t.G2=t1.p_g2,t.G3=t1.p_g3,t.G4=t1.p_g4,t.G5=t1.p_g5,t.G6=t1.p_g6,t.G7=t1.p_g7,t.G8=t1.p_g8,t.G9=t1.p_g9,
t.XIALIAO=t1.p_xialiao,t.DINGE=t1.p_dinge,t.LLDW=t1.p_lldw,t.DMARK=t1.p_d,t.INVCOST=t1.p_invcost,
t.DINVCOST=t1.p_dinvcost,t.CINVCODE=t1.p_cinvcode,t.INVUNIT=t1.p_invunit,t.YONGYOU=t1.p_yongyou,t.MODIFIED=t1.p_modified,t.MODIFYTIME =t1.MODIFYTIMEt
when not matched then insert values
(t1.p_upcode,t1.p_upname, t1.p_ditem,t1.p_dcode,t1.p_dname,t1.p_dmaterial,t1.p_dweight,t1.p_dtweight,
t1.p_dquwantity,t1.p_dre,t1.p_dother1,t1.p_productdwgcode,t1.p_g1,t1.p_g2,t1.p_g3,t1.p_g4,t1.p_g5,t1.p_g6,t1.p_g7,t1.p_g8,t1.p_g9,
t1.p_xialiao,t1.p_dinge,t1.p_lldw,t1.p_d,t1.p_invcost,t1.p_dinvcost,t1.p_cinvcode,t1.p_invunit,t1.p_yongyou,t1.p_creator,
t1.p_createtime,t1.p_modified,t1.p_modifytime,t1.MODIFYTIME);
end;
我觉得where后面还是要加条件好一些啊
可能这里的数据恰好是可以这样做,但到别的地方你这样写,那可能问题就大了,把别的不应该更改的数据也更改了;