|
Chapter 16 PLVgen: Generating PL/SQL Programs |
|
With PLVgen, you can generate functions, procedures, packages, and variations on those. You have already seen examples of functions and procedures. The examples in this section demonstrate the other kinds of code generated from this package. You can also modify the contents of generated code by using the many different toggles.
Don't get your hopes up too high. I can't generate a complete package for you. PLVgen can, on the other hand, generate a skeleton of the syntax required to build a package and also load it up with help text stubs and banners to break up the different elements of the package.
The header for the package generator is:
PROCEDURE pkg (name_in IN VARCHAR2);
You provide the name of the package, the procedure produced, and the code for a package (specification and body). Consider the following two commands executed in SQL*Plus:
SQL> exec PLVgen.usemax SQL> exec PLVgen.pkg ('PLVfile');
Notice that I call PLVgen.usemax before generating my code. PLVgen.usemax turns on all available toggles for code content, including the CREATE OR REPLACE syntax, line numbers, program header, and help text stubs. These individual toggles and usemax are explained later in the chapter. The code generated by these commands is shown below.
1 CREATE OR REPLACE PACKAGE PLVfile 2 /* 3 || Program: PLVfile 4 || Author: Steven Feuerstein 5 || File: PLVfile.SQL 6 || Created: May 30, 1996 13:34:59 7 */ 8 /*HELP 9 Add help text here... 10 HELP*/ 11 12 /*EXAMPLES 13 Add help text here... 14 EXAMPLES*/ 15 16 IS 17 /* Public Data Structures */ 18 19 /* Public Programs */ 20 21 PROCEDURE help (context_in IN VARCHAR2 := NULL); 22 23 END PLVfile; 24 / 25 26 CREATE OR REPLACE PACKAGE BODY PLVfile 27 IS 28 /* Private Data Structures */ 29 30 /* Private Programs */ 31 32 /* Public Programs */ 33 34 PROCEDURE help (context_in IN VARCHAR2 := NULL) 35 IS 36 BEGIN 37 PLVhlp.show ('s:PLVfile', context_in); 38 END help; 39 END PLVfile; 40 /
As you can see, this generated package has all the syntax required to create package specifications and bodies (which is not, after all, very much syntax). It also contains a single procedure, help (lines 34 through 38), which relies on the PLVhlp package to provide online help about this package. It also creates stubs for the help text (lines 8 through 14), to remind you to add the text and make that information available (see Chapter 17, PLVhlp: Online Help for PL/SQL Programs , for more information on how PLVhlp works).
Finally, PLVgen.pkg provides banners in comments to delineate the different kinds of code one normally finds inside a package. These banners (lines 17, 19, 28, 30, and 32) help developers organize their code. This organization will, in turn, make it easier to develop, debug, and enhance the package.
Once you have generated a package, you will most likely want to fill it up with procedures and functions. PLVgen provides the proc procedure to generate (as you might expect) procedures. Its header is:
PROCEDURE proc (name_in IN VARCHAR2, params_in IN VARCHAR2 := NULL, exec_in IN VARCHAR2 := NULL, incl_exc_in IN BOOLEAN := TRUE, indent_in IN INTEGER := 0);
The arguments of the proc procedure are explained below:
The name of the procedure. The only required argument.
The list of parameters to be enclosed in parentheses after the procedure name. The default is NULL (no parameters). The syntax of this argument must be a valid parameter list as would appear in the procedure definition (minus the parentheses).
One or more executable statements to place in the body of the procedure. If you want to provide more than one statement, you should concatenate a CHR(10) or PLVchr.newline_char between the statements to place them on separate lines. The default is NULL (no executable statements).
Pass TRUE (the default) to include an exception section and initial handler, FALSE to skip the exception section. Why give the user a choice? Sometimes an exception handler is just plain unnecessary and little more than clutter.
The incremental indentation by which the entire procedure's code should be indented. The default is 0.
Let's look at an example of procedure generation to get a feel for the way you can use the different arguments: generate a procedure with all the default argument values and the full set of additional code elements turned on by the call to usemax (explained later in this chapter).
SQL> exec PLVgen.usemax SQL> exec PLVgen.proc ('calc_totals'); 1 2 CREATE OR REPLACE PROCEDURE calc_totals 3 /* 4 || Program: calc_totals 5 || Author: null 6 || File: calc_totals.SQL 7 || Created: May 29, 1996 13:36:20 8 */ 9 /*HELP 10 Add help text here... 11 HELP*/ 12 13 /*EXAMPLES 14 Add help text here... 15 EXAMPLES*/ 16 17 IS 18 BEGIN 19 PLVtrc.startup ('calc_totals'); 20 PLVtrc.terminate; 21 22 EXCEPTION 23 /* Call PLVexc in every handler. */ 24 WHEN OTHERS 25 THEN 26 PLVexc.rec_continue; 27 END calc_totals; 28 /
Let's go through this code line by line so that you can understand clearly the different elements of code that are generated for a procedure and function. I won't repeat this explanation (or the volume of code) in the following sections.
Line 2 contains the header for the procedure header, including the CREATE OR REPLACE syntax. As you will see in later examples, you can provide a parameter list as part of this header as well.
Lines 3 through 8 contain the standard program header provided by PLVgen. Notice that the author is automatically set to "Steven Feuerstein". I make sure of this setting by including the following statement in my login.sql script:
exec PLVgen.set_author ('Steven Feuerstein');
Lines 9 through 15 are the stubs for help text on two topics: general help and examples help. These comment blocks are used both for inline documentation of the package and also for online help text for users (through the PLVhlp package).
Lines 19 and 20 call PLVtrc startup and terminate procedures. These programs provide an execution trace that is of particular use in the PLVexc exception- handling package. Again, you do not have to include these programs in your own generated code, but it is an available option.
Lines 22 through 26 show the standard PL/Vision exception handler section. A single WHEN OTHERS clause calls the PLVexc.rec_continue program, which records the error and continues processing. You can replace this PLVexc handler with another one (such as PLVexc.rec_halt or "record and halt"); the point to recognize here is that the generator creates an exception handler as a starting point, which reinforces best practices.
Lines 27 and 28 bring the procedure to a close. Notice that PLVgen automatically supplies an END label. It also provides the forward slash, that in conjunction with the CREATE OR REPLACE syntax results in a script that can be used immediately in SQL*Plus to store and compile the procedure.
NOTE: In most of the examples of generated code in the rest of this chapter, I turn off the many comments and other optional elements of the source code. By doing this, you can focus more easily on how PLVgen constructs the required elements of the PL/SQL programs. Section 16.3, "Modifying PLVgen Behavior" explains how to use some or all ( PLVgen.usemax ) of the optional elements of the code listed previously.
The following SQL*Plus session generates a procedure using a minimum of additional features, but with a parameter list and a single line of code in the body of the procedure.
SQL> exec PLVgen.usemin SQL> exec PLVgen.proc ('disptot', 'comp_in in integer', 'get_total;'); PROCEDURE disptot (comp_in IN INTEGER) IS BEGIN get_total; EXCEPTION WHEN OTHERS THEN NULL; END disptot;
Notice that the keywords IN and INTEGER are automatically uppercased by PLVgen. This package makes use of the PLVcase.string program to convert the case of the parameter list and the executable line of code.
In this final example of the procedure generator, I make use of the indentation parameter to add three spaces to the default three blanks provided by PLVgen. I could then easily cut and paste this text into the body of a package or into the declaration section of a nested, anonymous block.
SQL> exec PLVgen.usemin SQL> exec PLVgen.proc ('disptot',indent_in=> 3); PROCEDURE disptot IS BEGIN EXCEPTION WHEN OTHERS THEN NULL; END disptot;
Notice that I use named notation (the => symbol associating indent_in with the value 3) so that I can avoid specifying values for all the other arguments.
PLVgen offers seven overloadings of the function generator. Why? Because a function has a RETURN datatype and I want my package to automatically generate functions of the requested type. PLVgen.func generates functions with the following datatypes: BOOLEAN, DATE, NUMBER, and VARCHAR2. Four datatypes and seven overloadings...am I leaving something out? Not at all; PLVgen supports four different datatype functions, but provides additional overloadings to handle two kinds of default values for the functions (covered later in this section).
The format of the function generated by PLVgen.func follows my guidelines for a "template" function, the generic structure of which is shown below:
FUNCTION func_name (...) RETURN datatype IS /* Variable for RETURN */ return_value datatype; BEGIN <executable statements> /* Last line always: */ RETURN return_value; EXCEPTION WHEN OTHERS THEN RETURN NULL; END func_name;
A brief recap of the function template: There is a single RETURN statement in the body of the program and it is the last line of code. There is a local variable (in this case, return_value ) that is always the same datatype as the function itself and is RETURNed in that single, successful RETURN statement. There is an exception section that returns NULL if something goes wrong.
Rather than show the headers for all of the overloadings here and then again in the package specification section, I will show you the header for the string function generator and the headers for the Boolean function generators. All the other nonstring function generators are structured and used like the Boolean version.
Here is the header for the program that generates a string or VARCHAR2 function:
PROCEDURE func (name_in IN VARCHAR2, datadesc_in VARCHAR2, defval_in IN VARCHAR2 := NULL, length_in IN INTEGER := c_def_length, incl_exc_in IN BOOLEAN := TRUE);
Here are the headers for the two programs that generate Boolean functions:
PROCEDURE func (name_in IN VARCHAR2, datadesc_in BOOLEAN, defval_in IN BOOLEAN := NULL, incl_exc_in IN BOOLEAN := TRUE); PROCEDURE func (name_in IN VARCHAR2, datadesc_in BOOLEAN, defval_in IN VARCHAR2, incl_exc_in IN BOOLEAN := TRUE);
In all of these programs, the name_in is the name of the function, and datadesc_in is an expression or value that is the same datatype as the function you want to generate. The defval_in argument provides a default value for the RETURN statement of the function. Notice that this is the only parameter that is different for the two overloaded Boolean function generators. In one case, the default value is a Boolean. In the second, the default value is a string. This distinction is explained below.
When generating a string function, you can also provide (through the length_in argument) the length of the VARCHAR2 variable to be declared. The default is provided by the constant c_def_length and is set to 100.
The incl_exc_in argument indicates whether or not an exception section should be included in the function. The default is "Yes! Include an exception section!" (and that is a very important thing to do in functions).
Now I will explain how to use these and the other function generators. The only information you have to provide to a function generator is the name and the datatype of the function.
In this first example, I generate a string function named full_name simply by providing a string -- any string -- as the second argument.
SQL> exec PLVgen.func('full_name','a'); FUNCTION full_name RETURN VARCHAR2 IS retval VARCHAR2(100) := NULL; BEGIN RETURN retval; EXCEPTION WHEN OTHERS THEN RETURN NULL; END full_name;
NOTE: See Section 16.2.2, "Generating a Procedure" for a more complete description of the elements of code that can be included in a generated function.
Notice that this function conforms to the template structure described earlier. Based on the datatype of the second argument, PLVgen has automatically determined that the RETURN datatype should be VARCHAR2. It has also declared a local variable with matching datatype and appropriate default value.
In the next call to PLVgen.func , I generate a Boolean function, this time setting the default value to FALSE and rejecting the use of an exception section.
SQL> exec PLVgen.func ('valid_account', true, false, FALSE); FUNCTION valid_account RETURN BOOLEAN IS retval BOOLEAN := FALSE; BEGIN RETURN retval; END valid_account;
Now I will generate this same function, but instead of providing a literal default value, I pass in a string that contains an expression (and include an exception section):
SQL> exec PLVgen.func ('valid_account', true, 'sysdate < account.start_date'); FUNCTION valid_account RETURN BOOLEAN IS retval BOOLEAN := sysdate < account.start_date; BEGIN RETURN retval; EXCEPTION WHEN OTHERS THEN RETURN NULL; END valid_account;
Since the second argument to PLVgen.func was a Boolean and the third a string, the PL/SQL runtime engine executed the version of func that interprets the default value not as a literal, but as an expression.
And that is the reason for the overloading of the two different Boolean function generators. When the default value is a Boolean, that value is applied directly (as a TRUE, FALSE, or NULL) to the local variable declaration. If the default is a string, that string becomes the unevaluated expression used as the default value for the local variable.
The function generators for dates and numbers are overloaded and work in the same way as the Boolean procedures do.
PLVgen provides two distinct overloadings for Boolean, date, and number function generators to handle literals and expressions for default values. This same approach is not possible for the procedure that generates string functions. The way PLVgen.func supports expressions for default values is that they are passed as strings. But since the default value for a string function is already a string, another overloaded version with a default value of datatype VARCHAR2 to handle string expressions would simply not work.
A different approach is required for the string function generator. There is just one version of func to generate string functions, but the following special rule applies to the default value argument, namely: if the first character of the default value string is an equal sign ( = ), then the string is to be interpreted as an expression. In this case, the value in the string is not evaluated, but simply placed in the default value area for the local variable of the function.
Let's look at a couple of examples. In this first call to PLVgen.func , I generate a VARCHAR2 function with a default return value of ABC. Notice that since this is a string function, I also specify the maximize length of the return value and request that the function not have an exception section.
SQL> exec PLVgen.func ('full_name', 'a', 'ABC', 50, FALSE); FUNCTION full_name RETURN VARCHAR2 IS retval VARCHAR2(50) := 'ABC'; BEGIN RETURN retval; END full_name;
In this next call to PLVgen.func , I change the default value to prefix the "ABC" with the equal sign. In the resulting declaration of a local variable, the default value is set to the PL/SQL identifier named abc, and not to a literal with that value.
SQL> exec PLVgen.func ('full_name', 'a', '=ABC', 50, FALSE); FUNCTION full_name RETURN VARCHAR2 IS retval VARCHAR2(50) := abc; BEGIN RETURN retval; END full_name;
Let's look at an example that involves a more real world use of this expression default value. In the following call to PLVgen.func , I create a string function that sets the default value to a package-based constant.
SQL> exec PLVgen.func ('full_name', 'a', '=Names.formal_address'); FUNCTION full_name RETURN VARCHAR2 IS retval VARCHAR2(100) := names.formal_address; BEGIN RETURN retval; EXCEPTION WHEN OTHERS THEN RETURN NULL; END full_name;
If you ever want to generate a string function with a nonliteral default value, don't forget to prefix the default value string with an equal sign.
One of the most important of my best practices for package construction is to always hide package data behind a programmatic interface, otherwise known as get-and-set routines. Instead of declaring a variable directly in the specification, you would move that declaration to the body of the package and then build (a minimum of) two programs: a function to retrieve the current value of this variable and a procedure to change the value of the variable.
The gas (get-and-set) procedure of PLVgen generates the get-and-set code needed to hide variables of datatypes VARCHAR2, DATE, NUMBER, and Boolean. The overloading for the gas procedure is similar to that of the func procedure, which makes sense since PLVgen.gas does generate a function, as well as a variable declaration of the correct datatype. There are two versions of gas for every datatype except VARCHAR2. For this string datatype, there is only one overloading. The header for the string version of gas is shown below:
PROCEDURE gas (name_in IN VARCHAR2, valtype_in VARCHAR2, defval_in IN VARCHAR2 := NULL, length_in IN INTEGER := c_def_length);
The header for the NUMBER version of gas is shown below. The headers for the other datatypes are exactly the same as for the NUMBER version, except for the datatype of the valtype_in and defval_in arguments.
PROCEDURE gas (name_in IN VARCHAR2, valtype_in NUMBER, defval_in IN NUMBER := NULL); PROCEDURE gas (name_in IN VARCHAR2, valtype_in NUMBER, defval_in IN VARCHAR2);
Generally, the first argument provides the name of the variable. The second argument determines the type of the function and the variable being hidden inside the package body. The third argument provides a default value for the variable. When generating a string get-and-set, you can also provide the length of the VARCHAR2 variable to be declared. The default is provided by the constant c_def_length and is set to 100.
The listing below gives you an idea of the kind of code that is generated by a call to the PLVgen.gas procedure. I call the PLVgen.useln to turn on line numbers to use as a reference (after turning off all other toggles).
SQL> exec PLVgen.usemin SQL> exec PLVgen.useln SQL> exec PLVgen.gas ('pagesize', 1, 25); 1 2 PROCEDURE set_pagesize (pagesize_in IN NUMBER); 3 FUNCTION pagesize RETURN NUMBER; 4 5 v_pagesize NUMBER := 25; 6 7 PROCEDURE set_pagesize (pagesize_in IN NUMBER) 8 IS 9 BEGIN 10 v_pagesize := pagesize_in; 11 END set_pagesize; 12 13 FUNCTION pagesize RETURN NUMBER 14 IS 15 retval NUMBER := v_pagesize; 16 BEGIN 17 RETURN retval; 18 END pagesize;
Lines 2 and 3 contain the headers for the set and get programs. These should be cut and pasted into the package specification. Line 5 contains the declaration of the variable that is to be protected by the get-and-set programs. This declaration is placed in the body of the package, before any of the program definitions are listed. Lines 7 through 11 contain the definition of the set program. Lines 13 through 18 contain the definition of the get program. Both of these should be cut and pasted into the package body. Notice that even in the simple get function, the template approach is still followed.
The naming scheme and structure for programs generated for a Boolean get-and-set is a bit different from that for strings, dates, and numbers. When building a get-and-set around a Boolean variable, you can and should take into account the fact that a user can only set the variable to one of three values (TRUE, FALSE, and NULL). In fact, in many cases, a NULL value is not allowed. If PLVgen.gas used the same syntax for Booleans as it does for numbers (see above listing), then the set program would look like this:
PROCEDURE set_show_changes (show_changes_in IN BOOLEAN);
and this program would be used as follows:
PKG_NAME.set_show_changes (TRUE); PKG_NAME.set_show_changes (FALSE);
While there is certainly nothing wrong with this style, it strikes me as a cumbersome interface. A much cleaner, more natural style would allow me to call programs like this:
PKG_NAME.show_changes; -- Set variable to TRUE. PKG_NAME.noshow_changes; -- Set variable to FALSE.
This is the very approach taken with the Boolean gas procedure. The full set of generated code (specification, variable declaration, and body code) is shown below for a show_changes variable:
SQL> exec PLVgen.gas ('show_changes', true, true); PROCEDURE show_changes; PROCEDURE noshow_changes; FUNCTION show_changesing RETURN BOOLEAN; v_show_changes BOOLEAN := TRUE; PROCEDURE show_changes IS BEGIN v_show_changes := TRUE; END show_changes; PROCEDURE noshow_changes IS BEGIN v_show_changes := FALSE; END no_show_changes; FUNCTION show_changesing RETURN BOOLEAN IS retval BOOLEAN := v_show_changes; BEGIN RETURN retval; END show_changesing;
Notice the third program, a function, in the get-and-set routines for a Boolean. This function is the get program and it returns the current value of the Boolean variable. I use the "ing" structure for the name of this function, as in: "Am I currently showing the changes?" PLVgen automatically appends an "ing" to the name of the variable passed in the call to the gas procedure. It is smart enough to convert some formats to a readable name. For example, if you request a get-and-set for the use_lines variable, PLVgen.gas generates a function with this header:
FUNCTION using_lines RETURN BOOLEAN;
and if you use PLVgen.gas with a variable name propose , it generates a function whose header is:
FUNCTION proposing RETURN BOOLEAN;
but PLVgen does not pretend to handle all nuances of the infinitely nuanced English language. As a result, variable names like show_changes result in a function named show_changesing and you will just have to rename it yourself!
A toggle is an on-off switch; it is actually a special case of the Boolean get-and-set routines. The toggle procedure generates the code required to implement this switch in PL/SQL .
The header for toggle is:
PROCEDURE toggle (name_in IN VARCHAR2 := NULL);
where name_in is the name of the toggle. If you do not specify a toggle name, the following code is generated:
SQL> exec PLVgen.usemin SQL> exec PLVgen.useln SQL> exec PLVgen.toggle PROCEDURE turn_on; PROCEDURE turn_off; FUNCTION turned_on RETURN BOOLEAN; v_onoff BOOLEAN := TRUE; PROCEDURE turn_on IS BEGIN v_onoff := TRUE; END turn_on; PROCEDURE turn_off IS BEGIN v_onoff := FALSE; END turn_off; FUNCTION turned_on RETURN BOOLEAN IS retval BOOLEAN := v_onoff; BEGIN RETURN retval; END turned_on;
You have generated, in other words, a generic toggle to turn something off or turn it on. You can also generate more specific toggles by providing a toggle name. If you pass a non-NULL toggle name, the get-and-set code matches those lines of source code generated by a call to PLVgen.gas for a Boolean variable.
PL/Vision provides a mechanism for delivering online help for your PL/SQL programs (see Chapter 17 ). To take advantage of this mechanism you need to (a) put comments with an appropriate format in your source code, and (b) provide an easy way for developers to ask for that help. PLVgen allows you to generate the code and comments to handle both these tasks.
The helptext procedure generates a comment stub of the correct format so that the PLVhlp package can find and display the information. The header for helptext is:
PROCEDURE helptext (context_in IN VARCHAR2 := PLVhlp.c_main);
where context_in is the context or keyword that indicates the topic of the help text. The default value for this context is the main help topic constant from PLVhlp: HELP.
You can call helptext directly; it is also called by the various program unit generators to include stubs for help text in generated code.
To create a stub of help text for the main topic, you would do the following:
SQL> exec PLVgen.helptext; /*HELP Add help text here... HELP*/
To create a stub of help text for a different topic, such as KNOWN PROBLEMS, you would execute the following command:
SQL> exec PLVgen.helptext ('known problems'); /*KNOWN PROBLEMS Add help text here... KNOWN PROBLEMS*/
You can then cut and paste these stubs anywhere in your program definition, add some meaningful help text, and CREATE OR REPLACE the program. Users of the program can then access this text through the PLVhlp.show procedure. To make it easier to get at this information, however, you will most likely generate and include a special help procedure in your program unit (if it is a package, anyway). You do this with the PLVgen.helpproc program.
The helpproc procedure generates a program that provides help for a given program unit. The header for helpproc is:
PROCEDURE helpproc (prog_in IN VARCHAR2 := NULL, indent_in IN INTEGER := 0);
where prog_in is the name of the program and indent_in is an optional additional indentation. By default the generated code already has an indentation of three spaces. The following SQL*Plus session generates a program to show help text from the specification of the PLVio package.
SQL> exec PLVgen.helpproc ('s:PLVio'); PROCEDURE help (context_in IN VARCHAR2 := NULL) IS BEGIN PLVhlp.show ('s:PLVio', context_in); END help;
Let's see how you would use helpproc to provide help to your users. Suppose you have built a package named emp_maint . You want to allow developers to ask for general help on the employee maintenance tasks available. So you first execute PLVgen.helptext to create a stub help block:
SQL> exec PLVgen.helptext; /*HELP Add help text here... HELP*/
You cut and paste this text into the specification of emp_maint and add some real text. Then you generate a help program as follows:
SQL> exec PLVgen.helpproc ('s:emp_maint'); PROCEDURE help (context_in IN VARCHAR2 := NULL) IS BEGIN PLVhlp.show ('s:emp_maint', context_in); END help;
You then cut and paste this program into the body of the package. You must also cut only the header (first line) of the procedure and paste that into the specification of the package. Once the code is recompiled, a user of emp_maint can enter the following command to see the associated help text:
SQL> exec emp_maint.help
If, by the way, you used PLVgen.pkg to generate the first version of the emp_maint package, it would come with the help program and help text stubs already in place.
Many of us have gotten pretty good about using consistent indentation and white space for our PL/SQL code. Yet when we come to writing a SQL statement, we abandon any pretense of formatting and disgorge an absolutely awful mish-mash of SQL text into our otherwise orderly procedural logic. I believe that a consistent, readable format for the SQL in our PL/SQL programs is even more important than a good format for the procedural part of the programs. To help you satisfy my whim, PLVgen provides the curdecl procedure, which generates the declaration statement for a cursor.
The header for curdecl is as follows:
PROCEDURE curdecl (cur_in IN VARCHAR2, ind_in IN INTEGER := 0, table_in IN VARCHAR2 := NULL, collist_in IN VARCHAR2 := NULL, gen_rec_in IN BOOLEAN := TRUE);
where cur_in is the name of the cursor, ind_in is an additional amount of indentation, table_in is the name of the table (or list of tables), and collist_in is the list of columns in the SELECT list of the SQL statement. The gen_rec_in argument specifies whether or not you want a record declaration to be generated with the cursor.
This procedure implements several best practices and naming conventions for cursors: it automatically appends " _cur " to the end of the cursor name and " _rec " to the end of the record name. It formats the SQL statement to make it easier to read. It provides a record to go with the cursor as a discouragement against declaring individual variables into which data is then fetched.
The more you take advantage of these different arguments, the more likely it is that you will generate a cursor declaration that is very close to being ready for execution. Let's look at some examples.
Generate a cursor declaration relying on all default parameter values.
SQL> exec PLVgen.curdecl('emp'); CURSOR emp_cur IS SELECT FROM WHERE AND ORDER BY ; emp_rec emp_cur%ROWTYPE;
Generate a cursor against the order table to select the order_id and ship_date . Do not generate a record declaration since I am going to use this cursor in a FOR loop.
SQL> exec PLVgen.curdecl ('orders', 0, 'order', 'order_id, ship_date', FALSE); CURSOR orders_cur IS SELECT order_id, ship_date FROM order WHERE AND ORDER BY ;
The backbone of an SQL SELECT statement generated by curdecl supplies all the main clauses. You could greatly expand upon the PLVgen package to produce all manner of SQL statements.
The curdecl procedure generates a cursor declaration, which is certainly useful. Yet there are also many common kinds of code fragments and programs that use cursors in specific ways. One example is the "record found?" function. How many times have you written a function to return TRUE if the desired record exists, and FALSE otherwise?
PLVgen offers the recfnd procedure to generate a version of this "record found?" function that will save you many keystrokes if and when you need this functionality. The header for recfnd is, quite simply:
PROCEDURE recfnd (table_in IN VARCHAR2);
In other words, you provide the name of the table whose contents you wish to check, and recfnd does the rest of the work from there.
The following session generates a function that returns TRUE if the employee is found, and FALSE otherwise.
SQL> exec PLVgen.recfnd ('emp'); 1 FUNCTION empexists (empkey_in IN NUMBER) RETURN BOOLEAN 2 IS 3 CURSOR emp_cur 4 IS 5 SELECT 1 6 FROM emp 7 WHERE 8 AND 9 ORDER BY ; 10 emp_rec emp_cur%ROWTYPE; 11 retval BOOLEAN := FALSE; 12 BEGIN 13 OPEN emp_cur; 14 FETCH emp_cur INTO emp_rec; 15 retval := emp_cur%FOUND; 16 CLOSE emp_cur; 17 RETURN retval; 18 19 EXCEPTION 20 WHEN OTHERS 21 THEN 22 RETURN NULL; 23 END empexists;
Notice that the procedure creates appropriate names for the function, cursor, and record from the table name -- and places that table name into the cursor's query. It follows the template approach for functions, so that you have an exception handler to return a NULL in case of problems and a single RETURN in the body of the function. Sure, you have to change the SELECT statement and the key might not be a NUMBER. Regardless, with a simple cut and paste operation, you have more than twenty lines of consistent, readable code.
Another common programming construct for PL/SQL developers is the cursor FOR loop, so it will come as no surprise that PLVgen offers a procedure to generate this loop. The header for the cfloop procedure is:
PROCEDURE cfloop (table_in IN VARCHAR2);
You provide the name of the cursor and cfloop does the rest. The following SQL*Plus session demonstrates the kind of code generated, in this case for a cursor FOR loop based on the dept table.
SQL> exec PLVgen.cfloop('dept'); CURSOR dept_cur IS SELECT * FROM dept WHERE AND ORDER BY ; << dept_cur >> FOR dept_rec IN dept_cur LOOP END LOOP dept_cur;
The cfloop procedure does several convenient things for us: it defaults the SELECT list to * or all columns. It creates a loop label, which is also applied to the END LOOP statement to improve readability. It avoids declaring the record used by the FOR loop -- an unnecessary and potentially hazardous step often taken by poorly informed PL/SQL programmers.
You cut and paste the cursor declaration into the declaration section; move the FOR loop itself into the body of the program; put some executable code in the body of the loop; and off you go.
The PLVtmr package offers an easy-to-use mechanism for timing PL/SQL code execution down to a hundredth of a second. I found that I often used this package to time the performance of the same line or lines of code executed more than once -- in other words, inside a loop. Rather than write these loops again and again, I added a procedure to PLVgen to generate this code for me.
The timer procedure's header is as follows:
PROCEDURE timer (plsql_in IN VARCHAR2);
You provide the line of PL/SQL code you want to execute and PLVgen provides a SQL*Plus script as shown below:
SQL> exec PLVgen.timer ('calc_totals;'); BEGIN PLVtmr.set_factor (&1); PLVtmr.capture; FOR rep IN 1 .. &1 LOOP calc_totals; END LOOP; PLVtmr.show_elapsed ('MSG'); END; /
These lines of code should be placed in a file with the .sql extension. Change the text in the call to show_elapsed , add other lines of code to the body of the loop if desired. Upon execution, you provide the number of iterations (or, in PLVtmr terms, the factor), and the script executes (in this case) the calc_totals procedure that number of times. PLVtmr.show_elapsed then displays the total elapsed time and the per-execution time as well.
|
|
|
|
| 16.1 Options for Best Practices |
|
16.3 Modifying PLVgen Behavior |
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.