PL/SQL Anonymous Block

Example: 1

DECLARE

v_name  VARCHAR2(50);

v_total NUMBER; BEGIN

SELECT i.first_name||’ ‘||i.last_name, COUNT(*) INTO v_name, v_total

FROM instructor i, section s

WHERE i.instructor_id = s.instructor_id

AND i.instructor_id = 102

GROUP BY i.first_name||’ ‘||i.last_name;

 

DBMS_OUTPUT.PUT_LINE

(‘Instructor ‘||v_name||’ teaches ‘||v_total||’ courses’);

 

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE (‘There is no such instructor’);

END;

 

Based on the code example provided, describe the structure of a PL/SQL block.

ANSWER: PL/SQL blocks contain three sections: the declaration section, the executable section, and the exception-handling section. The executable section is the only mandatory section of the PL/SQL block.

The declaration section holds definitions of PL/SQL identifiers such as variables, constants, and cursors. The declaration section starts with the keyword DECLARE:

DECLARE

v_name  VARCHAR2(50);

v_total NUMBER;

 

It contains definitions of two variables, v_name and v_total.

The executable section, shown next in bold, holds executable statements. It starts with the keyword BEGIN and ends with the keyword END:

BEGIN

SELECT i.first_name||’ ‘||i.last_name, COUNT(*) INTO v_name, v_total

FROM instructor i, section s

WHERE i.instructor_id = s.instructor_id

AND i.instructor_id = 102

GROUP BY i.first_name||’ ‘||i.last_name;

 

DBMS_OUTPUT.PUT_LINE

(‘Instructor ‘||v_name||’ teaches ‘||v_total||’ courses’);

 

EXCEPTION

 

 

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE (‘There is no such instructor’);

END;

 

It contains a SELECT INTO statement that assigns values to the variables v_name and v_total. It also contains a DBMS_OUTPUT.PUT_LINE statement that displays the variables’ values on the screen.

The exception-handling section of the PL/SQL block contains statements that are executed only if runtime errors occur in the PL/SQL block:

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE (‘There is no such instructor’);

 

It also contains the DBMS_OUTPUT.PUT_LINE statement that is executed when the runtime error

NO_DATA_FOUND occurs.