title: Delete multiple records. Loop in loop in a procedure
body: <p>I have a procedure which compiles successfully.It updates the docmeta table and inserts into updatehistory table after the records flows into temp_pay_term table.The problem is code requires two fixes 1) when  their are multiple records in temp_pay_term table, delete command at line 65 in below code, deletes only one record of the temp_term table and not all. I want to delete all records of temp_pay_term table once this procedure completes its full work. Will i have to use loop within loop to achieve this. if yes, then how?? please show me the code.
  2) this is bothering me for some time now. from line 28-34 in below code, when i update docmeta table to set XPAYMENTTERM value,since the condition thereafter is a subquery, so i can get only one row. For this reason i want to return record wher pid   = d.xProject_id AND rownum &lt;= 1 order by paytermtimestamp desc. but order by clause does not applies here and throws an error. How can apply order by clause here to get the latest record.</p>
  
  <pre><code>CREATE OR REPLACE
  PROCEDURE TERMUPDATE
  IS
    RecordCount    INT;
    varDID         NUMBER(38);
    varpaymentterm VARCHAR2(200 CHAR);
    varprojectID   VARCHAR2(30 CHAR);
  BEGIN
    RecordCount :=0;
    SELECT COUNT(1)
    INTO RecordCount
    FROM temp_pay_term;
    IF RecordCount &gt; 0 THEN
      FOR X       IN
      (SELECT DISTINCT r.ddocName
      FROM temp_Pay_Term p,
        docMeta d,
        revisions r
      WHERE TO_CHAR(p.pid) = d.xproject_id
      AND r.did            = d.did
      )
      LOOP
        SELECT MAX(did)
        INTO varDid
        FROM revisions r
        WHERE r.ddocname = X.ddocName
        GROUP BY r.ddocname;
        UPDATE docmeta d
        SET XPAYMENTTERM =
          (SELECT paymentterm
          FROM temp_pay_term
          WHERE pid   = d.xProject_id
          AND rownum &lt;= 1
          )
        WHERE d.did = vardid returning xpaymentterm,
          xProject_id
        INTO varpaymentterm,
          varprojectID;
        INSERT
        INTO updatehistory
          (
            UPDATEID,
            DID,
            UPDATETIME,
            METADATAFIELD,
            NEWVALUE,
            OLDVALUE,
            UPDATEDBY
          )
          VALUES
          (
            (SELECT MAX(updateid)+1 FROM updatehistory
            )
            ,
            (SELECT did FROM revisions WHERE did = vardid
            ),
            systimestamp,
            'Payment term',
            varpaymentterm,
            ( SELECT oldpaymentterm FROM temp_pay_term WHERE pid = varprojectID
            ),
            'sysadmin'
          );
      END LOOP;
      DELETE FROM temp_Pay_Term WHERE pid = varprojectID;
      COMMIT;
    END IF;
  END TERMUPDATE; -- PL SQL code
  </code></pre>