PL/SQL Control Structures
- One ship drives east and another drives west
- With the selfsame winds that blow.
- 'Tis the set of the sails and not the gales
- Which tells us the way to go. --Ella Wheeler Wilcox
This chapter shows you how to structure the flow of control through a PL/SQL program. You learn how statements are connected by simple but powerful control structures that have a single entry and exit point. Collectively, these structures can handle any situation. Their proper use leads naturally to a well-structured program.
This chapter discusses the following topics:
- Overview of PL/SQL Control Structures
- Conditional Control: IF and CASE Statements
- Iterative Control: LOOP and EXIT Statements
- Sequential Control: GOTO and NULL Statements
Overview of PL/SQL Control Structures
According to the structure theorem, any computer program can be written using the basic control structures shown in Figure 4-1. They can be combined in any way necessary to deal with a given problem.
Figure 4-1 Control Structures
Text description of the illustration pls81008_control_structures.gif
The selection structure tests a condition, then executes one sequence of statements instead of another, depending on whether the condition is true or false. A condition is any variable or expression that returns a Boolean value ( or ). The iteration structure executes a sequence of statements repeatedly as long as a condition holds true. The sequence structure simply executes a sequence of statements in the order in which they occur.
Conditional Control: IF and CASE Statements
Often, it is necessary to take alternative actions depending on circumstances. The statement lets you execute a sequence of statements conditionally. That is, whether the sequence is executed or not depends on the value of a condition. There are three forms of statements: , , and . The statement is a compact way to evaluate a single condition and choose between many alternative actions.
The simplest form of statement associates a condition with a sequence of statements enclosed by the keywords and (not ), as follows:IF condition THEN sequence_of_statements END IF;
The sequence of statements is executed only if the condition is true. If the condition is false or null, the statement does nothing. In either case, control passes to the next statement. An example follows:IF sales > quota THEN compute_bonus(empid); UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id; END IF;
You might want to place brief statements on a single line, as inIF x > y THEN high := x; END IF;
The second form of statement adds the keyword followed by an alternative sequence of statements, as follows:IF condition THEN sequence_of_statements1 ELSE sequence_of_statements2 END IF;
The sequence of statements in the clause is executed only if the condition is false or null. Thus, the clause ensures that a sequence of statements is executed. In the following example, the first statement is executed when the condition is true, but the second statement is executed when the condition is false or null:IF trans_type = 'CR' THEN UPDATE accounts SET balance = balance + credit WHERE ... ELSE UPDATE accounts SET balance = balance - debit WHERE ... END IF;
The and clauses can include statements. That is, statements can be nested, as the following example shows:IF trans_type = 'CR' THEN UPDATE accounts SET balance = balance + credit WHERE ... ELSE IF new_balance >= minimum_balance THEN UPDATE accounts SET balance = balance - debit WHERE ... ELSE RAISE insufficient_funds; END IF; END IF;
Sometimes you want to select an action from several mutually exclusive alternatives. The third form of statement uses the keyword (not ) to introduce additional conditions, as follows:IF condition1 THEN sequence_of_statements1 ELSIF condition2 THEN sequence_of_statements2 ELSE sequence_of_statements3 END IF;
If the first condition is false or null, the clause tests another condition. An statement can have any number of clauses; the final clause is optional. Conditions are evaluated one by one from top to bottom. If any condition is true, its associated sequence of statements is executed and control passes to the next statement. If all conditions are false or null, the sequence in the clause is executed. Consider the following example:BEGIN ... IF sales > 50000 THEN bonus := 1500; ELSIF sales > 35000 THEN bonus := 500; ELSE bonus := 100; END IF; INSERT INTO payroll VALUES (emp_id, bonus, ...); END;
If the value of is larger than 50000, the first and second conditions are true. Nevertheless, is assigned the proper value of 1500 because the second condition is never tested. When the first condition is true, its associated statement is executed and control passes to the statement.
Like the statement, the statement selects one sequence of statements to execute. However, to select the sequence, the statement uses a selector rather than multiple Boolean expressions. (Recall from Chapter 2 that a selector is an expression whose value is used to select one of several alternatives.) To compare the and statements, consider the following code that outputs descriptions of school grades:IF grade = 'A' THEN dbms_output.put_line('Excellent'); ELSIF grade = 'B' THEN dbms_output.put_line('Very Good'); ELSIF grade = 'C' THEN dbms_output.put_line('Good'); ELSIF grade = 'D' THEN dbms_output. put_line('Fair'); ELSIF grade = 'F' THEN dbms_output.put_line('Poor'); ELSE dbms_output.put_line('No such grade'); END IF;
Notice the five Boolean expressions. In each instance, we test whether the same variable, , is equal to one of five values: , , , , or . Let us rewrite the preceding code using the statement, as follows:CASE grade WHEN 'A' THEN dbms_output.put_line('Excellent'); WHEN 'B' THEN dbms_output.put_line('Very Good'); WHEN 'C' THEN dbms_output.put_line('Good'); WHEN 'D' THEN dbms_output.put_line('Fair'); WHEN 'F' THEN dbms_output.put_line('Poor'); ELSE dbms_output.put_line('No such grade'); END CASE;
The statement is more readable and more efficient. So, when possible, rewrite lengthy statements as statements.
The statement begins with the keyword . The keyword is followed by a selector, which is the variable in the last example. The selector expression can be arbitrarily complex. For example, it can contain function calls. Usually, however, it consists of a single variable. The selector expression is evaluated only once. The value it yields can have any PL/SQL datatype other than , , an object type, a PL/SQL record, an index-by-table, a varray, or a nested table.
The selector is followed by one or more clauses, which are checked sequentially. The value of the selector determines which clause is executed. If the value of the selector equals the value of a -clause expression, that clause is executed. For instance, in the last example, if equals , the program outputs . Execution never falls through; if any clause is executed, control passes to the next statement.
The clause works similarly to the clause in an statement. In the last example, if the grade is not one of the choices covered by a clause, the clause is selected, and the phrase is output. The clause is optional. However, if you omit the clause, PL/SQL adds the following implicit clause:ELSE RAISE CASE_NOT_FOUND;
If the statement selects the implicit clause, PL/SQL raises the predefined exception . So, there is always a default action, even when you omit the clause.
The keywords terminate the statement. These two keywords must be separated by a space. The statement has the following form:[<<label_name>>] CASE selector WHEN expression1 THEN sequence_of_statements1; WHEN expression2 THEN sequence_of_statements2; ... WHEN expressionN THEN sequence_of_statementsN; [ELSE sequence_of_statementsN+1;] END CASE [label_name];
Like PL/SQL blocks, statements can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the statement. Optionally, the label name can also appear at the end of the statement.
Exceptions raised during the execution of a statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.
An alternative to the statement is the expression, where each clause is an expression. For details, see "CASE Expressions".
Searched CASE Statement
PL/SQL also provides a searched statement, which has the form:[<<label_name>>] CASE WHEN search_condition1 THEN sequence_of_statements1; WHEN search_condition2 THEN sequence_of_statements2; ... WHEN search_conditionN THEN sequence_of_statementsN; [ELSE sequence_of_statementsN+1;] END CASE [label_name];
The searched statement has no selector. Also, its clauses contain search conditions that yield a Boolean value, not expressions that can yield a value of any type. An example follows:CASE WHEN grade = 'A' THEN dbms_output.put_line('Excellent'); WHEN grade = 'B' THEN dbms_output.put_line('Very Good'); WHEN grade = 'C' THEN dbms_output.put_line('Good'); WHEN grade = 'D' THEN dbms_output.put_line('Fair'); WHEN grade = 'F' THEN dbms_output.put_line('Poor'); ELSE dbms_output.put_line('No such grade'); END CASE;
The search conditions are evaluated sequentially. The Boolean value of each search condition determines which clause is executed. If a search condition yields , its clause is executed. If any clause is executed, control passes to the next statement, so subsequent search conditions are not evaluated.
If none of the search conditions yields , the clause is executed. The clause is optional. However, if you omit the clause, PL/SQL adds the following implicit clause:ELSE RAISE CASE_NOT_FOUND;
Exceptions raised during the execution of a searched statement are handled in the usual way. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.
Guidelines for PL/SQL Conditional Statements
Avoid clumsy statements like those in the following example:IF new_balance < minimum_balance THEN overdrawn := TRUE; ELSE overdrawn := FALSE; END IF; ... IF overdrawn = TRUE THEN RAISE insufficient_funds; END IF;
This code disregards two useful facts. First, the value of a Boolean expression can be assigned directly to a Boolean variable. So, you can replace the first statement with a simple assignment, as follows:overdrawn := new_balance < minimum_balance;
Second, a Boolean variable is itself either true or false. So, you can simplify the condition in the second statement, as follows:IF overdrawn THEN ...
When possible, use the clause instead of nested statements. That way, your code will be easier to read and understand. Compare the following statements:IF condition1 THEN | IF condition1 THEN statement1; | statement1; ELSE | ELSIF condition2 THEN IF condition2 THEN | statement2; statement2; | ELSIF condition3 THEN ELSE | statement3; IF condition3 THEN | END IF; statement3; | END IF; | END IF; | END IF; |
These statements are logically equivalent, but the first statement obscures the flow of logic, whereas the second statement reveals it.
If you are comparing a single expression to multiple values, you can simplify the logic by using a single statement instead of an with several clauses.
Iterative Control: LOOP and EXIT Statements
statements let you execute a sequence of statements multiple times. There are three forms of statements: , , and .
The simplest form of statement is the basic (or infinite) loop, which encloses a sequence of statements between the keywords and , as follows:LOOP sequence_of_statements END LOOP;
With each iteration of the loop, the sequence of statements is executed, then control resumes at the top of the loop. If further processing is undesirable or impossible, you can use an statement to complete the loop. You can place one or more statements anywhere inside a loop, but nowhere outside a loop. There are two forms of statements: and .
The statement forces a loop to complete unconditionally. When an statement is encountered, the loop completes immediately and control passes to the next statement. An example follows:LOOP ... IF credit_rating < 3 THEN ... EXIT; -- exit loop immediately END IF; END LOOP; -- control resumes here
The next example shows that you cannot use the statement to complete a PL/SQL block:BEGIN ... IF credit_rating < 3 THEN ... EXIT; -- not allowed END IF; END;
Remember, the statement must be placed inside a loop. To complete a PL/SQL block before its normal end is reached, you can use the statement. For more information, see "Using the RETURN Statement".
The statement lets a loop complete conditionally. When the statement is encountered, the condition in the clause is evaluated. If the condition is true, the loop completes and control passes to the next statement after the loop. An example follows:LOOP FETCH c1 INTO ... EXIT WHEN c1%NOTFOUND; -- exit loop if condition is true ... END LOOP; CLOSE c1;
Until the condition is true, the loop cannot complete. So, a statement inside the loop must change the value of the condition. In the last example, if the statement returns a row, the condition is false. When the statement fails to return a row, the condition is true, the loop completes, and control passes to the statement.
The statement replaces a simple statement. For example, compare the following statements:IF count > 100 THEN | EXIT WHEN count > 100; EXIT; | END IF; |
These statements are logically equivalent, but the statement is easier to read and understand.
Like PL/SQL blocks, loops can be labeled. The label, an undeclared identifier enclosed by double angle brackets, must appear at the beginning of the statement, as follows:<<label_name>> LOOP sequence_of_statements END LOOP;
Optionally, the label name can also appear at the end of the statement, as the following example shows:<<my_loop>> LOOP ... END LOOP my_loop;
When you nest labeled loops, use ending label names to improve readability.
With either form of statement, you can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an statement, as follows:<<outer>> LOOP ... LOOP ... EXIT outer WHEN ... -- exit both loops END LOOP; ... END LOOP outer;
Every enclosing loop up to and including the labeled loop is exited.
The statement associates a condition with a sequence of statements enclosed by the keywords and , as follows:WHILE condition LOOP sequence_of_statements END LOOP;
Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement. An example follows:WHILE total <= 25000 LOOP ... SELECT sal INTO salary FROM emp WHERE ... total := total + salary; END LOOP;
The number of iterations depends on the condition and is unknown until the loop completes. The condition is tested at the top of the loop, so the sequence might execute zero times. In the last example, if the initial value of is larger than 25000, the condition is false and the loop is bypassed.
Some languages have a or structure, which tests the condition at the bottom of the loop instead of at the top. Therefore, the sequence of statements is executed at least once. PL/SQL has no such structure, but you can easily build one, as follows:LOOP sequence_of_statements EXIT WHEN boolean_expression; END LOOP;
To ensure that a loop executes at least once, use an initialized Boolean variable in the condition, as follows:done := FALSE; WHILE NOT done LOOP sequence_of_statements done := boolean_expression; END LOOP;
A statement inside the loop must assign a new value to the Boolean variable. Otherwise, you have an infinite loop. For example, the following statements are logically equivalent:WHILE TRUE LOOP | LOOP ... | ... END LOOP; | END LOOP;
Whereas the number of iterations through a loop is unknown until the loop completes, the number of iterations through a loop is known before the loop is entered. loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords and . A double dot () serves as the range operator. The syntax follows:FOR counter IN [REVERSE] lower_bound..higher_bound LOOP sequence_of_statements END LOOP;
The range is evaluated when the loop is first entered and is never re-evaluated.
As the next example shows, the sequence of statements is executed once for each integer in the range. After each iteration, the loop counter is incremented.FOR i IN 1..3 LOOP -- assign the values 1,2,3 to i sequence_of_statements -- executes three times END LOOP;
The following example shows that if the lower bound equals the higher bound, the sequence of statements is executed once:FOR i IN 3..3 LOOP -- assign the value 3 to i sequence_of_statements -- executes one time END LOOP;
By default, iteration proceeds upward from the lower bound to the higher bound. However, as the example below shows, if you use the keyword , iteration proceeds downward from the higher bound to the lower bound. After each iteration, the loop counter is decremented. Nevertheless, you write the range bounds in ascending (not descending) order.FOR i IN REVERSE 1..3 LOOP -- assign the values 3,2,1 to i sequence_of_statements -- executes three times END LOOP;
Inside a loop, the loop counter can be referenced like a constant but cannot be assigned values, as the following example shows:FOR ctr IN 1..10 LOOP IF NOT finished THEN INSERT INTO ... VALUES (ctr, ...); -- legal factor := ctr * 2; -- legal ELSE ctr := 10; -- not allowed END IF; END LOOP;
The bounds of a loop range can be literals, variables, or expressions but must evaluate to numbers. Otherwise, PL/SQL raises the predefined exception . The lower bound need not be 1, as the examples below show. However, the loop counter increment (or decrement) must be 1.j IN -5..5 k IN REVERSE first..last step IN 0..TRUNC(high/low) * 2
Internally, PL/SQL assigns the values of the bounds to temporary variables, and, if necessary, rounds the values to the nearest integer. The magnitude range of a is -2**31 .. 2**31. So, if a bound evaluates to a number outside that range, you get a numeric overflow error when PL/SQL attempts the assignment, as the following example shows:DECLARE hi NUMBER := 2**32; BEGIN FOR j IN 1..hi LOOP -- causes a 'numeric overflow' error ... END LOOP; END;
Some languages provide a clause, which lets you specify a different increment (5 instead of 1 for example). PL/SQL has no such structure, but you can easily build one. Inside the loop, simply multiply each reference to the loop counter by the new increment. In the following example, you assign today's date to elements 5, 10, and 15 of an index-by table:DECLARE TYPE DateList IS TABLE OF DATE INDEX BY BINARY_INTEGER; dates DateList; k CONSTANT INTEGER := 5; -- set new increment BEGIN FOR j IN 1..3 LOOP dates(j*k) := SYSDATE; -- multiply loop counter by increment END LOOP; ... END;
PL/SQL lets you determine the loop range dynamically at run time, as the following example shows:SELECT COUNT(empno) INTO emp_count FROM emp; FOR i IN 1..emp_count LOOP ... END LOOP;
The value of is unknown at compile time; the statement returns the value at run time.
What happens if the lower bound of a loop range evaluates to a larger integer than the upper bound? As the next example shows, the sequence of statements within the loop is not executed and control passes to the next statement:-- limit becomes 1 FOR i IN 2..limit LOOP sequence_of_statements -- executes zero times END LOOP; -- control passes here
The loop counter is defined only within the loop. You cannot reference it outside the loop. After the loop is exited, the loop counter is undefined, as the following example shows:FOR ctr IN 1..10 LOOP ... END LOOP; sum := ctr - 1; -- not allowed
You need not explicitly declare the loop counter because it is implicitly declared as a local variable of type . The next example shows that the local declaration hides any global declaration:DECLARE ctr INTEGER; BEGIN ... FOR ctr IN 1..25 LOOP ... IF ctr > 10 THEN ... -- refers to loop counter END LOOP; END;
To reference the global variable in this example, you must use a label and dot notation, as follows:<<main>> DECLARE ctr INTEGER; ... BEGIN ... FOR ctr IN 1..25 LOOP ... IF main.ctr > 10 THEN -- refers to global variable ... END IF; END LOOP; END main;
The same scope rules apply to nested loops. Consider the example below. Both loop counters have the same name. So, to reference the outer loop counter from the inner loop, you must use a label and dot notation, as follows:<<outer>> FOR step IN 1..25 LOOP FOR step IN 1..10 LOOP ... IF outer.step > 15 THEN ... END LOOP; END LOOP outer;
Using the EXIT Statement
The statement lets a loop complete prematurely. For example, the following loop normally executes ten times, but as soon as the statement fails to return a row, the loop completes no matter how many times it has executed:FOR j IN 1..10 LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%NOTFOUND; ... END LOOP;
Suppose you must exit from a nested loop prematurely. You can complete not only the current loop, but any enclosing loop. Simply label the enclosing loop that you want to complete. Then, use the label in an statement to specify which loop to exit, as follows:<<outer>> FOR i IN 1..5 LOOP ... FOR j IN 1..10 LOOP FETCH c1 INTO emp_rec; EXIT outer WHEN c1%NOTFOUND; -- exit both FOR loops ... END LOOP; END LOOP outer; -- control passes here
Sequential Control: GOTO and NULL Statements
Unlike the and statements, the and statements are not crucial to PL/SQL programming. The structure of PL/SQL is such that the statement is seldom needed. Occasionally, it can simplify logic enough to warrant its use. The statement can improve readability by making the meaning and action of conditional statements clear.
Overuse of statements can result in complex, unstructured code (sometimes called spaghetti code) that is hard to understand and maintain. So, use statements sparingly. For example, to branch from a deeply nested structure to an error-handling routine, raise an exception rather than use a statement.
The statement branches to a label unconditionally. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. When executed, the statement transfers control to the labeled statement or block. In the following example, you go to an executable statement farther down in a sequence of statements:BEGIN ... GOTO insert_row; ... <<insert_row>> INSERT INTO emp VALUES ... END;
In the next example, you go to a PL/SQL block farther up in a sequence of statements:BEGIN ... <<update_row>> BEGIN UPDATE emp SET ... ... END; ... GOTO update_row; ... END;
The label in the following example is not allowed because it does not precede an executable statement:DECLARE done BOOLEAN; BEGIN ... FOR i IN 1..50 LOOP IF done THEN GOTO end_loop; END IF; ... <<end_loop>> -- not allowed END LOOP; -- not an executable statement END;
To debug the last example, just add the statement, as follows:FOR i IN 1..50 LOOP IF done THEN GOTO end_loop; END IF; ... <<end_loop>> NULL; -- an executable statement END LOOP;
As the following example shows, a statement can branch to an enclosing block from the current block:DECLARE my_ename CHAR(10); BEGIN <<get_name>> SELECT ename INTO my_ename FROM emp WHERE ... BEGIN ... GOTO get_name; -- branch to enclosing block END; END;
The statement branches to the first enclosing block in which the referenced label appears.
Some possible destinations of a statement are not allowed. Specifically, a statement cannot branch into an statement, statement, statement, or sub-block. For example, the following statement is not allowed:BEGIN ... GOTO update_row; -- can't branch into IF statement ... IF valid THEN ... <<update_row>> UPDATE emp SET ... END IF; END;
As the example below shows, a statement cannot branch from one statement clause to another. Likewise, a statement cannot branch from one statement clause to another.BEGIN ... IF valid THEN ... GOTO update_row; -- can't branch into ELSE clause ELSE ... <<update_row>> UPDATE emp SET ... END IF; END;
The next example shows that a statement cannot branch from an enclosing block into a sub-block:BEGIN ... IF status = 'OBSOLETE' THEN GOTO delete_part; -- can't branch into sub-block END IF; ... BEGIN ... <<delete_part>> DELETE FROM parts WHERE ... END; END;
Also, a statement cannot branch out of a subprogram, as the following example shows:DECLARE ... PROCEDURE compute_bonus (emp_id NUMBER) IS BEGIN ... GOTO update_row; -- can't branch out of subprogram END; BEGIN ... <<update_row>> UPDATE emp SET ... END;
Finally, a statement cannot branch from an exception handler into the current block. For example, the following statement is not allowed:DECLARE ... pe_ratio REAL; BEGIN ... SELECT price / NVL(earnings, 0) INTO pe_ratio FROM ... <<insert_row>> INSERT INTO stats VALUES (pe_ratio, ...); EXCEPTION WHEN ZERO_DIVIDE THEN pe_ratio := 0; GOTO insert_row; -- can't branch into current block END;
However, a statement can branch from an exception handler into an enclosing block.
The statement does nothing other than pass control to the next statement. In a conditional construct, the statement tells readers that a possibility has been considered, but no action is necessary. In the following example, the statement shows that no action is taken for unnamed exceptions:EXCEPTION WHEN ZERO_DIVIDE THEN ROLLBACK; WHEN VALUE_ERROR THEN INSERT INTO errors VALUES ... COMMIT; WHEN OTHERS THEN NULL; END;
In statements or other places that require at least one executable statement, the statement to satisfy the syntax. In the following example, the statement emphasizes that only top-rated employees get bonuses:IF rating > 90 THEN compute_bonus(emp_id); ELSE NULL; END IF;
Also, the statement is a handy way to create stubs when designing applications from the top down. A stub is dummy subprogram that lets you defer the definition of a procedure or function until you test and debug the main program. In the following example, the statement meets the requirement that at least one statement must appear in the executable part of a subprogram:PROCEDURE debit_account (acct_id INTEGER, amount REAL) IS BEGIN NULL; END debit_account;
Overview of PL/SQL
The limits of my language mean the limits of my world. --Ludwig Wittgenstein
This chapter surveys the main features of PL/SQL and points out the advantages they offer. It also acquaints you with the basic concepts behind PL/SQL and the general appearance of PL/SQL programs. You see how PL/SQL bridges the gap between database technology and procedural programming languages.
This chapter discusses the following topics:
- Understanding the Main Features of PL/SQL
- PL/SQL Architecture
- Advantages of PL/SQL
Understanding the Main Features of PL/SQL
A good way to get acquainted with PL/SQL is to look at a sample program. The program below processes an order for a tennis racket. First, it declares a variable of type to store the quantity of tennis rackets on hand. Then, it retrieves the quantity on hand from a database table named . If the quantity is greater than zero, the program updates the table and inserts a purchase record into another table named . Otherwise, the program inserts an out-of-stock record into the table.-- available online in file 'examp1' DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM inventory WHERE product = 'TENNIS RACKET' FOR UPDATE OF quantity; IF qty_on_hand > 0 THEN -- check quantity UPDATE inventory SET quantity = quantity - 1 WHERE product = 'TENNIS RACKET'; INSERT INTO purchase_record VALUES ('Tennis racket purchased', SYSDATE); ELSE INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE); END IF; COMMIT; END;
With PL/SQL, you can use SQL statements to manipulate Oracle data and flow-of-control statements to process the data. You can also declare constants and variables, define procedures and functions, and trap runtime errors. Thus, PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.
PL/SQL is a block-structured language. That is, the basic units (procedures, functions, and anonymous blocks) that make up a PL/SQL program are logical blocks, which can contain any number of nested sub-blocks. Typically, each logical block corresponds to a problem or subproblem to be solved. Thus, PL/SQL supports the divide-and-conquer approach to problem solving called stepwise refinement.
A block (or sub-block) lets you group logically related declarations and statements. That way, you can place declarations close to where they are used. The declarations are local to the block and cease to exist when the block completes.
As Figure 1-1 shows, a PL/SQL block has three parts: a declarative part, an executable part, and an exception-handling part. (In PL/SQL, a warning or error condition is called an exception.) Only the executable part is required.
The order of the parts is logical. First comes the declarative part, in which items can be declared. Once declared, items can be manipulated in the executable part. Exceptions raised during execution can be dealt with in the exception-handling part.
Figure 1-1 Block Structure
Text description of the illustration pls81001_block_structure.gif
You can nest sub-blocks in the executable and exception-handling parts of a PL/SQL block or subprogram but not in the declarative part. Also, you can define local subprograms in the declarative part of any block. However, you can call local subprograms only from the block in which they are defined.
Variables and Constants
PL/SQL lets you declare constants and variables, then use them in SQL and procedural statements anywhere an expression can be used. However, forward references are not allowed. So, you must declare a constant or variable before referencing it in other statements, including other declarative statements.
Variables can have any SQL datatype, such as , , or , or any PL/SQL datatype, such as or . For example, assume that you want to declare a variable named to hold 4-digit numbers and a variable named to hold the Boolean value or . You declare these variables as follows:part_no NUMBER(4); in_stock BOOLEAN;
You can also declare nested tables, variable-size arrays (varrays for short), and records using the , , and composite datatypes.
Assigning Values to a Variable
You can assign values to a variable in three ways. The first way uses the assignment operator (), a colon followed by an equal sign. You place the variable to the left of the operator and an expression (which can include function calls) to the right. A few examples follow:tax := price * tax_rate; valid_id := FALSE; bonus := current_salary * 0.10; wages := gross_pay(emp_id, st_hrs, ot_hrs) - deductions;
The second way to assign values to a variable is by selecting (or fetching) database values into it. In the example below, you have Oracle compute a 10% bonus when you select the salary of an employee. Now, you can use the variable in another computation or insert its value into a database table.SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;
The third way to assign values to a variable is by passing it as an or parameter to a subprogram. As the following example shows, an parameter lets you pass initial values to the subprogram being called and return updated values to the caller:DECLARE my_sal REAL(7,2); PROCEDURE adjust_salary (emp_id INT, salary IN OUT REAL) IS ... BEGIN SELECT AVG(sal) INTO my_sal FROM emp; adjust_salary(7788, my_sal); -- assigns a new value to my_sal
Declaring a constant is like declaring a variable except that you must add the keyword and immediately assign a value to the constant. Thereafter, no more assignments to the constant are allowed. In the following example, you declare a constant named :credit_limit CONSTANT REAL := 5000.00;
Oracle uses work areas to execute SQL statements and store processing information. A PL/SQL construct called a cursor lets you name a work area and access its stored information. There are two kinds of cursors: implicit and explicit. PL/SQL implicitly declares a cursor for all SQL data manipulation statements, including queries that return only one row. For queries that return more than one row, you can explicitly declare a cursor to process the rows individually. An example follows:DECLARE CURSOR c1 IS SELECT empno, ename, job FROM emp WHERE deptno = 20;
The set of rows returned by a multi-row query is called the result set. Its size is the number of rows that meet your search criteria. As Figure 1-2 shows, an explicit cursor "points" to the current row in the result set. This allows your program to process the rows one at a time.
Figure 1-2 Query Processing
Text description of the illustration pls81003_query_processing.gif
Multi-row query processing is somewhat like file processing. For example, a COBOL program opens a file, processes records, then closes the file. Likewise, a PL/SQL program opens a cursor, processes rows returned by a query, then closes the cursor. Just as a file pointer marks the current position in an open file, a cursor marks the current position in a result set.
You use the , , and statements to control a cursor. The statement executes the query associated with the cursor, identifies the result set, and positions the cursor before the first row. The statement retrieves the current row and advances the cursor to the next row. When the last row has been processed, the statement disables the cursor.
Cursor FOR Loops
In most situations that require an explicit cursor, you can simplify coding by using a cursor loop instead of the , , and statements. A cursor loop implicitly declares its loop index as a record that represents a row fetched from the database. Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, then closes the cursor when all rows have been processed. In the following example, the cursor loop implicitly declares as a record:DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, deptno FROM emp; ... BEGIN FOR emp_rec IN c1 LOOP ... salary_total := salary_total + emp_rec.sal; END LOOP;
To reference individual fields in the record, you use dot notation, in which a dot () serves as the component selector.
Like a cursor, a cursor variable points to the current row in the result set of a multi-row query. But, unlike a cursor, a cursor variable can be opened for any type-compatible query. It is not tied to a specific query. Cursor variables are true PL/SQL variables, to which you can assign new values and which you can pass to subprograms stored in an Oracle database. This gives you more flexibility and a convenient way to centralize data retrieval.
Typically, you open a cursor variable by passing it to a stored procedure that declares a cursor variable as one of its formal parameters. The following procedure opens the cursor variable for the chosen query:PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS BEGIN IF choice = 1 THEN OPEN generic_cv FOR SELECT * FROM emp; ELSIF choice = 2 THEN OPEN generic_cv FOR SELECT * FROM dept; ELSIF choice = 3 THEN OPEN generic_cv FOR SELECT * FROM salgrade; END IF; ... END;
PL/SQL variables and cursors have attributes, which are properties that let you reference the datatype and structure of an item without repeating its definition. Database columns and tables have similar attributes, which you can use to ease maintenance. A percent sign () serves as the attribute indicator.
The attribute provides the datatype of a variable or database column. This is particularly useful when declaring variables that will hold database values. For example, assume there is a column named in a table named . To declare a variable named that has the same datatype as column , use dot notation and the attribute, as follows:my_title books.title%TYPE;
Declaring with has two advantages. First, you need not know the exact datatype of . Second, if you change the database definition of (make it a longer character string for example), the datatype of changes accordingly at run time.
In PL/SQL, records are used to group data. A record consists of a number of related fields in which data values can be stored. The attribute provides a record type that represents a row in a table. The record can store an entire row of data selected from the table or fetched from a cursor or cursor variable.
Columns in a row and corresponding fields in a record have the same names and datatypes. In the example below, you declare a record named . Its fields have the same names and datatypes as the columns in the table.DECLARE dept_rec dept%ROWTYPE; -- declare record variable
You use dot notation to reference fields, as the following example shows:my_deptno := dept_rec.deptno;
If you declare a cursor that retrieves the last name, salary, hire date, and job title of an employee, you can use to declare a record that stores the same information, as follows:DECLARE CURSOR c1 IS SELECT ename, sal, hiredate, job FROM emp; emp_rec c1%ROWTYPE; -- declare record variable that represents -- a row fetched from the emp table
When you execute the statementFETCH c1 INTO emp_rec;
the value in the column of the table is assigned to the field of , the value in the column is assigned to the field, and so on. Figure 1-3 shows how the result might appear.
Figure 1-3 %ROWTYPE Record
Text description of the illustration pls81002_rowtype_record.gif
Control structures are the most important PL/SQL extension to SQL. Not only does PL/SQL let you manipulate Oracle data, it lets you process the data using conditional, iterative, and sequential flow-of-control statements such as , , , , , and . Collectively, these statements can handle any situation.
Often, it is necessary to take alternative actions depending on circumstances. The statement lets you execute a sequence of statements conditionally. The clause checks a condition; the clause defines what to do if the condition is true; the clause defines what to do if the condition is false or null.
Consider the program below, which processes a bank transaction. Before allowing you to withdraw $500 from account 3, it makes sure the account has sufficient funds to cover the withdrawal. If the funds are available, the program debits the account. Otherwise, the program inserts a record into an audit table.-- available online in file 'examp2' DECLARE acct_balance NUMBER(11,2); acct CONSTANT NUMBER(4) := 3; debit_amt CONSTANT NUMBER(5,2) := 500.00; BEGIN SELECT bal INTO acct_balance FROM accounts WHERE account_id = acct FOR UPDATE OF bal; IF acct_balance >= debit_amt THEN UPDATE accounts SET bal = bal - debit_amt WHERE account_id = acct; ELSE INSERT INTO temp VALUES (acct, acct_balance, 'Insufficient funds'); -- insert account, current balance, and message END IF; COMMIT; END;
To choose among several values or courses of action, you can use constructs. The CASE expression evaluates a condition and returns a value for each case. The case statement evaluates a condition and performs an action (which might be an entire PL/SQL block) for each case.-- This CASE statement performs different actions based -- on a set of conditional tests. CASE WHEN shape = 'square' THEN area := side * side; WHEN shape = 'circle' THEN BEGIN area := pi * (radius * radius); DBMS_OUTPUT.PUT_LINE('Value is not exact because pi is irrational.'); END; WHEN shape = 'rectangle' THEN area := length * width; ELSE BEGIN DBMS_OUTPUT.PUT_LINE('No formula to calculate area of a' || shape); RAISE PROGRAM_ERROR; END; END CASE;
A sequence of statements that uses query results to select alternative actions is common in database applications. Another common sequence inserts or deletes a row only if an associated entry is found in another table. You can bundle these common sequences into a PL/SQL block using conditional logic.
statements let you execute a sequence of statements multiple times. You place the keyword before the first statement in the sequence and the keywords after the last statement in the sequence. The following example shows the simplest kind of loop, which repeats a sequence of statements continually:LOOP -- sequence of statements END LOOP;
The statement lets you specify a range of integers, then execute a sequence of statements once for each integer in the range. For example, the following loop inserts 500 numbers and their square roots into a database table:FOR num IN 1..500 LOOP INSERT INTO roots VALUES (num, SQRT(num)); END LOOP;
The statement associates a condition with a sequence of statements. Before each iteration of the loop, the condition is evaluated. If the condition is true, the sequence of statements is executed, then control resumes at the top of the loop. If the condition is false or null, the loop is bypassed and control passes to the next statement.
In the following example, you find the first employee who has a salary over $2500 and is higher in the chain of command than employee 7499:-- available online in file 'examp3' DECLARE salary emp.sal%TYPE := 0; mgr_num emp.mgr%TYPE; last_name emp.ename%TYPE; starting_empno emp.empno%TYPE := 7499; BEGIN SELECT mgr INTO mgr_num FROM emp WHERE empno = starting_empno; WHILE salary <= 2500 LOOP SELECT sal, mgr, ename INTO salary, mgr_num, last_name FROM emp WHERE empno = mgr_num; END LOOP; INSERT INTO temp VALUES (NULL, salary, last_name); COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO temp VALUES (NULL, NULL, 'Not found'); COMMIT; END;
The statement lets you complete a loop if further processing is impossible or undesirable. When the statement is encountered, the condition in the clause is evaluated. If the condition is true, the loop completes and control passes to the next statement. In the following example, the loop completes when the value of exceeds 25,000:LOOP ... total := total + salary; EXIT WHEN total > 25000; -- exit loop if condition is true END LOOP; -- control resumes here
The statement lets you branch to a label unconditionally. The label, an undeclared identifier enclosed by double angle brackets, must precede an executable statement or a PL/SQL block. When executed, the statement transfers control to the labeled statement or block, as the following example shows:IF rating > 90 THEN GOTO calc_raise; -- branch to label END IF; ... <<calc_raise>> IF job_title = 'SALESMAN' THEN -- control resumes here amount := commission * 0.25; ELSE amount := salary * 0.10; END IF;
Modularity lets you break an application down into manageable, well-defined modules. Through successive refinement, you can reduce a complex problem to a set of simple problems that have easy-to-implement solutions. PL/SQL meets this need with program units, which include blocks, subprograms, and packages.
PL/SQL has two types of subprograms called procedures and functions, which can take parameters and be invoked (called). As the following example shows, a subprogram is like a miniature program, beginning with a header followed by an optional declarative part, an executable part, and an optional exception-handling part:PROCEDURE award_bonus (emp_id NUMBER) IS bonus REAL; comm_missing EXCEPTION; BEGIN -- executable part starts here SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN RAISE comm_missing; ELSE UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id; END IF; EXCEPTION -- exception-handling part starts here WHEN comm_missing THEN ... END award_bonus;
When called, this procedure accepts an employee number. It uses the number to select the employee's commission from a database table and, at the same time, compute a 15% bonus. Then, it checks the bonus amount. If the bonus is null, an exception is raised; otherwise, the employee's payroll record is updated.
PL/SQL lets you bundle logically related types, variables, cursors, and subprograms into a package. Each package is easy to understand and the interfaces between packages are simple, clear, and well defined. This aids application development.
Packages usually have two parts: a specification and a body. The specification is the interface to your applications; it declares the types, constants, variables, exceptions, cursors, and subprograms available for use. The body defines cursors and subprograms and so implements the specification.
In the following example, you package two employment procedures:CREATE PACKAGE emp_actions AS -- package specification PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions; CREATE PACKAGE BODY emp_actions AS -- package body PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS BEGIN INSERT INTO emp VALUES (empno, ename, ...); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions;
Only the declarations in the package specification are visible and accessible to applications. Implementation details in the package body are hidden and inaccessible.
Packages can be compiled and stored in an Oracle database, where their contents can be shared by many applications. When you call a packaged subprogram for the first time, the whole package is loaded into memory. So, subsequent calls to related subprograms in the package require no disk I/O. Thus, packages can enhance productivity and improve performance.
Data abstraction lets you extract the essential properties of data while ignoring unnecessary details. Once you design a data structure, you can forget the details and focus on designing algorithms that manipulate the data structure.
The collection types and allow you to declare index-by tables, nested tables, and variable-size arrays (varrays for short). A collection is an ordered group of elements, all of the same type. Each element has a unique subscript that determines its position in the collection.
To reference an element, use standard subscripting syntax. For example, the following call references the fifth element in the nested table (of type ) returned by function :DECLARE TYPE Staff IS TABLE OF Employee; staffer Employee; FUNCTION new_hires (hiredate DATE) RETURN Staff IS BEGIN ... END; BEGIN staffer := new_hires('10-NOV-98')(5); ... END;
Collections work like the arrays found in most third-generation programming languages. Also, collections can be passed as parameters. So, you can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.
You can use the attribute to declare a record that represents a row in a table or a row fetched from a cursor. But, with a user-defined record, you can declare fields of your own.
Records contain uniquely named fields, which can have different datatypes. Suppose you have various data about an employee such as name, salary, and hire date. These items are dissimilar in type but logically related. A record containing a field for each item lets you treat the data as a logical unit.
Consider the following example:DECLARE TYPE TimeRec IS RECORD (hours SMALLINT, minutes SMALLINT); TYPE MeetingTyp IS RECORD ( date_held DATE, duration TimeRec, -- nested record location VARCHAR2(20), purpose VARCHAR2(50));
Notice that you can nest records. That is, a record can be a component of another record.
In PL/SQL, object-oriented programming is based on object types. An object type encapsulates a data structure along with the functions and procedures needed to manipulate the data. The variables that form the data structure are called attributes. The functions and procedures that characterize the behavior of the object type are called methods.
Object types reduce complexity by breaking down a large system into logical entities. This lets you create software components that are modular, maintainable, and reusable.
When you define an object type using the statement (in SQL*Plus for example), you create an abstract template for some real-world object. As the following example of a bank account shows, the template specifies only those attributes and behaviors the object will need in the application environment:CREATE TYPE Bank_Account AS OBJECT ( acct_number INTEGER(5), balance REAL, status VARCHAR2(10), MEMBER PROCEDURE open (amount IN REAL), MEMBER PROCEDURE verify_acct (num IN INTEGER), MEMBER PROCEDURE close (num IN INTEGER, amount OUT REAL), MEMBER PROCEDURE deposit (num IN INTEGER, amount IN REAL), MEMBER PROCEDURE withdraw (num IN INTEGER, amount IN REAL), MEMBER FUNCTION curr_bal (num IN INTEGER) RETURN REAL );
At run time, when the data structure is filled with values, you have created an instance of an abstract bank account. You can create as many instances (called objects) as you need. Each object has the number, balance, and status of an actual bank account.
With information hiding, you see only the details that are relevant at a given level of algorithm and data structure design. Information hiding keeps high-level design decisions separate from low-level design details, which are more likely to change.
You implement information hiding for algorithms through top-down design. Once you define the purpose and interface specifications of a low-level procedure, you can ignore the implementation details. They are hidden at higher levels. For example, the implementation of a procedure named is hidden. All you need to know is that the procedure will increase a specific employee's salary by a given amount. Any changes to the definition of are transparent to calling applications.
You implement information hiding for data structures though data encapsulation. By developing a set of utility subprograms for a data structure, you insulate it from users and other developers. That way, other developers know how to use the subprograms that operate on the data structure but not how the structure is represented.
With PL/SQL packages, you can specify whether subprograms are public or private. Thus, packages enforce data encapsulation by letting you put subprogram definitions in a black box. A private definition is hidden and inaccessible. Only the package, not your application, is affected if the definition changes. This simplifies maintenance and enhancement.
PL/SQL makes it easy to detect and process predefined and user-defined error conditions called exceptions. When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. To handle raised exceptions, you write separate routines called exception handlers.
Predefined exceptions are raised implicitly by the runtime system. For example, if you try to divide a number by zero, PL/SQL raises the predefined exception automatically. You must raise user-defined exceptions explicitly with the statement.
You can define exceptions of your own in the declarative part of any PL/SQL block or subprogram. In the executable part, you check for the condition that needs special attention. If you find that the condition exists, you execute a statement. In the example below, you compute the bonus earned by a salesperson. The bonus is based on salary and commission. So, if the commission is null, you raise the exception .DECLARE ... comm_missing EXCEPTION; -- declare exception BEGIN ... IF commission IS NULL THEN RAISE comm_missing; -- raise exception END IF; bonus := (salary * 0.10) + (commission * 0.15); EXCEPTION WHEN comm_missing THEN ... -- process the exception
The PL/SQL compilation and run-time system is a technology, not an independent product. Think of this technology as an engine that compiles and executes PL/SQL blocks and subprograms. The engine can be installed in an Oracle server or in an application development tool such as Oracle Forms or Oracle Reports. So, PL/SQL can reside in two environments:
- The Oracle database server
- Oracle tools
These two environments are independent. PL/SQL is bundled with the Oracle server but might be unavailable in some tools. In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram. Figure 1-4 shows the PL/SQL engine processing an anonymous block. The engine executes procedural statements but sends SQL statements to the SQL Statement Executor in the Oracle server.
Figure 1-4 PL/SQL Engine
Text description of the illustration pls81004_plsql_engine.gif
In the Oracle Database Server
Application development tools that lack a local PL/SQL engine must rely on Oracle to process PL/SQL blocks and subprograms. When it contains the PL/SQL engine, an Oracle server can process PL/SQL blocks and subprograms as well as single SQL statements. The Oracle server passes the blocks and subprograms to its local PL/SQL engine.
Anonymous PL/SQL blocks can be embedded in an Oracle Precompiler or OCI program. At run time, the program, lacking a local PL/SQL engine, sends these blocks to the Oracle server, where they are compiled and executed. Likewise, interactive tools such as SQL*Plus and Enterprise Manager, lacking a local PL/SQL engine, must send anonymous blocks to Oracle.
Subprograms can be compiled separately and stored permanently in an Oracle database, ready to be executed. A subprogram explicitly d using an Oracle tool is called a stored subprogram. Once compiled and stored in the data dictionary, it is a schema object, which can be referenced by any number of applications connected to that database.
Stored subprograms defined within a package are called packaged subprograms. Those defined independently are called standalone subprograms. Those defined within another subprogram or within a PL/SQL block are called local subprograms, which cannot be referenced by other applications and exist only for the convenience of the enclosing block.
Stored subprograms offer higher productivity, better performance, memory savings, application integrity, and tighter security. For example, by designing applications around a library of stored procedures and functions, you can avoid redundant coding and increase your productivity.
You can call stored subprograms from a database trigger, another stored subprogram, an Oracle Precompiler application, an OCI application, or interactively from SQL*Plus or Enterprise Manager. For example, you might call the standalone procedure from SQL*Plus as follows:SQL> CALL create_dept('FINANCE', 'NEW YORK');
Subprograms are stored in parsed, compiled form. So, when called, they are loaded and passed to the PL/SQL engine immediately. Also, they take advantage of shared memory. So, only one copy of a subprogram need be loaded into memory for execution by multiple users.
A database trigger is a stored subprogram associated with a database table, view, or event. For instance, you can have Oracle fire a trigger automatically before or after an , , or statement affects a table. One of the many uses for database triggers is to audit data modifications. For example, the following table-level trigger fires whenever salaries in the table are updated:CREATE TRIGGER audit_sal AFTER UPDATE OF sal ON emp FOR EACH ROW BEGIN INSERT INTO emp_audit VALUES ... END;
The executable part of a trigger can contain procedural statements as well as SQL data manipulation statements. Besides table-level triggers, there are instead-of triggers for views and system-event triggers for schemas. For more information, see Oracle9i Application Developer's Guide - Fundamentals.
In Oracle Tools
When it contains the PL/SQL engine, an application development tool can process PL/SQL blocks and subprograms. The tool passes the blocks to its local PL/SQL engine. The engine executes all procedural statements at the application site and sends only SQL statements to Oracle. Thus, most of the work is done at the application site, not at the server site.
Furthermore, if the block contains no SQL statements, the engine executes the entire block at the application site. This is useful if your application can benefit from conditional and iterative control.
Frequently, Oracle Forms applications use SQL statements merely to test the value of field entries or to do simple computations. By using PL/SQL instead, you can avoid calls to the Oracle server. Moreover, you can use PL/SQL functions to manipulate field entries.
Advantages of PL/SQL
PL/SQL is a completely portable, high-performance transaction processing language that offers the following advantages:
- Support for SQL
- Support for object-oriented programming
- Better performance
- Higher productivity
- Full portability
- Tight integration with Oracle
- Tight security
Support for SQL
SQL has become the standard database language because it is flexible, powerful, and easy to learn. A few English-like commands such as , , , and make it easy to manipulate the data stored in a relational database.
SQL is non-procedural, meaning that you can state what you want done without stating how to do it. Oracle determines the best way to carry out your request. There is no necessary connection between consecutive statements because Oracle executes SQL statements one at a time.
PL/SQL lets you use all the SQL data manipulation, cursor control, and transaction control commands, as well as all the SQL functions, operators, and pseudocolumns. So, you can manipulate Oracle data flexibly and safely. Also, PL/SQL fully supports SQL datatypes. That reduces the need to convert data passed between your applications and the database.
PL/SQL also supports dynamic SQL, an advanced programming technique that makes your applications more flexible and versatile. Your programs can build and process SQL data definition, data control, and session control statements "on the fly" at run time.
Support for Object-Oriented Programming
Object types are an ideal object-oriented modeling tool, which you can use to reduce the cost and time required to build complex applications. Besides allowing you to create software components that are modular, maintainable, and reusable, object types allow different teams of programmers to develop software components concurrently.
By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details without affecting client programs.
In addition, object types allow for realistic data modeling. Complex real-world entities and relationships map directly into object types. That helps your programs better reflect the world they are trying to simulate.
Without PL/SQL, Oracle must process SQL statements one at a time. Each SQL statement results in another call to Oracle and higher performance overhead. In a networked environment, the overhead can become significant. Every time a SQL statement is issued, it must be sent over the network, creating more traffic.
However, with PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce communication between your application and Oracle. As Figure 1-5 shows, if your application is database intensive, you can use PL/SQL blocks and subprograms to group SQL statements before sending them to Oracle for execution.
PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Also, stored procedures, which execute in the server, can be invoked over slow network connections with a single call. That reduces network traffic and improves round-trip response times. Executable code is automatically cached and shared among users. That lowers memory requirements and invocation overhead.
Figure 1-5 PL/SQL Boosts Performance
Text description of the illustration pls81005_plsql_boosts_performance.gif
PL/SQL also improves performance by adding procedural processing power to Oracle tools. Using PL/SQL, a tool can do any computation quickly and efficiently without calling on the Oracle server. This saves time and reduces network traffic.
PL/SQL adds functionality to non-procedural tools such as Oracle Forms and Oracle Reports. With PL/SQL in these tools, you can use familiar procedural constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger. You need not use multiple trigger steps, macros, or user exits. Thus, PL/SQL increases productivity by putting better tools in your hands.
Also, PL/SQL is the same in all environments. As soon as you master PL/SQL with one Oracle tool, you can transfer your knowledge to other tools, and so multiply the productivity gains. For example, scripts written with one tool can be used by other tools.
Applications written in PL/SQL are portable to any operating system and platform on which Oracle runs. In other words, PL/SQL programs can run anywhere Oracle can run; you need not tailor them to each new environment. That means you can write portable program libraries, which can be reused in different environments.
Tight Integration with SQL
The PL/SQL and SQL languages are tightly integrated. PL/SQL supports all the SQL datatypes and the non-value . That allows you manipulate Oracle data easily and efficiently. It also helps you to write high-performance code.
The and attributes further integrate PL/SQL with SQL. For example, you can use the attribute to declare variables, basing the declarations on the definitions of database columns. If a definition changes, the variable declaration changes accordingly the next time you compile or run your program. The new definition takes effect without any effort on your part. This provides data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.
PL/SQL stored procedures enable you to partition application logic between the client and server. That way, you can prevent client applications from manipulating sensitive Oracle data. Database triggers written in PL/SQL can disable application updates selectively and do content-based auditing of user inserts.
Furthermore, you can restrict access to Oracle data by allowing users to manipulate it only through stored procedures that execute with their definer's privileges. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself.