How to Set a Column Value to Null in PL/SQL?

In PL/SQL, setting a column value to NULL is a common requirement when working with databases. Understanding how to set column values to NULL is essential for database developers and administrators. In this article, we will look into the concept of setting a column value to NULL in PL/SQL, covering the syntax, examples, and output explanations.

Key Concepts in PL/SQL and Database Management

• PL/SQL: A procedural language extension for SQL by Oracle.
• NULL: Represents a missing or unknown value in a database.
• Database Management: A crucial skill for developers and administrators.

Setting Column Value to NULL in Oracle PL/SQL

To set a column value to NULL in Oracle PL/SQL, the UPDATE statement is used. The syntax for updating a column to NULL is as follows:

UPDATE table_name
SET column_name = NULL
WHERE condition;

Here,

• UPDATE: SQL keyword used to modify or change existing records in a table.
• table_name: The name of the table that you want to update.
• SET: SQL keyword indicating the column you want to modify.
• column_name: The specific column within the table that you want to update.
• NULL: Set the value of the specified column to NULL.
• WHERE: SQL keyword used to filter the rows that will be updated.
• condition: The criteria that the rows must meet to be updated. If omitted, all rows will be updated.

Examples of Updating Column Value to NULL

Example 1: Setting a Column to NULL in the "Employee" Table

We can create the 'Employee' table using the following SQL code, which defines the table structure with columns such as 'Emp_ID,' 'Emp_Name,' 'Salary', and 'department_Id,' specifying appropriate data types for each column to store information about Employee' details.

CREATE TABLE Employee (
    Emp_ID INT PRIMARY KEY,
    Emp_Name VARCHAR(255),
    Salary DECIMAL(10, 2),
    department_Id INT
);

INSERT INTO Employee (Emp_ID, Emp_Name, Salary, department_Id)
VALUES (101, 'John Doe', 50000.00, 1),
(102, 'Jane Smith', 60000.00, 2),
(103, 'Robert Johnson', 55000.00, 1),
(104, 'Emily Davis', 48000.00, 2);

And we have to set the salary to NULL for an employee with Emp_id 101, the following query can be used:

UPDATE Employee
SET Salary = NULL
WHERE Emp_Id = 101;

Output:

We can observe that the salary for the employee with 'Emp_Id' 101 is set to NULL. This operation is useful when you want to mark certain data as unavailable or remove outdated salary information.

Example 2: Setting a Column to NULL in the "Student" Table

We can create the 'Students' table using the following SQL code, which defines the table structure with columns such as 'Students,' 'Subject,' and 'Marks,' specifying appropriate data types for each column to store information about students' marks in different subjects.

CREATE TABLE Student (
    Student_ID INT PRIMARY KEY,
    Name VARCHAR(50), 
    Date_of_Birth DATE,
    Major VARCHAR(100),
    GPA DECIMAL(3, 2)
);

INSERT INTO Student (Student_ID, Name, Date_of_Birth, Major, GPA)
VALUES
    (1, 'John Doe', '2000-05-15', 'Computer Science', 3.75),
    (2, 'Jane Smith', '2001-02-28', 'Mathematics', 3.90),
    (3, 'Michael Johnson', '1999-11-10', 'Physics', 3.60),
    (4, 'Emily Williams', '2000-08-05', 'Biology', 3.80);

And we have to set the GPA to NULL for an Student with Student_ID 4, the following query can be used:

UPDATE Student
SET GPA = NULL
WHERE Student_ID = 4;

Output:

We can observe that the GPA for the Student with 'Student_ID' 4 is set to NULL. This approach is helpful when GPA information is temporarily unavailable or requires re-evaluation.

Conclusion

In conclusion, setting a column value to NULL in PL/SQL, using the UPDATE statement, is essential for maintaining data accuracy and integrity. "Data Accuracy" ensures the correctness of records, while "Data Integrity" preserves data reliability over time. Mastering this concept is vital for efficient database management and consistent, error-free data handling in PL/SQL projects.