CREATE PROCEDURE [dbo].[sp_1] @n int AS declare @str varchar(8000),@sql nvarchar(4000) set @str='' set @sql=N'select top '+cast(@n as nvarchar)+N' id into ##tem from t group by id order by newid()' exec sp_executesql @sql select @str=@str+','+cast(id as varchar) from ##tem order by id drop table ##tem set @str=right(@str,len(@str)-1) select @str id调用: exec sp_1 3
create or replace function aa(i integer) is j integer;(产生随机数次数) k integer;(每产生一个数字与现有的比较次数) a integer;(记录每次产生的数) s string:=null;(变a为字符相加输出) maxrow integer;(表A中最大的id) begin select count(*) into maxrow from a ; j :=1 ; while j<i+1 loop a := round(dbms_random.value(1,maxrow)); if length(s) > 0 then k:=1; while substr(s,k,',') is not null then for n in 1..k loop if a != substr(s,k,',') then s := s||a||‘,'; j := j+1; else exit; end if; end loop; k:=k+1; end loop; else s:= s||a||','; end if; end loop; return s; end;随便写一下,供楼主参考吧!在oracle中不知道使用数组.
AS
declare @str varchar(8000),@sql nvarchar(4000)
set @str=''
set @sql=N'select top '+cast(@n as nvarchar)+N' id into ##tem from t group by id order by newid()'
exec sp_executesql @sql
select @str=@str+','+cast(id as varchar) from ##tem order by id
drop table ##tem
set @str=right(@str,len(@str)-1)
select @str id调用:
exec sp_1 3
j integer;(产生随机数次数)
k integer;(每产生一个数字与现有的比较次数)
a integer;(记录每次产生的数)
s string:=null;(变a为字符相加输出)
maxrow integer;(表A中最大的id)
begin
select count(*) into maxrow from a ;
j :=1 ;
while j<i+1 loop
a := round(dbms_random.value(1,maxrow));
if length(s) > 0 then
k:=1;
while substr(s,k,',') is not null then
for n in 1..k loop
if a != substr(s,k,',') then
s := s||a||‘,';
j := j+1;
else
exit;
end if;
end loop;
k:=k+1;
end loop;
else
s:= s||a||',';
end if;
end loop;
return s;
end;随便写一下,供楼主参考吧!在oracle中不知道使用数组.