CREATE or replace procedure test(
p_StartTime in date, --开始时间
p_EndTime in date, --结束时间
p_SMTP in number, --样品分类
p_STCD in varchar2, --
p_JL in varchar2, --准值:
p_flag in number, --标志1,,2,3:
p_ZYCBXM out varchar2,
p_ReturnSZLB out varchar2)
as
v_str varchar2(500);
v_s varchar(4000);
lstr varchar2(1000);
ln number;
begin
v_str:='CREATE GLOBAL TEMPORARY TABLE rec (a number,b NUMBER,c number) ON COMMIT PRESERVE(DELETE) ROWS ;
execute immediate v_str;lstr:='1,2,3,4,5,6,7,8,9'||','
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
--ldata.extend;
insert into rec values(ltrim(rtrim(substr(lstr,1,ln-1))),10,10);
lstr:=substr(lstr,ln+1);
end loop;
end;
p_StartTime in date, --开始时间
p_EndTime in date, --结束时间
p_SMTP in number, --样品分类
p_STCD in varchar2, --
p_JL in varchar2, --准值:
p_flag in number, --标志1,,2,3:
p_ZYCBXM out varchar2,
p_ReturnSZLB out varchar2)
as
v_str varchar2(500);
v_s varchar(4000);
lstr varchar2(1000);
ln number;
begin
v_str:='CREATE GLOBAL TEMPORARY TABLE rec (a number,b NUMBER,c number) ON COMMIT PRESERVE(DELETE) ROWS ;
execute immediate v_str;lstr:='1,2,3,4,5,6,7,8,9'||','
loop
ln:=instr(lstr,',');
exit when (nvl(ln,0)=0);
--ldata.extend;
insert into rec values(ltrim(rtrim(substr(lstr,1,ln-1))),10,10);
lstr:=substr(lstr,ln+1);
end loop;
end;
To:beckhambobo(beckham)
如果定义的是 临时表呢!!,那怎么修改呢!!CREATE table #Rec ( --存储过程的返回值(表)
a int,
b int,
c int
)
谢谢老大!,解决了.马上接贴!!CREATE proc test
@StartTime as datetime, --开始时间
@EndTime as datetime, --结束时间
@SMTP as int, --样品分类
@STCD as varchar(50), --
@JL as varchar(50), --准值:
@flag as int, --标志1,,2,3:
@@ZYCBXM as varchar(8000) output,
@@ReturnSZLB as varchar(200) output
as
CREATE table #Rec ( --存储过程的返回值(表)
a int,
b int,
c int
)
declare @s as varchar(8000)
declare @var_a as int
declare @var_b as int
declare @var_c as int
set @s='1,2,3,4,5,6,7,8,9'
declare @i as int
set @i=charindex(',',@s)
while @i>0
begin
set @var_a=left(@s,@i-1)
insert #Rec values(@var_a,10,10)
set @s=right(@s,len(@s)-@i)
set @i=charindex(',',@s)
end
if len(@s)>0
begin
insert #Rec values(@s,10,10)
endselect * from #Rec /* 测试declare @@a as varchar(2000)
declare @@b as varchar(2000)
exec test '1999-1-1','2003-1-1','1','11111','IV','2',@@a out,@@b out
*/
其實差別不大的,
你自己編寫然後調試,那樣你可以更好的學習oracle.
我們能幫你解決的是一時的問題