oracle游标加入字符串,超级困难:存储过程中执行SQL字符串游标 或 临时表 期待解决...

第一种情况是游标的情况:大体说明是传进来N个参数 然后根据N个参数来组合一个带有一个游标的SQL语句来循环取出数据 执行另一条函数(这块不在这个问题里面)

第二种情况是临时表的情况:大体说明是传进来N个参数 然后根据N个参数来组合一个Select SQL语句,将这个Select 语句结果转成一个临时表,再遍历临时表取出数据 执行另一条函数(这块不在这个问题里面)

以上两种SQL语句在SQL 2000 有办法解决 .不知道Oracle怎么解决.很奇怪 总是提示这样,那样的问题.

以下为两种SQL语句的简洁

第一种:

-----------------------------------------------------------------

CREATE OR REPLACE PROCEDURE GetPositionInfo

(

v_Voyage varchar2,

v_CntrStatusCode varchar2,

v_CntrSize varchar2,

v_CntrType varchar2,

v_EF varchar2,

v_OptrCode varchar2,

returnstr out varchar2

)IS

tempstr varchar2(2000);

orderstr varchar2(200);

v_bayid number(12,0);

begin

returnstr := '';

tempstr := 'declare cursor cur is Select BayID from TPlCtrl where CntrSize=v_CntrSize and EF=v_EF and TaskCode=v_TaskCode';

orderstr := '';

--以下是按照传进来的条件来进行组织SQL语句.

if v_Voyage <>''  then

tempstr := tempstr || ' and (Voyage=v_voyage or nvl(Voyage,'''')='''')';

if orderstr <> '' then

orderstr := orderstr || ' ,Voyage desc';

else

orderstr := ' Order by Voyage desc';

end if;

else

if orderstr <> '' then

orderstr := orderstr || ' ,Voyage ';

else

orderstr := ' Order by Voyage ';

end if;

end if;

if v_CntrStatusCode <>''  then

tempstr := tempstr || ' and (CntrStatusCode=v_CntrStatusCode or nvl(CntrStatusCode,'''')='''')';

if orderstr <> '' then

orderstr := orderstr || ' ,CntrStatusCode desc';

else

orderstr := ' Order by CntrStatusCode desc';

end if;

else

if orderstr <> '' then

orderstr := orderstr || ' ,CntrStatusCode ';

else

orderstr := ' Order by CntrStatusCode ';

end if;

end if;

if v_CNTRType <>''  then

tempstr := tempstr || ' and (CNTRType=v_CNTRType or nvl(CNTRType,'''')='''')';

if orderstr <> '' then

orderstr := orderstr || ' ,CNTRType desc';

else

orderstr := ' Order by CNTRType desc';

end if;

else

if orderstr <> '' then

orderstr := orderstr || ' ,CNTRType ';

else

orderstr := ' Order by CNTRType ';

end if;

end if;

if v_OPTRCode <>''  then

tempstr := tempstr || ' and (OPTRCode=v_OPTRCode or nvl(OPTRCode,'''')='''')';

if orderstr <> '' then

orderstr := orderstr || ' ,OPTRCode desc';

else

orderstr := ' Order by OPTRCode desc';

end if;

else

if orderstr <> '' then

orderstr := orderstr || ' ,OPTRCode ';

else

orderstr := ' Order by OPTRCode ';

end if;

end if;

orderstr:=orderstr || ',Pri asc';

tempstr := tempstr || orderstr;

--以上为组织完SQL语句.以下进行执行.

execute immediate tempstr;

begin

--问题出在下面的这条语句.说没有定义这个游标.可是我把这个游标定义在那个字符串里面了.应该怎么办.

open cur;loop

fetch cur into v_bayid;

CheckPosition(v_TaskTypeCode,to_char(v_bayid),v_CntrStatusCode,v_CntrSize,v_CntrType,v_EF,v_OptrCode,tempstr);

if substr(tempstr,1,1) = '#' then

returnstr := tempstr;

