create or replace procedure selectbyname
(
namestr nvarchar2
)as
declare sqlstr varchar2(5000)
begin
set sqlstr='select a.XTAJBH,a.wsbh1,a.wsbh2,a.ladjr,a.ladjsj,a.wfdsr,a.wfdsrdwzz,a.ajly,a.ay,a.bz,b.WFSS,b.cbryj,b.cbrqm,b.cbrqmsj,b.cbbmyj,b.cbbmqm,b.cbbmqmsj,b.fzjgshyj,b.fzjgqm,b.fzjgqmsj,b.zfjgldyj,b.zfjgldqm,b.zfjgldqmsj,b.qmbh,c.sjlzlj,c.dqjd from aj_lianshenpi a,aj_lingdaoqianming b,lc_flow_instance c where a.XTAJBH=b.XTAJBH and a.xtajbh=c.xtajbh and b.la=1 and a.wfdsr like ''%'
set sqlstr=sqlstr+namestr
set sqlstr=sqlstr+'%'' order by a.ladjsj desc'
exec sqlstr
end这个是我创建的存储过程。创建成功了,但是提示“selectbyname”无效。大家帮我看看,有哪里错了
(
namestr nvarchar2
)as
declare sqlstr varchar2(5000)
begin
set sqlstr='select a.XTAJBH,a.wsbh1,a.wsbh2,a.ladjr,a.ladjsj,a.wfdsr,a.wfdsrdwzz,a.ajly,a.ay,a.bz,b.WFSS,b.cbryj,b.cbrqm,b.cbrqmsj,b.cbbmyj,b.cbbmqm,b.cbbmqmsj,b.fzjgshyj,b.fzjgqm,b.fzjgqmsj,b.zfjgldyj,b.zfjgldqm,b.zfjgldqmsj,b.qmbh,c.sjlzlj,c.dqjd from aj_lianshenpi a,aj_lingdaoqianming b,lc_flow_instance c where a.XTAJBH=b.XTAJBH and a.xtajbh=c.xtajbh and b.la=1 and a.wfdsr like ''%'
set sqlstr=sqlstr+namestr
set sqlstr=sqlstr+'%'' order by a.ladjsj desc'
exec sqlstr
end这个是我创建的存储过程。创建成功了,但是提示“selectbyname”无效。大家帮我看看,有哪里错了
是在哪里执行该存储过程(SQL*Plus, PL/SQL Developer...)
(
namestr nvarchar2
)as
sqlstr varchar2(5000);
begin
sqlstr:='select a.XTAJBH,a.wsbh1,a.wsbh2,a.ladjr,a.ladjsj,a.wfdsr,a.wfdsrdwzz,a.ajly,a.ay,a.bz,b.WFSS,b.cbryj,b.cbrqm,b.cbrqmsj,b.cbbmyj,b.cbbmqm,b.cbbmqmsj,b.fzjgshyj,b.fzjgqm,b.fzjgqmsj,b.zfjgldyj,b.zfjgldqm,b.zfjgldqmsj,b.qmbh,c.sjlzlj,c.dqjd from aj_lianshenpi a,aj_lingdaoqianming b,lc_flow_instance c where a.XTAJBH=b.XTAJBH and a.xtajbh=c.xtajbh and b.la=1 and a.wfdsr like ''%';
sqlstr:=sqlstr+namestr;
sqlstr:=sqlstr+'%'' order by a.ladjsj desc';
execute immediate sqlstr using namestr;
end selectbyname;
楼主的语句有很多问题:
1.不需要declare
2.不需要SET
3.少了很多";"号
4.执行动态语句语法错误