What Is Number Data Type in PL SQL?

What Is Number Data Type in PL SQL?

In PL/SQL, the number data type is used to store numeric values. It is a fundamental data type that allows you to work with numbers in various operations. The number data type is flexible and can store both integers and decimal numbers.

Declaring a Number Variable

To declare a variable of the number data type, you can use the following syntax:

DECLARE
  variable_name NUMBER;
BEGIN
  -- Code here
END;

You can replace variable_name with the desired name for your variable.

Numeric Precision and Scale

The number data type allows you to specify precision and scale. Precision represents the total number of digits that can be stored in a number, while scale represents the number of digits to the right of the decimal point.

The default precision is 38, which means that PL/SQL can handle numbers with up to 38 digits. The default scale is 0, indicating that numbers are treated as integers by default.

You can specify precision and scale when declaring a number variable:

DECLARE
  my_number NUMBER(10, 2);
BEGIN
  -- Code here
END;

In this example, my_number has a precision of 10 and a scale of 2. It can store numbers with up to two decimal places.

Arithmetic Operations

The number data type supports various arithmetic operations such as addition (+), subtraction (-), multiplication (*), and division (/).

DECLARE
  num1 NUMBER := 10;
  num2 NUMBER := 5;
  result NUMBER;
BEGIN
  result := num1 + num2; -- Addition
  DBMS_OUTPUT.PUT_LINE('Result: ' || result);
  
  result := num1 - num2; -- Subtraction
  DBMS_OUTPUT.PUT_LINE('Result: ' || result);
  
  result := num1 * num2; -- Multiplication
  DBMS_OUTPUT.PUT_LINE('Result: ' || result);
  
  result := num1 / num2; -- Division
  DBMS_OUTPUT.PUT_LINE('Result: ' || result);
END;

Common Functions

The number data type also provides several built-in functions that you can use to manipulate numeric values:

ABS: Returns the absolute value of a number.
CEIL: Rounds a number up to the nearest integer.
FLOOR: Rounds a number down to the nearest integer.
ROUND: Rounds a number to a specified decimal place or precision.
TRUNC: Truncates a number to a specified decimal place or precision.

DECLARE
  my_number NUMBER := -3.75;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Absolute value: ' || ABS(my_number));
  
  DBMS_OUTPUT.PUT_LINE('Ceiling value: ' || CEIL(my_number));
  
  DBMS_OUTPUT.PUT_LINE('Floor value: ' || FLOOR(my_number));
  
  DBMS_OUTPUT.PUT_LINE('Rounded value: ' || ROUND(my_number, 1));
  
  DBMS_OUTPUT.PUT_LINE('Truncated value: ' || TRUNC(my_number, 0));
END;

These functions can be helpful when you need to perform specific calculations or manipulate numerical data.

Conclusion

The number data type in PL/SQL is essential for working with numeric values. It allows you to perform arithmetic operations, control precision and scale, and utilize built-in functions for various calculations. Understanding the number data type is crucial for developing robust PL/SQL programs that involve numerical computations.