动态游标用法

DECLARE
  sqlstr        VARCHAR2(2000);
  v_vendor_id   NUMBER := 100;
  v_cursor_id   NUMBER; --游标ID
  l_vendor_id   NUMBER;
  l_vendor_name VARCHAR2(240);
  v_dummy       NUMBER;
BEGIN
  sqlstr := 'SELECT PV.VENDOR_ID,PV.VENDOR_NAME,PV.SEGMENT1 FROM PO_VENDORS PV where 1=1 and PV.VENDOR_ID = ';
  fnd_dsql.init; --初始化
  fnd_dsql.add_text(sqlstr); --添加游标内容
  fnd_dsql.add_bind(v_vendor_id); --添加Where条件
  v_cursor_id := dbms_sql.open_cursor(); -- 打开游标
  fnd_dsql.set_cursor(v_cursor_id);
  dbms_sql.parse(v_cursor_id, fnd_dsql.get_text(), dbms_sql.native);
  fnd_dsql.do_binds();
  dbms_sql.define_column(v_cursor_id, 1, l_vendor_id, 1000);
  dbms_sql.define_column(v_cursor_id, 2, l_vendor_name, 1000);
  v_dummy := dbms_sql.execute(v_cursor_id);

  WHILE (dbms_sql.fetch_rows(v_cursor_id) > 0) LOOP
    l_vendor_id   := NULL;
    l_vendor_name := NULL;
    dbms_sql.column_value(v_cursor_id, 1, l_vendor_id);
    dbms_sql.column_value(c => v_cursor_id, position => 2, VALUE => l_vendor_name);
  END LOOP;

  dbms_output.put_line(l_vendor_id);
  dbms_output.put_line(l_vendor_name);
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25684327/viewspace-693108/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25684327/viewspace-693108/