动态sql游标实现模糊查询

查了很多地方，下面这个写法是对的，以oracle hr用户为例。参考：http://www.veryhuo.com/a/view/16095.html

declare 
  c char(8);
  lc_sql char(4000);
  lc_job_rec jobs%rowtype;
  type cur_type is ref cursor;
  cur_jobs   cur_type;
begin
  c := 'pu';
  dbms_output.put_line(c);
  lc_sql := 'select j.* from jobs j where lower(j.job_title) like :1 ';
  open cur_jobs for lc_sql using '%'||trim(c)||'%';
  loop
    fetch cur_jobs into lc_job_rec;
    Exit when cur_jobs%notfound;
    dbms_output.put_line(lc_job_rec.min_salary);
  end loop;
  close cur_jobs;
end;

另一种写法，几乎一样。

declare 
  c char(8);
  lc_sql char(4000);
  lc_job_rec jobs%rowtype;
  type cur_type is ref cursor;
  cur_jobs   cur_type;
begin
  c := '%pu%';
  dbms_output.put_line(c);
  lc_sql := 'select j.* from jobs j where lower(j.job_title) like :1 ';
  open cur_jobs for lc_sql using trim(c);
  loop
    fetch cur_jobs into lc_job_rec;
    Exit when cur_jobs%notfound;
    dbms_output.put_line(lc_job_rec.min_salary);
  end loop;
  close cur_jobs;
end;