4.1.6 User-Defined PL/SQL Subtypes

Table of Contents Previous Next


4 Stored Procedure Language : 4.1 Basic SPL Elements : 4.1.6 User-Defined PL/SQL Subtypes

SUBTYPE subtype_name IS type_name[(constraint)] [NOT NULL]
Where constraint is:
{precision [, scale]} | length
subtype_name
subtype_name specifies the name of the subtype.
type_name
type_name specifies the name of the original type on which the subtype is based. type_name may be:
A column anchored by a %TYPE operator.
Include the constraint clause to define restrictions for types that support precision or scale.
precision specifies the total number of digits permitted in a value of the subtype.
scale specifies the number of fractional digits permitted in a value of the subtype.
length specifies the total length permitted in a value of CHARACTER, VARCHAR, or TEXT base types
Include the NOT NULL clause to specify that NULL values may not be stored in a column of the specified subtype.
To create an unconstrained subtype, use the SUBTYPE command to specify the new subtype name and the name of the type on which the subtype is based. For example, the following command creates a subtype named address that has all of the attributes of the type, CHAR:
This command creates a subtype named cust_address that shares all of the attributes of the address subtype. Include the NOT NULL clause to specify that a value of the cust_address may not be NULL.
Include a length value when creating a subtype that is based on a character type to define the maximum length of the subtype. For example:
This example creates a subtype named acct_name that is based on a VARCHAR data type, but is limited to 15 characters in length.
Include values for precision (to specify the maximum number of digits in a value of the subtype) and optionally, scale (to specify the number of digits to the right of the decimal point) when constraining a numeric base type. For example:
This example creates a subtype named acct_balance that shares all of the attributes of a NUMBER type, but that may not exceed 3 digits to the left of the decimal point and 2 digits to the right of the decimal.
An argument declaration (in a function or procedure header) is a formal argument. The value passed to a function or procedure is an actual argument. When invoking a function or procedure, the caller provides (0 or more) actual arguments. Each actual argument is assigned to a formal argument that holds the value within the body of the function or procedure.
You can use %TYPE notation to declare a subtype anchored to a column. For example:
This command creates a subtype named emp_type whose base type matches the type of the empno column in the emp table. A subtype that is based on a column will share the column size constraints; NOT NULL and CHECK constraints are not inherited.

4 Stored Procedure Language : 4.1 Basic SPL Elements : 4.1.6 User-Defined PL/SQL Subtypes

Table of Contents Previous Next