Question

State any four PL/SQL data types.

Answer 1

Of course. PL/SQL supports a wide range of data types, which can be categorized into scalar, composite, reference, and large object (LOB) types. Here are four of the most common and fundamental scalar data types used in PL/SQL, along with their descriptions and examples.

---

1. VARCHAR2

• Description:
  The VARCHAR2 (Variable Character String) data type is used to store variable-length character strings, such as names, addresses, or descriptions. You must specify a maximum size for a VARCHAR2 variable, and it can hold a string up to that size. It is the most commonly used data type for text in Oracle PL/SQL.

• Syntax:
  variable_name VARCHAR2(max_size);
  * max_size is an integer from 1 to 32,767 that specifies the maximum number of bytes.

• Example:
  This example declares a VARCHAR2 variable to hold a customer's name.
  `sql
  DECLARE

  v_customer_name VARCHAR2(100);
  

  BEGIN

  v_customer_name := 'John Smith';
  DBMS_OUTPUT.PUT_LINE('Customer Name: ' || v_customer_name);
  

  END;
  /
  `

2. NUMBER

• Description:
  The NUMBER data type is used to store numeric values, including both integers and floating-point (decimal) numbers. It is highly flexible and can store a wide range of values with high precision.

• Syntax:
  variable_name NUMBER(precision, scale);
precision (optional): The total number of digits.
  scale (optional): The number of digits to the right of the decimal point. If omitted, it can store floating-point numbers.

• Example:
  This example declares different NUMBER variables to store an integer quantity, a fixed-decimal price, and a floating-point salary.
  `sql
  DECLARE

  v_item_quantity NUMBER(5);       -- An integer up to 99999
  v_item_price    NUMBER(7, 2);    -- A number like 12345.67
  v_employee_salary NUMBER;        -- A floating-point number
  

  BEGIN

  v_item_quantity := 150;
  v_item_price    := 49.99;
  v_employee_salary := 65000.75;
  
  DBMS_OUTPUT.PUT_LINE('Quantity: ' || v_item_quantity);
  DBMS_OUTPUT.PUT_LINE('Price: ' || v_item_price);
  DBMS_OUTPUT.PUT_LINE('Salary: ' || v_employee_salary);
  

  END;
  /
  `

3. DATE

• Description:
  The DATE data type is used to store date and time values. In Oracle, a DATE value stores the century, year, month, day, hour, minute, and second. It is essential for tracking time-based information like hire dates, order dates, or birthdays.

• Syntax:
  variable_name DATE;

• Example:
  This example declares a DATE variable and assigns it the current system date and time.
  `sql
  DECLARE

  v_hire_date DATE;
  

  BEGIN

  -- SYSDATE is a built-in function that returns the current database server date and time
  v_hire_date := SYSDATE;
  
  -- TO_CHAR is used to format the date for display
  DBMS_OUTPUT.PUT_LINE('Hire Date: ' || TO_CHAR(v_hire_date, 'DD-MON-YYYY HH24:MI:SS'));
  

  END;
  /
  `

4. BOOLEAN

• Description:
  The BOOLEAN data type is used to store logical values. It can only hold one of three possible values: TRUE, FALSE, or NULL. It is primarily used for conditional logic in IF-THEN-ELSE statements and loops to control the flow of a program.

• Syntax:
  variable_name BOOLEAN;
  Note: The BOOLEAN data type is a PL/SQL-only data type. You cannot create a table column with a BOOLEAN type, and you cannot select a BOOLEAN value directly from a SQL query.

• Example:
  This example uses a BOOLEAN flag to check if an employee is eligible for a bonus.
  `sql
  DECLARE

  v_salary          NUMBER := 50000;
  v_is_eligible     BOOLEAN;
  

  BEGIN

  IF v_salary > 40000 THEN
      v_is_eligible := TRUE;
  ELSE
      v_is_eligible := FALSE;
  END IF;
  
  IF v_is_eligible THEN
      DBMS_OUTPUT.PUT_LINE('Employee is eligible for a bonus.');
  ELSE
      DBMS_OUTPUT.PUT_LINE('Employee is not eligible for a bonus.');
  END IF;
  

  END;
  /
  `