-->
Declaration Section
-->
The declaration section is the first section of the PL/SQL block. It contains definitions of PL/SQL identifiers such as variables, constants, cursors, and so on. PL/SQL identifiers are covered in detail throughout this book.
FOR EXAMPLE
DECLARE
v_first_name VARCHAR2(35);
v_last_name VARCHAR2(35);
v_counter NUMBER := 0;
The example given shows a declaration section of an anonymous PL/SQL block. It begins with the keyword DECLARE and contains two variable declarations and one constant declaration. The names of the variables, v_first_name and v_last_name, are followed by their datatypes and sizes. The name of the constant, v_counter, is followed by its datatype and a value assigned to it. Notice that a semicolon terminates each declaration.
The executable section is the next section of the PL/SQL block. This section contains executable statements that allow you to manipulate the variables that have been declared in the declaration section.
FOR EXAMPLE
BEGIN
SELECT first_name, last_name
INTO v_first_name, v_last_name
FROM student
WHERE student_id = 123;
DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||
' '||v_last_name);
END;
The example given shows the executable section of the PL/SQL block. It begins with the keyword BEGIN and contains a SELECT INTO statement from the STUDENT table. The first and last names for student ID 123 are selected into two variables: v_first_name and v_last_name.. Then the values of the variables, v_first_name and v_last_name, are displayed on the screen with the help of DBMS_OUTPUT.PUT_LINE statement. This statement will be covered later in this chapter in greater detail. The end of the executable section of this block is marked by the keyword END. The executable section of any PL/SQL block always begins with the keyword BEGIN and ends with the keyword END.
The exception-handling section is the last section of the PL/SQL block. This section contains statements that are executed when a runtime error occurs within the block. Runtime errors occur while the program is running and cannot be detected by the PL/SQL compiler. Once a runtime error occurs, control is passed to the exception-handling section of the block. The error is then evaluated, and a specific exception is raised or executed. This is best illustrated by the following example.
FOR EXAMPLE
BEGIN
SELECT first_name, last_name
INTO v_first_name, v_last_name
FROM student
WHERE student_id = 123;
DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||
' '||v_last_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no student with '||
'student id 123');
END;
This shows the exception-handling section of the PL/SQL block. It begins with the keyword EXCEPTION. The WHEN clause evaluates which exception must be raised. In this example, there is only one exception, called NO_DATA_FOUND, and it is raised when the SELECT statement does not return any rows. If there is no record for student ID 123 in the STUDENT table, control is passed to the exception-handling section and the DBMS_OUTPUT.PUT_LINE statement is executed
You have seen examples of the declaration section, executable section, and exception-handling section. Consider combining these examples into a single PL/SQL block.
FOR EXAMPLE
DECLARE
v_first_name VARCHAR2(35);
v_last_name VARCHAR2(35);
BEGIN
SELECT first_name, last_name
INTO v_first_name, v_last_name
FROM student
WHERE student_id = 123;
DBMS_OUTPUT.PUT_LINE ('Student name: '||v_first_name||
' '||v_last_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no student with '||
'student id 123'); End;
No comments:
Post a Comment