Quick Search:
 
 The Oracle PL/SQL SUBTYPE Keyword      [Return To Index] Jump to:  

Term: SUBTYPE

Definition:
In Oracle Pl/SQL, a SUBTYPE can be declared on base data type available in Oracle with some additional customization. The subtypes available in Oracle STANDARD package are CHARACTER, INTEGER, POSITIVE, and POSITIVEN.

Constrained and Unconstrained are the two types of available subtypes. Constrained subtypes are the one which are evolved by imposing a constraint on base type. For example, subtype POSITIVE is evolved from BINARY_INTEGER by restricting value range between 1 and 2147483647. Unconstrained subtype is an alternate name for the base type to enhance readability and flexibility in applications. FLOAT and CHARACTER are unconstrained subtypes of NUMBER and CHAR respectively.

User defined subtypes can also be declared in DECLARE section for usage local to PL/SQL block.

Example Syntax:

SUBTYPE <subtype_name> IS <base_type> [(constraint)] [NOT NULL];


Base type can be a predefined data type or subtype or %TYPE or %ROWTYPE variable or PL/SQL table type.

Example Usage:

The PL/SQL block below declares a subtype HIREDATE which is a NOT NULL form of DATE. A variable PFDATE is declared of HIREDATE type and initialized to NULL.

DECLARE
SUBTYPE HIREDATE IS DATE NOT NULL;
PFDATE HIREDATE := SYSDATE;
BEGIN
DBMS_OUTPUT.PUT_LINE(PFDATE);
END;
/

27-JAN-11

PL/SQL procedure successfully completed.



Related Links:
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org