Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL SUBTYPE Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 164 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?