第一种情况是游标的情况:大体说明是传进来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的他们都没有办法解决.