Oracle PL/SQL Language Pocket Reference- P8

Oracle PL/SQL Language Pocket Reference- P8: This pocket guide features quick-reference information to help you use Oracles PL/SQL language. It includes coverage of PL/SQL features in the newest version of Oracle, Oracle8i. It is a companion to Steven Feuerstein and Bill Pribyls bestselling Oracle PL/SQL Programming. Updated for Oracle8, that large volume (nearly 1,000 pages) fills a huge gap in the Oracle market, providing developers with a single, comprehensive guide to building applications with PL/SQL and building them the right way. ...
Nội dung trích xuất từ tài liệu:
Oracle PL/SQL Language Pocket Reference- P8 exception: weensy_plus := Lots of room for me to type now; When you create a subtype based on an existing variable or database column, that subtype inherits the length (or precision and scale, in the case of a NUMBER datatype) from the original datatype. This constraint takes effect when you declare variables based on the subtype, but only as a default. You can always override that constraint. You will have to wait for a future version of PL/SQL, however, to actually enforce the constraint in a programmer-defined subtype. Finally, an anchored subtype does not carry over the NOT NULL constraint to the variables it defines. Nor does it transfer a default value that was included in the original declaration of a variable or column specification. Previous: 4.5 Anchored Oracle PL/SQL Next: 4.7 Tips for Creating Declarations Programming, 2nd Edition and Using Variables 4.5 Anchored Declarations Book Index 4.7 Tips for Creating and Using Variables The Oracle Library Navigation Copyright (c) 2000 OReilly & Associates. All rights reserved.Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Previous: 4.4 Variable Chapter 4 Next: 4.6 Programmer- Declarations Variables and Program Data Defined Subtypes 4.5 Anchored Declarations This section describes the use of the %TYPE declaration attribute to anchor the datatype of one variable to another data structure, such as a PL/SQL variable or a column in a table. When you anchor a datatype, you tell PL/SQL to set the datatype of one variable from the datatype of another element. The syntax for an anchored datatype is: %TYPE [optional default value assignment]; where is the name of the variable you are declaring and is any of the following: q Previously declared PL/SQL variable name q Table column in format table.column Figure 4.2 shows how the datatype is drawn both from a database table and PL/SQL variable. Figure 4.2: Anchored declarations with %TYPEPlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Here are some examples of %TYPE used in declarations: q Anchor the datatype of monthly_sales to the datatype of total_sales: total_sales NUMBER (20,2); monthly_sales total_sales%TYPE; q Anchor the datatype of the company ID variable to the database column: company_id# company.company_id%TYPE; Anchored declarations provide an excellent illustration of the fact that PL/SQL is not just a procedural-style programming language but was designed specifically as an extension to the Oracle SQL language. A very thorough effort was made by Oracle Corporation to tightly integrate the programming constructs of PL/SQL to the underlying database (accessed through SQL). NOTE: PL/SQL also offers the %ROWTYPE declaration attribute, which allows you to create anchored datatypes for PL/SQL record structures. %ROWTYPE is described in Chapter 9. 4.5.1 Benefits of Anchored Declarations All the declarations you have so far seen -- character, numeric, date, Boolean -- specify explicitly the type of data for that variable. In each of these cases, the declaration contains a direct reference to a datatype and, in most cases, a constraint on that datatype. You can think of this as a kind of hardcoding in your program. While this approach to declarations is certainly valid, it can cause problems in the following situations: q Synchronization with database columns. The PL/SQL variable represents database information in the program. If I declare explicitly and then change the structure of the underlying table, my program may not work properly. q Normalization of local variables. The PL/SQL variable stores calculated values used throughout the application. What are the consequences of repeating (hardcoding) the same datatype and constraint for each declaration in all of my programs? Lets take a look at each of these scenarios in more detail. 4.5.1.1 Synchronization with database columns Databases hold information that needs to be stored and manipulated. Both SQL and PL/SQL perform these manipulations. Your PL/SQL programs often read data from a database into local program variables, and then write information from those variables back into the database. Suppose I have a company table with a column called NAME and a datatype of VARCHAR2(60). IPlease purchase PDF Split-Merge on www.verypdf.com to remove this watermark. can therefore create a local variable to hold this data as follows: DECLARE cname VARCHAR2(60); and then use this variable to represent this database information in my program. Now, consider an application which uses the company entity. There may be a dozen different screens, procedures, and reports which contain this same PL/SQL declaration, VARCHAR2(60), over and over again. And everything works just fine...until the business requirements change or the DBA has a change of heart. With a very small effort, the definition of the name column in the company table changes to VARCHAR2(100), in order to accommodate longer company names. Suddenly the database can store names which will raise VALUE_ERROR exceptions when FETCHed into the company_name variable. My programs have become incompatible with the underlying data structures. All declarations of cname (and all the variations programmers employed for this data throughout the system) must be modified. Otherwise, my application is simply a ticking time bomb, ...