Lab 2.1 PL/SQL In Client-Server Architecture Lab Objectives After this Lab, you will be able to: Many Oracle applications are built using client-server architecture. The Oracle database resides on the server. The program that makes requests against this database resides on the client machine. This program can be written in C, Java, or PL/SQL. Because PL/SQL is just like any other programming language, it has syntax and rules that determine how programming statements work together. It is important for you to realize that PL/SQL is not a stand-alone programming language. PL/SQL is a part of the Oracle RDBMS, and it can reside in two environments, the client and the server. As a result, it is very easy to move PL/SQL modules between server-side and client-side applications. In both environments, any PL/SQL block or subroutine is processed by the PL/SQL engine, which is a special component of many Oracle products. Some of these products are Oracle server, Oracle Forms, and Oracle Reports. The PL/SQL engine processes and executes any PL/SQL statements and sends any SQL statements to the SQL statement processor. The SQL statement processor is always located on the Oracle server. Figure 2.1 illustrates the PL/SQL engine residing on the Oracle server. Figure 2.1. The PL/SQL Engine and Oracle Server  When the PL/SQL engine is located on the server, the whole PL/SQL block is passed to the PL/SQL engine on the Oracle server. The PL/SQL engine processes the block according to Figure 2.1. When the PL/SQL engine is located on the client, as it is in Oracle Developer Tools, the PL/SQL processing is done on the client side. All SQL statements that are embedded within the PL/SQL block are sent to the Oracle server for further processing. When PL/SQL block contains no SQL statements, the entire block is executed on the client side. Using PL/SQL has several advantages. For example, when you issue a SELECT statement in SQL*Plus against the STUDENT table, it retrieves a list of students. The SELECT statement you issued at the client computer is sent to the database server to be executed. The results of this execution are then sent back to the client. As a result, you will see rows displayed on your client machine. Now, assume that you need to issue multiple SELECT statements. Each SELECT statement is a request against the database and is sent to the Oracle server. The results of each SELECT statement are sent back to the client. Each time a SELECT statement is executed, network traffic is generated. Hence, multiple SELECT statements will result in multiple round trip transmissions, adding significantly to the network traffic. When these SELECT statements are combined into a PL/SQL program, they are sent to the server as a single unit. The SELECT statements in this PL/SQL program are executed at the server. The server sends the results of these SELECT statements back to the client, also as a single unit. Therefore, a PL/SQL program encompassing multiple SELECT statements can be executed at the server and have the results returned to the client in one round trip. This obviously is a more efficient process than having each SELECT statement executed independently. This model is illustrated in Figure 2.2. Figure 2.2. PL/SQL in Client-Server Architecture  Figure 2.2 compares two applications. The first application uses four independent SQL statements that generate eight trips on the network. The second application combines SQL statements into a single PL/SQL block. This PL/SQL block is then sent to the PL/SQL engine. The engine sends SQL statements to the SQL statement processor and checks the syntax of PL/SQL statements. As you can see, only two trips are generated on the network. In addition, applications written in PL/SQL are portable. They can run in any environment that Oracle can run in. Since PL/SQL does not change from one environment to the next, different tools can use a PL/SQL script. PL/SQL Block Structure A block is the most basic unit in PL/SQL. All PL/SQL programs are combined into blocks. These blocks can also be nested one within the other. Usually, PL/SQL blocks combine statements that represent a single logical task. Therefore, different tasks within a single program can be separated into blocks. As a result, it is easier to understand and maintain the logic of the program. PL/SQL blocks can be divided into two groups: named and anonymous. Named PL/SQL blocks are used when creating subroutines. These subroutines are procedures, functions, and packages. The subroutines then can be stored in the database and referenced by their names later. In addition, subroutines such as procedures and functions can be defined within the anonymous PL/SQL block. These subroutines exist as long as this block is executing and cannot be referenced outside the block. In other words, subroutines defined in one PL/SQL block cannot be called by another PL/SQL block or referenced by their names later. Subroutines are discussed in Chapters 12 through 14. Anonymous PL/SQL blocks, as you have probably guessed, do not have names. As a result, they cannot be stored in the database and referenced later. PL/SQL blocks contain three sections: declaration section, executable section, and exception-handling section. The executable section is the only mandatory section of the block. Both the declaration and exception-handling sections are optional. As a result, a PL/SQL block has the following structure: DECLARE Declaration statements BEGIN Executable statements EXCEPTION Exception-handling statements END; 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. Executable Section 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. Chapter 4 contains a detailed explanation of the SELECT INTO statement. 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. Exception-Handling Section 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. Chapters 7, 10, and 11 contain more detailed explanations of the exception-handling section. 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; How PL/SQL Gets Executed Every time an anonymous PL/SQL block is executed, the code is sent to the PL/SQL engine on the server, where it is compiled. A named PL/SQL block is compiled only at the time of its creation, or if it has been changed. The compilation process includes syntax checking, binding, and p-code generation. Syntax checking involves checking PL/SQL code for syntax or compilation errors. Syntax error occurs when a statement does not exactly correspond to the syntax of the programming language. Errors such as a misspelled keyword, a missing semicolon at the end of the statement, or an undeclared variable are examples of syntax errors. Once the programmer corrects syntax errors, the compiler can assign a storage address to program variables that are used to hold data for Oracle. This process is called binding. It allows Oracle to reference storage addresses when the program is run. At the same time, the compiler checks references to the stored objects such as table names or column names in the SELECT statement, or a call to a named PL/SQL block. 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. Once the process of compilation has completed successfully, the status of a named PL/SQL block is set to VALID, and it is also stored in the database. If the compilation process was not successful, the status of a named PL/SQL block is set to INVALID.  | It is important to remember that successful compilation of the named PL/SQL block does not guarantee successful execution of this block in the future. If, at the time of execution, any one of the stored objects referenced by the block is not present in the database or not accessible to the block, execution will fail. At such time, the status of the named PL/SQL block will be changed to INVALID. |
Lab 2.1 Exercises 2.1.1 Use PL/SQL Anonymous Blocks | a) | Why it is more efficient to combine SQL statements into PL/SQL blocks? | | b) | What are the differences between named and anonymous PL/SQL blocks? |
For the next two questions, consider the following code: 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; | | The SELECT statement in the preceding example is supported by multiple versions of Oracle. However, Oracle 9i also supports the new ANSI 1999 SQL standard, and the SELECT statement can be modified as follows according to this new standard: |
SELECT i.first_name||' '||i.last_name, COUNT(*) INTO v_name, v_total FROM instructor i JOIN section s ON (i.instructor_id = s.instructor_id) WHERE i.instructor_id = 102 GROUP BY i.first_name||' '||i.last_name; Notice, the FROM clause contains only one table, INSTRUCTOR. Following the FROM clause is the JOIN clause that lists the second table, SECTION. Next, the ON clause lists the join condition between the two tables i.instructor_id = s.instructor_id which has been moved from the WHERE clause. | | You will find detailed explanations and examples of the statements using new ANSI 1999 SQL standard in Appendix E and in the Oracle help.Throughout this book we will try to provide you with examples illustrating both standards; however, our main focus will remain on PL/SQL features rather than SQL . |
| c) | Based on the example just provided, describe the structure of a PL/SQL block. | | d) | What happens when runtime error NO_DATA_FOUND occurs in the PL/SQL block just shown? |
2.1.2 Understand How PL/SQL Gets Executed | a) | What happens when an anonymous PL/SQL block is executed? | | b) | What steps are included in the compilation process of a PL/SQL block? | | c) | What is a syntax error? | | d) | How does a syntax error differ from a runtime error? |
Lab 2.1 Exercise Answers 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. |
Lab 2.1 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 2.1. | 1) | SQL statements combined into PL/SQL blocks cause an increase in the network traffic. _____ True _____ False
| | 2) | Which of the following sections is mandatory for a PL/SQL block? _____ Exception-handling section _____ Executable section _____ Declaration section
| | 3) | The exception-handling section in a PL/SQL block is used to _____ handle compilation errors. _____ handle runtime errors. _____ handle both compilation and runtime errors.
| | 4) | A PL/SQL compiler can detect _____ syntax errors. _____ runtime errors. _____ both compilation and runtime errors.
| | 5) | P-code is stored in the database for _____ anonymous PL/SQL blocks. _____ named PL/SQL blocks.
|
|
