Question

存储过程报错：PLS-00103: 出现符号 "("在需要下列之一时:

这个是写的存储过程：create or replace procedure temp_prod_inst_ppp (vpn_lan_id in number(9)) is
vpn_sql varchar2(100);
vpn_commit_id number(10);
cursor cc is
select a.lan_id,a.acc_nbr,a.prod_inst_id,b.product_id from access_prod_inst a ,func_prod_inst b
where a.lan_id=vpn_lan_id and a.comp_inst_id=b.comp_inst_id and a.product_id in (80000045,80000048) and b.product_id in (80001307,80014967,80001308) and rownum<10 ;

t cc%rowtype;
begin
vpn_commit_id:=0;
vpn_sql:='insert into /*+append*/ temp_prod_inst_'||vpn_lan_id||' nologging(lan_id,acc_nbr,prod_inst_id,product_id)values(t.lan_id,t.acc_nbr,t.prod_inst_id,t.product_id)';

open cc;
loop fetch cc into t;
exit when cc%notfound;

execute immediate vpn_sql;
vpn_commit_id:=vpn_commit_id+1;
if mod(vpn_commit_id,5000)=0 then
commit;
end if;

end loop;
commit;
exception when others then rollback;
close cc;
end;

----编译的时候报错额：
PROCEDURE CRMDB.TEMP_PROD_INST_PPP 编译错误
错误：PLS-00103: 出现符号 "("在需要下列之一时：
:= . ) , @ % default
character
符号 ":=" 被替换为 "(" 后继续。
行：1
文本：create or replace procedure temp_prod_inst_ppp (vpn_lan_id in number(9)) is

Answer 1

问题属于入参类型错误：
plsql中procedure的入参类型，如果是number或varchar2的话不需要定义长度。否则编译不能通过。

plsql中procedure的入参类型，如果是number或varchar2的话不需要定义长度。否则编译不能通过。
改正这样：

create or replace procedure temp_prod_inst_ppp (vpn_lan_id in number)
把number(9)改成number。

改正后如下：

create or replace procedure temp_prod_inst_ppp (vpn_lan_id in number) is
vpn_sql varchar2(100);

vpn_commit_id number(10);

cursor cc is

select a.lan_id,a.acc_nbr,a.prod_inst_id,b.product_id from access_prod_inst a ,func_prod_inst b 

where a.lan_id=vpn_lan_id and a.comp_inst_id=b.comp_inst_id and a.product_id in (80000045,80000048) and b.product_id in (80001307,80014967,80001308) and rownum<10 ; 

t cc%rowtype;

begin
vpn_commit_id:=0;

vpn_sql:='insert into /*+append*/ temp_prod_inst_'||vpn_lan_id||' nologging(lan_id,acc_nbr,prod_inst_id,product_id)values(t.lan_id,t.acc_nbr,t.prod_inst_id,t.product_id)';

open cc;

loop fetch cc into t;

exit when cc%notfound;

execute immediate vpn_sql;

vpn_commit_id:=vpn_commit_id+1;

if mod(vpn_commit_id,5000)=0 then
commit;

end if;

end loop;

commit;

exception when others then rollback;

close cc;

end;