[ Pobierz całość w formacie PDF ]
.(You can probably figure out what my job at OracleCorporation was.) If the commission is a null value, then neither of the first two expressions willevaluate to TRUE:IF :employee.commission >= comp_plan.target_commissionTHENjust_send_THANK_YOU_note (:employee_id);4.3.1 NULL Values in Comparisons 170[Appendix A] What's on the Companion Disk?ELSIF :employee.commission junk'junk' || NULL || ' ' || NULL || 'mail' ==> junk mailOf course, if all the individual strings in a concatenation are NULL, then the result is also NULL."The NVL function.The NVL function (described in Chapter 13) exists specifically to translate a nullvalue to a non-null value.It takes two arguments.If the first argument is NULL, then the secondargument is returned.In the following example, I return the string `Not Applicable' if the incomingstring is NULL:new_description := NVL (old_description, 'Not Applicable');"The REPLACE function.The REPLACE function (described in Chapter 11) returns a string in whichall occurrences of a specified match string are replaced with a replacement string.If the match_stringis NULL, then REPLACE does not try to match and replace any characters in the original string.Ifthe replace_string is NULL, then REPLACE removes from the original string any characters found inmatch_string.Although there are some exceptions to the rules for null values, nulls must generally be handled differentlyfrom other data.If your data has NULLS, whether from the database or in local variables, you will need toadd code to either convert your null values to known values, or use the IS NULL and IS NOT NULLoperators for special case null value handling.4.2 Scalar Datatypes 4.4 Variable Declarations4.3.3 Function Results with NULL Arguments 171[Appendix A] What's on the Companion Disk?Copyright (c) 2000 O'Reilly & Associates.All rights reserved.4.3.3 Function Results with NULL Arguments 172Chapter 4Variables and ProgramData4.4 Variable DeclarationsBefore you can make a reference to a variable, you must declare it.(The only exception to this rule is for theindex variables of FOR loops.) All declarations must be made in the declaration section of your anonymousblock, procedure, function, or package (see Chapter 15, Procedures and Functions, for more details on thestructure of the declaration section).When you declare a variable, PL/SQL allocates memory for the variable's value and names the storagelocation so that the value can be retrieved and changed.The declaration also specifies the datatype of thevariable; 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 subtypewhich 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 DeclarationsThe datatype in a declaration can either be constrained or unconstrained.A datatype is constrained when youspecify a number which constrains or restricts the magnitude of the value which can be assigned to thatvariable.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 neededfor all those digits.If your variable does not require this much memory, you could declare a number with aconstraint, such as the following:itty_bitty_# NUMBER(1);large_but_constrained_# NUMBER(20,5);Constrained variables require less memory than unconstrained number declarations like this:no_limits_here NUMBER;4.4.2 Declaration ExamplesHere are some examples of variable declarations:"Declaration of date variable:hire_date DATE;"173[Appendix A] What's on the Companion Disk?This variable can only have one of three values: TRUE, FALSE, NULL:enough_data BOOLEAN;"This number rounds to the nearest hundredth (cent):total_revenue NUMBER (15,2);"This variable-length string will fit in a VARCHAR2 database column:long_paragraph VARCHAR2 (2000);"This constant date is unlikely to change:next_tax_filing_date CONSTANT DATE := '15-APR-96';4.4.3 Default ValuesYou can assign default values to a variable when it is declared.When declaring a constant, you must includea default value in order for the declaration to compile successfully.The default value is assigned to thevariable with one of the following two formats::= ;DEFAULT ;The can be a literal, previously declared variable, or expression, as the following examplesdemonstrate:"Set variable to 3:term_limit NUMBER DEFAULT 3;"Default value taken from Oracle Forms bind variable:call_topic VARCHAR2 (100) DEFAULT :call.description;"Default value is the result of a function call:national_debt FLOAT DEFAULT POWER (10,10);"Default value is the result of the expression:order_overdue CONSTANT BOOLEAN :=ship_date > ADD_MONTHS (order_date, 3) ORpriority_level (company_id) = 'HIGH';I like to use the assignment operator (:=) to set default values for constants, and the DEFAULT syntax forvariables.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.3 Default Values 174[Appendix A] What's on the Companion Disk?4.4.4 NOT NULL ClauseIf you do assign a default value, you can also specify that the variable must be NOT NULL.For example, thefollowing declaration initializes the company_name variable to PCS R US and makes sure that the name cannever 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 errorwith this next declaration:company_name VARCHAR2(60) NOT NULL;Why? Because your NOT NULL constraint conflicts instantly with the indeterminate or NULL value of thecompany_name variable when it is instantiated.4.3 NULLs in PL/SQL 4.5 Anchored DeclarationsCopyright (c) 2000 O'Reilly & Associates [ Pobierz całość w formacie PDF ]