灵活运用绑定变量---declare匿名块使用绑定变量 - Oracle-fans - 博客园

   declare

        type cur01 is ref cursor;
     v_cur cur01;

        v_match123 varchar2(2000);

        v_sqltext varchar2(2000);
       v_FORCE_MATCHING_SIGNATURE varchar2(2000);
  v_EXACT_MATCHING_SIGNATURE varchar2(2000);

        begin

        :v_match123 := '%/*+haha*/%';

        open v_cur for 'select sql_text,FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE from v$sql where sql_text like :x and sql_text not like ''%like%'''
       using :v_match123;

        loop

               fetch v_cur into v_sqltext,v_FORCE_MATCHING_SIGNATURE,v_EXACT_MATCHING_SIGNATURE;
       exit when v_cur%notfound;
       dbms_output.put_line(v_sqltext||'  '||v_FORCE_MATCHING_SIGNATURE||'  '||v_EXACT_MATCHING_SIGNATURE);
     end loop;

        if v_cur%rowcount = 0 then
       dbms_output.put_line('No matched data found');
     end if;

        close v_cur;

      exception
     when others then
        dbms_output.put_line(sqlerrm);
   end;
   /