Lab 2.1 Exercise Answers | Oracle PL[s]SQL by Example

This section gives you some suggested answers to the questions in Lab 2.1, with discussion related to how those answers resulted. The most important thing to realize is whether your answer works. You should figure out the implications of the answers here and what the effects are from any different answers you may come up with.

2.1.1 Answers

a)	Why it is more efficient to combine SQL statements into PL/SQL blocks?
A1:	Answer: It is more efficient to use SQL statements within PL/SQL blocks because network traffic can be decreased significantly, and an application becomes more efficient as well.

When an SQL statement is issued on the client computer, the request is made to the database on the server, and the result set is sent back to the client. As a result, a single SQL statement causes two trips on the network. If multiple SELECT statements are issued, the network traffic can increase significantly very quickly. For example, four SELECT statements cause eight network trips. If these statements are part of the PL/SQL block, there are still only two network trips made, as in the case of a single SELECT statement.

b)	What are the differences between named and anonymous PL/SQL blocks?
A2:	Answer: Named PL/SQL blocks can be stored in the database and referenced later by their names. Since anonymous PL/SQL blocks do not have names , they cannot be stored in the database and referenced later.

c)	Based on the example just provided, describe the structure of a PL/SQL block.
A1:	Answer: PL/SQL blocks contain three sections: declaration section , executable section , and 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. The declaration section

  DECLARE   v_name VARCHAR2(50);   v_total NUMBER;

contains definitions of two variables, v_name and v_total .

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

  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;

contains a SELECT INTO statement that assigns values to the variables v_name and v_total , and a DBMS_OUTPUT.PUT_LINE statement that displays their 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. The following exception-handling section

  EXCEPTION   WHEN NO_DATA_FOUND THEN   DBMS_OUTPUT.PUT_LINE ('There is no such instructor');

contains the DBMS_OUTPUT.PUT_LINE statement that is executed when runtime error NO_DATA_FOUND occurs.

d)	What happens when runtime error NO_DATA_FOUND occurs in the PL/SQL block just shown?
A2:	Answer: When a runtime error occurs in the PL/SQL block, control is passed to the exception-handling section of the block. The exception NO_DATA_FOUND is evaluated then with the help of the WHEN clause.

When the SELECT INTO statement

  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;

does not return any rows, control of execution is passed to the exception-handling section of the block. Next , the DBMS_OUTPUT.PUT_LINE statement associated with the exception NO_DATA_FOUND is executed. As a result, the message "There is no such instructor" is displayed on the screen.

2.1.2 Answers

a)	What happens when an anonymous PL/SQL block is executed?
A1:	Answer: When an anonymous PL/SQL block is executed, the code is sent to the PL/SQL engine on the server, where it is compiled.
b)	What steps are included in the compilation process of a PL/SQL block?
A2:	Answer: The compilation process includes syntax checking, binding, and p-code generation.

Syntax checking involves checking PL/SQL code for compilation errors. Once syntax errors have been corrected, a storage address is assigned to the variables that are used to hold data for Oracle. This process is called binding. Next, p-code is generated for the PL/SQL block. P-code is a list of instructions to the PL/SQL engine. For named blocks, p-code is stored in the database, and it is used the next time the program is executed.

c)	What is a syntax error?
A3:	Answer: A syntax error occurs when a statement does not correspond to the syntax rules of the programming language. An undefined variable or a misplaced keyword are examples of syntax error.
d)	How does a syntax error differ from a runtime error?
A4:	Answer: A syntax error can be detected by the PL/SQL compiler. A runtime error occurs while the program is running and cannot be detected by the PL/SQL compiler.

A misspelled keyword is an example of the syntax error. For example, the script

  BEIN   DBMS_OUTPUT.PUT_LINE ('This is a test');   END;

contains a syntax error. You should try to find this error.

A SELECT INTO statement returning no rows is an example of a runtime error. This error can be handled with the help of the exception-handling section of the PL/SQL block.