create or replace procedure P_TEST_sql is  
TYPE ref_cursor_type IS REF CURSOR;  --定义一个动态游标   
tablename varchar2(200) default 'ess_client';  
v_sql varchar2(1000);  
mobile varchar2(15);  
usrs ref_cursor_type;  
begin  
  --使用连接符拼接成一条完整sql   
  v_sql := 'select usrmsisdn from '||tablename||' where rownum < 11';  
  --打开游标   
  open usrs for v_sql ;  
  loop  
      fetch usrs into mobile;   
      exit when usrs%notfound;  
      insert into tmp(usrmsisdn) values(mobile);  
  end loop;  
  close usrs;  
  commit;  
end P_TEST_sql;