请教存储过程写法!
create or replace procedure p_test(v_rtn out sys_refcursor)asv_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_testasbegin 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
[解决办法]
create procedure p_test(@v_rtn cursor varying output)asbegin 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
上面结合实例介绍了存储过程的写法。
建议找一本关于存储过程的书看一下,会有很深的感悟。