Lab 2.1 PLSQL in Client-Server Architecture


Lab 2.1 PL/SQL in Client-Server Architecture

Lab Objectives

After this Lab, you will be able to:

Use PL/SQL Anonymous Blocks

Understand How PL/SQL Gets Executed


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

graphics/02fig01.gif

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

graphics/02fig02.gif

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.

graphics/trick_icon.gif

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.




Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
EAN: N/A
Year: 2003
Pages: 289

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net