PLSQL iteration and bind variable

How to execute anonymous PL/SQL from Java and return resultset

Examples of PL/SQL

• While loop
• Loop labels
• Loop control
• bind variables in Dynamic execution

set serveroutput on;
declare
  entityId data_table.entity_id%TYPE := 123;
  delimiter char := '';
  more char := 'T';
  cnt NUMBER := 1;
  lastTs data_table.timestamp%TYPE := get_ms_from_date(sysdate);
  maxTs data_table.timestamp%TYPE;
  timestampInClause VARCHAR2(100);
  queryStr VARCHAR2(2000);
  queryCnt NUMBER;
begin
  <<ts_loop>>
  while more = 'T' and cnt <= 5
  LOOP
    select max(timestamp) into maxTs from data_table where entity_id = deviceId and timestamp < lastTs;

    IF (maxTs IS NOT NULL) THEN
      timestampInClause := timestampInClause || delimiter || maxTs;
      lastTs := maxTs;
      delimiter := ',';
      cnt := cnt + 1;
    ELSE
      EXIT ts_loop;
    END IF;
  END LOOP ts_loop;

  DBMS_OUTPUT.PUT_LINE('timestampInClause ' || timestampInClause);

  queryStr := 'SELECT count(*)
  FROM data_table a
  WHERE a.entity_id    = ' || entityId || '
  AND a.timestamp in (' || timestampInClause || ')
  ORDER BY a.timestamp DESC,
    a.id DESC
  ) x
WHERE rownum <= 5';

  EXECUTE IMMEDIATE queryStr
    INTO queryCnt;

  DBMS_OUTPUT.PUT_LINE('queryCnt ' || queryCnt);

end;
/