How to Add Column in View in PL/SQL?

Adding a column to a view in PL/SQL is a fundamental operation for modifying the structure and data presented by a view. Views are virtual tables created from one or more underlying tables or views, and altering them allows for customization of data presentation without affecting the underlying tables. This article discusses how to add columns to PL/SQL views, providing syntax and examples.

How to Add a Column in View in PL/SQL

The process of adding a column to a view in PL/SQL involves altering the view definition using the ALTER VIEW statement. However, this statement does not directly support adding columns. Instead, you need to recreate the view with the new column included. The syntax for adding a column to a view is as follows:

Syntax:

CREATE OR REPLACE VIEW view_name AS

SELECT column1, column2, …, new_column

FROM existing_table;

This statement recreates the view view_name with the additional column new_column. The columns from the existing table or view are retained, and the new column is appended to the select list.

Examples of How to Add a Column in View in PL/SQL

Example 1: Modifying View to Include City Information

Suppose we have a view named customer_info, which displays information about customers. Now, let’s add a new column named city to this view.

-- Schema for existing table
CREATE TABLE customers (
    customer_id INT
    customer_name VARCHAR(50),
    address VARCHAR(100)
);

-- Insert some sample data
INSERT INTO customers VALUES (1, 'John Doe', '123 Main St');
INSERT INTO customers VALUES (2, 'Jane Smith', '456 Elm St');

-- Create the initial view
CREATE OR REPLACE VIEW customer_info AS
SELECT customer_id, customer_name, address
FROM customers;

-- Add a new column to the view
CREATE OR REPLACE VIEW customer_info AS
SELECT customer_id, customer_name, address, 'New York' AS city
FROM customers;

Output:

+-------------+---------------+-------------+----------+
| customer_id | customer_name | address     | city     |
+-------------+---------------+-------------+----------+
|           1 | John Doe      | 123 Main St | New York |
|           2 | Jane Smith    | 456 Elm St  | New York |
+-------------+---------------+-------------+----------+

View CUSTOMER_INFO created.

Explanation: This statement creates or replaces a view named customer_info, combining data from the customers table with a constant value ‘New York‘ assigned to a new column named city. The view will display customer information along with the fixed city value for all records.

Example 2: Adding City and Phone Number Columns to Customer View

Let’s add another column named phone_number to the customer_info view, this time using a different approach.

-- Add a new column to the view
CREATE OR REPLACE VIEW customer_info AS
SELECT customer_id, customer_name, address,
       'New York' AS city, NULL AS phone_number
FROM customers;

Output:

+-------------+---------------+-------------+----------+--------------+
| customer_id | customer_name | address     | city     | phone_number |
+-------------+---------------+-------------+----------+--------------+
|           1 | John Doe      | 123 Main St | New York |         NULL |
|           2 | Jane Smith    | 456 Elm St  | New York |         NULL |
+-------------+---------------+-------------+----------+--------------+

View CUSTOMER_INFO created.

Explanation: This code alters the existing view customer_info by adding a new column phone_number with NULL values for all rows. Additionally, it retains the previous columns customer_id, customer_name, and address, and introduces a constant value of ‘New York‘ as the city for all records. The view now includes customer details along with a placeholder for phone numbers.

Conclusion

Adding columns to views in PL/SQL is a crucial aspect of customizing data presentation without altering underlying tables. By understanding the syntax and examples provided in this article, users can effectively modify views to include additional columns as per their requirements. Whether adding columns derived from existing data or with default values, the flexibility of PL/SQL enables seamless customization of view structures.