Wednesday, June 27, 2012

%TYPE


This example was tested on Oracle 11.2. It shows how you can use %TYPE to create a variable like another variable. In this example, a variable called MAX_OBJ_ID is defined like OBJECT_ID from DBA_OBJECTS. Then SELECT INTO is used to select the maximum value of OBJECT_ID into it and the result is displayed:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2   MAX_OBJ_ID DBA_OBJECTS.OBJECT_ID%TYPE;
  3  BEGIN
  4   SELECT MAX(OBJECT_ID)
  5    INTO MAX_OBJ_ID
  6    FROM DBA_OBJECTS;
  7   DBMS_OUTPUT.PUT_LINE('MAX_OBJ_ID = '||MAX_OBJ_ID);
  8  END;
  9  /
MAX_OBJ_ID = 132369

PL/SQL procedure successfully completed.

SQL>

Finally, the same result is obtained in SQL*Plus:

SQL> SELECT MAX(OBJECT_ID) FROM DBA_OBJECTS
  2  /

MAX(OBJECT_ID)
--------------
        132369

SQL>

No comments: