Visual Representation
Step 4: Declaration of variables within a PL/SQL Block
You must have to declare the PL/SQL variables in the declaration section as global variables. After the declaration, the variable’s name is associated with storage location, and variable’s value is allocated by PL/SQL in memory.
Syntax:
DECLARE
variable_name datatype;
-- additional variable's declaration
BEGIN
-- PL/SQL statements here
END;
/
Step 5: Working with Cursor
A PL/SQL cursor is a pointer that points to the result set of an SQL query against database tables. The following picture describes steps that you need to follow when you work with PL/SQL Cursor :
Syntax:
CURSOR cursor_name [([parameter_1 [, parameter_2...])]
[RETURN return_specification]
IS sql_select_statements
[FOR UPDATE [OF [Column-_list]];
Step 6: Retrieving the Data from the SQL query using the PL/SQL loop
In this step, we use cursor to retrieve and process data that is returned by SQL queries. Since the cursor provides mechanism for iterating over the rows of a result set and enables us to perform operations on each row individually.
Step 7: End of PL/SQL block
The end of PL/SQL block is marked by ‘END’ statement. This represents the conclusion of the block’s executable section.
Syntax:
DECLARE
-- variable declaration
BEGIN
-- PL/SQL Statements
-- End of executive section
END;
/
How to Retrieve Data from Multiple Tables in PL/SQL
PL/SQL is “Procedural Language extensions to the Structured Query Language”. SQL is a popular language for both querying and updating data in relational database management systems (RDBMS). PL/SQL adds many procedural constructs to SQL language to overcome some limitations of SQL. In addition, PL/SQL provides a more comprehensive programming language solution for building mission-critical applications on Oracle Databases.
Retrieving data from multiple tables in PL/SQL mainly involves using SQL Joins, which allows to combination of rows from one or more tables based on the related column between them. Before going with the query of retrieving data from multiple tables make sure you have sufficient or basic knowledge about SQL joins.