|
Chapter 6 |
|
|
|
Chapter 6 |
|
With HTML safely out of the way, we can turn our attention to the second half of our web development platform: PL/SQL, Oracle's proprietary extension to structured query language (SQL). The PL stands for Procedural Language, since PL/SQL is used to create procedural constructs (loops, variables, etc.) on top of the relational constructs of SQL.
Although it has some object-oriented features, PL/SQL is based largely on Ada, a structured programming language used heavily by the Department of Defense. As such, PL/SQL has more in common with languages like C, Pascal, or COBOL than it does with C++ or Java. Although it follows an older design model (structured versus object), PL/SQL has the advantages of being easy to learn, tightly integrated to the Oracle database, and extensible. If you know how to write a SQL script and know at least one 3GL language, you can learn to develop useful PL/SQL programs in just a few hours.
In the next three sections we'll cover what you need to know to start developing in PL/SQL: how to structure a PL/SQL program, how to fill in its major programming constructs, and how to create reusable modules called packages. In the last section we'll look at two third-party tools, TOAD and PL/Formatter, that make PL/SQL development much more enjoyable and productive.
There is much more to say about the PL/SQL language. For complete information, see Steven Feuerstein and Bill Pribyl's Oracle PL/SQL Programming (O'Reilly & Associates).
The idea behind structured, or modular, programming is that complex problems can be broken down into smaller, more manageable pieces. For example, I can break the daunting task of driving from Boston to New York into four simpler steps: find I-95 south in Boston, drive four hours, exit in New York, and find a parking space. I repeat this process on each of the previous steps, breaking each one into even smaller units until I eventually reach a level of complexity that I can reasonably handle. For example, I can break "find a parking spot" into the steps: drive around aimlessly, yell at somebody, honk my horn, and then turn around and go home.[ 1 ] Once I have identified all these simpler steps, I can solve the original problem.
[1] Sometimes these problems are maddeningly recursive. For example, "Turn around and go home" breaks into "Find I-95 north in New York, drive four hours, exit in Boston, and find a parking space."
The structure of a PL/SQL program reflects this underlying philosophy. A complex program is made up of units called blocks (as in building blocks) that can contain variables, SQL and PL/SQL instructions, error handling routines, and even other blocks. Each block may have four distinct parts: an optional header, optional variable declarations, executable instructions, and optional error handling code. These parts are described in the following list:
This section, also known as the specification , comes at the beginning of a block. It defines the block's name, its type, and any parameters it requires. If the header is omitted, the block is called an anonymous block because it does not have a name. The header is required for procedures and functions, but is replaced with a simple DECLARE keyword for anonymous blocks. Anonymous blocks typically include just an executable section, and are often found as the executable portions of a conditional statement.
This section contains declarations for all local variables and structures used in the block. Variables can include simple numbers and strings, as well as more complex structures, like cursors and arrays. The declaration section is optional; your program does not have to use any variables. However, PL/SQL is strongly typed , which means that you must declare every variable you plan to use (the one exception to this rule is the implicit loop index, which we'll discuss later).
This section, also known as the body , contains your actual code. This is the only required section; all blocks must have at least one executable instruction.[ 2 ] Sometimes you may want to just use the NULL instruction, which doesn't perform any action, as the entire executable portion of a block. This technique stems from the two approaches to modular design. The first approach, bottom-up design , begins by coding the simplest pieces and works up to more complex structures. The second approach, top-down design , begins at the highest level and works down. With the second method, you may know a particular module is necessary, but not yet know (or care) how it will be implemented, so you create a stub that serves as a placeholder until you are ready to fill in the details.
[2] The exceptions to this rule are external procedures or specifications for Java methods (a way to call Java from PL/SQL). These, however, are beyond the scope of this book.
This section handles problems (exceptions) that arise while the program is running. When an exception occurs (for instance, an attempt to insert a duplicate primary key into a table), the RDBMS immediately transfers control to the exception section, if it exists. Each error is associated, either by the system or the programmer, with a name like DUP_VAL_ON_INDEX or NO_DATA_FOUND.
The exception section is a CASE statement that associates an error name with a handler that executes when the error occurs. Of course, it would be inconvenient to explicitly test for all the possible things that could go wrong with a particular program (how often do you test whether you have enough memory to execute a SQL statement?). The catch-all exception OTHERS traps exceptions not explicitly listed in the CASE statement.
If there is no handler at all for an exception, the block terminates and the error is passed back to the calling block to be resolved. If there is no handler for the error in any block, then the entire program terminates immediately, leaving the user staring at an ugly error message. Although the use of the exception section is optional, well-designed programs should always minimize the occurrence of unhandled exceptions.
Blocks allow you to build modularized programs. While anything between BEGIN and END is considered a block, the two most important blocks are procedures and functions.
A procedure is a modular block of code with the following general structure:
PROCEDURE
name
(
parameter1
IN | OUT | IN OUT AS
datatype
,
parameter2
IN | OUT | IN OUT AS
datatype
, ... ) IS
Local variable declarations
BEGIN
Program instructions
EXCEPTION WHEN
exception1
THEN
Handler 1
WHEN
exception2
THEN
Handler
2 WHEN OTHERS THEN
Default error handler
END;
The name of the procedure should reflect the task it performs. The task should be fairly simple; a procedure shouldn't be more than a few hundred lines long. If it is, you probably haven't broken your problem down sufficiently. Here's a fairly simple procedure:[ 3 ]
[3] This listing, and the others throughout this chapter, begin with the line "CREATE OR REPLACE...," which is a SQL command and not part of the actual procedure. Since procedures (as well as functions and packages) are database objects, you must use SQL to CREATE and compile them. The REPLACE option allows you to run these scripts over and over without having to first drop the procedure.
/* Formatted by PL/Formatter v.1.1.13 */ CREATE OR REPLACE PROCEDURE give_raise ( emp_id IN VARCHAR2 DEFAULT NULL, raise_pct IN VARCHAR2 DEFAULT NULL ) IS monthly_salary NUMBER DEFAULT 0; BEGIN -- Fetch current salary using a SELECT...INTO; SELECT sal INTO monthly_salary FROM emp WHERE id = emp_id; -- Decide what to do IF monthly_salary > 10000 THEN HTP.print ('You are rich enough already!'); ELSE UPDATE emp SET sal = sal * (1 + raise_pct) WHERE id = emp_id; COMMIT ; HTP.print ('Your wish is my command'); END IF; HTP.print ('All done.'); EXCEPTION WHEN OTHERS THEN HTP.print ('Sorry, no raise for now.'); END;
A function , the second kind of modular block has the following general structure:
FUNCTION
name
(
parameter1
IN | OUT | IN OUT AS
datatype
,
parameter2
IN | OUT | IN OUT AS
datatype
) RETURN
return_datatype
IS
Local variable declarations
BEGIN
Function instructions
RETURN
return_value
EXCEPTION WHEN
exception1
THEN
Handler1
WHEN
exception2
THEN
Handler2
WHEN OTHERS THEN
Default error handler
END;
A function computes and returns a single value (its return value) of the datatype defined in its header section. The RETURN command, which can appear in the executable or exception section (or both), sends the return value back to the program that called the function. The RETURN command terminates the function immediately.
You can use functions to perform common computations or return special values. For example, you might want to include some descriptive information at the end of each page, like your company's name and the date the page was created. You can write a simple function that you can call inside each program to avoid hard-coding. Here, for example, is such a function:
CREATE OR REPLACE FUNCTION get_web_tag_line RETURN VARCHAR2 Ret_val VARCHAR2(500); IS BEGIN ret_val := 'Copyright ACME Incorporated, '; RETURN ret_val || TO_CHAR (SYSDATE, 'DD-MON-YY'); END;
Even this trivial example points out one of the main advantages of modularizing your code: it helps you avoid problems down the road. For example, what if a big German conglomerate buys your company? You certainly don't want to have to insert a bunch of umlauts into your programs. You probably also noticed that the date format shows only the last two digits of the year. Had you hardcoded the date format into each program, millennium fever would force you to change every occurrence to display the full four-digit year. Calling a function lets you change the code in just one place.
You can include parameters in a procedure or function header to better control how it works. A parameter is similar to a local variable, but it acts more like a placeholder for a value that will be passed to the procedure by some future program. This allows a procedure to handle general situations, rather than specific instances of a given problem. These symbols are called formal parameters . The values provided by the calling program are called actual parameters because they represent actual, concrete values.
A procedure or function can accept any number of parameters, or even omit them entirely. For example, the give_raise procedure needed two parameters: one to pass the employee ID and one to pass the amount of the raise.
Parameter declarations follow this general format:
PROCEDURE/FUNCTION name ( Name1 mode datatype DEFAULT defaul_val , Name2 mode datatype DEFAULT default_val , ... Name3 mode datatype DEFAULT default_val ) IS
Each parameter must have a name, a mode, a datatype, and (optionally) a default value, as defined in the following list:
The name for the parameter as it is used in the body of the block. Each name must be unique. Parameter names should be reasonably meaningful.
There are three modes: IN, OUT, and IN OUT. The IN mode means that the parameter is read only; the block can see the value (i.e., reference) of the parameter but cannot change it. The OUT mode is write only; the block can set, but not reference, the value of the parameter. This mode is used to return values from the procedure back to the calling program. The IN OUT mode means the parameter can be both referenced and updated.
The datatype specifies the parameter's type. These types are unrestrained; the size of the formal parameter is determined by the size of the corresponding actual parameter.
The default value specifies the value of the parameter if no corresponding actual parameter is provided.
Here are some sample declarations:
PROCEDURE give_raise ( emp_id IN NUMBER, job_code IN VARCHAR2 DEFAULT 'CEO' ); PROCEDURE print_emp_info ( dpt_name_parm IN VARCHAR2 DEFAULT 'HUMAN RESOURCES' ); FUNCTION get_emp_name (emp_id IN NUMBER) RETURN VARCHAR2;
The last example is worth commenting on. It's a function that, given a primary key, returns an employee's name. What if you wanted to modify the function to return more information, like the employee's job code and department? Since a function can return only one value, we can't modify the get_emp_name function to return several different things. Instead, we can convert it into a procedure and use OUT parameters to pass the new values back. Here's an example:
PROCEDURE get_emp_info ( emp_id IN NUMBER, emp_number OUT VARCHAR2, emp_name OUT VARCHAR2, emp_dept_id OUT NUMBER )
You call a procedure or function by name. You must also pass actual parameters for its formal parameters (if a module does not have any parameters, then the name alone is sufficient). Here are some examples of how to call a procedure or function from inside another PL/SQL program:
delete_all_customers; dbms_sql.put_line (todays_date); today_string := todays_date;
The first example calls the delete_all_customers procedure. This is the simplest type of call, since no parameters are passed to the procedure. As you can see, the ability to represent a complex sequence of actions with a single command makes for much more readable programs. The second example prints the results returned by the todays_date function. You can use a function call anywhere you can use a literal or a variable, as long as its return type is appropriate. The third example assigns a local variable to the value returned by todays_date.
You must supply actual parameters to procedures or functions that have a formal parameter list. There are two notations for doing this: positional notation and named notation.
Positional notation uses an actual parameter's ordinal position to map it to a corresponding formal parameter. This is the notation used most frequently in languages like C or Pascal. The following examples show positional notation in action:
give_raise (101,'PROGRAMMER'); give_raise (105); print_emp_info('ACCOUNTING'); emp_name := get_emp_name (current_emp_id); get_emp_info ( 101, enum, ename, edpt);
The first call passes two literal values to the two formal parameters (emp_id and job_code) of the give_raise procedure. The values are assigned to the formal parameters based on their order in the list, so emp_id is assigned the value "101" and job_code is assigned "PROGRAMMER." The second example seems to violate these rules because it only provides one parameter. Remember, however, that we have assigned a default value ("CEO") to the job_code. You can omit an actual value for a formal parameter if it has a default value. The RDBMS generates an error if you omit a value for a parameter that does not have a default value.
The third example has only one parameter, dpt_name_parm, which is assigned the value "ACCOUNTING." The fourth assigns the result of the get_emp_name function to a local variable called emp_name. The fifth sample calls get_emp_info. As we would expect, the emp_id formal parameter is assigned the value procedure, and each of the OUT formal parameters is associated with a corresponding local variable: emp_number with enum, emp_name with ename, and emp_dept_id with edpt. When the procedure finishes, the values of the local variables will have the values assigned to formal parameters in the procedure.
The second way to supply parameters to a function or procedure is named notation , which eliminates the call's reliance on parameter position by explicitly mapping formal parameters to actual parameters. This is done by using the formal parameter name to which an actual parameter corresponds directly in the call. The syntax of a named notation call is:
procedure_name ( formal_parameter1 => actual_parameter1 , formal_parameter2 => actual_parameter2 , formal_parameter3 => actual_parameter3 , ...)
Here are two of the positional notation examples we looked at in the previous section rewritten in this format:
give_raise ( job_code => 'PROGRAMMER', emp_id => 101 ); get_emp_info ( emp_dpt_id => edpt, emp_name => ename, emp_number => enum, emp_id = > 101 );
While named notation requires more typing, there are many situations in which this notation is preferable to positional notation. Let's take as an example a procedure that performs a logic test based on a large set of flags. Suppose you had a procedure called complex_test with 10 parameters and that each parameter governed the execution of a distinct step. If a parameter value is `Y', then the step executes. Otherwise, it does not. Here is the specification:
PROCEDURE complex_test ( step1_ctl IN VARCHAR2 DEFAULT 'N', step2_ctl IN VARCHAR2 DEFAULT 'N', ... step9_ctl IN VARCHAR2 DEFAULT 'N', step10_ctl IN VARCHAR2 DEFAULT 'N' );
Now suppose that you want to execute just the tenth step. The positional syntax requires a value for the parameter based on its ordinal position. Executing just the tenth step in complex_test requires this ugly command:
complex_test ('N','N','N','N','N','N','N','N','N','Y');
Using the named notation, we can replace the complex with a simple substitute:
complex_test ( step10_ctl => 'Y' );
Overloading allows you to create multiple versions of a procedure or function. Each version has the same name but a different signature , the technical term for the full set of declarations in a parameter list. The compiler uses a procedure or function call's name and signature to find a corresponding overloaded function.
Overloading is a powerful technique that makes procedures and functions easier to use. Say you want to create a generic function that returns any passed date in DD-MON-YYYY format. The input should allow the caller to pass a date in a variety of formats. For example, he could pass a DATE variable, a VARCHAR2 string that represents a date, or even numbers that represent the month, day, and year of the date. Here is the example without overloading:
FUNCTION get_nice_date_date (dt IN DATE) RETURN VARCHAR2; FUNCTION get_nice_date_vchar (dt IN VARCHAR2) RETURN VARCHAR2; FUNCTION get_nice_date_month ( m IN NUMBER, d IN NUMBER, y IN NUMBER ) RETURN VARCHAR2;
With overloading, however, you can use the same function name over and over as long as each version has a unique signature. All the programmer has to do to call the function is supply the data in one of the overloaded formats; the compiler automatically does the dirty work of mapping the call to the correct signature. Here are the three specifications required by the get_nice_date function:
FUNCTION get_nice_date (dt IN DATE) RETURN VARCHAR2; FUNCTION get_nice_date (dt IN VARCHAR2) RETURN VARCHAR2; FUNCTION get_nice_date ( m IN NUMBER, d IN NUMBER, y IN NUMBER ) RETURN VARCHAR2;
Without overloading, you had to create three different versions of the same function and leave the programmer to call the correct version. This is a real nuisance because the programmer must know (or, more likely, look up) the applicable name. This flexibility helps ensure that the procedure and functions you develop are easy to use and understand.
In this section we'll look at how to share procedures and functions among a number of different schemas. Like all database objects, procedures and functions (as well as packages, which are covered later in this chapter) are owned by a single schema. Consequently, only that schema can execute them unless other database schemas are explicitly granted EXECUTE permission. When you want to write generic modules that can be used by a large number of developers, you can use the GRANT EXECUTE command. This command should be executed within the owner's account, and it has the following syntax:
SQL> GRANT EXECUTE ON program_or_package_name TO schema;
This grant highlights an extremely important aspect of Oracle's security rules. By default, procedures and functions execute with all the permissions of their owner , and not those of the account in which they are executed. For example, you can execute a complex procedure that hits sensitive tables from a minimally privileged account by selectively using GRANT EXECUTE. Oracle8 i has a second model, called invoker's rights , which requires that the user (the invoker ) has the necessary underlying privileges.
|
|
|
|
| 5.2 A Whirlwind Tour |
|
6.2 Programming Constructs |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.