create or replace procedure p_test(v_rtn out sys_refcursor)

as

v_sql VARCHAR2(4000):='select * from test union all select ';

CURSOR aaa IS SELECT COLUMN_name FROM user_tab_columns WHERE table_name='TEST';

BEGIN
  
FOR rec IN aaa LOOP
  
v_sql:=v_sql||'AVG('||rec.COLUMN_name||'),';

END LOOP;

v_sql:=substr(v_sql,1,INSTR(v_sql,',',-1)-1);

v_sql:=v_sql||' from TEST';

open v_rtn for v_sql;

END;

create proc p_test
as
begin
    declare @sql varchar(8000);
    set @sql ='select * from test union all select '
    select 
        @sql=@sql+'avg('+name+'), ' 
    from SysColumns where id=object_id('test')
    set @sql=left(@sql,len(@sql)-1)+' from test'
    exec(@sql) 
end

------解决方案--------------------

SQL code


create procedure p_test
(@v_rtn cursor varying output)
as
begin
 declare @v_sql VARCHAR(4000),@sql varchar(6000)
 
 select @v_sql='select * from test union all select '

 select @v_sql=@v_sql+'AVG('+c.name+'),'
 from sys.columns c
 inner join sys.tables t on c.[object_id]=t.[object_id]
 where t.name='TEST'

 select @v_sql=left(@v_sql,len(@v_sql)-1)

 select @v_sql=@v_sql+' from TEST;'

 select @sql='set @v_rtn=cursor for '+@v_sql

 select @sql=@sql+'open @v_rtn;'

 exec(@sql)
end

------解决方案--------------------

给你一个链接：http://www.cnblogs.com/Nina-piaoye/archive/2006/09/18/507183.html
上面结合实例介绍了存储过程的写法。
建议找一本关于存储过程的书看一下，会有很深的感悟。