create or replace procedure sp_test(p_date in varchar2) is type t_cursor is ref cursor; v_str varchar(1000); v_cur t_cursor; r_cur 计量点表码表200701%rowtype; begin v_str := 'select * from 计量点表码表 ' ¦ ¦p_date ¦ ¦ ' '; open v_cur for v_str; loop Fetch v_cur into r_cur; exit when v_cur%notfound; dbms_output.put_line( 'the result is: ' ¦ ¦r_cur.计量点编码 ¦ ¦ ' ' ¦ ¦r_cur.费率类型); end loop; close v_cur; end sp_test;
declare t_cursor cursor for select name from syscolumns where id=object_id( 'test1 ') and colid> 1 order by colid
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status=0 begin exec( 'select '+@name+ ' as t into test3 from test1 ') set @s= 'insert into test2 select ' ' '+@name+ ' ' ' ' select @s=@s+ ', ' ' '+rtrim(t)+ ' ' ' ' from test3 exec(@s) exec( 'drop table test3 ') fetch next from t_cursor into @name end close t_cursor deallocate t_cursor
