pl/sql--動態SQL的範例

declare
  type a is ref cursor;  --自訂一個ref cursor類型，名稱為a
  rc a;  --定義變數rc為a類型
  b varchar2(100);
  r1 ciadm.cbr1_passage_trn%rowtype;
begin
  b:='select * from ciadm.cbr1_passage_trn where 日期 = :v_date';
  open rc for b using '980311'; --會將980311代入 :v_date
  loop
    fetch rc into r1;
    exit when rc%notfound;
    dbms_output.put_line(r1.備貨單號 || '--' || r1.客戶);
  end loop;
end;


DECLARE
  TYPE TP_REF IS REF CURSOR;
  CUR_1 TP_REF;
  V_SQL VARCHAR2(100);
  V_DATE VARCHAR2(10);
  R_1 HLADM.RS01A%ROWTYPE;
BEGIN
  V_DATE := '20090101';
  V_SQL := 'SELECT * FROM RS00 WHERE IDAY = ' || V_DATE ;
  OPEN CUR_1 FOR V_SQL ;
  LOOP
    FETCH CUR_1 INTO R_1;
    EXIT WHEN CUR_1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(R_1.RV || '的主房號為' || R_1.MRV);
  END LOOP;
  CLOSE CUR_1;
END;