Number types in pl/sql, PL-SQL Programming

Number Types

The Number types permit you to store the numeric data (real numbers, integers, and floating-point numbers), show quantities, and do computations.

BINARY_INTEGER

You use the BINARY_INTEGER datatype to store the signed integers. Its magnitude ranges from -2147483647 to 2147483647. Like PLS_INTEGER values, BINARY_INTEGER values need less storage than NUMBER values. Though, most BINARY_INTEGER operations are slower than the PLS_INTEGER operations.

BINARY_INTEGER Subtypes

A base type is the datatype from which a subtype is derived. A subtype links a base type with a constraint and so defines a subset of the values. For your convenience, the PL/SQL predefines the following BINARY_INTEGER subtypes as shown below:

NATURAL

NATURALN

POSITIVE

POSITIVEN

SIGNTYPE

The subtypes NATURAL & POSITIVE restrict an integer variable to non-negative or positive values, respectively. The NATURALN and POSITIVEN prevent the assigning of nulls to an integer variable. The SIGNTYPE restrict an integer variable to the values -1, 0, and 1, which is useful in the programming tri-state logic.

NUMBER

You use the NUMBER datatype to store a fixed-point or floating-point numbers of virtually any sizes. Its magnitude range is 1E-130 to 10E125. If the value of an expression falls exterior to this range, you get a numeric overflow or underflow error. You can specify the precision that is the total number of digits, and the scale, which is the number of digits to the right of the decimal point. The syntax is as shown below:

NUMBER[(precision,scale)]

To declare a fixed-point numbers, for which you must specify the scale, use the form as shown:

NUMBER(precision,scale)

To declare a floating-point numbers, for which you cannot identify the precision or scale as the decimal point can "float" to any position, use the form as shown:

NUMBER

To declare the integers, that have no decimal point, use the form sown below:

NUMBER(precision) -- same as NUMBER(precision,0)

You cannot use the constants or variables to specify the precision and scale; you should use the integer literals. The highest precision of a NUMBER value is 38 decimal digits. If you do not specify the precision, it default to 38 or the highest supported by your system, either is less.

NUMBER Subtypes

You can use the NUMBER subtypes for compatibility with ANSI/ISO and IBM types or when you want a more expressive name:

DEC

DECIMAL

DOUBLE PRECISION

FLOAT

INTEGER

INT

NUMERIC

REAL

SMALLINT

Use the subtypes DEC, DECIMAL, & NUMERIC to declare the fixed-point numbers with a greatest precision of 38 decimal digits.

Use the subtypes DOUBLE PRECISION & FLOAT to declare the floating-point numbers with a greatest precision of 126 binary digits, which is roughly equal to 38 decimal digits. Or, use the subtype REAL to declare a floating-point numbers with a greatest precision of 63 binary digits, which is roughly equal to 18 decimal digits.

Use the subtypes INTEGER, INT, & SMALLINT to declare integers with a greatest precision of 38 decimal digits.

PLS_INTEGER

You use the PLS_INTEGER datatype to store the signed integers. Its magnitude ranges from

2147483647 to 2147483647. The PLS_INTEGER values need less storage than NUMBER values. The PLS_INTEGER operations also use the machine arithmetic; therefore they are faster than NUMBER & BINARY_INTEGER operations that use library arithmetic. For superior performance, use PLS_INTEGER for all computations that fall within its magnitude range.

Though PLS_INTEGER and BINARY_INTEGER have the same magnitude range, they are not fully compatible. If a PLS_INTEGER computations overflows, an exception is raised. However, if  a BINARY_INTEGER calculation overflows, no exception is raised if the result is assigned to a NUMBER variable.Number Types

The Number types permit you to store the numeric data (real numbers, integers, and floating-point numbers), show quantities, and do computations.

BINARY_INTEGER

You use the BINARY_INTEGER datatype to store the signed integers. Its magnitude ranges from -2147483647 to 2147483647. Like PLS_INTEGER values, BINARY_INTEGER values need less storage than NUMBER values. Though, most BINARY_INTEGER operations are slower than the PLS_INTEGER operations.

BINARY_INTEGER Subtypes

A base type is the datatype from which a subtype is derived. A subtype links a base type with a constraint and so defines a subset of the values. For your convenience, the PL/SQL predefines the following BINARY_INTEGER subtypes as shown below:

NATURAL

NATURALN

POSITIVE

POSITIVEN

SIGNTYPE

