ORACLE PL/SQL: HHANDLING DATA IN PL/SQL BLOCK

Use SQL queries in a PL/SQL executable block

• It refers to the ability to include SQL statements within a PL/SQL block of code.

• PL/SQL allows for the execution of SQL statements along with the ability to incorporate
programming constructs such as variables, loops, conditionals, and exception handling.

• The key point to note is that the SQL query is embedded within the PL/SQL block using the
appropriate syntax. The result of the SQL query can be stored in PL/SQL variables, used in
conditional statements, or even used to perform DML (Data Manipulation Language)
operations.

• For example the PL/SQL block includes a SQL query to retrieve a value from a table based on
a specific condition. The result of the query is stored in the my_variable PL/SQL variable,
which can then be used within the block for further processing or display.

DECLARE

my_variable VARCHAR2(50);

BEGIN

-- SQL query to retrieve data

SELECT column_name INTO my_variable

FROM table_name

WHERE condition;

-- Do something with retrieved data

DBMS_OUTPUT.PUT_LINE('Value retrieved from table: ' || my_variable);

END;

Manipulate data with DML statements in PL/SQL

Data Manipulation Language:

• Collection of instructions

• Used to alter database-stored data.

DML statements, as examples, include:

• INSERT − Used to add new data to a table.

• UPDATE − Used to modify existing data in a table.

• DELETE − Used to remove data from a table.

• SELECT − Used to retrieve data from one or more tables in a database.

Example:
 • Let's suppose we have a "Customers" table with columns: "ID", "Name", and "Age".

• Inserting data: We can use an INSERT statement to add a new customer into the table.

BEGIN

INSERT INTO Customers (ID, Name, Age)

VALUES (1, 'John Doe', 25);

COMMIT;

END;

Updating data: If we need to change the age of a customer, we can use an UPDATE statement.

BEGIN

UPDATE Customers

SET Age = 30

WHERE Name = 'John Doe';

COMMIT;

END;

Make use of the INTO clause to hold the values returned by a SQL statement

• The INTO clause is used to store the result of a SELECT query into a set of variables or a single
variable.

• It is a way to fetch a single row or a single column value from a SQL query and assign it to a
variable.

• Here is an example to better understand the usage of the INTO clause:

• Let's say we have a table called "Employees" with columns "employee_id" and
"employee_name". Suppose we want to retrieve the name of an employee based on their
ID:

DECLARE

v_employee_name Employees.employee_name%TYPE; -- declare a variable to hold the employee

BEGIN

SELECT employee_name INTO v_employee_name -- use the INTO clause to store the employee name

FROM Employees

WHERE employee_id = 1; -- assuming the employee ID is 1

DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); -- printing the employee name

END;
Embedding DML statements in PL/SQL

• Including data manipulation statements such as INSERT, UPDATE, DELETE, or SELECT within a
PL/SQL block or program.

• This allows the PL/SQL program to manipulate or interact with the data in a database.

• For example:

The PL/SQL block first retrieves the salary of an employee with the last name 'Smith' from the
employees table using a SELECT statement. Then, it updates the salary of that employee using an
UPDATE statement. Finally, it prints the updated salary using the DBMS_OUTPUT.PUT_LINE function.

DECLARE

lname employees.last_name%TYPE := 'Smith';

salary employees.salary%TYPE;

BEGIN

-- Retrieve the salary for an employee

SELECT salary INTO salary

FROM employees

WHERE last_name = lname;

-- Update the salary for the employee

UPDATE employees

SET salary = salary * 1.1

WHERE last_name = lname;

-- Print the updated salary

DBMS_OUTPUT.PUT_LINE('The new salary for ' || lname || ' is ' || salary);

END;

Use record variable

• Record variable is used to hold a row of data from a table or a query result.

• Here is an example of how to declare and use a record variable in PL/SQL:

DECLARE

TYPE employee_record IS RECORD (

employee_id NUMBER,

first_name VARCHAR2(100),

last_name VARCHAR2(100),
 hire_date DATE

);

emp employee_record;

BEGIN

-- Assign values to the record variable

emp.employee_id := 123;

emp.first_name := 'John';

emp.last_name := 'Doe';

emp.hire_date := SYSDATE;

-- Display the values of the record variable

DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.employee_id);

DBMS_OUTPUT.PUT_LINE('First Name: ' || emp.first_name);

DBMS_OUTPUT.PUT_LINE('Last Name: ' || emp.last_name);

DBMS_OUTPUT.PUT_LINE('Hire Date: ' || emp.hire_date);

-- Use the record variable in a SQL statement

INSERT INTO employees (employee_id, first_name, last_name, hire_date)

VALUES (emp.employee_id, emp.first_name, emp.last_name, emp.hire_date);

COMMIT;

END;

When using record variable:

• First declare a record type called "employee_record" that defines the structure of the record
variable "emp". It has four fields: employee_id (NUMBER), first_name (VARCHAR2),
last_name (VARCHAR2), and hire_date (DATE).