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.
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.
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.
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.
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.
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.