Oracle PLSQL Language- P18

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

66
lượt xem 7
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'oracle plsql language- p18', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle PLSQL Language- P18

END; /* The total_cost function uses net_profit. */ FUNCTION total_cost (. . .) RETURN NUMBER IS BEGIN IF net_profit (. . .) < 0 THEN RETURN 0; ELSE RETURN . . .; END IF; END; BEGIN . . . END; Here are some rules to remember concerning forward declarations: q You cannot make forward declarations of a variable or cursor. This technique works only with modules (procedures and functions). q The definition for a forwardly-declared program must be contained in the declaration section of the same PL/SQL block (anonymous block, procedure, function, or package) in which you code the forward declaration. In some situations, you absolutely require forward declarations; in most situations, they just help make your code more readable and presentable. As with every other advanced or unusual feature of the PL/SQL language, use forward declarations only when you really need the functionality. Otherwise, the declarations simply add to the clutter of your program, which is the last thing you want. Previous: 15.8 Module Oracle PL/SQL Next: 15.10 Go Forth and Overloading Programming, 2nd Edition Modularize! 15.8 Module Overloading Book Index 15.10 Go Forth and Modularize! The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 15.9 Forward Chapter 15 Next: 16. Packages Declarations Procedures and Functions 15.10 Go Forth and Modularize! As the PL/SQL language and Oracle tools mature, you will find that you are being asked to implement increasingly complex applications with this technology. To be quite frank, you don't have much of a chance of success in implementing such large-scale projects without an intimate familiarity with the modularization techniques available in PL/SQL. While this book could not possibly provide a full treatment of modularization in PL/SQL, it should give you some solid pointers and a foundation from which to work. There is still much more for you to learn -- the full capabilities of packages, the awesome range of package extensions Oracle Corporation now provides with the tools and database, and the various options for code reusability -- and more. Behind all of that technology, however, you must develop an instinct, a sixth sense, for modularization. Develop a deep and abiding allergy to code redundancy and the hardcoding of values and formulas. Apply a fanatic's devotion to the modular construction of true black boxes which easily plug-and-play in and across applications. You will find yourself spending more time in the design phase and less time in debug mode. Your programs will be more readable and maintainable. They will stand as elegant testimonies to your intellectual integrity. You will be the most popular kid in your class and ... but enough already. I am sure you are properly motivated. Go forth and modularize! Previous: 15.9 Forward Oracle PL/SQL Next: 16. Packages Declarations Programming, 2nd Edition 15.9 Forward Declarations Book Index 16. Packages The Oracle Library Navigation Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 15.10 Go Forth Chapter 16 Next: 16.2 Overview of and Modularize! Package Structure 16. Packages Contents: The Benefits of Packages Overview of Package Structure The Package Specification The Package Body Package Data Package Initialization A package is a collection of PL/SQL objects that are packaged or grouped together within a special BEGIN-END syntax, a kind of "meta-block." Here is a partial list of the kinds of objects you can place in a package: q Cursors q Variables (scalars, records, tables, etc.) q Constants q Exception names q PL/SQL table and record TYPE statements q Procedures q Functions Packages are among the least understood and most underutilized features of PL/SQL. That is a shame, because the package structure is also one of the most useful constructs for building well- designed PL/SQL-based applications. Packages provide a structure in which you can organize your modules and other PL/SQL elements. They encourage proper structured programming techniques in an environment that often befuddles the implementation of structured programming. Oracle Corporation itself uses the package construct to extend the PL/SQL language. Appendix C, Built-In Packages, contains descriptions of many of these predefined packages. In fact, the most basic operators of the PL/SQL language, such as the + and LIKE operators and the INSTR function, are all defined in a special package called STANDARD. If Oracle believes that packages are the way to go when it comes to building both fundamental and complex programs, don't you think that you could Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
benefit from the same? Packages are, by nature, highly modular. When you place a program unit into a package you automatically create a context for that program. By collecting related PL/SQL elements in a package, you express that relationship in the very structure of the code itself. Packages are often called "the poor man's objects" because they support some, but not all, object-oriented rules. For example, packages allow you to encapsulate and abstract your data and functions. The PL/SQL package is a deceptively simple, powerful construct. You can in just a few hours learn the basic elements of package syntax and rules; there's not all that much to it. You can spend days and weeks, however, uncovering all the nuances and implications of the package structure. This chapter -- and the next one filled with examples of packages -- will help you absorb the features and benefits of the PL/SQL package more rapidly. 16.1 The Benefits of Packages Before we explore all the aspects of working with packages, let's review some of the most important benefits of the package: 16.1.1 Enforced Information Hiding When you build a package, you decide which of the package elements are public (can be referenced outside of the package) and which are private (available only within the package itself). You also can restrict access to the package to only the specification. In this way, you use the package to hide the implementational details of your programs. This is most important when you want to isolate the most volatile aspects of your application, such as platform dependencies, frequently changing data structures, and temporary workarounds. 16.1.2 Object-Oriented Design While PL/SQL does not yet offer full object-oriented capabilities, packages do offer the ability to follow many object-oriented design principles. The package gives developers very tight control over how the modules and data structures inside the package can be accessed. You can, therefore, embed all the rules about and access to your entities (whether they are database tables or memory-based structures) in the package. Because this is the only way to work with that entity, you have in essence created an abstracted and encapsulated object. 16.1.3 Top-Down Design A package's specification can be written before its body. You can, in other words, design the interface to the code hidden in the package (the modules, their names, and their parameters) before you have actually implemented the modules themselves. This feature dovetails nicely with top-down design, in which you move from high-level requirements to functional decompositions to module calls. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Of course, you can design the names of standalone modules just as you can the names of packages and their modules. The big difference with the package specification is that you can compile it even without its body. Furthermore, and most remarkably, programs that call packaged modules will compile successfully -- as long as the specification compiles. 16.1.4 Object Persistence PL/SQL packages offer the ability to implement global data in your application environment. Global data is information that persists across application components; it isn't just local to the current module. If you designed screens with SQL*Forms or Oracle Forms, you are probably familiar with its GLOBAL variables, which allow you to pass information between screens. Those globals have their limitations (GLOBAL variables are always represented as fixed-length CHAR variables with a length of 254), but they sure can be useful. Objects declared in a package specification (that is, visible to anyone with EXECUTE authority on that package) act as global data for all PL/SQL objects in the application. If you have access to the package, you can modify package variables in one module and then reference those changed variables in another module. This data persists for the duration of a user session (connection to the database). If a packaged procedure opens a cursor, that cursor remains open and is available to other packaged routines throughout the session. You do not have to explicitly define the cursor in each program. You can open it in one module and fetch it in another module. In addition, package variables can carry data across the boundaries of transactions, because they are tied to the session itself and not to a transaction. 16.1.5 Performance Improvement When an object in a package is referenced for the first time, the entire package (already compiled and validated) is loaded into memory (the Shared Global Area [SGA] of the RDBMS). All other package elements are thereby made immediately available for future calls to the package. PL/SQL does not have to keep retrieving program elements or data from disk each time a new object is referenced. This feature is especially important in a distributed execution environment. You may reference packages from different databases across a local area or even a wide area network. You want to minimize the network traffic involved in executing your code. Packages also offer performance advantages on the development side (with potential impact on overall database performance). The Oracle RDBMS automatically tracks the validity of all program objects (procedures, functions, packages) stored in the database. It determines what other objects that program is dependent on, such as tables. If a dependent object such as a table's structure changes, for example, then all programs that rely on that object are flagged as invalid. The database then automatically recompiles these invalid programs before they are used. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
You can limit automatic recompiles by placing functions and procedures inside packages. If program A calls packaged module B, it does so through the package's specification. As long as the specification of a packaged module does not change, any program that calls the module is not flagged as invalid and will not have to be recompiled. This chapter should provide you with all the information and examples you need to put packages to work immediately in your applications. If you are still unsure about packages after reading it, try out a couple of small packages. Test those hard-to-believe features like global package data to prove to yourself that they really work as advertised. Examine carefully the examples in Chapter 18, Object Types. Do whatever you need to do to incorporate packages into every level of your application, from database server to client applications. Previous: 15.10 Go Forth Oracle PL/SQL Next: 16.2 Overview of and Modularize! Programming, 2nd Edition Package Structure 15.10 Go Forth and Book Index 16.2 Overview of Package Modularize! Structure The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 16.1 The Benefits Chapter 16 Next: 16.3 The Package of Packages Packages Specification 16.2 Overview of Package Structure A package provides an extra layer of code and structure over that of an individual module. Many of the concepts needed to understand a package's structure will be familiar to you. In fact, a package is very similar in structure to a PL/SQL module that has local modules defined within it. Whereas a module has a header and a body, a package has a specification and a body. Just as the module's header explains to a developer how to call that module, the package specification describes the different elements of the package that can be called. Beyond that, however, there are key differences between the constructs in the module and in the package. A module's specification and body are connected by the IS keyword; both are required and one cannot be written without the other. The specification determines how you call the module. The body, after the IS keyword, contains the code that is executed when the function is used. These two components of a module are coded together and are completely inseparable. A package also has a specification and a body, but the package's two parts are structured differently, and have a different significance, from those for a single module. With a package, the specification and body are completely distinct objects. You can write and compile the specification independently of the body. When you create and replace stored packages in the database, you perform this action separately for each of the specification and body. This separation of specification and body allows you to employ top-down design techniques in a powerful way. Don't worry about the details of how a procedure or function is going to do its job. Just concentrate on the different modules you need and how they should be connected together. 16.2.1 The Specification The package specification contains the definition or specification of all elements in the package that may be referenced outside of the package. These are called the public elements of the package (see the section entitled Section 16.2.4, "Public and Private Package Elements"" for more information). Figure 16.1 shows an example of a package specification containing two module definitions. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 16.1: The specification of sp_timer package Like the module, the package specification contains all the code that is needed for a developer to understand how to call the objects in the package. A developer should never have to examine the code behind the specification (which is the body) in order to understand how to use and benefit from the package. 16.2.2 The Body The body of the package contains all the code behind the package specification: the implementation of the modules, cursors, and other objects. Figure 16.2 illustrates the body required to implement the specification of the sp_timer package shown in Figure 16.1. Figure 16.2: The body of sp_timer package The body may also contain elements that do not appear in the specification. These are called the private elements of the package. A private element cannot be referenced outside of the package, since Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
it does not appear in the specification. The body of the package resembles a standalone module's declaration section. It contains both declarations of variables and the definitions of all package modules. The package body may also contain an execution section, which is called the initialization section because it is only run once, to initialize the package. 16.2.3 Package Syntax The general syntax for the two parts of a package follows: q The package specification: PACKAGE package_name IS [ declarations of variables and types ] [ specifications of cursors ] [ specifications of modules ] END [ package_name ]; You can declare variables and include specifications of both cursors and modules (and only the specifications). You must have at least one declaration or specification statement in the package specification. Notice that the package specification has its own BEGIN-END block syntax. This enables its independent existence and compilation from the package body. q The package body: PACKAGE BODY package_name IS [ declarations of variables and types ] [ specification and SELECT statement of cursors ] [ specification and body of modules ] [ BEGIN executable statements ] [ EXCEPTION exception handlers ] Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
END [ package_name ]; In the body you can declare other variables, but you do not repeat the declarations in the specification. The body contains the full implementation of cursors and modules. In the case of a cursor, the package body contains both specification and SQL statement for the cursor. In the case of a module the package body contains both the specification and body of the module. The BEGIN keyword indicates the presence of an execution or initialization section for the package. This section can also optionally include an exception section. As with a procedure or function, you can add the name of the package, as a label, after the END keyword in both the specification and package. 16.2.4 Public and Private Package Elements A central concept of packages is the privacy level of its elements. One of the most valuable aspects of a package is its ability to actually enforce information hiding. With a package you can not only modularize your secrets behind a procedural interface, you can keep these parts of your application completely private. An element of a package, whether it is a variable or a module, can either be private or public, as defined below: Public Defined in the specification. A public element can be referenced from other programs and PL/ SQL blocks. Private Defined only in the body of the package, but does not appear in the specification. A private element cannot be referenced outside of the package. Any other element of the package may, however, reference and use a private element. Private elements in a package must be defined before they can be referenced by other elements of the package. If, in other words, a public procedure calls a private function, that function must be defined above the public procedure in the package body. You can, alternatively, use a forward declaration if you wish to keep your private programs at the bottom of the package body (see Chapter 15, Procedures and Functions). If you find that a formerly private object such as a module or cursor should instead be made public, simply add that object to the package specification and recompile. It will then be visible outside of the package. The distinct difference between public and private elements gives PL/SQL developers unprecedented Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
control over their data structures and programs. Figure 16.3 shows a Booch diagram[1] for the package that displays private and public package elements. [1] This diagram is named after Grady Booch, who pioneered many of the ideas of the package, particularly in the context of object-oriented design. Figure 16.3: Booch diagram showing public and private package elements The diagram offers a clear representation of the public/private character of PL/SQL packages. The large rectangle establishes the graphical boundary of the package. A program external to the package may only call a package element if a part of the element extends past the boundary of the package. Thus, all public elements defined in the specification straddle the package boundary. Part of the element is inside the boundary, and part lies outside, accessible to other programs. All the objects (data and modules) that are completely surrounded by the boundary of the package are private objects. These are defined only in the package body and do not appear in the specification. Because they are wholly contained, no external program can reference those elements. Because the border of the public elements exists both outside and inside the package boundary, all elements in the package (private and public) can use those elements. The next section offers a quick tour of a simple package which illustrates these concepts. 16.2.5 How to Reference Package Elements A package owns its objects, just as a table owns its columns. You use the same dot notation to provide a fully qualified specification for a package's object as you would for a table's column. The following package specification declares a constant, an exception, a cursor, and several modules: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PACKAGE pets_inc IS max_pets_in_facility CONSTANT INTEGER := 120; pet_is_sick EXCEPTION; CURSOR pet_cur RETURN pet%ROWTYPE; FUNCTION next_pet_shots (pet_id_in IN NUMBER) RETURN DATE; PROCEDURE set_schedule (pet_id_in IN NUMBER); END pets_inc; To reference any of these objects, I preface the object name with the package name, as follows: BEGIN IF pets_inc.max_pets_in_facility > 100 THEN ... END IF; EXCEPTION WHEN pets_inc.pet_is_sick THEN ... END; OPEN pets_inc.pet_cur; :pet_master.next_appointment := pets_inc.next_pet_shots (:pet_master.pet_id); If you do not preface the call to next_pet_shots with the package name, pets_inc, PL/SQL is not able to resolve the reference and the compile fails. So, the rule for referencing package elements is simple and clear: To reference a stored package element, use dot notation. The one exception is that inside a package, you do not need to qualify references to other elements of that package. PL/SQL will automatically resolve your reference within the scope of the package. Suppose, for example, that the set_schedule procedure of pets_inc references the max_pets_in_facility constant. Such a reference would be unqualified as shown here: PROCEDURE set_schedule (pet_id_in IN NUMBER) IS ... Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN ... IF total_pets < max_pets_in_facility THEN ... END IF; END; Of course, if you want to reference the element of a second package inside the current package, you will need to include the name of that package. 16.2.6 Quick Tour of a Package The pet maintenance package defined below is used by veterinarians to keep track of their patients and to determine when a pet needs another visit. Its specification identifies the public elements of the package. The body implements those elements and also creates two private elements. 16.2.6.1 The pets_inc package specification The specification for the pets_inc package establishes the five public elements: Name Type Description petid_type Subtype definition Creates a programmer-defined subtype for the pet table primary key. petid_nu Variable declaration Represents the primary key in pet table. pet_cur Cursor specification Retrieves information about specified pet. next_pet_shots Function specification Returns the date for next shots. set_schedule Procedure Sets the schedule for the specified pet. specification Here is the pets_inc package specification: PACKAGE pets_inc IS SUBTYPE petid_type IS pet.pet_id%TYPE; petid_nu petid_type; CURSOR pet_cur (pet_name_in IN VARCHAR2) RETURN pet% ROWTYPE; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
FUNCTION next_pet_shots (pet_id_in IN petid_type) RETURN DATE; PROCEDURE set_schedule (pet_id_in IN petid_type) END pets_inc; The header for the package specification simply states PACKAGE. You do not explicitly indicate that it is the specification, as in PACKAGE SPECIFICATION. Instead, when you create the body of a package, you indicate explicitly in the first line of the definition that you are defining the body of the pets_inc package. Since all of these elements are in the package, I can reference them in other programs, such as the following procedure: PROCEDURE show_next_visit (pet_in IN VARCHAR2) IS next_visit DATE; /* Declare record to receive row fetched from package cursor. */ pet_rec pets_inc.pet_cur%ROWTYPE; BEGIN /* Open the package-based cursor. */ OPEN pets_inc.pet_cur (pet_in); /* Fetch from cursor into local record. */ FETCH pets_inc.pet_cur INTO pet_rec; IF pets_inc.pet_cur%FOUND THEN /* Call packaged function to get next visit date. */ next_visit := pets_inc.next_pet_shots (pet_rec. pet_id); /* Display the information. */ DBMS_OUTPUT.PUT_LINE ('Schedule next visit for ' || pet_in || ' on ' || TO_CHAR (next_visit)); END IF; CLOSE pets_inc.pet_cur; END; 16.2.6.2 The pets_inc package body Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The package body for pets_inc contains elements shown in the following table: Name Type Description max_date Constant Private variable. Maximum date llowed. declaration pet_cur Cursor declaration The cursor specification and SQL statement that retrieves information about specified pet. pet_status Function definition Private module. The specification and body for the function that returns the status of teh pet. next_pet_shots Function definition The specification and body for the function that returns date for next shots. set_schedule Procedure The specification and body for the procedure that sets the definition schedule for the specified pet. Here is the pets_inc package body: PACKAGE BODY pets_inc IS max_date CONSTANT DATE := SYSDATE + 10; CURSOR pet_cur (pet_name_in IN VARCHAR2) RETURN pet% ROWTYPE IS SELECT * FROM pet; FUNCTION pet_status (pet_id_in IN petid_type) RETURN VARCHAR2 IS BEGIN ... code behind the module ... END; FUNCTION next_pet_shots (pet_id_in IN petid_type) RETURN DATE IS BEGIN ... the code behind the module ... END; PROCEDURE set_schedule (pet_id_in IN petid_type) IS BEGIN Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
... the code behind the module ... END; END pets_inc; The body for the pet maintenance package contains the SELECT statement for the pet_cur cursor as well as the code required to implement all the modules. This package body contains two private elements: max_date and pet_status. The max_date constant is used inside the package modules to validate dates that are manipulated in the package. The pet_status function is used by other modules to retrieve the status of the pet. Because these elements are private, they can only be referenced by other elements of the package. 16.2.6.3 Observations about pets_inc There are several interesting facts to point out about the previous two package components: q The package specification does not contain any executable statements or exception handlers. A specification only specifies, or declares, those objects in the package that are public -- that is, visible outside of the package and callable by other programs. q The declaration of the petid_nu variable and the petid_type subtype are not repeated inside the body. The declaration in the specification is enough for the whole package. Any module in the body can reference variables declared in the specification. q Both the body and the specification of pets_inc are actually extended declaration sections. The package body can, however, also contain execution and exception sections. These two parts of the package body make up the initialization section of the package, which is explored later in this chapter. Now that you've had an introduction to the various parts of the package, let's take a closer look at each package component. Previous: 16.1 The Benefits Oracle PL/SQL Next: 16.3 The Package of Packages Programming, 2nd Edition Specification 16.1 The Benefits of Packages Book Index 16.3 The Package Specification The Oracle Library Navigation Copyright (c) 2000 O'Reilly & Associates. All rights reserved. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 16.2 Overview of Chapter 16 Next: 16.4 The Package Package Structure Packages Body 16.3 The Package Specification The specification of a package lists all the objects in that package that are available for use in applications, and provides all the information a developer needs in order to use objects in the package. A package specification may contain any of the following object specification statements: q Variable declaration. Any kind of variable declaration statement, from a Boolean variable to a character string to a number. This variable is then available outside of the package (as well as within the body of the package). q TYPE declaration (PL/SQL Version 2 only). Any kind of valid TYPE statement, such as those to create a programmer-defined record type or a PL/SQL table. These complex data structures are then available outside of the package (as well as within the body of the package). q Exception declaration. Declare exceptions in a package that can then be raised and handled outside of the package. q Cursor specification (PL/SQL Version 2 only). Specify a cursor's name and its RETURN clause. This cursor can then be opened, fetched, and closed outside of the package (as well as within the body of the package). This is available only in PL/SQL Version 2 because Version 1 does not support the required RETURN clause for a cursor. For more information on cursors, see Chapter 6, Database Interaction and Cursors. q Module specification. Place the full specification for a module in the package specification. A module specification is the module type (PROCEDURE or FUNCTION), module name, parameter list, and RETURN clause (for a function). This module can then be called both from within and outside of the package. Of these object specification statements, only the cursor and the module need to be defined in the body of the package. The cursor and module, in other words, are not completely determined by the specification alone. The cursor needs its SELECT statement. The module needs its executable section. The variable, TYPE, and exception declarations, on the other hand, do not need any additional code for completion -- they need only their declaration statements. You can, therefore, write a package that consists solely of a specification and has no body at all, as you will see in the next section. The specification is the API (Application Programmatic Interface) into the package's contents. A Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
developer should never have to look at the actual code in a package in order to use an object in the specification. The pets_inc package specification shown earlier contains a variable, cursor, and two modules. I do not need to know how the package function next_pet_shots determines the date when a pet will next need its shots. I need to know just the name of the function, its parameters, and the function's return datatype. Similarly, the package makes available a predefined cursor that gets me everything I need to know about a pet based on its primary key, pet_id. I, as a developer, do not have to understand the underlying data structures involved. The cursor might use a SELECT statement against a base table or against a three-way join with a sub-SELECT. None of that matters to me. In fact, my ignorance in these matters liberates my brain to work on how I can apply these package elements to good use in my application. 16.3.1 Packages Without Bodies A package only requires a body if one of the following is true: q You want to define private package elements q You have included a cursor or module in your specification A package may consist solely of declarations of public package elements. In this case, there is no need for a package body. This section offers two examples of scenarios where a bodiless package may be just the ticket. 16.3.1.1 A package of exceptions The exception handler package in the next example declares a set of programmer-defined exception numbers and exceptions to go with them. It also declares a PL/SQL table to hold the associated error messages (see Chapter 8, Exception Handlers, for more information about exceptions and the pragma statement, EXCEPTION_INIT). PACKAGE exchdlr IS en_general_error NUMBER := -20000; exc_general_error EXCEPTION; PRAGMA EXCEPTION_INIT (exc_general_error, -20000); en_must_be_eighteen NUMBER := -20001; exc_must_be_eighteen EXCEPTION; PRAGMA EXCEPTION_INIT (exc_must_be_eighteen, -20001); max_error_number_used NUMBER := -20001; TYPE error_msg_tabtype IS TABLE OF VARCHAR2 (240) INDEX BY BINARY_INTEGER; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
error_msg_table error_msg_tabtype; END exchdlr; Because this package does not specify any cursors or modules, I do not need to create a body for the exception handler package. In Chapter 8 I include a version of this package that does, in fact, specify two procedures in the package. That version does need a package body). 16.3.1.2 A package of magic values A magic value is a literal that has special significance in a system. These values might be type codes or validation limits. Your users will tell you that these magic values never change. "I will always have only 25 line items in my profit-and-loss," one will say. "The name of the parent company," swears another, "will always be ATLAS HQ." Don't take these promises at face value, and never code them into your programs. Instead of writing code like this: IF footing_difference BETWEEN 1 and 100 THEN adjust_line_item; END IF; IF cust_status = 'C' THEN reopen_customer; END IF; you should instead replace the magic values with named constants, as follows: IF footing_difference BETWEEN min_difference and max_difference THEN adjust_line_item; END IF; IF cust_status = closed_status THEN reopen_customer; END IF; The same magic values often appear in many different modules. Rather than declaring them repeatedly in each module, these magic values should be treated as global data in your application. And where can you create global data in PL/SQL? That's right, in a package! The bodiless package shown in the next example contains all the magic values in my application: PACKAGE config_pkg IS closed_status CONSTANT VARCHAR2(1) := 'C'; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.