Program Elements
| Now that we've seen the basic elements of PL/SQL, we can start to put them together to build programs. Although we will start by writing anonymous blocks, as we'll see later, what we learn here can be easily extended to functions, procedures, and triggers. An anonymous block has the following format: [DECLARE variable declarations] BEGIN executable statements [EXCEPTION exception handlers] END; The first section, beginning with the keyword DECLARE, is where we declare any variables we will use in our program. The next section, beginning with the word BEGIN, is where we put the statements to be executed normally. The final section is where we provide exception handlers to be executed in case of errors. DeclarationsBefore we can use a variable, we must declare it. We must do this (with a few exceptions, such as counters in a FOR…LOOP) in the DECLARE section of a PL/SQL block. Optionally, we can also assign a value to it at the same time. The format for a declaration is identifier [CONSTANT] datatype [ := value | DEFAULT value]; The datatype can be any of the Oracle SQL types, such as NUMBER, VARCHAR2, DATE, or CHAR. In addition, PL/SQL supports a large number of types; examples are BINARY_INTEGER, in which we can store integer values and its subtypes, such as NATURAL, which is restricted to all nonnegative integers, and SIGNTYPE, which is restricted to 1, 0, and 1. We'll restrict ourselves to SQL types, except for the occasional internal use of BOOLEAN, where it makes sense in the program's logic. A variable can optionally be declared CONSTANT, meaning that its value cannot change. A variable declared CONSTANT must be given a value when declared. We can also assign a value to a variable when we declare it, using either the assignment operator (:=) or the keyword DEFAULT. The two methods are functionally equivalent, but Oracle recommends using DEFAULT to assign values that have a typical value, such as hours worked per week, and using the assignment operator for values that have no typical value. We can use previously defined variables in the declarations that follow but forward references are not allowed. Here are some typical declarations. DECLARE MYVAR NUMBER; START_DATE DATE := '1-JAN-2002'; END_DATE DATE := START_DATE + 90; COMMISSION NUMBER DEFAULT 0.15; Cursor variables a special type of buffer or data structure used for holding information from the database can also be declared in this section. We will learn more about cursors later in this chapter. Variables can also be defined for user-defined types these will be covered in the next chapter on Oracle object-relational features. Program StatementsThe main part of a PL/SQL routine, the executable statements, is the BEGIN…END section of a PL/SQL block. These statements can include the usual assignments, calls to other procedures, and so on. But because PL/SQL is Oracle's procedural extension of SQL, it shouldn't surprise us to learn that we can also include SQL statements directly in our PL/SQL code with some restrictions. SQL StatementsMost SQL statements don't require that we do anything special in order to use them in PL/SQL and to include PL/SQL variables in our SQL statements. PL/SQL can usually determine by parsing an SQL statement whether an identifier is a PL/SQL variable or a column. Suppose, for example, that we have a table, USER_TABLE, with columns USER_ID, NAME, STREET, and CITY. If we also had PL/SQ variables with the same names, the following statement is legal but ambiguous (at least to us) and confusing: UPDATE USER_TABLE SET NAME=NAME, STREET=STREET, CITY=CITY WHERE USER_ID=USER_ID; It's just not a good idea to use the same name for different things. Because cases like this, where we have variables that correspond to column names, are common in PL/SQL, it's good to establish some sort of convention. We can, for example, follow the convention of adding V_ to distinguish between a PL/SQL variable name and its associated column. We might then have the following INSERT statement: INSERT INTO USER TABLE(USER_ID, NAME, STREET, CITY) VALUES(V_USER_ID, V_NAME, V_STREET, V_CITY); The one type of SQL statement that we can't always use easily in PL/SQL is the SELECT statement. We'll see later that if our SELECT statement returns multiple rows, we explicitly need to declare a cursor variable to handle those results. In the meantime, we'll use a special form of the SELECT statement SELECT…INTO that allows us to query and obtain values for a single row. It has the basic format SELECT select_list INTO variable_list FROM table_list [WHERE condition] [ORDER BY order_list]; Although the WHERE is optional, it's usually necessary in order to ensure that the results are restricted to a single row. Assuming the same USER_TABLE as before, we might have the following query: SELECT NAME, STREET, CITY INTO V_NAME, V_STREET, V_CITY WHERE USER_ID=V_USER_ID; Control Flow StructuresLike other procedural languages, such as Java, statements are generally executed sequentially to perform assignments, execute SQL statements, etc. This flow of control can be altered conditionally using the following control structures:
These structures each enclose a set of statements which, in turn, can include other nested control structures. Sets of statements are not enclosed using braces, as they are in Java; instead, the end is marked with the keywords END, END IF, or END LOOP. The normal sequential flow can also be altered unconditionally: GOTO label The use of GOTO is properly discouraged in PL/SQL but in rare instances it can help make code easier to understand. IF…THENThe condition in an IF…THEN statement is an expression that evaluates to a Boolean TRUE or FALSE. If the condition is true, any statements after THEN and before END IF are executed. IF X>100 THEN X := 100; END IF; Alternative statements to be executed if the condition is false can be specified using ELSE. IF X>100 THEN X := 100; Y := 0; ELSE Y := X; END IF; A sequence of conditions can be tested using ELSIF. An IF statement can have any number of ELSIF statements. Optionally, the sequence can end with ELSE. IF X<= 100 THEN Y := 'A'; ELSIF X<=200 THEN Y := 'B' ELSEIF X<=300 THEN Y := 'C' ELSE Y := 'Z' END IF Oracle 9i introduces a CASE statement, which is a more efficient way to implement long IF…ELSIF sequences. CASE…WHENOracle 9i's CASE is the equivalent of Java's switch control structure. The syntax is CASE selector WHEN option THEN statement; [statement;[...]] [WHEN option THEN statement; [statement;[...]] [...]] [ELSE statement; [statement;[...]]] END CASE; The ELSE case at the end is optional, but if you don't provide a default case, an error is raised if the selector does not match any of the WHEN options. This is useful only if you are prepared to handle the exception we'll take a look at the optional exception handler below. Oracle's CASE statement is more flexible than Java's because it is not limited to integer types. It can be used with strings, for example: CASE UPPER(USER_INPUT) WHEN 'HELLO' THEN RESPONSE := 'Hi there!'; WHEN 'GOODBYE' THEN RESPONSE := 'See you later!'; QUIT_FLAG := TRUE; ELSE RESPONSE := 'I''m sorry, I don''t understand.'; END CASE; Some caution needs to be exercised when using it with real numbers because they are not exact values you may need to use the ROUND() or TRUNC() functions to ensure consistent behavior. There is another form of the CASE statement, the searched CASE statement, that is semantically identical to the IF…ELSIF…ELSE control structure. It has the form: CASE WHEN condition THEN statement; [statement;[...]] [WHEN condition THEN statement; [statement;[...]] [...]] [ELSE statement; [statement;[...]]] END CASE; Using a searched CASE statement, we can rewrite the previous example as follows: CASE WHEN UPPER(USER_INPUT) = 'HELLO' THEN RESPONSE := 'Hi there!'; WHEN UPPER(USER_INPUT) = 'GOODBYE' THEN RESPONSE := 'See you later!'; QUIT_FLAG := TRUE; ELSE RESPONSE := 'I''m sorry, I don''t understand.'; END CASE; CASE ExpressionsThe CASE statement allows us to select statements to execute based on a selector or a set of conditions. But we can also use CASE to form an expression that returns a value based on a selector or set of conditions. The syntax for a CASE expression is: CASE selector WHEN option THEN value [WHEN option THEN value [...]] [ELSE value] END; Notice that the CASE expression takes END at the end, not END CASE. The following example is nearly identical to the previous examples, except that we are limited to making a single assignment. RESPONSE := CASE UPPER(USER_INPUT) WHEN 'HELLO' THEN 'Hi there!' WHEN 'GOODBYE' THEN 'See you later!' ELSE 'I''m sorry, I don''t understand.' END; Like the CASE statement, the CASE expression also has a form that uses conditions for each option instead of a selector. CASE WHEN condition THEN value [WHEN condition THEN value [...]] [ELSE value] END; The previous example, using the searched CASE format, is: RESPONSE := CASE WHEN UPPER(USER_INPUT)='HELLO' THEN 'Hi there!' WHEN UPPER(USER_INPUT)='GOODBYE' THEN 'See you later!' ELSE 'I''m sorry, I don''t understand.' END; LOOPThe LOOP statement lets you create infinite loops. This normally isn't useful by itself but, when used with an EXIT WHEN… statement to end conditionally, the LOOP statement can provide more flexibility than other control structures. PL/SQL, for example, does not have a control structure like Java's do...while, which ensures that the body of a loop is executed at least once, but LOOP with EXIT WHEN… as the last statement in the loop accomplishes the same end. The syntax for a LOOP is LOOP [statements]; [IF condition THEN EXIT [label]|EXIT [label] WHEN condition]; [statements]; END LOOP; For example, suppose we have a table, MANAGERS, listing employees in column NAME and their managers in a column MANAGER. If we know the name of an employee, we can work our way up the chain of command from that person like this: LOOP DBMS_OUTPUT.PUT_LINE(CURRENT_NAME); SELECT MANAGER INTO CURRENT_NAME FROM MANAGERS WHERE NAME=CURRENT_NAME; EXIT WHEN CURRENT_NAME IS NULL; END LOOP; Each time through the loop, the SELECT statement uses the current employee's name to get the manager's name, making the manager the new current employee. Eventually, we reach the top and come to an employee who has no manager; when the SELECT statement pulls up NULL, we exit the loop. WHILE…LOOPThe idea of a WHILE…LOOP should be familiar from Java. It repeats as long as a condition is true. The format is: WHILE condition LOOP statements; END LOOP; The following example will print out a string, MYSTR, letter by letter. WHILE LENGTH(MYSTR)>0 LOOP DBMS_OUTPUT.PUT_LINE(SUBSTR(MYSTR,1,1)); MYSTR := SUBSTR(MYSTR,2); END LOOP; It's worth remarking on two things about the condition in this example's WHILE…LOOP statement. First, like Java (but unlike certain other languages), PL/SQL requires that the condition be a Boolean value so the comparison with zero is necessary. Second, side effects aren't allowed, so we can't store the results of the call to LENGTH() in the condition for use inside the loop. If we needed that value inside the loop, we'd need either to call LENGTH() again or to modify our logic to store it in a variable and then test it probably by using an unconditional LOOP with an EXIT WHEN... statement, something like this: LOOP LEN := LENGTH(MYSTR); EXIT WHEN LEN=0; /*... */ END LOOP; FOR…LOOPIn PL/SQL, the FOR…LOOP is a relatively simple control structure. It allows iterating through a range of integral values, in either ascending or descending order, incrementing or decrementing by one each time. The format is: FOR counter IN [REVERSE] lower_bound expression … upper_bound expression LOOP statements; END LOOP; We can summarize the parts of the FOR…LOOP syntax:
The following example will calculate X factorial for values of X that are positive integers. For example, if X is 5, X factorial is 1*2*3*4*5 = 120. Real positive values of X that are not integers (such as 5.333) are rounded to the nearest integer. X_FACTORIAL := 1; FOR I IN 2 .. X LOOP X_FACTORIAL := X_FACTORIAL * I; END LOOP; DBMS_OUTPUT.PUT_LINE(X_FACTORIAL); (Notice that we don't need to start with 1 because if X is 1, the loop will not be executed at all and the initial value of X_FACTORIAL is already the right answer.) Sometimes we want to increment or decrement by something other than 1. This isn't possible in PL/SQL, but we can usually use a multiplier or some other type of expression within our loop to get the range of values we want. If this isn't possible, it's worth remembering that a FOR…LOOP is really just a WHILE…LOOP with most of the mechanics counter declaration and initialization, incrementing (or decrementing), etc. performed automatically. If we need more than that, we need to it ourselves. Exceptions and Exception HandlersThe final part of a PL/SQL block, optionally, is the EXCEPTION section. Exceptions in PL/SQL are similar in some ways to those in Java. During processing, a warning or error condition occurs, normal execution is stopped, and control is passed to exception handling code. Perhaps the biggest difference is that catching exceptions is optional in PL/SQL. It often makes sense to ignore them at the PL/SQL level and let them get passed up to the next level, then deal with them there. If we call a PL/SQL function from Java, for example, most JDBC methods already require us to catch SQLException, so it may make sense to handle PL/SQL errors together with all other database exceptions. We catch exceptions by name. For each error that we want to catch, we need a line such as the following in our exception section: WHEN exception_name THEN statements; System ExceptionsThe most common system exceptions have predefined names. For example, division by zero has the name DIVISION_BY_ZERO. The following example demonstrates catching a division by zero error and printing its Oracle error code and message. DECLARE X NUMBER; BEGIN X := 100/0; EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT('Error caught: '); DBMS_OUTPUT.PUT_LINE(SQLCODE()); DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE())); END; Generally, we catch system exceptions because we know something about the circumstances and know that it is safe to ignore the error because we know how to correct the error, or because we need to perform some other action before passing the exception on to the next higher level. For example, if our system allows users to log in to our application without registering, we may wish to catch the system exception NO_DATA_FOUND when we look up an unregistered user and make a temporary entry in the USERS table: BEGIN /* ... */ EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO USERS VALUES(LOGINNAME,'TEMP',SYSDATE); END; After we've handled an exception, it will not get propagated any further. If we want the next higher level to get the exception so that it can do additional exception processing, we need to re-raise the exception. We do this by using the RAISE statement. In an exception handler, the RAISE statement without any parameters re-raises the current exception: BEGIN /* ... */ EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO USERS VALUES(LOGINNAME, 'TEMP',SYSDATE); RAISE; END; Some system exceptions do not have predefined names. If you need to catch an unnamed system exception, it is possible to define your own names for system exceptions, using an exception declaration and a special compiler instruction, EXCEPTION_INIT. But the easiest way to catch an unnamed system exception is to use the catch-all exception name OTHERS, then use the error code to distinguish between the cases you want to deal with specially and the rest. BEGIN /* ... */ EXCEPTION WHEN OTHERS THEN IF SQLCODE=-60 THEN /* take special action */ END IF; END; Note that all Oracle error codes except NO_DATA_FOUND are negative. Using a Subblock to Continue after an ExceptionWhen an exception occurs, PL/SQL does not allow us to correct the error in our error handler then go back and pick up where we left off. If there is a situation where we can anticipate that a particular exception is likely to occur and can plan on fixing that error, we can put that code in a subblock with its own exception handler. After handling and correcting the exception, control returns to the enclosing block. In the following example, we populate a table with the tangent for a consecutive series of degrees. The important point to note about the tangent function is that it is undefined at 90 degrees and 270 degrees we'd like our table to reflect that fact by setting the tangent to NULL for those values. We'll create our table like this: CREATE TABLE TANGENTS ( DEGREES NUMBER, TANGENT NUMBER); This is our PL/SQL code for populating the table: BEGIN DELETE TANGENTS; FOR V_DEGREES IN 0..359 LOOP BEGIN INSERT INTO TANGENTS (DEGREES, TANGENT) VALUES (V_DEGREES,TAN(V_DEGREES*ACOS(0)/90)); EXCEPTION WHEN OTHERS THEN IF(SQLCODE=-1426) THEN INSERT INTO TANGENTS (DEGREES, TANGENT) VALUES (V_DEGREES, NULL); ELSE RAISE; END IF; END; END LOOP; COMMIT; END; The program loops through the degrees in a circle, from 0 to 359. Within this loop, where we expect that an error will occur, there is a subblock that contains the logic for performing the calculation and doing the insert. Notice that the subblock has access to the variable V_DEGREES, the loop counter from the enclosing block.
As the loop iterates, all is fine until V_DEGREES hits 90. This causes a numeric overflow ORA-01426. This is not a predefined error, so we can't catch it by name. Instead, we add a catch-all exception handler, OTHERS, and test the SQLCODE to see whether it's 1426. If it is, we insert using NULL for the tangent value. If it's not, we got some other unexpected error; we can re-raise that error and pass it to the enclosing block by calling RAISE. After running this program, we can select from the TANGENTS table and check the tangent at 90 and 270 degrees to see that it is, in fact, null. We'll include a couple of neighbors for context: SQL> SELECT * FROM TANGENTS 2 WHERE DEGREES >= 89 AND DEGREES <= 91 3 ORDER BY DEGREES; DEGREES TANGENT ---------- ---------- 89 57.2899616 90 91 -57.289962 SQL> SELECT * FROM TANGENTS 2 WHERE DEGREES >= 269 AND DEGREES <= 271 3 ORDER BY DEGREES; DEGREES TANGENT ---------- ---------- 269 57.2899616 270 271 -57.289962 User-Defined ExceptionsSometimes, cases that aren't exceptional to Oracle or to the system may be exceptional to us, and we want to discontinue normal processing and deal with the exception in a different way than the normal course of action. We can do this in one of two ways, depending on whether we want to handle the exception entirely in the PL/SQL block or pass the error back to the calling program. To handle a user-defined exception in our program, we first need to declare the exception in the declarations section of our PL/SQL block. Once we've done this, we can raise this exception in the code in our program code. This obligates us to provide an exception handler for this exception; otherwise, the calling program will get an unhandled user-defined exception error. The format for defining, raising, and catching an exception is: DECLARE [other declarations] exception_name EXCEPTION; [other declarations] BEGIN [program statements] IF exception_condition THEN RAISE exception_name; END IF; [program statements] EXCEPTION [other exception handlers] WHEN exception_name THEN exception handling code; [other exception handlers] END; An exception handler cannot return a value, so user-defined exceptions aren't useful for functions because the failure to return a value causes an additional error. Let's consider a procedure to process an order. It will total the values of a customer's line items in an order and compare it with the customer's credit. If there's enough credit, we'll change the status of the line items to SHIP and deduct the total from the customer's credit line. Otherwise, we'll raise an exception, change the status of the order to NO CREDIT, and add a record to an exceptions table. We'll need several tables for this example. Normally, we would create a table containing general information for the order as a whole, but we'll omit that in this example for the sake of simplicity. We'll include only a table for the order's line items: CREATE TABLE ORDERS ( ORDER_ID NUMBER, CUSTOMER_ID NUMBER, ITEM_NUM NUMBER, QUANTITY NUMBER, PRICE NUMBER, STATUS VARCHAR2(12) ); Next, a table for storing information on how much credit the customer has in the system: CREATE TABLE CUSTOMER_CREDITS ( CUSTOMER_ID NUMBER, CREDIT NUMBER ); Finally, a table for logging any exceptions: CREATE TABLE EXCEPTIONS ( ORDER_ID NUMBER, CUSTOMER_ID NUMBER, EXCEPTION_NUMBER NUMBER, EXCEPTION_MESSAGE VARCHAR2(200) ); And we'll need to populate the tables with some data: INSERT INTO ORDERS VALUES(100, 25, 23001, 1, 3.25,'PENDING'); INSERT INTO ORDERS VALUES(100, 25, 67023, 5, 4.95,'PENDING'); INSERT INTO ORDERS VALUES(100, 25, 33320, 1, 18.00,'PENDING'); INSERT INTO ORDERS VALUES(100, 25, 23102, 10, 9.95,'PENDING'); INSERT INTO CUSTOMER_CREDITS VALUES(25, 100); Notice that the order totals 145.50, but the customer has a credit line of only 100. We haven't been formally introduced to procedures yet, but for now it will suffice to say that we need to replace the DECLARE statement with a CREATE PROCEDURE statement that defines the procedure's arguments, followed by local declarations, including any exception declarations. CREATE PROCEDURE PROCESS_ORDER (ORDER_NUMBER NUMBER) AS CREDIT_EXCEPTION EXCEPTION; ORDER_TOTAL NUMBER; CUSTOMER NUMBER; CREDIT_LIMIT NUMBER; BEGIN SELECT SUM(PRICE*QUANTITY), MAX(CUSTOMER_ID) INTO ORDER_TOTAL, CUSTOMER FROM ORDERS WHERE ORDER_ID=ORDER_NUMBER AND STATUS='PENDING'; SELECT CREDIT INTO CREDIT_LIMIT FROM CUSTOMER_CREDITS WHERE CUSTOMER_ID=CUSTOMER; IF ORDER_TOTAL>CREDIT_LIMIT THEN RAISE CREDIT_EXCEPTION; END IF; UPDATE ORDERS SET STATUS='SHIP' WHERE ORDER_ID=ORDER_NUMBER AND STATUS='PENDING'; UPDATE CUSTOMER_CREDITS SET CREDIT=CREDIT-ORDER_TOTAL WHERE CUSTOMER_ID=CUSTOMER; COMMIT; EXCEPTION WHEN CREDIT_EXCEPTION THEN UPDATE ORDERS SET STATUS='NO CREDIT' WHERE ORDER_ID=ORDER_NUMBER AND STATUS='PENDING'; INSERT INTO EXCEPTIONS VALUES(ORDER_NUMBER, CUSTOMER, 1, 'Order total exceeds credit.'); END; / We can call this procedure interactively from an SQL*Plus prompt by using a CALL command. SQL> CALL PROCESS_ORDER(100); Call completed. We can verify the effects of this procedure by selecting from the EXCEPTIONS table. SQL> SELECT * FROM EXCEPTIONS; ORDER_ID CUSTOMER_ID EXCEPTION_NUMBER EXCEPTION_MESSAGE -------- ----------- ---------------- -------------------------------- 100 25 1 Order total exceeds credit. We can also see that the ORDERS table has been affected the STATUS column has been changed. SQL> SELECT * FROM ORDERS; ORDER_ID CUSTOMER_ID ITEM_NUM QUANTITY PRICE STATUS -------- ------------ --------- ---------- ----- ---------- 100 25 23001 1 3.25 NO CREDIT 100 25 67023 5 4.95 NO CREDIT 100 25 33320 1 18.00 NO CREDIT 100 25 23102 10 9.95 NO CREDIT User-defined exceptions should be used sparingly because they introduce dependencies between code in the database and application code. In general, they should only be used for conditions that should not normally arise; that is, conditions that are truly exceptional. The example here is not very good in this respect because customers are likely to exceed their credit limit fairly as a matter of course. Returning Error Codes to an ApplicationIf we want to return a user-defined exception to a calling program instead of handling it in our PL/SQL block, we can call the RAISE_APPLICATION _ERROR() procedure. This procedure part of the DBMS_STANDARD package that is normally installed when a database is created allows us to select our own error code and message to be returned to the calling application. It accepts either two or three parameters. RAISE_APPLICATION_ERROR(error_number, error_description [,add_to_stack]) The parameters for this procedure are:
Unlike user-defined exceptions declared in the DECLARE section, an exception raised with RAISE_APPLICATION_ERROR() cannot be caught with a handler in the PL/SQL EXCEPTIONS section. Instead, the program stops and the error is immediately returned to the calling application. In Java, this will be caught as an SQLException. The SQLException method getErrorCode() will retrieve the error converted to a positive number. We can change the previous example to notify the calling program and let the program take whatever action is appropriate perhaps prompting the user to modify the order or to augment the credit in the system. To do so, we need to remove the declaration of CREDIT_EXCEPTION and the corresponding exception handler. Then we replace the RAISE statement with RAISE_APPLICATION_ERROR(), as follows: CREATE OR REPLACE PROCEDURE PROCESS_ORDER (ORDER_NUMBER NUMBER) AS ORDER_TOTAL NUMBER; CUSTOMER NUMBER; CREDIT_LIMIT NUMBER; BEGIN SELECT SUM(PRICE*QUANTITY), MAX(CUSTOMER_ID) INTO ORDER_TOTAL, CUSTOMER FROM ORDERS WHERE ORDER_ID=ORDER_NUMBER AND STATUS='PENDING'; SELECT CREDIT INTO CREDIT_LIMIT FROM CUSTOMER_CREDITS WHERE CUSTOMER_ID=CUSTOMER; IF ORDER_TOTAL>CREDIT_LIMIT THEN RAISE_APPLICATION_ERROR(-20201, 'Order total exceeds credit'); END IF; UPDATE ORDERS SET STATUS='SHIP' WHERE ORDER_ID=ORDER_NUMBER AND STATUS='PENDING'; UPDATE CUSTOMER_CREDITS SET CREDIT=CREDIT-ORDER_TOTAL WHERE CUSTOMER_ID=CUSTOMER; COMMIT; END; / If we call this procedure from Java, it will generate an SQLException exception. We can call the exception's getErrorCode method to obtain the specific error number in this case, 20201 as shown in this code snippet: try { /* Register JDBC driver, get Connection, get Statement stmt ...*/ stmt.executeUpdate("CALL PROCESS_ORDER(" + orderNumber + ")"); } catch (SQLException e) { if(e.getErrorCode()==20201) { /* Take appropriate action for insufficient credit */ } } Notice that, although we specify a negative number in PL/SQL, 20201, the error code that Java receives is positive, 20201. Stored Procedures, Functions, and Database TriggersAnonymous blocks are useful for learning PL/SQL, but except for the occasional utility program, they are rarely useful in building applications. Instead, we generally need to use functions and procedures programs that are stored inside the database and can be called by SQL statements or by other applications. One special type of procedure, the database trigger, is particularly useful for providing logging or data integrity checks because it is called automatically when a table is changed. The key difference between functions and procedures is that a function returns a value but a procedure does not. This, in turn, affects how they can be used. Functions are usually called in an SQL statement, for example, in a select list or in a WHERE clause; because of this, they are also subject to certain restrictions. Procedures are usually called from other programs, such as Java programs or database triggers; procedures can also be invoked interactively in SQL*Plus, using the CALL command. In terms of syntax, procedures and functions differ from anonymous blocks mainly in the header. Instead of DECLARE, we use a CREATE FUNCTION, CREATE PROCEDURE, or CREATE TRIGGER statement to start off the function or procedure. We'll look at each of these separately. PL/SQL FunctionsThe format for creating a PL/SQL function is: CREATE [OR REPLACE] FUNCTION function_name [(parameter_list)] RETURN return_type {AS|IS} [local_declarations] BEGIN program_statements [EXCEPTION exception_handlers] END; Note that:
We'll start with a simple example a function that takes a name and returns a greeting using that name: CREATE FUNCTION GREET (NAME VARCHAR2) RETURN VARCHAR2 AS /* no local declarations */ BEGIN RETURN 'Hello, '||NAME||'!'; END; Like previous examples, we can either type this at the sqlplus prompt or we can save this in a file greet.sql, for example and run it with the START command. SQL> START GREET 8 / Function created. Notice that running greet.sql only creates the function. To call it, we can use the select list of an SQL statement querying the dummy table DUAL: SQL> SELECT GREET('Molly') FROM DUAL; GREET('MOLLY') ------------------------------------------ Hello, Molly! There are a number of rules that a function is required to follow in order to prevent side effects. Principally, these restrictions are:
Let's take a look at another example, a function designed to make it easier to display names from a table in a culturally correct way. One of the problems we face when designing an application to work in an international context is that different cultures have different rules for names. English speakers sometimes make the assumption that last name, family name, and surname all refer to the same thing. When a name is broken up into different fields in a database, these fields are frequently called FIRST_NAME, MIDDLE_INITIAL, and LAST_NAME (or something equivalent). But in some countries, such as China, the family name is the first name. In Spanish-speaking countries, people usually use both paternal and maternal family names (in that order), and the second-to-last name is the surname. In other countries, such as Indonesia (and particularly in Java), people often do not use family names but go by one or more given names. To solve this problem, we'll use a table with a given name, surname (which can be one or more family names to solve the problem with multiple last names), and a locale code. (A locale is a two-letter language code from ISO-639 plus, optionally, a country or region code from ISO-3166, and possibly an additional, nonstandard, variant code. We'll use locales, rather than Oracle's NLS parameters because locales are standards-based and because they are what we need to use in Java.) Our table looks like this: CREATE TABLE DIRECTORY ( GIVEN_NAME VARCHAR2(32), SURNAME VARCHAR2(32), LOCALE VARCHAR2(8)); We'll also have a table that maps a locale to surname order. In addition to locale, this table also has a CHAR(1) that we'll use as Boolean value, 'F' or 'T', to indicate whether the surname is the first name. CREATE TABLE SURNAME_ORDER ( LOCALE VARCHAR2(8), SURNAME_FIRST CHAR(1)); Here is some data: INSERT INTO DIRECTORY VALUES('Po', 'Li', 'zh'); INSERT INTO DIRECTORY VALUES('Gabriel', 'García Marquez', 'es'); INSERT INTO DIRECTORY VALUES('Eni Su''aida', NULL, 'id'); INSERT INTO SURNAME_ORDER VALUES('zh', 'T'); INSERT INTO SURNAME_ORDER VALUES('es', 'F'); INSERT INTO SURNAME_ORDER VALUES('id', 'F'); Notice that the DIRECTORY table does not represent the way the data is to be displayed; it maintains a distinction only between surname and given name. The display order is determined by the second table, the SURNAME _ORDER table. In those cases where the given name is the only name, it will come first, of course. The following function, DISPLAY_NAME, uses the SURNAME_ORDER table to determine in which order to concatenate a surname and given name pair, and returns a string with the name in the correct order. CREATE FUNCTION DISPLAY_NAME (IN_GIVEN_NAME VARCHAR2, IN_SURNAME VARCHAR2, IN_LOCALE VARCHAR2) RETURN VARCHAR2 AS NAME VARCHAR2(65); V_SURNAME_FIRST CHAR(1); BEGIN SELECT SURNAME_FIRST INTO V_SURNAME_FIRST FROM SURNAME_ORDER WHERE LOCALE=IN_LOCALE; IF V_SURNAME_FIRST='T' THEN NAME := IN_SURNAME||' '||IN_GIVEN_NAME; ELSE NAME := IN_GIVEN_NAME||' '||IN_SURNAME; END IF; RETURN NAME; END; / We can use this function in a SELECT statement querying the DIRECTORY table as follows: SELECT DISPLAY_NAME(GIVEN_NAME, SURNAME, LOCALE) FROM DIRECTORY; Notice that this function will fail if a valid locale is not found. One way to ensure that this doesn't happen is to make the LOCALE column in the DIRECTORY table a foreign key that references the SURNAME_ORDER table. We can make data entry easier by providing a default value for the table. To do these two things, we need to make LOCALE a primary key in the SURNAME_ORDER table, and we need to make LOCALE a foreign key in the DIRECTORY table. To do this, we need to create the tables like this: CREATE TABLE SURNAME_ORDER ( LOCALE VARCHAR2(8), SURNAME_FIRST CHAR(1), CONSTRAINT LOCALE_PK PRIMARY KEY (LOCALE) ); CREATE TABLE DIRECTORY ( GIVEN_NAME VARCHAR2(32), SURNAME VARCHAR2(32), LOCALE VARCHAR2(8) DEFAULT 'DEFAULT', CONSTRAINT LOCALE_SURNAME_ORDER_FK FOREIGN KEY (LOCALE) REFERENCES SURNAME_ORDER(LOCALE) ); Next, we need to make an entry the SURNAME_ORDER table to support this default value. INSERT INTO SURNAME_ORDER VALUES('DEFAULT', 'F'); We can also provide a default value programmatically by trapping the NO_DATA_FOUND exception that occurs when we query the SURNAME _ORDER table for a value that doesn't exist by using a subblock for the SELECT statement. /* ... */ BEGIN BEGIN SELECT SURNAME_FIRST INTO V_SURNAME_FIRST FROM SURNAME_ORDER WHERE LOCALE=IN_LOCALE; EXCEPTION WHEN NO_DATA_FOUND THEN V_SURNAME_FIRST = 'F' END; IF V_SURNAME_FIRST='T' THEN NAME := IN_SURNAME||' '||IN_GIVEN_NAME; /* ... */ Either method makes it much easier to deal with cases where we don't know what language or country to assign to a name, but because it's easy to omit this data, it's tempting to do so. This leads to problems of data integrity and consistency that we need to solve some other way. Further, none of this addresses the practical matter that most people do not know the two-letter codes from IS0-639 and ISO-3166. An application that supports internationalization would need to provide a reasonable user interface for selecting language and country but this is an application design issue that we can't really address here. Let's take a look at one more function, a function that will assist us in sorting titles. Its primary job is to see whether the first word of a title (a word or a phrase) is an article and, if so, add a comma to the phrase and move the article to the end. "The Doors" becomes "Doors, The", for example. To support this function, we'll need a table of articles. To provide international support, we'll add a locale column so we can include articles in different languages. CREATE TABLE ARTICLES ( ARTICLE VARCHAR2(12), LOCALE VARCHAR2(8)); We'll provide our data in uppercase: INSERT INTO ARTICLES VALUES('THE', 'en'); INSERT INTO ARTICLES VALUES('A', 'en'); INSERT INTO ARTICLES VALUES('EL', 'es'); INSERT INTO ARTICLES VALUES('LA', 'es'); INSERT INTO ARTICLES VALUES('LOS', 'es'); INSERT INTO ARTICLES VALUES('LAS', 'es'); For this function, we'll provide a default locale in our parameter list. This lets us call the function with either a title and a locale or with just a title. If it's the latter, the best thing to do would be to default to the locale that corresponds to the NLS_LANGUAGE session parameter (which would require another table mapping Oracle NLS_LANGUAGE parameters to our Java-style locales); but for this example, we'll just default to English: CREATE OR REPLACE FUNCTION PARSE_TITLE (IN_TITLE VARCHAR2, IN_LOCALE VARCHAR2 DEFAULT 'en') RETURN VARCHAR2 AS IS_ARTICLE BOOLEAN; WORD_BREAK NUMBER; OUT_TITLE VARCHAR2(100); ARTICLE_COUNT VARCHAR2(12); BEGIN WORD_BREAK := INSTR(RTRIM(IN_TITLE), ' '); SELECT COUNT(ARTICLE) INTO ARTICLE_COUNT FROM ARTICLES WHERE ARTICLE=UPPER(SUBSTR(IN_TITLE,1,WORD_BREAK 1)) AND LOCALE=IN_LOCALE; IF ARTICLE_COUNT = 0 THEN IS_ARTICLE := FALSE; ELSE IS_ARTICLE := TRUE; END IF; IF IS_ARTICLE AND LENGTH(IN_TITLE) > WORD_BREAK THEN OUT_TITLE := SUBSTR(IN_TITLE, WORD_BREAK + 1) || ', ' || SUBSTR(IN_TITLE,1, WORD_BREAK 1); ELSE OUT_TITLE := IN_TITLE; END IF; RETURN OUT_TITLE; END; / Let's try a few tests: SQL> SELECT PARSE_TITLE('The Pixies') FROM DUAL; PARSE_TITLE('THEPIXIES') -------------------------------------------------------- Pixies, The SQL> SELECT PARSE_TITLE('Los Lobos') FROM DUAL; PARSE_TITLE('LOSLOBOS') -------------------------------------------------------- Los Lobos SQL> SELECT PARSE_TITLE('Los Lobos', 'es') FROM DUAL; PARSE_TITLE('LOSLOBOS','ES') -------------------------------------------------------- Lobos, Los We'll visit this function again later when we examine database triggers. PL/SQL Stored ProceduresThe format for creating a procedure differs from a function principally in that it has no return value. CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_list)] {AS|IS} [local_declarations] BEGIN program_statements [EXCEPTION exception_handlers] END; By default, the parameters in the parameter list are for input only. They can, however, be used for output, too, to pass a value back to the calling program. (This is true for functions, too, but it's poor practice, particularly because functions are not supposed to have side effects.) The format of a parameter list item is parameter_name [IN|OUT|IN OUT] datatype where IN indicates an input parameter, OUT indicates an output parameter, and IN OUT indicates a parameter used for both input and output. An IN parameter is the type we are most familiar with. It is a value provided by the calling program that we can use inside our procedure. We are not allowed to change it in our procedure, so it is essentially a constant. OUT and IN OUT parameters allow the calling program, in effect, to pass a variable by reference. This allows our procedure to change the value of the variable in the calling program. The difference between OUT and IN OUT is that an OUT parameter is initialized to NULL in our procedure, but an IN OUT parameter retains whatever value the calling program set. Let's take a look at some code that exemplifies this behavior. First we'll create a procedure that takes a variable of each type. CREATE OR REPLACE PROCEDURE CALLEE (INVAR IN VARCHAR2, OUTVAR OUT VARCHAR2, INOUTVAR IN OUT VARCHAR2) AS BEGIN DBMS_OUTPUT.PUT_LINE('- In CALLEE Before changing variables'); DBMS_OUTPUT.PUT_LINE('- INVAR: '||INVAR); DBMS_OUTPUT.PUT_LINE('- OUTVAR: '||OUTVAR); DBMS_OUTPUT.PUT_LINE('- INOUTVAR: '||INOUTVAR); /* INVAR := 'Invar changed'; Not allowed */ OUTVAR := 'Outvar changed'; INOUTVAR := 'Inoutvar changed'; DBMS_OUTPUT.PUT_LINE('- In CALLEE After changing variables'); DBMS_OUTPUT.PUT_LINE('- INVAR: '||INVAR); DBMS_OUTPUT.PUT_LINE('- OUTVAR: '||OUTVAR); DBMS_OUTPUT.PUT_LINE('- INOUTVAR: '||INOUTVAR); END; / Now we'll create another procedure to call our CALLEE procedure. CREATE OR REPLACE PROCEDURE CALLING_PROCEDURE AS FIRSTVAR VARCHAR2(25) := 'Original first var'; SECONDVAR VARCHAR2(25) := 'Original second var'; THIRDVAR VARCHAR2(25) := 'Original third var'; BEGIN DBMS_OUTPUT.PUT_LINE('Before calling CALLEE:'); DBMS_OUTPUT.PUT_LINE('FIRSTVAR: '|| FIRSTVAR); DBMS_OUTPUT.PUT_LINE('SECONDVAR: '|| SECONDVAR); DBMS_OUTPUT.PUT_LINE('THIRDVAR: '|| THIRDVAR); DBMS_OUTPUT.PUT_LINE('---'); CALLEE(FIRSTVAR, SECONDVAR, THIRDVAR); DBMS_OUTPUT.PUT_LINE('---'); DBMS_OUTPUT.PUT_LINE('After calling CALLEE:'); DBMS_OUTPUT.PUT_LINE('FIRSTVAR: '|| FIRSTVAR); DBMS_OUTPUT.PUT_LINE('SECONDVAR: '|| SECONDVAR); DBMS_OUTPUT.PUT_LINE('THIRDVAR: '|| THIRDVAR); END; / Here are the results of running the second procedure. SQL> CALL CALLING_PROCEDURE(); Before calling CALLEE: FIRSTVAR: Original first var SECONDVAR: Original second var THIRDVAR: Original third var --- - In CALLEE - Before changing variables - INVAR: Original first var - OUTVAR: - INOUTVAR: Original third var - In CALLEE - After changing variables - INVAR: Original first var - OUTVAR: Outvar changed - INOUTVAR: Inoutvar changed --- After calling CALLEE: FIRSTVAR: Original first var SECONDVAR: Outvar changed THIRDVAR: Inoutvar changed Call completed. Let's investigate the fates of our three parameters. In CALLING _PROCEDURE, we assigned a value to FIRSTVAR before passing it as an IN parameter to CALLEE. Its value is, indeed, retained when we first print it out in CALLEE. If you try to compile the CALLEE with the assignment to INVAR, you will get an error indicating that INVAR is not allowed as an assignment target, demonstrating that an IN variable is, in fact, a constant we cannot change. In CALLING_PROCEDURE, we assigned a value to SECONDVAR before passing it as an OUT parameter to CALLEE, but when we first print it out in CALLEE, we see that it has not retained that value it is initialized to NULL in CALLEE. As expected for an OUT variable, the value we assign it in CALLEE is retained when we return and print it out in CALLING_PROCEDURE. In CALLING_PROCEDURE, we assigned a value to THIRDVAR before passing it as an IN OUT parameter to CALLEE. In this case, when we first print it out in CALLEE, we see that it still has the value we assigned it in CALLING_PROCEDURE. And, like the OUT variable, when we assign it a value in CALLEE, it retains that value when it is returned to CALLING _PROCEDURE. We'll see a more detailed example of a procedure when we examine how PL/SQL interacts with SQL and Oracle, using cursors. Database TriggersDatabase triggers are procedures that run automatically when a table is modified. Triggers are associated with INSERT, UPDATE, and DELETE SQL statements affecting a specific table. They can be set to run either before or after the SQL statement. The basic format for a database trigger is: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} dml_event_list [FOR EACH ROW [WHEN condition]] {CALL procedure}|{pl/sql block}; where dml_event_list is one or more of the following, separated by OR: INSERT UPDATE [OF column_name1[, column_name2 [...]]] DELETE A trigger can be executed either BEFORE or AFTER the SQL statement makes its changes to the table. A BEFORE trigger has the advantage of being able to validate or modify data before it is inserted into a table. An AFTER trigger has the advantage of being able to query or change the table after integrity constraints have been applied and the table is in a consistent state. Specifying FOR EACH ROW causes the trigger to be executed for each row that is modified by the SQL statement this is called a row trigger. By default, a trigger is a statement trigger and is executed only once for the SQL statement that triggered it. The WHEN clause specifies a condition that must be satisfied for the trigger to be executed. Because the WHEN clause must be evaluated on a per-row basis, the FOR EACH ROW option must also be specified. The trigger may call a procedure like those we saw in the previous section or it may include the executable code in a PL/SQL block of its own. If we include a PL/SQL block in our trigger, we automatically have access to a number of useful variables. First are the correlation names that allow you to refer to the old and new values of a row. You can change the correlation names by adding a REFERENCING clause to the CREATE TRIGGER STATEMENT, but by default the names are OLD and NEW. We can access the old or new values of a column as variables in a BEFORE trigger using this format: :OLD.columnname :NEW.columnname If we have a BEFORE trigger on a table that has a column named VALUE, we could conceivably have some code in our trigger's body similar to the following that limits the amount by which a column's value can increase. /* ... */ IF :NEW.VALUE > :OLD.VALUE * 1.10 THEN :NEW.VALUE := :OLD.VALUE * 1.10; END IF; /* ... */ In the case of an UPDATE trigger, any :NEW column values not set by the UPDATE statement take their values from the :OLD values. In an AFTER trigger, we can read only the :OLD values, which, of course, are the table's updated values. Another useful set of variables available to us in a trigger's PL/SQL block lets us determine why the trigger is being executed. The following are Boolean values we can use in our PL/SQL block.
To take a look at an example of why and how we might use a trigger, let's return once again our CD collection database. One issue that we haven't addressed is how to sort the artists by name. When we first created our tables back in Chapter 2, we didn't consider this issue because a solution would cloud the basic issues we were first learning then. In brief, there are two problems we face. The first is the internationalization issue that we saw earlier in this chapter: A person's surname is not always his or her last name. The second is the problem that the rules are different for sorting individuals' names, as opposed to groups' names; groups do not have surnames, so they sort by what we could consider to be their given names, but they have the complication that we generally need to ignore articles such as The. Earlier in this chapter we considered these issues and wrote functions to solve them. We can use these two functions in our triggers but we'll need to change our CD_COLLECTION table a bit. To support different types of names properly we're going to need to:
This information is specific to the artist or group, not the CD, so it makes sense to make this a separate table for artist information. We could probably use the SORT_NAME column as a primary key but it's more convenient and efficient to have a numeric key, so we'll add an ARTIST_ID field too. CREATE TABLE ARTISTS ( ARTIST_ID NUMBER, NAME VARCHAR2(50), SURNAME VARCHAR2(50), IS_GROUP CHAR(1), SORT_NAME VARCHAR2(102), LOCALE VARCHAR2(8), CONSTRAINT ARTIST_PK PRIMARY KEY (ARTIST_ID) ); We'll need a sequence to generate unique ARTIST_ID numbers. CREATE SEQUENCE ARTIST_ID_SEQ; Our trigger will use the BEFORE INSERT option because we need to do some validation and processing of the data before adding it to the ARTISTS table. It will be a row trigger because we need to process individual rows hence the FOR EACH ROW option. We will do the following when someone inserts or updates a row in the ARTISTS table:
Here is the function. CREATE OR REPLACE TRIGGER ARTISTS_TRIGGER BEFORE INSERT OR UPDATE OF NAME, SURNAME, IS_GROUP, LOCALE ON ARTISTS FOR EACH ROW DECLARE IS_SURNAME_FIRST CHAR(1); BEGIN DBMS_OUTPUT.PUT_LINE('ARTISTS_TRIGGER ...'); /* Set default locale */ IF :NEW.LOCALE IS NULL THEN :NEW.LOCALE := 'en'; END IF; /* Group or individual? */ IF :NEW.IS_GROUP='T' THEN DBMS_OUTPUT.PUT('group - '); /* A group cannot have a surname */ IF :NEW.SURNAME IS NOT NULL THEN RAISE_APPLICATION_ERROR(-20501, 'Group cannot have surname'); ELSE SELECT PARSE_TITLE(:NEW.NAME, :NEW.LOCALE) INTO :NEW.SORT_NAME FROM DUAL; END IF; ELSE /* Individual */ DBMS_OUTPUT.PUT('individual - '); /* if surname is normally first, don't use comma */ SELECT SURNAME_FIRST INTO IS_SURNAME_FIRST FROM SURNAME_ORDER WHERE LOCALE=:NEW.LOCALE; IF IS_SURNAME_FIRST='T' THEN :NEW.SORT_NAME := :NEW.SURNAME || ' ' || :NEW.NAME; ELSE :NEW.SORT_NAME := :NEW.SURNAME || ', ' || :NEW.NAME; END IF; END IF; DBMS_OUTPUT.PUT_LINE(:NEW.SORT_NAME); END; / We'll insert these values. INSERT INTO ARTISTS(ARTIST_ID, NAME, SURNAME, IS_GROUP, LOCALE) VALUES(ARTIST_ID_SEQ.NEXTVAL, 'Jian', 'Cui', 'F', 'zh'); INSERT INTO ARTISTS(ARTIST_ID, NAME, SURNAME, IS_GROUP, LOCALE) VALUES(ARTIST_ID_SEQ.NEXTVAL, 'Laurie', 'Anderson', 'F', 'en'); INSERT INTO ARTISTS(ARTIST_ID, NAME, SURNAME, IS_GROUP, LOCALE) VALUES(ARTIST_ID_SEQ.NEXTVAL, 'A Perfect Circle', NULL, 'T', 'en'); INSERT INTO ARTISTS(ARTIST_ID, NAME, SURNAME, IS_GROUP, LOCALE) VALUES(ARTIST_ID_SEQ.NEXTVAL, 'Pearl Jam', NULL, 'T', 'es'); We'll select the SORT_NAME column to see what the trigger produced. SQL> SELECT SORT_NAME FROM ARTISTS; SORT_NAME ----------------------------------- Cui Jian Anderson, Laurie Perfect Circle, A Pearl Jam To display these properly to a user, we would use the DISPLAY _NAME() function we created a few sections ago in the select list and sort by putting the SORT_NAME column in an ORDER BY clause. It doesn't matter with our particular set of data, as it happens, but when we are sorting names in languages other than English, we need to enable linguistic sorting by setting the NLS_SORT session parameter to our preferred language or to a generic linguistic sort, such as WEST_EUROPEAN or, in Oracle 9i, GENERIC_M. (See Chapter 4 for more information about linguistic sorting.) The following command, as you may remember, will accomplish this in SQL*Plus: SQL> ALTER SESSION SET NLS_SORT='WEST_EUROPEAN'; Session altered. Now our data will be displayed in a multiculturally correct order and format. SQL> SELECT DISPLAY_NAME(NAME, SURNAME, LOCALE) 2 FROM ARTISTS 3 ORDER BY SORT_NAME; DISPLAY_NAME(NAME,SURNAME,LOCALE) ------------------------------------------------- Laurie Anderson Cui Jian Pearl Jam A Perfect Circle Cursors and Cursor LoopsWe've seen that the biggest benefit of using PL/SQL to write procedural database programs is that it lets us easily use SQL statements directly in our code. PL/SQL always performs database operations using a memory buffer called a cursor. In the statements we've seen so far, PL/SQL has taken care of creating and using this cursor automatically. An automatic cursor of this type is called an implicit cursor. By limiting ourselves to implicit cursors, we've restricted the types of queries we can perform to those that return only a single row. To perform more general queries that can return multiple rows, we can either declare and manage an explicit cursor ourselves, using OPEN, FETCH…INTO, and CLOSE cursor statements or we can use a cursor FOR…LOOP to take care of the details for us. We'll first see how to manage a cursor ourselves. Declaring and Using a CursorA cursor is associated with a specific query. The first step in using one is to declare it in the DECLARE section of our PL/SQL block. The basic format for declaring a cursor is CURSOR cursor_name IS select_statement; The cursor_name is an identifier for the cursor it associates a name with a memory buffer used for performing database operations. The select_statement can be any valid SQL SELECT statement. Suppose we want to retrieve the artists listed in the ARTISTS table from the last example. We can declare a cursor like this: DECLARE CURSOR C_ARTISTS IS SELECT * FROM ARTISTS ORDER BY SORT_NAME;
Declaring a cursor does not actually execute the query. To execute the query, we must use an OPEN statement in the BEGIN section of our PL/SQL block using the following format: OPEN cursor_name; Once the cursor is open, we can begin retrieving data from it, row by row, using the FETCH…INTO statement. The FETCH…INTO statement requires us to provide a variable or variables to store the values retrieved. FETCH cursor_name INTO variable_list; When we are finished using a cursor, we need to close the cursor using the CLOSE statement. CLOSE cursor_name; Let's use the cursor we previously declared to retrieve a row from the ARTISTS table. We'll declare variables of the appropriate type, using the %TYPE attribute of the columns in the ARTISTS table. DECLARE CURSOR C_ARTISTS IS SELECT * FROM ARTISTS ORDER BY SORT_NAME; V_ARTIST_ID ARTISTS.ARTIST_ID%TYPE; V_NAME ARTISTS.NAME%TYPE; V_SURNAME ARTISTS.SURNAME%TYPE; V_IS_GROUP ARTISTS.IS_GROUP%TYPE; V_SORT_NAME ARTISTS.SORT_NAME%TYPE; V_LOCALE ARTISTS.LOCALE%TYPE; BEGIN OPEN C_ARTISTS; FETCH C_ARTISTS INTO V_ARTIST_ID, V_NAME, V_SURNAME, V_IS_GROUP, V_SORT_NAME, V_LOCALE; DBMS_OUTPUT.PUT_LINE(V_SORT_NAME); END; / We can use functions or expressions in our select list, too, but we need to assign them aliases. Here, we'll use the cursor's %ROWTYPE to define a record. We'll include the DISPLAY_NAME() function with the alias DISPLAY_NAME in a cursor's select list so that we can access it using dot notation like a column. DECLARE CURSOR C_ARTISTS IS SELECT DISPLAY_NAME(NAME, SURNAME, LOCALE) AS DISPLAY_NAME FROM ARTISTS ORDER BY SORT_NAME; C_ARTISTS_REC C_ARTISTS%ROWTYPE; BEGIN OPEN C_ARTISTS; FETCH C_ARTISTS INTO C_ARTISTS_REC; DBMS_OUTPUT.PUT_LINE(C_ARTISTS_REC.DISPLAY_NAME); CLOSE C_ARTISTS; END; / There's little point in using a cursor that returns multiple rows if we fetch only one. Although we could use multiple FETCH statements, it's obviously better to use a loop to process multiple rows. We have at our disposal two cursor attributes that can help %FOUND and %NOTFOUND. We can use these in the following way: LOOP FETCH cursor_name INTO variable_list; IF cursor_name%FOUND THEN /* process rows */ ELSE EXIT; ENDIF; END LOOP; Or, better: LOOP FETCH cursor_name INTO variable_list; IF cursor_name%NOTFOUND THEN EXIT; END IF; /* process rows */ END LOOP; We can display the complete list of artists with the following: DECLARE CURSOR C_ARTISTS IS SELECT DISPLAY_NAME(NAME, SURNAME, LOCALE) AS DISPLAY_NAME FROM ARTISTS ORDER BY SORT_NAME; C_ARTISTS_REC C_ARTISTS%ROWTYPE; BEGIN OPEN C_ARTISTS; LOOP FETCH C_ARTISTS INTO C_ARTISTS_REC; IF C_ARTISTS%NOTFOUND THEN EXIT; END IF; DBMS_OUTPUT.PUT_LINE(C_ARTISTS_REC.DISPLAY_NAME); END LOOP; CLOSE C_ARTISTS; END; / Cursor Parameters and Return ValuesCursors have two other options that we've omitted from the basic format we saw above. They can accept parameters and they can return a value. The complete format for declaring a cursor is CURSOR cursor_name [(parameter_list)] [RETURN return_type] IS select_statement; The return_type is used in creating cursor specifications for inclusion in a package; we will not cover that here. The parameter_list is similar to the parameter list for a function or procedure. Multiple parameters are separated by commas. Each parameter has the format parameter_name datatype [{DEFAULT| := } default_value] We can use a parameter_name in the cursor's query in the same way that we can use PL/SQL variables in SQL statements that use an implicit cursor. The parameters are passed when the cursor is opened, like this: OPEN cursor_name(parameters); For example, we could alter the last example to display a range of names by passing the cursor start and end parameters and using them in a WHERE clause. DECLARE CURSOR C_ARTISTS (START_NAME VARCHAR2, END_NAME VARCHAR2) IS SELECT DISPLAY_NAME(NAME, SURNAME, LOCALE) AS DISPLAY_NAME FROM ARTISTS WHERE SORT_NAME>= START_NAME AND SORT_NAME<= END_NAME ORDER BY SORT_NAME; C_ARTISTS_REC C_ARTISTS%ROWTYPE; BEGIN OPEN C_ARTISTS('A', 'M'); /* ... */ Normally, of course, the START_NAME and END_NAME parameters would not be hard-coded but would've been passed in as the arguments to the function or procedure, for example. Cursor FOR…LOOPPL/SQL offers an easier alternative to opening, fetching, testing, and closing a cursor ourselves the cursor FOR…LOOP. This is similar to the standard FOR…LOOP we've already seen. The format for a FOR…LOOP is FOR record_name IN {cursor_name|query} LOOP /* process record */ END LOOP; In place of a simple counter variable, like that in a regular FOR…LOOP, we have an implicitly declared row object, or record. This record is equivalent to a record declared using the %ROWTYPE attribute of the FOR…LOOP's cursor. The fields of a record, as we've seen previously, can be accessed using dot notation. The scope of the record is limited to the loop body. There are two basic options for a cursor FOR…LOOP: We can use an explicitly declared cursor with the query specified in a cursor declaration or we can use an implicitly declared cursor by specifying the query in the FOR…LOOP statement instead of using an already declared cursor. These two examples are equivalent. /* Explicitly declared cursor */ DECLARE CURSOR C_ARTISTS IS SELECT ARTIST_ID, SORT_NAME FROM ARTISTS ORDER BY SORT_NAME; BEGIN FOR C_ARTISTS_REC IN C_ARTISTS LOOP DBMS_OUTPUT.PUT_LINE(C_ARTISTS_REC.SORT_NAME); END LOOP; END; /* Cursor implicitly declared by query */ BEGIN FOR C_ARTISTS_REC IN (SELECT ARTIST_ID, SORT_NAME FROM ARTISTS ORDER BY SORT_NAME) LOOP DBMS_OUTPUT.PUT_LINE(C_ARTISTS_REC.SORT_NAME); END LOOP; END; The second format, using the query directly in the FOR…LOOP, is more concise. If we want to use a cursor with parameters we need to use the first format, with an explicit cursor. This isn't an important advantage because we can accomplish the same thing in a FOR…LOOP query by including variables in the query. This next example shows how we can use a cursor's parameters in the query's WHERE clause. /* Explicitly declared cursor * with parameters */ DECLARE CURSOR C_ARTISTS(START_NAME VARCHAR2, END_NAME VARCHAR2) IS SELECT ARTIST_ID, SORT_NAME FROM ARTISTS WHERE SORT_NAME >= START_NAME AND SORT_NAME <= END_NAME ORDER BY SORT_NAME; BEGIN_RANGE VARCHAR2(1) := 'A'; END_RANGE VARCHAR2(1) := 'M'; BEGIN FOR C_ARTISTS_REC IN C_ARTISTS(BEGIN_RANGE, END_RANGE) LOOP DBMS_OUTPUT.PUT_LINE(C_ARTISTS_REC.SORT_NAME); END LOOP; END; This next example shows how we can accomplish the same thing by using PL/SQL variables in the FOR…LOOP query. /* Cursor implicitly declared by query * using PL/SQL variables */ DECLARE BEGIN_RANGE VARCHAR2(1) := 'A'; END_RANGE VARCHAR2(1) := 'M'; BEGIN FOR C_ARTISTS_REC IN (SELECT ARTIST_ID, SORT_NAME FROM ARTISTS WHERE SORT_NAME >= BEGIN_RANGE AND SORT_NAME <= END_RANGE ORDER BY SORT_NAME) LOOP DBMS_OUTPUT.PUT_LINE(C_ARTISTS_REC.SORT_NAME); END LOOP; END; |
EAN: 2147483647
Pages: 71