Oracle SQL and PL SQL Tutorial
This in-depth Oracle SQL and PL SQL tutorial is geared exclusively to beginners, taking you through the basics of Oracle SQL (Structured Query Language) and PL/SQL (Procedural Language/SQL). Whether you want to learn database programming for beginners, become an expert on Oracle database queries and stored procedures, or just learn how to handle data using Oracle SQL and PL/SQL, you’ve arrived at the right location.
If you’re ready to speed up your learning and acquire organized knowledge, then have a look at our Oracle SQL and PL/SQL Course Syllabus for an in-depth topic breakdown and learning paths.
Understanding Databases: The Foundation
Before we get into the nitty-gritty details of Oracle SQL and PL/SQL, let’s gain a basic understanding of what databases are and why they are so important in our data-driven world today.
What is a Database?
A database is a structured group of data, usually stored and retrieved electronically from a computer system. Imagine it as an extremely structured digital filing cabinet. Instead of folders and paper, you have columns and rows of data, tables.
Relational Database Management Systems (RDBMS)
All of the most current databases, including Oracle, are Relational Database Management Systems (RDBMS). Data is structured into tables (also relations), each with rows and columns, within an RDBMS. These tables are connected with one another on common fields so you can associate and merge data from multiple sources.
The relational model is an unambiguous, logical means of storing and managing data so you can query and maintain data consistency easily.
Why Oracle Database?
Oracle Database is a very powerful RDBMS system globally. It’s renowned for its:
- Scalability: It handles enormous data and huge user bases.
- Reliability: It is built for mission-critical use with robust data integrity and recovery capabilities.
- Security: It has full-fledged security features to safeguard sensitive information.
- Performance: It is optimized to process data at high speed.
- Portability: It oerates across different operating systems and hardware platforms.
Acquiring Oracle database management skills is extremely worthwhile in the world of IT, leading to positions such as database administrator, developer, and data analyst.
SQL vs. PL/SQL: The Main Difference
It is easy to get confused between SQL and PL/SQL for newbies. Let’s clear the air:
SQL (Structured Query Language): It is the default language used to communicate with relational databases. You use SQL to execute commands such as:
- Querying data: SELECT
- Manipulating data: INSERT, UPDATE, DELETE (DML)
- Defining database structures: CREATE, ALTER, DROP (DDL)
- Controlling access: GRANT, REVOKE (DCL)
- SQL is mostly a declarative language – you specify what you want, not how to do it.
PL/SQL (Procedural Language/SQL): This is Oracle’s procedural extension of SQL. While SQL is for data operations, PL/SQL enables you to write programmatic logic, like other programming languages. It enables you to:
- Declare variables and constants.
- Implement conditional logic (IF-THEN-ELSE, CASE).
- Use looping control constructs (LOOP, WHILE, FOR).
- Catch errors properly (exceptions).
- Make reusable code blocks (procedures, functions, packages, triggers).
- PL/SQL is a procedural language – you instruct the database to execute a set of steps.
In short, SQL is for direct communication with the database, and PL/SQL offers the programming flexibility to create sophisticated applications that communicate with the database. SQL statements are typically inserted within PL/SQL code blocks. Both need to be mastered to do Oracle database development.
Suggested: Oracle PL/SQL Course Online.
Introduction to Oracle SQL
We start our hands-on journey with SQL, the database language. We will learn how to connect to Oracle and execute simple data retrieval.
Connecting to Oracle
In order to work with an Oracle database, you require a client tool. Some common ones for beginners are:
- Oracle SQL Developer: A free, graphical integrated development environment (IDE) from Oracle. It’s well worth using for its ease of use and rich features.
- SQL*Plus: A command-line tool that is part of every Oracle installation. It is lightweight and useful for scripting and short commands.
For the purpose of this Oracle SQL *Plus tutorial, we will mostly present SQL examples, and you can execute them in either tool.
SQL Basic Syntax: SELECT Statement
The SELECT statement is the most basic SQL statement. It is utilized to obtain data from a single or multiple tables.
Syntax:
SELECT column1, column2, …
FROM table_name;
Example: All data retrieval from a table
Filtering Data with WHERE Clause
The WHERE clause is applied to filter the rows yielded by a SELECT statement according to certain conditions.
Syntax:
SELECT column1, column2, …
FROM table_name
WHERE condition;
Example: Employees whose salary is more than 5000
SELECT first_name, last_name, salary
FROM EMPLOYEES
WHERE salary > 5000;
SQL Operators: Arithmetic, Comparison, Logical
SQL employs a range of operators to specify conditions and execute calculations:
- Arithmetic Operators: +, -, *, /
- Comparison Operators: =, != (or <>), >, <, >=, <=
- Logical Operators:
- AND: Both conditions need to be true.
- OR: One of the conditions needs to be true.
- NOT: Reverses a condition.
- BETWEEN: Verifies if a value falls within a range (inclusive).
- IN: Verifies if a value exists in a list.
- LIKE: Used for pattern matching with wildcards (% for any string of characters, _ for one character).
- IS NULL / IS NOT NULL: Verifies null values.
Example: Multiple WHERE conditions and LIKE
SELECT first_name, last_name, email, salary
FROM EMPLOYEES
WHERE salary BETWEEN 3000 AND 7000
AND first_name LIKE ‘S%’
AND email IS NOT NULL;
This SQL query fetches employees whose salary is between 3000 and 7000 (inclusive), whose first name begins with ‘S’, and who possess an email address. This is one of the simple Oracle SQL query examples for beginners.
Sorting Data with ORDER BY Clause
The ORDER BY clause orders the result set of a SELECT statement.
Syntax:
SELECT column1, column2, …
FROM table_name
WHERE condition
ORDER BY column_name [ASC | DESC];
- ASC: Ascending order (default).
- DESC: Descending order.
Example: Sorting employees in descending order of salary.
SELECT first_name, last_name, salary
FROM EMPLOYEES
ORDER BY salary DESC;
This sorts by department ID in ascending order, and by salary in descending order for each department. This illustrates Oracle SQL data sorting methods.
Related: Oracle DBA Course Online.
Working with Data: DML Operations
Database tables can have data added, changed, and removed using Data Manipulation Language (DML) statements.
INSERT Statement: Including New Documents
New rows of data are added to a table using the INSERT statement.
Syntax (Inserting all columns):
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);
Syntax (Inserting into specific columns):
INSERT INTO table_name
VALUES (value1, value2, …); — All columns must be provided in order
Example: Adding a new employee
INSERT INTO EMPLOYEES (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
VALUES (207, ‘Rahul’, ‘Sharma’, ‘RAHUL.SHARMA@example.com’, SYSDATE, ‘IT_PROG’, 6500, 60);
Here, the current date and time are returned by the built-in Oracle function SYSDATE.
UPDATE Statement: Changing Current Information
A table’s existing rows can be changed using the UPDATE statement.
Syntax:
UPDATE table_name
SET column1 = new_value1, column2 = new_value2, …
WHERE condition;
WHERE clause omission will cause the UPDATE statement to change every row in the table!
Example: Increasing salary for a specific employee
UPDATE EMPLOYEES
SET salary = salary * 1.10 — Increase by 10%
WHERE employee_id = 207;
Example: Updating multiple columns
UPDATE EMPLOYEES
SET job_id = ‘SA_REP’, salary = 8000
WHERE first_name = ‘Rahul’ AND last_name = ‘Sharma’;
DELETE Statement: Removing Records
The DELETE statement removes rows from a table.
DELETE FROM table_name
WHERE condition;
Example: Deleting a specific employee
DELETE FROM EMPLOYEES
WHERE employee_id = 207;
Transaction Control Language (TCL): COMMIT, ROLLBACK, SAVEPOINT
The transaction includes DML operations (INSERT, UPDATE, DELETE), COMMIT, ROLLBACK, and SAVEPOINT. These transactions are managed by Oracle’s Transaction Control Language (TCL) statements.
- COMMIT: Permanently saves all pending database modifications. Changes cannot be undone with ROLLBACK once they have been committed.
- ROLLBACK: Restores the database to its initial state by undoing all pending changes made during the current transaction.
- SAVEPOINT: Makes a point in a transaction that you can go back to at a later time. For partial rollbacks, this is helpful.
Understanding Oracle SQL transactions is crucial for data integrity.
Related: MySQL Online Course.
Database Structure: DDL Operations
Database objects such as tables, indexes, and views are defined, modified, and deleted using Data Definition Language (DDL) statements.
CREATE TABLE: Defining Your Data Storage
To create a new table in the database, use the CREATE TABLE statement.
Syntax:
CREATE TABLE table_name (
column1 datatype [constraints],
column2 datatype [constraints],
…
[table_level_constraints]
);
Data Types in Oracle
For effective storage and data integrity, each column must have the appropriate data type selected. Typical Oracle data types consist of:
- VARCHAR2(size): Variable-length string (for example, VARCHAR2(100) for names).
- CHAR(size): A fixed-length string, which is less frequently used for plain text.
- NUMBER(precision, scale): Numerical information (for example, NUMBER(10,2) for a salary with two decimal places).
- DATE: Holds the time and date for up to a second.
- TIMESTAMP: Uses fractional seconds to store the date and time.
- CLOB (Character Large Object): For extremely large text data.
- Binary Large Objects, or BLOBs: They are used for binary data, such as audio or pictures.
Example: Creating a CUSTOMERS table
CREATE TABLE CUSTOMERS (
customer_id NUMBER(10) PRIMARY KEY,
first_name VARCHAR2(50) NOT NULL,
last_name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE,
phone_number VARCHAR2(20),
registration_date DATE DEFAULT SYSDATE
);
This specifies a table that has a unique constraint, not null constraints, and a primary key constraint. This is a real-world illustration of how to create tables in Oracle SQL.
ALTER TABLE: Modifying Table Structures
An existing table’s columns, constraints, and other attributes can be added, changed, or removed using the ALTER TABLE statement.
Example: Adding a new column
ALTER TABLE CUSTOMERS
ADD (address VARCHAR2(200));
Example: Modifying a column’s data type or size
ALTER TABLE CUSTOMERS
MODIFY (phone_number VARCHAR2(30));
DROP TABLE: Deleting Tables
A table, along with all of its data, constraints, and indexes, can be deleted from the database using the DROP TABLE statement.
Syntax:
DROP TABLE table_name;
Example:
DROP TABLE CUSTOMERS;
Joining Tables: Retrieving Data from Multiple Sources
To prevent data redundancy, databases are built with multiple tables. Based on a shared column, SQL joins merge rows from two or more tables.
INNER JOIN: Only rows with matching values in both tables are returned by an inner join. The most popular kind of join is this one.
SELECT e.first_name, e.last_name, d.department_name
FROM EMPLOYEES e
INNER JOIN DEPARTMENTS d
ON e.department_id = d.department_id;
LEFT JOIN (or LEFT OUTER JOIN): All rows from the left table and the corresponding rows from the right table are returned by it. NULL values are returned for the columns in the right table if there is no match.
SELECT e.first_name, e.last_name, d.department_name
FROM EMPLOYEES e
LEFT JOIN DEPARTMENTS d
ON e.department_id = d.department_id;
RIGHT JOIN (or RIGHT OUTER JOIN): All rows from the right table and the corresponding rows from the left table are returned by it. NULL values are returned for the columns in the left table if there isn’t a match.
SELECT e.first_name, e.last_name, d.department_name
FROM EMPLOYEES e
RIGHT JOIN DEPARTMENTS d
ON e.department_id = d.department_id;
FULL JOIN (or FULL OUTER JOIN): When there is a match in one of the tables, it returns every row. NULL values are returned for the table without a match if there isn’t one.
SELECT e.first_name, e.last_name, d.department_name
FROM EMPLOYEES e
FULL JOIN DEPARTMENTS d
ON e.department_id = d.department_id;
SELF JOIN: Connecting a table to itself. beneficial for data that is hierarchical (e.g., employees and their managers).
SELECT
e.first_name || ‘ ‘ || e.last_name AS employee_name,
m.first_name || ‘ ‘ || m.last_name AS manager_name
FROM EMPLOYEES e
JOIN EMPLOYEES m
ON e.manager_id = m.employee_id;
Complex data retrieval from relational databases requires an understanding of Oracle SQL join types.
Set Operators: Combining Query Results
The output of two or more SELECT statements is combined by set operators. There must be an equal number of columns in each query, and the corresponding columns must contain compatible data types.
- UNION: It removes duplicate rows and combines the output of two SELECT statements.
- UNION ALL: It adds all duplicate rows to the output of two SELECT statements.
- INTERSECT: Only the rows shared by the two SELECT statements are returned by it.
- MINUS: Rows from the first SELECT statement that are absent from the second SELECT statement are returned by it.
Related: Oracle SQL Tutorial for Beginners.
Basics of PL SQL in Oracle
Let’s explore PL/SQL, Oracle’s procedural extension that adds programming logic to your database interactions, now that you have a firm understanding of SQL.
What is PL/SQL?
As previously stated, PL/SQL blends the procedural features of a programming language with the data manipulation capabilities of SQL. Within the Oracle database, it enables you to develop logic for handling errors, processing data, and creating intricate applications.
Block Structure in PL/SQL: Anonymous Blocks
This is the basic building block of PL/SQL. They can be named (stored as procedures, functions, packages, or triggers) or anonymous (not stored in the database). Anonymous blocks work well for one-time tasks and testing.
There are three sections in every PL/SQL block, but only the BEGIN…END section is required:
- DECLARE (Optional): Declaring variables, constants, cursors, and other elements is done with the optional DECLARE function.
- BEGIN (Mandatory): The executable SQL and PL/SQL statements are contained in the BEGIN (Mandatory) section.
- EXCEPTION (Optional): Addresses issues that arise when the BEGIN section is being executed.
Basic Anonymous Block Example: “Hello World”
DECLARE
message VARCHAR2(100) := ‘Hello, Oracle PL/SQL World!’;
BEGIN
DBMS_OUTPUT.PUT_LINE(message);
END;
/
Variables and Constants in PL/SQL
Variables: Within a PL/SQL block, variables are used to temporarily store data. In the DECLARE section, you declare them.
- Syntax: variable_name DATATYPE [:= initial_value];
- Example: v_employee_name VARCHAR2(100);
- The %TYPE attribute is a very helpful feature that ensures data type compatibility even if the type of the table column changes by declaring a variable with the same data type as the column.
- Example: v_emp_salary EMPLOYEES.SALARY%TYPE;
Constants are variables that, once declared, cannot have their values altered.
- Syntax: constant_name CONSTANT DATATYPE := value;
- Example: c_tax_rate CONSTANT NUMBER := 0.15;
Declaring and Using Variables
DECLARE
v_employee_id EMPLOYEES.EMPLOYEE_ID%TYPE := 100;
v_employee_salary EMPLOYEES.SALARY%TYPE;
v_bonus_amount NUMBER(10,2);
c_bonus_percentage CONSTANT NUMBER := 0.10;
BEGIN
SELECT salary
INTO v_employee_salary — Store the result of the query into the variable
FROM EMPLOYEES
WHERE employee_id = v_employee_id;
v_bonus_amount := v_employee_salary * c_bonus_percentage;
DBMS_OUTPUT.PUT_LINE(‘Employee ID: ‘ || v_employee_id);
DBMS_OUTPUT.PUT_LINE(‘Salary: ‘ || v_employee_salary);
DBMS_OUTPUT.PUT_LINE(‘Bonus Amount: ‘ || v_bonus_amount);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Employee not found!’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An unexpected error occurred: ‘ || SQLERRM);
END;
/
This example demonstrates basic error handling and PL/SQL variable declaration.
Explore: Oracle Tutorial for Beginners.
Control Structures: IF-THEN-ELSE, CASE Statements
You can use control structures to run different sections of your code in response to certain conditions.
IF-THEN-ELSIF-ELSE:
Example:
DECLARE
p_salary NUMBER := 7000;
p_grade VARCHAR2(10);
BEGIN
IF p_salary >= 10000 THEN
p_grade := ‘A’;
ELSIF p_salary >= 5000 THEN
p_grade := ‘B’;
ELSE
p_grade := ‘C’;
END IF;
DBMS_OUTPUT.PUT_LINE(‘Salary Grade: ‘ || p_grade);
END;
/
Looping Constructs: LOOP, WHILE LOOP, FOR LOOP
A block of code can be repeatedly run using loops.
LOOP (Basic Loop): The Basic Loop (LOOP) repeatedly runs statements until an EXIT condition is satisfied.
DECLARE
v_counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(‘Count: ‘ || v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 5;
END LOOP;
END;
/
WHILE LOOP: It continues as long as a specified condition is true.
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 5 LOOP
DBMS_OUTPUT.PUT_LINE(‘Count: ‘ || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
/
FOR LOOP (Numeric FOR Loop): It iterates a specific number of times.
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(‘Iteration: ‘ || i);
END LOOP;
END;
/
Working with Cursors in PL/SQL
Data is processed by SQL statements one set at a time. But occasionally, you have to process data one row at a time. Cursors are useful in this situation. A cursor is a pointer to the memory location where SQL statements are processed, known as the context area.
Understanding Cursors: Implicit vs. Explicit
- Implicit Cursors: Oracle automatically generates an implicit cursor for each SQL statement (DML, DQL) that is run in PL/SQL. They are not specifically declared or under your control. Their status can be checked using attributes such as SQL%ROWCOUNT (number of rows affected).
- Explicit Cursors: For SELECT statements that return more than one row, you define and manage explicit cursors. This enables you to handle every row separately.
Explicit Cursor Steps
- Declaration: It defines the cursor in the DECLARE section.
- Opening: It executes the query associated with the cursor.
- Fetching: It retrieves rows one by one into PL/SQL variables.
- Closing: It releases the resources held by the cursor.
Example: Using an Explicit Cursor
DECLARE
CURSOR c_employee_salaries IS
SELECT first_name, last_name, salary
FROM EMPLOYEES
WHERE department_id = 90;
v_first_name EMPLOYEES.FIRST_NAME%TYPE;
v_last_name EMPLOYEES.LAST_NAME%TYPE;
v_salary EMPLOYEES.SALARY%TYPE;
BEGIN
OPEN c_employee_salaries;
LOOP
FETCH c_employee_salaries INTO v_first_name, v_last_name, v_salary;
EXIT WHEN c_employee_salaries%NOTFOUND; — Exit loop when no more rows
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ || v_first_name || ‘ ‘ || v_last_name || ‘, Salary: ‘ || v_salary);
END LOOP;
CLOSE c_employee_salaries;
END;
/
Upgrade: Oracle DBA Course in Chennai.
CURSOR FOR LOOP: Simplified Cursor Processing
A more sophisticated and frequently chosen method for handling explicit cursors is CURSOR FOR LOOP. A record variable is automatically declared, the cursor is opened, rows are retrieved, and the cursor is closed.
Example: Using CURSOR FOR LOOP
BEGIN
FOR emp_rec IN (SELECT first_name, last_name, salary FROM EMPLOYEES WHERE department_id = 90) LOOP
DBMS_OUTPUT.PUT_LINE(‘Name: ‘ || emp_rec.first_name || ‘ ‘ || emp_rec.last_name || ‘, Salary: ‘ || emp_rec.salary);
END LOOP;
END;
/
PL/SQL Stored Programs: Procedures and Functions
PL/SQL blocks that are kept in the database are the names given to procedures and functions. They encourage improved performance, modularity, and reusability.
Creating Stored Procedures
An action is carried out by a procedure. It does not immediately return a value, but it can take input parameters and generate output parameters.
Syntax:
CREATE [OR REPLACE] PROCEDURE procedure_name (
parameter1 [IN | OUT | IN OUT] datatype,
parameter2 [IN | OUT | IN OUT] datatype,
…
)
IS
— Declarations
BEGIN
— Executable statements
EXCEPTION
— Exception handling
END;
/
Difference Between Procedures and Functions
Procedures:
- Perform an action.
- Do not return a value directly (can have OUT parameters).
- Cannot be used directly in SQL statements.
Functions:
- Compute and return a single value.
- Can be used in SQL expressions, WHERE clauses, etc.
- Typically used for calculations or retrieving a single piece of data.
Error Handling in PL/SQL: Exceptions
Errors or unusual circumstances that arise while a PL/SQL block is being executed are known as exceptions. Your code is more reliable and easier to use when exceptions are handled correctly.
You define handlers for various exception types in the EXCEPTION section of a PL/SQL block.
Predefined Exceptions
Oracle offers numerous pre-established exceptions for frequent mistakes. Here are a few that are commonly used:
- When a SELECT INTO statement yields no rows, the error NO_DATA_FOUND appears.
- When a SELECT INTO statement yields more than one row, the error TOO_MANY_ROWS occurs.
- When an INSERT or UPDATE tries to store duplicate values in a column that is subject to a unique index, the result is DUP_VAL_ON_INDEX.
- When a division by zero takes place, the result is ZERO_DIVIDE.
- OTHERS: A general handler for any exception that isn’t specifically caught. WHEN OTHERS should always be the final exception handler.
Example: Handling NO_DATA_FOUND and OTHERS
DECLARE
v_employee_salary EMPLOYEES.SALARY%TYPE;
p_employee_id NUMBER := 999; — An ID that likely doesn’t exist
BEGIN
SELECT salary
INTO v_employee_salary
FROM EMPLOYEES
WHERE employee_id = p_employee_id;
DBMS_OUTPUT.PUT_LINE(‘Salary: ‘ || v_employee_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘Error: Employee ID ‘ || p_employee_id || ‘ not found.’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An unexpected error occurred: ‘ || SQLERRM);
— SQLCODE returns the Oracle error number
— SQLERRM returns the error message
END;
/
Explore: All Software Training Courses.
Conclusion
This tutorial offers a good foundation for learning how to develop Oracle databases. Keep in mind that practice is essential. Continue writing PL/SQL blocks, developing small applications, and experimenting with various queries. These ideas will become more apparent the more you use them.
Consider taking a specialized Oracle PL/SQL course in Chennai to increase your knowledge and speed up your development. These classes provide practical labs, knowledgeable instruction, and a well-organized syllabus that covers complex subjects and real-world situations.