PL SQL primer: If Exists, Constants, and Cursors

Helpful SQL statements…

 

DECLARE

  CNT  INT;

  -- Declare a constant

  IN_COMPONENT_ID  CONSTANT INT := 142;

BEGIN

  SELECT COUNT(* )

  INTO   CNT

  FROM   TABLE1

  WHERE  BASE_COMPONENT_ID = IN_COMPONENT_ID;

 

  -- Similar to If Exists in T-SQL

  IF CNT > 0 THEN

    DBMS_OUTPUT.PUT_LINE('found');

   

    DECLARE

      CURSOR C1 IS

        SELECT PLATFORM_ID

        FROM   TABLE1

        WHERE  COMPONENT_ID = IN_COMPONENT_ID;

    BEGIN

        -- Implicit For row declaration

      FOR APP_REC IN C1 LOOP

        DBMS_OUTPUT.PUT_LINE('id: ' || APP_REC.PLATFORM_ID);

      END LOOP;

    END;

  END IF;

END;