With the SUB TYPE statement, PL/SQL allows
you to define your own sub types or aliases of predefined datatypes, sometimes
referred to as abstract datatypes.
There are two kinds of subtypes.
Ø Constrained
Ø Unconstrained
CONSTRAINED SUB TYPE
A subtype that restricts or constrains the values normally allowd
by the datatype itself.
Ex:
Subtype positive
is binary_integer range 1..2147483647;
In the above declaration a variable that is declared as positive
can store only ingeger greater than zero even though binary_integer ranges from
-2147483647..+2147483647.
UNCONSTRAINED SUB TYPE
A subtype that does not restrict the values of the original
datatype in variables declared with the subtype.
Ex:
Subtype float is
number;
DATATYPE CONVERSIONS
PL/SQL can
handle conversions between different families among the datatypes.
Conversion can be done in two ways.
Ø Explicit conversion
Ø Implicit conversion
EXPLICIT CONVERSION
This can be done using the built-in functions available.
IMPLICIT CONVERSION
PL/SQL will automatically convert between datatype families when
possible.
Ex:
DECLARE
a varchar(10);
BEGIN
select deptno into a from dept where dname='ACCOUNTING';
END;
In the above variable a is char type and deptno is number type
even though, oracle will automatically converts the numeric data into char type
assigns to the variable.
PL/SQL can
automatically convert between
Ø Characters and numbers
Ø Characters and dates
VARIABLE SCOPE AND VISIBILITY
The scope of a variable is the portion of the program in which the
variable can be accessed. For PL/SQL variables, this is from the variable
declaration until the end of the block. When a variable goes out of scope, the PL/SQL engine will free the memory used to store the variable.
The visibility of a variable is the portion of the program where
the variable can be accessed without having to qualify the reference. The
visibility is always within the scope. If it is out of scope, it is not
visible.
Ex1:
DECLARE
a number; -- scope of a
BEGIN
--------
DECLARE
b number; -- scope of b
BEGIN
-----
END;
------
END;
Ex2:
DECLARE
a number;
b
number;
BEGIN
-- a , b available here
DECLARE
b char(10);
BEGIN
-- a and char type b is available here
END;
-----
END;
Ex3:
<<my_block>>
DECLARE
a number;
b
number;
BEGIN
-- a , b available here
DECLARE
b char(10);
BEGIN
-- a and char type b is available here
-- number type b is available using <<my_block>>.b
END;
------
END;
No comments:
Post a Comment