新手在线急等,第一次提问就这点分,先行谢过!执行存储过程报错 "无效的sql语句"
--------------------------------------------
supplierinfo 供应商表
spli_sid 供应商ID VARCHAR2(50)
spli_sno 供应商编号 VARCHAR2(50)
--------------------------------------------
存储过程
create or replace procedure Pro_SPL_Upsno is
v_sql varchar2(4000);
cursor c1 is
select spli_sid v_supplierid,
lpad(spli_sno, 10, '0') v_supplierno,
spli_sname v_suppliername
from spl_supplierinfo
where spil_nvalid = 1
and length(spli_sno) = 8
order by spli_sno;
begin
v_sql := '';
for v_c1 in c1 loop
v_sql:= 'update spl_supplierinfo set sqli_no = ('||v_c1.v_supplierno||') where spli_sid = ('||v_c1.v_supplierid||')';
Execute immediate v_sql;
commit;
end loop;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end Pro_SPL_Upsno;
-------------------------------------------------------
EXEC Pro_SPL_Upsno;
执行报错:无效sql语句
-------------------------------------------------------
--------------------------------------------
supplierinfo 供应商表
spli_sid 供应商ID VARCHAR2(50)
spli_sno 供应商编号 VARCHAR2(50)
--------------------------------------------
存储过程
create or replace procedure Pro_SPL_Upsno is
v_sql varchar2(4000);
cursor c1 is
select spli_sid v_supplierid,
lpad(spli_sno, 10, '0') v_supplierno,
spli_sname v_suppliername
from spl_supplierinfo
where spil_nvalid = 1
and length(spli_sno) = 8
order by spli_sno;
begin
v_sql := '';
for v_c1 in c1 loop
v_sql:= 'update spl_supplierinfo set sqli_no = ('||v_c1.v_supplierno||') where spli_sid = ('||v_c1.v_supplierid||')';
Execute immediate v_sql;
commit;
end loop;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end Pro_SPL_Upsno;
-------------------------------------------------------
EXEC Pro_SPL_Upsno;
执行报错:无效sql语句
-------------------------------------------------------
这句里面为什么加括号呢
1: 那个游标的SELECT语句可以执行正确吗?
2: 把你UPDATE中的括号去掉了。
3: 如果还有错看下面代码打印出来的V_SQL语句是不是正确的:
CREATE OR REPLACE PROCEDURE PRO_SPL_UPSNO IS
V_SQL VARCHAR2(4000);
CURSOR C1 IS
SELECT SPLI_SID V_SUPPLIERID,
LPAD(SPLI_SNO, 10, '0') V_SUPPLIERNO,
SPLI_SNAME V_SUPPLIERNAME
FROM SPL_SUPPLIERINFO
WHERE SPIL_NVALID = 1
AND LENGTH(SPLI_SNO) = 8
ORDER BY SPLI_SNO;BEGIN
V_SQL := '';
FOR V_C1 IN C1 LOOP
V_SQL := 'UPDATE SPL_SUPPLIERINFO SET SQLI_NO = ' || V_C1.V_SUPPLIERNO ||
' WHERE SPLI_SID = ' || V_C1.V_SUPPLIERID || '';
DBMS_OUTPUT.PUT_LINE('V_SQL: '||V_SQL);
EXECUTE IMMEDIATE V_SQL;
COMMIT;
END LOOP;EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
END PRO_SPL_UPSNO;
v_sql:= 'update spl_supplierinfo set sqli_no = ('||v_c1.v_supplierno||') where spli_sid = ('||v_c1.v_supplierid||')';
括号干嘛的
1: 那个游标的SELECT语句可以执行正确吗?
正确
2: 把你UPDATE中的括号去掉了。
去掉了(不过加上运行不会报错,我试过的^^)
3: 调试中非常感谢,ok后给分:)
另 V_SQL := 'UPDATE SPL_SUPPLIERINFO SET SQLI_NO = ' || V_C1.V_SUPPLIERNO ||
' WHERE SPLI_SID = ' || V_C1.V_SUPPLIERID || '';SQLI_NO应为SPLI_SNO我的疏忽
非常感谢楼上,问题已经解决了
1、SQLI_NO应为SPLI_SNO
2、在sqlwindow执行的exec pro_spl_upsno(汗哦。。)