CREATE OR REPLACE PROCEDURE TEST (in_Filter IN NUMBER)
IS
  Ssql varchar2(2000);
  TYPE c1 IS REF CURSOR;
  the_cursor c1;
BEGIN
  -- SQL Basis (Basisstatement)
  Ssql := 'select distinct kunde.id as kunde_id 
      FROM customer kunde
      WHERE kunde.status = 1';
 
  -- Filter and das Statement anhängen (wenn ein Filter vorhanden)
  IF (in_Filter IS NOT NULL) THEN
    Ssql := Ssql || ' AND kunde.code = ' || in_Filter;
  END IF;
 
  -- Daten auslesen
  OPEN the_cursor FOR Ssql;
 
  -- Für jeden gefundenen Kunden Aktionen verarbeiten
  LOOP
    fetch the_cursor INTO tmp_kunde_id;
    EXIT WHEN the_cursor%NOTFOUND;
 
    -- Historie löschen
    DELETE FROM customer_state
    WHERE customer = tmp_kunde_id
    AND week < (SELECT TO_NUMBER(TO_CHAR(SYSDATE,'IW')) AS week FROM dual);
 
    -- Stati des Kunden kopieren
    INSERT INTO customer_invoices (customer,STATUS,week)
      SELECT cust_id, state_id, week
      FROM state_tbl
      WHERE STATUS > 0
  END loop;
  close the_cursor;
END;