想写一个存储过程,返回值是 一个表里的前十条记录的id;(如果该表的记录数小于十条,不足的记录id用0000000代替)总之不管有多少数据一定要取到10条。DECLARE
n_rec number:=10;
Cursor c_goodsId is select goods_Id from m_goods where rownum <= n_rec;
c_row c_goodsId%rowtype;
n_count number:=0;
BEGIN
select count(goods_Id) into n_count from m_goods_auction;
IF n_count < n_rec THEN
x:= n_rec - n_count;
DBMS_OUTPUT.PUT_LINE(x);
END IF ; 。。(请帮忙补充)END;应该怎么把它改成带返回值的存储过程。
n_rec number:=10;
Cursor c_goodsId is select goods_Id from m_goods where rownum <= n_rec;
c_row c_goodsId%rowtype;
n_count number:=0;
BEGIN
select count(goods_Id) into n_count from m_goods_auction;
IF n_count < n_rec THEN
x:= n_rec - n_count;
DBMS_OUTPUT.PUT_LINE(x);
END IF ; 。。(请帮忙补充)END;应该怎么把它改成带返回值的存储过程。
等大大们更好的答案
from (SELECT ROWNUM as aa FROM dual CONNECT BY ROWNUM <= 10) a
left join (select rownum as bb, [表内的字段1], [表内的字段2]
from [楼主自己的表]
where rownum <= 5) b on aa = bb
create or replace package mypackage001 as
type mytabletype is table of char(10)
index by binary_integer;
end mypackage001;
--创建存储过程,使用包mypackage001中定义的表类型
create or replace procedure pro_choose(
v_table out mypackage001.mytabletype)
as
cursor mycursor is
select c_goodsId from m_goods_auction where rownum<=10;
mytable mypackage001.mytabletype;
v_id char(10);
i number:=1;
begin
open mycursor;
loop
fetch mycursor into v_id;
if mycursor%found then
mytable(i):=v_id;
elsif mycursor%notfound then
mytable(i):='0000000';
end if;
--dbms_output.put_line(mytable(i));
exit when i=10;
i:=i+1;
end loop;
close mycursor;
v_table:=mytable;
end;