动态游标示例

DECLARE
  l_sql VARCHAR2(3000) := 'SELECT osh.process_status
                           FROM oms_so_order_header osh, oms_so_order_line osl
                           WHERE osh.source_code = osl.source_code
                                 AND osh.source_id = osl.source_id
                                 AND osh.process_status <> ''COMPLETE''
                                 AND (:1 IS NULL OR trunc(osl.transaction_date) >=:2)
                                 AND ROWNUM = 1';
  TYPE cursor_type IS REF CURSOR;
  cur_oms cursor_type;

  rec_oms     VARCHAR2(30);
  l_date_from DATE;
BEGIN

  OPEN cur_oms FOR l_sql
    USING l_date_from, l_date_from;
  LOOP
    FETCH cur_oms
      INTO rec_oms;
    EXIT WHEN cur_oms%NOTFOUND;
    dbms_output.put_line(rec_oms);
  END LOOP;
END;