The subtypes NATURAL & POSITIVE restrict an integer variable to non-negative or positive values, respectively. The NATURALN and POSITIVEN prevent the assigning of nulls to an integer variable. The SIGNTYPE restrict an integer variable to the values -1, 0, and 1, which is useful in the programming tri-state logic.

NUMBER

You use the NUMBER datatype to store a fixed-point or floating-point numbers of virtually any sizes. Its magnitude range is 1E-130 to 10E125. If the value of an expression falls exterior to this range, you get a numeric overflow or underflow error. You can specify the precision that is the total number of digits, and the scale, which is the number of digits to the right of the decimal point. The syntax is as shown below:

NUMBER[(precision,scale)]

To declare a fixed-point numbers, for which you must specify the scale, use the form as shown:

NUMBER(precision,scale)

To declare a floating-point numbers, for which you cannot identify the precision or scale as the decimal point can "float" to any position, use the form as shown:

NUMBER

To declare the integers, that have no decimal point, use the form sown below:

NUMBER(precision) -- same as NUMBER(precision,0)

You cannot use the constants or variables to specify the precision and scale; you should use the integer literals. The highest precision of a NUMBER value is 38 decimal digits. If you do not specify the precision, it default to 38 or the highest supported by your system, either is less.

NUMBER Subtypes

You can use the NUMBER subtypes for compatibility with ANSI/ISO and IBM types or when you want a more expressive name:

DEC

DECIMAL

DOUBLE PRECISION

FLOAT

INTEGER

INT

NUMERIC

REAL

SMALLINT

Use the subtypes DEC, DECIMAL, & NUMERIC to declare the fixed-point numbers with a greatest precision of 38 decimal digits.

Use the subtypes DOUBLE PRECISION & FLOAT to declare the floating-point numbers with a greatest precision of 126 binary digits, which is roughly equal to 38 decimal digits. Or, use the subtype REAL to declare a floating-point numbers with a greatest precision of 63 binary digits, which is roughly equal to 18 decimal digits.

Use the subtypes INTEGER, INT, & SMALLINT to declare integers with a greatest precision of 38 decimal digits.

PLS_INTEGER

You use the PLS_INTEGER datatype to store the signed integers. Its magnitude ranges from

2147483647 to 2147483647. The PLS_INTEGER values need less storage than NUMBER values. The PLS_INTEGER operations also use the machine arithmetic; therefore they are faster than NUMBER & BINARY_INTEGER operations that use library arithmetic. For superior performance, use PLS_INTEGER for all computations that fall within its magnitude range.

Though PLS_INTEGER and BINARY_INTEGER have the same magnitude range, they are not fully compatible. If a PLS_INTEGER computations overflows, an exception is raised. However, if  a BINARY_INTEGER calculation overflows, no exception is raised if the result is assigned to a NUMBER variable.

Posted Date: 10/2/2012 2:30:54 AM | Location : United States







Related Discussions:- Number types in pl/sql, Assignment Help, Ask Question on Number types in pl/sql, Get Answer, Expert's Help, Number types in pl/sql Discussions

Write discussion on Number types in pl/sql
Your posts are moderated
Related Questions
ROWID The ROWID returns the rowid (binary address) of a row in the database table. You can use the variables of the type UROWID to store rowids in a readable format. In the il

On occasion, some of Brewbean's customers mistakenly leave an item out of a basket already checked out, so they create a new basket containing the missing items. However, they requ

Deriving Predicates from Predicates in SQL The corresponding section in the theory book describes how predicates can be derived from predicates using (a) the logical connectiv

DBMS_PIPE: The Package DBMS_PIPE allows various sessions to communicate over the named pipes. (A pipe is a region of memory used by one of the process to pass information to

DELETE Command - SQL Loosely speaking, DELETE removes some existing rows from its target table. Suppose the university decides that course C3 is to be withdrawn. Example shows

Relational Algebra - SQL It describes some operators, that together constitute an algebra that is not only relationally complete but also irreducibly so (very nearly- apart f

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi

Description: Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that pr

Semidifference via NOT IN and a subquery SELECT StudentId FROM IS_CALLED WHERE Name = 'Devinder' AND StudentId NOT IN (SELECT StudentId FROM IS_ENROLLED_ON WHER

Some Varray Examples In SQL Plus, assume that you define an object type Project, as described below: SQL> CREATE TYPE Project AS OBJECT ( 2 project_no NUMBER(2), 3 title VARCHA