Question

存储过程报错，添加的sql语句单独查询时 没有报错

create or replace procedure AA(MATCHID IN VARCHAR2)
as
begin
INSERT INTO L_APPLICATIONBILL_ITEM_T
(MATCHID,
VALIDFLAG,
CARNO,
ICID,
RFIDID,
OPERATYPE,
CARTYPE,
SOURCECODE,
SOURCENAME,
TARGETCODE,
TARGETNAME,
OPERATYPE,
USERMEMO,
SYSMEMO,
CREATEDATE,
VALIDFLAGOPERANAME,
VALIDFLAGTIME) values
(select MATCHID||to_char(lpad(seq1.nextval, 1, '0')),
VALIDFLAG,
CARNO,
ICID,
RFIDID,
OPERATYPE,
CARTYPE,
SOURCECODE,
SOURCENAME,
TARGETCODE,
TARGETNAME,
OPERATYPE,
USERMEMO,
SYSMEMO,
CREATEDATE,
VALIDFLAGOPERANAME,
VALIDFLAGTIME
from m_measure_t
/*where MATCHID = MATCHID*/);
insert into L_APPLICATIONBILL_T
(MATCHID,
VALIDFLAG,
CARNO,
ICID,
RFIDID,
OPERATYPE,
CARTYPE,
SOURCECODE,
SOURCENAME,
TARGETCODE,
TARGETNAME,
USERMEMO,
SYSMEMO,
CREATEDATE,
VALIDFLAGOPERANAME,
VALIDFLAGTIME) values
(select OPERATYPE || to_char('00') || to_char(sysdate, 'yyMMdd') ||

lpad(to_char(seq1.nextval), 5, '0'),
VALIDFLAG,
CARNO,
ICID,
RFIDID,
OPERATYPE,
CARTYPE,
SOURCECODE,
SOURCENAME,
TARGETCODE,
TARGETNAME,
USERMEMO,
SYSMEMO,
CREATEDATE,
VALIDFLAGOPERANAME,
VALIDFLAGTIME from m_measure_t
);
end;

Answer 1

INSERT INTO 用SELECT的是时候 是不需要 VALUES 这个特殊字的！去掉

insert into table1(xxx)
select XXX
from table2
这样的

Answer 2

INSERT INTO 的VALUES 里用SELECT这样不行吧
我试了一下在单独测试sql也是不行的
不是说缺失表达式吗 应该就是第一个赋值不知别吧

Answer 3

你SELECT后面有函数的地方加个别名试试