Oracle PL/SQL Language Pocket Reference- P8

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

Thêm vào BST

Báo xấu

102
lượt xem 18
download

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

Oracle PL/SQL Language Pocket Reference- P8: This pocket guide features quick-reference information to help you use Oracle's 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 Pribyl's 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. ...

Chủ đề:

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

Lưu

Nội dung Text: 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 O'Reilly & 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 %TYPE Please 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? Let's 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). I Please 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, just waiting to fail. My variable, which is a local representation of database information, is no longer synchronized with that database column. 4.5.1.2 Normalization of local variables Another drawback to explicit declarations arises when working with PL/SQL variables which store and manipulate calculated values not found in the database. Suppose my programmers built an application to manage my company's finances. I am very bottom-line oriented, so many different programs make use of a total_revenue variable, declared as follows: total_revenue NUMBER (10,2); Yes, I like to track my total revenue down to the last penny. Now, in 1992, when specifications for the application were first written, the maximum total revenue I ever thought I could possibly obtain from any single customer was $99 million, so we used the NUMBER (10,2) declaration, which seemed like plenty. Then in 1995, my proposal to convert B-2 bombers to emergency transport systems to deliver Midwestern wheat to famine regions was accepted: a $2 billion contract! I was just about ready to pop the corks on the champagne when my lead programmer told me the bad news: I wouldn't be able to generate reports on this newest project and customer: those darn total_revenue variables were too small! What a bummer...I had to fire the guy. Just kidding. Instead, we quickly searched out any and all instances of the revenue variables so that we could change the declarations. This was a time-consuming job because we had spread equivalent declarations throughout the entire application. I had, in effect, denormalized my local data structures, with the usual consequences on maintenance. If only I had a way to define each of local total revenue variables in relation to a single datatype. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
If only they had used %TYPE! 4.5.2 Anchoring at Compile Time The %TYPE declaration attribute anchors the datatype of one variable to that of another data structure at the time a PL/SQL block is compiled. If a change is made to the "source" datatype, then any program which contains a declaration anchored to this datatype must be recompiled before it will be able to use this new state of the datatype. The consequences of this rule differ for PL/SQL modules stored in the database and those defined in client-side tools, such as Oracle Forms. Consider the following declaration of company_name in the procedure display_company: PROCEDURE display_company (company_id_in IN INTEGER) IS company_name company.name%TYPE; BEGIN ... END; When PL/SQL compiles this module, it looks up the structure of the company table in the data dictionary, finds the column NAME, and obtains its datatype. It then uses this data dictionary-based datatype to define the new variable. What, then, is the impact on the compiled display_company procedure if the datatype for the name column of the company table changes? There are two possibilities: q If display_company is a stored procedure, then the compiled code will be marked as "invalid." The next time a program tries to run display_company, it will be recompiled automatically before it is used. q If display_company is a client-side procedure, then the Oracle Server cannot mark the program as invalid. The compiled client source code remains compiled using the old datatype. The next time you execute this module, it could cause a VALUE_ERROR exception to be raised. Whether stored or in client-side code, you should make sure that all affected modules are recompiled after data structure changes. 4.5.3 Nesting Usages of the %TYPE Attribute You can nest usages of %TYPE in your declarations as well: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DECLARE /* The "base" variable */ unlimited_revenue NUMBER; /* Anchored to unlimited revenue */ total_revenue unlimited_revenue%TYPE; /* Anchored to total revenue */ total_rev_94 total_revenue%TYPE; total_rev_95 total_revenue%TYPE; BEGIN In this case total_revenue is based on unlimited_revenue and both variables for 1994 and 1995 are based on the total_revenue variable. There is no practical limit on the number of layers of nested usages of %TYPE. 4.5.4 Anchoring to Variables in Other PL/SQL Blocks The declaration of the source variable for your %TYPE declarations does not need to be in the same declaration section as the variables which use it. That variable must simply be visible in that section. The variable could be a global PL/SQL variable (defined in a package) or be defined in an PL/SQL block which contains the current block, as in the following example: PROCEDURE calc_revenue IS unlimited_revenue NUMBER; total_revenue unlimited_revenue%TYPE; BEGIN IF TO_CHAR (SYSDATE, 'YYYY') = '1994' THEN DECLARE total_rev_94 total_revenue%TYPE; BEGIN ... END; END IF; END calc_revenue; 4.5.5 Anchoring to NOT NULL Datatypes When you declare a variable, you can also specify the need for the variable to be NOT NULL This NOT NULL declaration constraint is transferred to variables declared with the %TYPE attribute. If I include a NOT NULL in my declaration of a source variable (one that is referenced afterwards in a % TYPE declaration), I must also make sure to specify a default value for the variables which make use of that source variable. Suppose I declare max_available_date NOT NULL in the following example: Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DECLARE max_available_date DATE NOT NULL := LAST_DAY (ADD_MONTHS (SYSDATE, 3)); last_ship_date max_available_date%TYPE; The declaration of last_ship_date will then fail to compile, with the following message: a variable declared NOT NULL must have an initialization assignment. If you use a NOT NULL variable in a %TYPE declaration, the new variable must have a default value provided. The same is not true, however, for variables declared with %TYPE where the source is a database column. The NOT NULL column constraint does not apply to variables declared with the %TYPE attribute. The following code will compile successfully: DECLARE -- Company name is a NOT NULL column in the company table. comp_name company.name%TYPE; BEGIN comp_name := NULL; You will be able to declare the comp_name variable without specifying a default, and you will be able to NULL out the contents of that variable. Previous: 4.4 Variable Oracle PL/SQL Next: 4.6 Programmer- Declarations Programming, 2nd Edition Defined Subtypes 4.4 Variable Declarations Book Index 4.6 Programmer-Defined Subtypes 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: 4.3 NULLs in PL/ Chapter 4 Next: 4.5 Anchored SQL Variables and Program Data Declarations 4.4 Variable Declarations Before you can make a reference to a variable, you must declare it. (The only exception to this rule is for the index variables of FOR loops.) All declarations must be made in the declaration section of your anonymous block, procedure, function, or package (see Chapter 15, Procedures and Functions, for more details on the structure of the declaration section). When you declare a variable, PL/SQL allocates memory for the variable's value and names the storage location so that the value can be retrieved and changed. The declaration also specifies the datatype of the variable; this datatype is then used to validate values assigned to the variable. The basic syntax for a declaration is: [optional default assignment]; where is the name of the variable to be declared and is the datatype or subtype which determines the type of data which can be assigned to the variable. The [optional default assignment] clause allows you to initialize the variable with a value, a topic covered in the next section. 4.4.1 Constrained Declarations The datatype in a declaration can either be constrained or unconstrained. A datatype is constrained when you specify a number which constrains or restricts the magnitude of the value which can be assigned to that variable. A datatype is unconstrained when there are no such restrictions. Consider the datatype NUMBER. It supports up to 38 digits of precision -- and uses up the memory needed for all those digits. If your variable does not require this much memory, you could declare a number with a constraint, such as the following: itty_bitty_# NUMBER(1); large_but_constrained_# NUMBER(20,5); Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Constrained variables require less memory than unconstrained number declarations like this: no_limits_here NUMBER; 4.4.2 Declaration Examples Here are some examples of variable declarations: q Declaration of date variable: hire_date DATE; q This variable can only have one of three values: TRUE, FALSE, NULL: enough_data BOOLEAN; q This number rounds to the nearest hundredth (cent): total_revenue NUMBER (15,2); q This variable-length string will fit in a VARCHAR2 database column: long_paragraph VARCHAR2 (2000); q This constant date is unlikely to change: next_tax_filing_date CONSTANT DATE := '15-APR-96'; 4.4.3 Default Values You can assign default values to a variable when it is declared. When declaring a constant, you must include a default value in order for the declaration to compile successfully. The default value is assigned to the variable with one of the following two formats: := ; DEFAULT ; The can be a literal, previously declared variable, or expression, as the following examples demonstrate: q Set variable to 3: term_limit NUMBER DEFAULT 3; Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
q Default value taken from Oracle Forms bind variable: call_topic VARCHAR2 (100) DEFAULT :call.description; q Default value is the result of a function call: national_debt FLOAT DEFAULT POWER (10,10); q Default value is the result of the expression: order_overdue CONSTANT BOOLEAN := ship_date > ADD_MONTHS (order_date, 3) OR priority_level (company_id) = 'HIGH'; I like to use the assignment operator (:=) to set default values for constants, and the DEFAULT syntax for variables. In the case of the constant, the assigned value is not really a default, but an initial (and unchanging) value, so the DEFAULT syntax feels misleading to me. 4.4.4 NOT NULL Clause If you do assign a default value, you can also specify that the variable must be NOT NULL. For example, the following declaration initializes the company_name variable to PCS R US and makes sure that the name can never be set to NULL: company_name VARCHAR2(60) NOT NULL DEFAULT 'PCS R US'; If your code includes a line like this: company_name := NULL; then PL/SQL will raise the VALUE_ERROR exception. You will, in addition, receive a compilation error with this next declaration: company_name VARCHAR2(60) NOT NULL; Why? Because your NOT NULL constraint conflicts instantly with the indeterminate or NULL value of the company_name variable when it is instantiated. Previous: 4.3 NULLs in PL/ Oracle PL/SQL Next: 4.5 Anchored SQL Programming, 2nd Edition Declarations 4.3 NULLs in PL/SQL Book Index 4.5 Anchored Declarations Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
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: 4.2 Scalar Chapter 4 Next: 4.4 Variable Datatypes Variables and Program Data Declarations 4.3 NULLs in PL/SQL Wouldn't it be nice if everything was knowable, and known? Hmmm. Maybe not. The question, however, is moot. We don't know the answer to many questions. We are surrounded by the Big Unknown, and because Oracle Corporation prides itself on providing database technology to reflect the real world, it supports the concept of a null value. When a variable, column, or constant has a value of NULL, its value is unknown -- indeterminate. "Unknown" is very different from a blank or a zero or the Boolean value FALSE. "Unknown" means that the variable has no value at all and so cannot be compared directly with other variables. The following three rules hold for null values: q A null is never equal to anything else. None of the following IF statements can ever evaluate to TRUE: my_string := ' '; IF my_string = NULL THEN ...--This will never be true. max_salary := 0; IF max_salary = NULL THEN ...--This will never be true. IF NULL = NULL THEN ...--Even this will never be true. q A null is never not equal to anything else. Remember: with null values, you just never know. None of the following IF statements can ever evaluate to TRUE. my_string := 'Having Fun'; your_string := NULL; IF my_string != your_string THEN ..--This will never be true. max_salary := 1234; IF max_salary != NULL THEN ...--This will never be Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
true. IF NULL != NULL THEN ...--This will never be true. q When you apply a function to a null value, you generally receive a null value as a result (there are some exceptions, listed below). A null value cannot be found in a string with the INSTR function. A null string has a null length, not a zero length. A null raised to the 10th power is still null. my_string := NULL; IF LENGTH (my_string) = 0 THEN ...--This will not work. new_value := POWER (NULL, 10);--new_value is set to null value. 4.3.1 NULL Values in Comparisons In general, whenever you perform a comparison involving one or more null values, the result of that comparison is also a null value -- which is different from TRUE or FALSE -- so the comparison cannot help but fail. Whenever PL/SQL executes a program, it initializes all locally declared variables to null (you can override this value with your own default value). Always make sure that your variable has been assigned a value before you use it in an operation. You can also use special syntax provided by Oracle to check dependably for null values, and even assign a null value to a variable. PL/SQL provides a special reserved word, NULL, to represent a null value in PL/SQL. So if you want to actually set a variable to the null value, you simply perform the following assignment: my_string := NULL; If you want to incorporate the possibility of null values in comparison operations, you must perform special case checking with the IS NULL and IS NOT NULL operators. The syntax for these two operators is as follows: IS NULL IS NOT NULL where is the name of a variable, a constant, or a database column. The IS NULL operator returns TRUE when the value of the identifier is the null value; otherwise, it returns FALSE. The IS NOT NULL operator returns TRUE when the value of the identifier is not a null value; otherwise, it returns FALSE. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
4.3.2 Checking for NULL Values Here are some examples describing how to use operators to check for null values in your program: q In the following example, the validation rule for the hire_date is that it cannot be later than the current date and it must be entered. If the user does not enter a hire_date, then the comparison to SYSDATE will fail because a null is never greater than or equal to (>=) anything. The second part of the OR operator, however, explicitly checks for a null hire_date. If either condition is TRUE, then we have a problem. IF hire_date >= SYSDATE OR hire_date IS NULL THEN DBMS_OUTPUT.PUT_LINE (' Date required and cannot be in future.'); END IF; q In the following example, a bonus generator rewards the hard-working support people (not the salespeople). If the employee's commission is over the target compensation plan target, then send a thank you note. If the commission is under target, tell them to work harder, darn it! But if the person has no commission at all (that is, if the commission IS NULL), give them a bonus recognizing that everything they do aids in the sales effort. (You can probably figure out what my job at Oracle Corporation was.) If the commission is a null value, then neither of the first two expressions will evaluate to TRUE: IF :employee.commission >= comp_plan.target_commission THEN just_send_THANK_YOU_note (:employee_id); ELSIF :employee.commission < comp_plan. target_commission THEN send_WORK_HARDER_singing_telegram (:employee_id); ELSIF :employee.commission IS NULL THEN non_sales_BONUS (:employee_id); END IF; q PL/SQL treats a string of zero-length as a NULL. A zero-length string is two single quotes without any characters in between. The following two assignments are equivalent: my_string := NULL; my_string := ''; 4.3.3 Function Results with NULL Arguments Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
While it is generally true that functions which take a NULL argument return the null value, there are several exceptions: q Concatenation. There are two ways to concatenate strings: the CONCAT function (described in Chapter 11) and the concatenation operator (double vertical bars: ||). In both cases, concatenation ignores null values, and simply concatenates "around" the null. Consider the following examples: CONCAT ('junk', NULL) ==> junk 'junk' || NULL || ' ' || NULL || 'mail' ==> junk mail Of course, if all the individual strings in a concatenation are NULL, then the result is also NULL. q The NVL function. The NVL function (described in Chapter 13) exists specifically to translate a null value to a non-null value. It takes two arguments. If the first argument is NULL, then the second argument is returned. In the following example, I return the string `Not Applicable' if the incoming string is NULL: new_description := NVL (old_description, 'Not Applicable'); q The REPLACE function. The REPLACE function (described in Chapter 11) returns a string in which all occurrences of a specified match string are replaced with a replacement string. If the match_string is NULL, then REPLACE does not try to match and replace any characters in the original string. If the replace_string is NULL, then REPLACE removes from the original string any characters found in match_string. Although there are some exceptions to the rules for null values, nulls must generally be handled differently from other data. If your data has NULLS, whether from the database or in local variables, you will need to add code to either convert your null values to known values, or use the IS NULL and IS NOT NULL operators for special case null value handling. Previous: 4.2 Scalar Oracle PL/SQL Next: 4.4 Variable Datatypes Programming, 2nd Edition Declarations 4.2 Scalar Datatypes Book Index 4.4 Variable Declarations 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: 4.1 Identifiers Chapter 4 Next: 4.3 NULLs in PL/ Variables and Program Data SQL 4.2 Scalar Datatypes Each constant and variable element you use in your programs has a datatype. The datatype dictates the storage format, the restrictions on how the variable can be used, and the valid values which may be placed in that variable. PL/SQL offers a comprehensive set of predefined scalar and composite datatypes. A scalar datatype is an atomic; it is not made up of other variable components. A composite datatype has internal structure or components. The two composite types currently supported by PL/SQL are the record and table (described in Chapter 9, Records in PL/SQL, and Chapter 10, PL/SQL Tables, respectively). The scalar datatypes fall into one of four categories or families: number, character, Boolean, and date- time, as shown in Table 4.1. Table 4.1: Datatype Categories Category Datatype Number BINARY_INTEGER DEC DECIMAL DOUBLE PRECISION FLOAT Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
INT INTEGER NATURAL NUMBER NUMERIC PLS_INTEGER POSITIVE REAL SMALLINT Character CHAR CHARACTER LONG LONG RAW NCHAR NVARCHAR2 RAW ROWID Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
STRING VARCHAR VARCHAR2 Boolean BOOLEAN Date-time DATE Large object (LOB) BFILE BLOB CLOB NCLOB Let's take a closer look at each of the scalar datatypes. 4.2.1 Numeric Datatypes PL/SQL, just like the Oracle RDBMS, offers a variety of numeric datatypes to suit different purposes. There are generally two types of numeric data: whole number and decimal (in which digits to the right of the decimal point are allowed). 4.2.1.1 Binary integer datatypes The whole number, or integer, datatypes are: BINARY_INTEGER INTEGER SMALLINT INT POSITIVE NATURAL Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The BINARY_INTEGER datatype allows you to store signed integers. The range of magnitude of a BINARY_INTEGER is -231 + 1 through 231 - 1 (231 is equal to 2147483647). BINARY_INTEGERs are represented in the PL/SQL compiler as signed binary numbers. They do not, as a result, need to be converted before PL/SQL performs numeric calculations. Variables of type NUMBER (see Section 4.2.1.2, "Decimal numeric datatypes"") do, however, need to be converted. So if you will be performing intensive calculations with integer values, you might see a performance improvement by declaring your variables as BINARY_INTEGER. In most situations, to be honest, the slight savings offered by BINARY_INTEGER will not be noticeable. NATURAL and POSITIVE are both subtypes of BINARY_INTEGER. A subtype uses the storage format and restrictions on how the variable of this type can be used, but it allows only a subset of the valid values allowed by the full datatype. In the case of BINARY_INTEGER subtypes, we have the following value subsets: NATURAL 0 through 231 POSITIVE 1 through 231 If you have a variable whose values must always be non-negative (0 or greater), you should declare that variable to be NATURAL or POSITIVE. This improves the self-documenting aspect of your code. 4.2.1.2 Decimal numeric datatypes The decimal numeric datatypes are: NUMBER FLOAT DEC DECIMAL DOUBLE PRECISION NUMBER NUMERIC REAL Use the NUMBER datatype to store fixed or floating-point numbers of just about any size. The maximum precision of a variable with NUMBER type is 38 digits. This means that the range of magnitude of values is 1.0E-129 through 9.999E125; you are unlikely to require numbers outside of this range. Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
When you declare a variable type NUMBER, you can also optionally specify the variable's precision and scale, as follows: NUMBER (precision, scale) The precision of a NUMBER is the total number of digits. The scale dictates the number of digits to the right or left of the decimal point at which rounding occurs. Both the precision and scale values must be literal values (and integers at that); you cannot use variables or constants in the declaration. Legal values for the scale range from -84 to 127. Rounding works as follows: q If the scale is positive, then the scale determines the point at which rounding occurs to the right of the decimal point. q If the scale is negative, then the scale determines the point at which rounding occurs to the left of the decimal point. q If the scale is zero, then rounding occurs to the nearest whole number. q If the scale is not specified, then no rounding occurs. The following examples demonstrate the different ways you can declare variables of type NUMBER: q The bean_counter variable can hold values with up to ten digits of precision, three of which are to the right of the decimal point. If you assign 12345.6784 to bean_counter, it is rounded to 12345.678. If you assign 1234567891.23 to the variable, the operation will return an error because there are more digits than allowed for in the precision. bean_counter NUMBER (10,3); q The big_whole_number variable contains whole numbers spanning the full range of supported values, because the default precision is 38 and the default scale is 0. big_whole_number NUMBER; q The rounded_million variable is declared with a negative scale. This causes rounding to the left of the decimal point. Just as a scale of -1 would cause rounding to the nearest tenth, a scale of -2 would round to the nearest hundred and a scale of -6 would round to the nearest million. If you assign 53.35 to rounded_million, it will be rounded to 0. If you assign 1,567,899 to rounded_million, it will be rounded to two million (2,000,000). rounded_million NUMBER (10,-6); q In the following unusual but perfectly legitimate declaration, the scale is larger than the precision. In this case, the precision indicates the maximum number of digits allowed -- all to the right of the decimal point. If you assign .003566 to small_value, it will be rounded to .00357. Because the scale is two greater than the precision, any value assigned to small_value must have two zeros directly to the right of the decimal point, followed by up to Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.