Nested Blocks Database Management System

Nested Blocks Database Management System

Nested Blocks Database Management System : One of the advantages that PL/SQL has over SOL is the ability to nest statements. You can nest blocks wherever an executable statement is allowed, thus making the nested block a statement. Therefore, you can breakdown the executable part of a block into smallerblocks. The exception section can also contain nested blocks.

Variable Scope

References to an identifier are resolved according to its scope and visibility. The scope of an identifier is that region of a program unit (block, subprogram, or package) from which you can reference the identifier. An identifier is visible only in the regions from which you can reference the identifier using an unqualified name. Identifiers declared in a PL/SQL block are considered local to that block and globalto all its subblocks. If a global identifier is redeclared in a Subblock, both identifiers remain in scope. Within the Subblock, however, only the local identifier is visible because you must use a qualified name to reference the global identifier.

Although you cannot declare an identifiertwice in the same block, you can declare the same identifier in two different blocks. The two items represented by the identifier are distinct, and any change in one does not affect the other. However, a block cannot reference identifiers declared in other blocks at the same level because those identifiers are neither local nor global to the block.

Nested Blocks and Variable Scope

Nested Blocks Database Management System

Visibility

An identifier is visible only in the regions from which you can reference the identifier using an undualified name.


identifier Scope

An identifier is visible in the block in which it is declared and in all nested subblocks, procedures, and functions. If the block does not find the identifier declared locally, it looks up to the declarative section of the enclosing (or parent) blocks. The block neverlooks down to enclosed (or child) blocks or sideways to sibling blocks.
Scope applies to all declared objects, including variables, cursors, user-defined exceptions, and constants.


Qualify am identifier

  • The qualifier can be the label of an enclosing block.
  • Qualify an identifier by using the block label prefix.

<outer>
DECLARE
birthdate DATE;
BEGIN
DECARE
birthdate DATE;
BEGIN
outer.birthdate:= TODATE(’03-AUG-1976, “DD-MON-YYYY’); END; END;


Qualify an Identifier

Qualify an identifier by using the block label prefix. In the example on the slide, the outer block is labelled outer. In the inner block, a variable with the same name, birthdate, as the variable in the outer block is declared. To reference the variable, birthdate, from the outer block in the inner block, prefix the variable by the block name, outer birthdate.

SELECT Statements in PL/SQL
Retrieve data from the database with a SELECT statement.
Syntax:
SELECTselect list
INTO variable name, variable name).
I record name}
FROM table
(WHERE condition);
Retrieving Data Using PL/SQL

Use the SELECT statement to retrieve data from the database. In the syntax:
select list is a list of at least one column and can include SOL expressions, row functions, or group functions.
variable_name is the scalar variable that holds the retrieved value.
record_name is the PL/SQL RECORD that holds the retrieved values.
table specifies the database table name.

Condition is composed of column names, expressions, constants, and Comparison operators,including PL/SQL variables and constants.
Example:
DECLARE
war deptno NUMBER(4); war location_id NUMBER(4);
BEGIN SELECT department id, locationid INTO war deptno, war location id FROM departments WHERE department name = ‘Sales;
END, / Retrieving Data in PL/SQL
Retrieve the hire date and the sal for the specified emp.
Example:
DECLARE var_hire_date emphire_date%TYPE, war salemp.sal%TYPE;
BEGIN
SELECT hire_date, sal INTO war hire date, varsal FROM emp
WHERE emp_id=100;
END; /

Retrieving Data in PL/SQL

In the example on the slide, the variables war hire date and varsal are declared in the DECLARESection of the PL/SQL block. In the executable section, the values of the columns HIRE DATE and SAL for the emp with the EMPID 100 is retrieved from the EMP table and stored in the war hire date and war salvariables, respectively. Observe how the INTO clause, along with the SELECT statement, retrieves the database column values into the PL/SQL variables.

Naming Conventions

DECLARE
hire_date emphire_date%TYPE;
sysdatehire_date%TYPE;
emp_id emp…emp_id%TYPE := 176;
BEGIN
SELECThire_date, sysdate
INTO hire date, sysdate
FROM emp
WHERE emp_id=emp id;
END;
/

Naming Conventions

In potentially ambiguous SQL statements, the names of database columns take precedence over the names of local variables. The example shown on the slide is defined as follows: Retrieve the hire date and today’s date from the EMP table for emp ID 176. This example raises an unhandled run-time exception because in the WHERE clause, the PL/SQL variable names are the same as that of the database column names in the EMP table.