Data Access: Getting Your Hands on the Data

Hello everyone, and welcome back to our Oracle Database Tutorial for Beginners. Today, we're diving into data access – the key to unlocking all that valuable information you've stored in your database. Let's get started!

Data Access in Oracle Database

When we talk about data access, we're really talking about how we interact with our database. It's all about retrieving, modifying, and managing the data we've stored. Let's explore the tools we have at our disposal.

SQL: The Foundation of Data Access

Let's start with the most fundamental tool in our data access toolkit: SQL, or Structured Query Language. SQL is the primary language we use to communicate with our Oracle Database.

Example – Viewing All Movies:

SELECT * FROM movies;

1 2	SELECT * FROM movies;

This command retrieves a list of movies with their titles, directors, and release dates.

Example – Filtering Movies by Director:

SELECT title FROM movies WHERE director = 'Emma Thompson';

1 2	SELECT title FROM movies WHERE director = 'Emma Thompson';

This retrieves all movies directed by Emma Thompson.

Example – Adding New Information:

INSERT INTO movies (title, director, release_date) 
VALUES ('Sense and Sensibility', 'Ang Lee', TO_DATE('1995-12-13', 'YYYY-MM-DD'));

INSERT INTO movies (title, director, release_date)

VALUES ('Sense and Sensibility', 'Ang Lee', TO_DATE('1995-12-13', 'YYYY-MM-DD'));

This adds a new movie to the database.

Example – Updating Existing Data:

UPDATE movies 
SET director = 'Emma Thompson' 
WHERE title = 'Sense and Sensibility';

UPDATE movies

SET director = 'Emma Thompson'

WHERE title = 'Sense and Sensibility';

This updates the director for ‘Sense and Sensibility'.

PL/SQL: Taking It to the Next Level

While SQL is powerful, sometimes we need even more functionality. That's where PL/SQL comes in. PL/SQL is Oracle's procedural language extension to SQL, allowing us to write more complex operations and create reusable procedures.

Let's walk through an example. We'll add a new column to our movies table and then create a procedure to populate it.

Adding a New Column:

ALTER TABLE movies ADD release_year NUMBER(4);

1 2	ALTER TABLE movies ADD release_year NUMBER(4);

This command adds a ‘release_year' column to our table.

Creating a PL/SQL Procedure:

CREATE OR REPLACE PROCEDURE update_release_years AS 
BEGIN   
  UPDATE movies 
  SET release_year = EXTRACT(YEAR FROM release_date);      
  DBMS_OUTPUT.PUT_LINE('Updated release years for ' || SQL%ROWCOUNT || ' movies.');   
  COMMIT; 
EXCEPTION   
  WHEN OTHERS THEN     
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);     
    ROLLBACK; 
END; 
/

CREATE OR REPLACE PROCEDURE update_release_years AS

BEGIN

UPDATE movies

SET release_year = EXTRACT(YEAR FROM release_date);

DBMS_OUTPUT.PUT_LINE('Updated release years for ' || SQL%ROWCOUNT || ' movies.');

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

ROLLBACK;

END;

This procedure updates the ‘release_year' column for all movies, extracts the year from ‘release_date', and includes error handling.

Running the Procedure:

SET SERVEROUTPUT ON
EXEC update_release_years;

SET SERVEROUTPUT ON

EXEC update_release_years;

This command executes the procedure and shows the output.

Verifying the Results:

SELECT title, release_date, release_year FROM movies;

1 2	SELECT title, release_date, release_year FROM movies;

This command displays the updated information.

Beyond SQL and PL/SQL

This example demonstrates the power of PL/SQL. We've created a reusable procedure that can update our entire table in one operation. But Oracle doesn't stop there. It also supports Java and JavaScript for even more advanced operations, allowing you to perform complex calculations, implement intricate business logic, and even create dynamic web content directly within your database.

Conclusion

To wrap up, remember that mastering data access is about knowing which tool to use for each job. Start with SQL for your day-to-day needs, move on to PL/SQL for more complex operations, and explore Java and JavaScript as you advance.