function q_actdat(
v_msg out varchar2
) return integer is
begin
for rec in(select orgidt,actnam,upddat,clsdat
from actdat) loop
begin
for clsdat in 2000-12-02 .. 2004-12-30 loop
select orgidt,
actnam,
upddat,
clsdat
into rec.orgidt,
rec.actnam,
rec.upddat,
rec.clsdat
from actdat
where clsdat between '2000-12-02'and '2004-12-30';
end loop;
dbms_output.put_line(rec.orgidt||' '||
rec.actnam||' '||
rec.upddat||' '||
'rec.clsdat是 '||
rec.clsdat);
return 0;
end;
end loop;
exception
when NO_DATA_FOUND then begin
dbms_output.put_line('no data');
end;
when others then begin
rollback;
v_msg := sqlerrm;
dbms_output.put_line('v_msg='||v_msg);
return 0;
end;
end q_actdat;
orgidt,actnam,upddat,clsdat 表中的字段。
本来想在范围内查找所有数据,但是现在TEST以后只能查到一条。不明白哪写错了
还有本人是新学的,如果写完,返回个集合到外面提供调用应该怎么写。麻烦前辈门给我指点指点啊。
v_msg out varchar2
) return integer is
begin
for rec in(select orgidt,actnam,upddat,clsdat
from actdat) loop
begin
for clsdat in 2000-12-02 .. 2004-12-30 loop
select orgidt,
actnam,
upddat,
clsdat
into rec.orgidt,
rec.actnam,
rec.upddat,
rec.clsdat
from actdat
where clsdat between '2000-12-02'and '2004-12-30';
end loop;
dbms_output.put_line(rec.orgidt||' '||
rec.actnam||' '||
rec.upddat||' '||
'rec.clsdat是 '||
rec.clsdat);
return 0;
end;
end loop;
exception
when NO_DATA_FOUND then begin
dbms_output.put_line('no data');
end;
when others then begin
rollback;
v_msg := sqlerrm;
dbms_output.put_line('v_msg='||v_msg);
return 0;
end;
end q_actdat;
orgidt,actnam,upddat,clsdat 表中的字段。
本来想在范围内查找所有数据,但是现在TEST以后只能查到一条。不明白哪写错了
还有本人是新学的,如果写完,返回个集合到外面提供调用应该怎么写。麻烦前辈门给我指点指点啊。
end;
end loop;你都return了 ,当然一条了
for clsdat in 2000-12-02 .. 2004-12-30 loop
这个条件写的不好 ,但是不知道怎么写。请指点一下啊如果改成
for clsdat in 1..50 loop
会提示:ORA-01422: exact fetch returns more than requested number of rows
请指点一下啊
rec.orgidt,
rec.actnam,
rec.upddat,
rec.clsdat
等于前一次的就不保存,始终是最后一条啊
TYPE tab_tmp is TABLE of actdat%rowtype index by binary_integer;
i binary_integer;
.
.
. into rec(i).orgidt,
rec(i).actnam,
rec(i).upddat,
rec(i).clsdat ;
i:=i+1;
v_msg out varchar2
) return integer is
TYPE emprec IS RECORD
( orgidt actdat.orgidt%TYPE,
actnam actdat.actnam%TYPE,
upddat actdat.upddat%TYPE,
clsdat actdat.clsdat%TYPE
);
TYPE tab_tmp is TABLE of emprec%type index by binary_integer;
i binary_integer;
cursor c1 is select orgidt,actnam,upddat,clsdat
from actdat
begin
for rec in c1 loop
begin
for rec.clsdat in 2000-12-02 .. 2004-12-30 loop
tab_tmp(i) := rec.orgidt;
tab_tmp(i) := rec.actnam;
tab_tmp(i) := rec.upddat;
tab_tmp(i) := rec.clsdat ;
i:=i+1;
where clsdat between '2000-12-02'and '2004-12-30';
end loop;
dbms_output.put_line(rec.orgidt | |' ' | |
rec.actnam | |' ' | |
rec.upddat | |' ' | |
'rec.clsdat是 ' | |
rec.clsdat);
return 0;
end;
end loop;
exception
when NO_DATA_FOUND then begin
dbms_output.put_line('no data');
end;
when others then begin
rollback;
v_msg := sqlerrm;
dbms_output.put_line('v_msg=' | |v_msg);
return 0;
end;
end q_actdat;