The Project | About Us | Contribute | Donations | License
HOME My courses Completed
◄ Previous
◄

Variables and Data Types

Next ►
►

In this section, we will explore the fundamental concepts of variables and data types in PL/SQL. Understanding these concepts is crucial as they form the building blocks for writing effective PL/SQL code.

What are Variables?

Variables in PL/SQL are used to store data that can be manipulated and retrieved during the execution of a PL/SQL block. They are placeholders for values that can change.

Declaring Variables

To declare a variable in PL/SQL, you need to specify:

  1. The name of the variable.
  2. The data type of the variable.
  3. (Optional) An initial value.

The syntax for declaring a variable is as follows:

variable_name data_type [NOT NULL] [:= initial_value];

Example:

DECLARE
    v_employee_id NUMBER(6);
    v_employee_name VARCHAR2(50) := 'John Doe';
    v_salary NUMBER(8,2) := 50000.00;
BEGIN
    -- PL/SQL block code
END;

Explanation:

  • v_employee_id is a variable of type NUMBER with a precision of 6.
  • v_employee_name is a variable of type VARCHAR2 with a maximum length of 50 characters, initialized to 'John Doe'.
  • v_salary is a variable of type NUMBER with a precision of 8 and scale of 2, initialized to 50000.00.

Data Types

PL/SQL supports various data types that can be broadly categorized into the following groups:

  1. Scalar Data Types: Hold a single value.
  2. Composite Data Types: Hold multiple values of the same or different data types.
  3. Reference Data Types: Hold pointers to other data items.
  4. LOB Data Types: Hold large objects such as text, images, and videos.

Scalar Data Types

Scalar data types include:

  • NUMBER: Stores numeric values.
  • CHAR and VARCHAR2: Store character strings.
  • DATE: Stores date and time values.
  • BOOLEAN: Stores TRUE, FALSE, or NULL.

Example:

DECLARE
    v_age NUMBER(3);
    v_first_name VARCHAR2(30);
    v_is_active BOOLEAN;
    v_hire_date DATE;
BEGIN
    v_age := 25;
    v_first_name := 'Alice';
    v_is_active := TRUE;
    v_hire_date := SYSDATE;
END;

Composite Data Types

Composite data types include:

  • RECORD: A group of related data items.
  • TABLE: A collection of data items of the same type.

Example:

DECLARE
    TYPE EmployeeRec IS RECORD (
        employee_id NUMBER(6),
        employee_name VARCHAR2(50),
        salary NUMBER(8,2)
    );
    v_employee EmployeeRec;
BEGIN
    v_employee.employee_id := 101;
    v_employee.employee_name := 'Bob';
    v_employee.salary := 60000.00;
END;

Reference Data Types

Reference data types include:

  • REF CURSOR: A pointer to a cursor that can be used to fetch rows from a query.

Example:

DECLARE
    TYPE ref_cursor IS REF CURSOR;
    v_cursor ref_cursor;
BEGIN
    OPEN v_cursor FOR SELECT * FROM employees;
    -- Fetch rows from the cursor
    CLOSE v_cursor;
END;

LOB Data Types

LOB data types include:

  • BLOB: Binary Large Object.
  • CLOB: Character Large Object.
  • NCLOB: National Character Large Object.
  • BFILE: Binary File stored outside the database.

Example:

DECLARE
    v_document CLOB;
BEGIN
    -- Initialize and manipulate the CLOB variable
END;

Practical Exercises

Exercise 1: Declaring and Initializing Variables

Declare variables for an employee's ID, name, and salary. Initialize them with appropriate values.

DECLARE
    v_employee_id NUMBER(6) := 123;
    v_employee_name VARCHAR2(50) := 'Jane Smith';
    v_salary NUMBER(8,2) := 75000.00;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;

Exercise 2: Using Composite Data Types

Create a record type for an employee and initialize it with values.

DECLARE
    TYPE EmployeeRec IS RECORD (
        employee_id NUMBER(6),
        employee_name VARCHAR2(50),
        salary NUMBER(8,2)
    );
    v_employee EmployeeRec;
BEGIN
    v_employee.employee_id := 456;
    v_employee.employee_name := 'Michael Johnson';
    v_employee.salary := 85000.00;
    
    DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee.employee_id);
    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee.employee_name);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_employee.salary);
END;

Common Mistakes and Tips

  • Uninitialized Variables: Always initialize variables to avoid unexpected NULL values.
  • Data Type Mismatch: Ensure that the data type of the variable matches the type of the value being assigned.
  • Scope of Variables: Remember that variables declared in a block are local to that block and cannot be accessed outside it.

Conclusion

In this section, we covered the basics of variables and data types in PL/SQL. We learned how to declare and initialize variables, explored different data types, and practiced using them through exercises. Understanding these concepts is essential for writing effective PL/SQL code. In the next section, we will delve into control structures, which will allow us to control the flow of our PL/SQL programs.

◄ Previous
◄
Next ►
►
The Project | About Us | Contribute | Donations | License
© Copyright 2024. All rights reserved