else

returnstr := tempstr;

close cur;

return ;

end if;

end loop;

close cur;

end;

END GetPositionInfo;

第二种:

-----------------------------------------------------------------

CREATE OR REPLACE PROCEDURE GetPositionInfo

(

v_Voyage varchar2,

v_CntrStatusCode varchar2,

v_CntrSize varchar2,

v_CntrType varchar2,

v_EF varchar2,

v_OptrCode varchar2,

returnstr out varchar2

)IS

tempstr varchar2(2000);

orderstr varchar2(200);

v_bayid number(12,0);

begin

returnstr := '';

tempstr := 'insert into temptable Select BayID from TPlCtrl where CntrSize=v_CntrSize and EF=v_EF and TaskCode=v_TaskCode';

orderstr := '';

--以下是按照传进来的条件来进行组织SQL语句.

if v_Voyage <>''  then

tempstr := tempstr || ' and (Voyage=v_voyage or nvl(Voyage,'''')='''')';

if orderstr <> '' then

orderstr := orderstr || ' ,Voyage desc';

else

orderstr := ' Order by Voyage desc';

end if;

else

if orderstr <> '' then

orderstr := orderstr || ' ,Voyage ';

else

orderstr := ' Order by Voyage ';

end if;

end if;

if v_CntrStatusCode <>''  then

tempstr := tempstr || ' and (CntrStatusCode=v_CntrStatusCode or nvl(CntrStatusCode,'''')='''')';

if orderstr <> '' then

orderstr := orderstr || ' ,CntrStatusCode desc';

else

orderstr := ' Order by CntrStatusCode desc';

end if;

else

if orderstr <> '' then

orderstr := orderstr || ' ,CntrStatusCode ';

else

orderstr := ' Order by CntrStatusCode ';

end if;

end if;

if v_CNTRType <>''  then

tempstr := tempstr || ' and (CNTRType=v_CNTRType or nvl(CNTRType,'''')='''')';

if orderstr <> '' then

orderstr := orderstr || ' ,CNTRType desc';

else

orderstr := ' Order by CNTRType desc';

end if;

else

if orderstr <> '' then

orderstr := orderstr || ' ,CNTRType ';

else

orderstr := ' Order by CNTRType ';

end if;

end if;

if v_OPTRCode <>''  then

tempstr := tempstr || ' and (OPTRCode=v_OPTRCode or nvl(OPTRCode,'''')='''')';

if orderstr <> '' then

orderstr := orderstr || ' ,OPTRCode desc';

else

orderstr := ' Order by OPTRCode desc';

end if;

else

if orderstr <> '' then

orderstr := orderstr || ' ,OPTRCode ';

else

orderstr := ' Order by OPTRCode ';

end if;

end if;

orderstr:=orderstr || ',Pri asc';

tempstr := tempstr || orderstr;

--以上为组织完SQL语句.以下进行执行.

execute immediate 'CREATE GLOBAL TEMPORARY TABLE temptable (bayid number(12,0)) from ON COMMIT PRESERVE ROWS';

execute immediate tempstr;

--问题出在下面的那一条语句.说没有这个名字的临时表.可是这个临时表已经在上面的那个字符串里面定义了.

declare cursor cur is Select bayid from temptable;--如果把上面的这条语句也放在一个字符串里面执行的话,就会提示下面的 Open cur 不存在 没有定义.我总不能把下面所有的语句都用字符串来执行吧.

begin

open cur;

loop

fetch cur into v_bayid;

CheckPosition(v_TaskTypeCode,to_char(v_bayid),v_CntrStatusCode,v_CntrSize,v_CntrType,v_EF,v_OptrCode,tempstr);

if substr(tempstr,1,1) = '#' then

returnstr := tempstr;

else

returnstr := tempstr;

close cur;

return ;

end if;

end loop;

close cur;

end;

END GetPositionInfo;

================================

请各位专家帮忙解决.问过身边很多搞Oracle的他们都没有办法解决.