Teach Yourself PL/SQL in 21 Days- P3

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

Thêm vào BST

Báo xấu

89
lượt xem 11
download

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

Teach Yourself PL/SQL in 21 Days- P3: Welcome to Sams Teach Yourself PL/SQL in 21 Days, Second Edition. The fact that you purchased this book indicates that you have an interest in learning the PL/SQL language, which is Oracle’s relational database procedural programming language. It allows you to develop powerful and complex programs to access and manipulate data in the Oracle8i database. We have attempted to include as many examples of PL/SQL code as possible to illustrate PL/SQL features....

Chủ đề:

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

Lưu

Nội dung Text: Teach Yourself PL/SQL in 21 Days- P3

Writing PL/SQL Expressions 77 AND The AND operator is used to join two comparison expressions when you are interested in testing whether both expressions are true. It can also be used for the same purpose with two Boolean variables—to check to see whether both are equal to true. The Syntax for the AND Operator SYNTAX boolean_expression AND boolean_expression In this syntax, boolean_expression can be any expression resulting in a Boolean, or true/false, value. This is often a comparison expression such as (a = b), but can also be a variable of the BOOLEAN datatype. The AND operator returns a value of true if both expressions each evaluate to true; other- wise, a value of false is returned. Use AND when you need to test several conditions and execute some code only when they are all true. Table 3.7 shows some sample expres- 3 sions using the AND operator. TABLE 3.7 Expressions Using the AND Operator Expression Result (5 = 5) AND (4 < 100) AND (2 >= 2) true (5 = 4) AND (5 = 5) false ‘Mon’ IN (‘Sun’,’Sat’) AND (2 = 2) false OR The OR operator is used to join two comparison expressions when you are interested in testing whether at least one of them is true. It can also be used with two Boolean vari- ables to see whether at least one is set to true. The Syntax for the OR Operator SYNTAX boolean_expression OR boolean_expression In this syntax, boolean_expression can be any expression resulting in a Boolean, or true/false, value. This is often a comparison expression such as (a = b), but can also be a variable of the BOOLEAN datatype. The OR operator returns a value of true if any one of the expressions evaluates to true. A value of false is returned only if both the expressions evaluate to false. Table 3.8 shows some sample expressions using the OR operator.
78 Day 3 TABLE 3.8 Expressions Using the OR Operator Expression Result (5 5) OR (4 >= 100) OR (2 < 2) false (5 = 4) OR (5 = 5) true ‘Mon’ IN (‘Sun’,’Sat’) OR (2 = 2) true String Operators PL/SQL has two operators specifically designed to operate only on character string data. These are the LIKE operator and the concatenation (||) operator. The LIKE operator is a comparison operator used for pattern matching and was described earlier in the section titled “Comparison Operators,” so only the concatenation operator is described here. The Syntax for the Concatenation Operator , SYNTAX string_1 || string_2 In this syntax, string_1 and string_2 are both character strings and can be either string constants, string variables, or a string expression. The concatenation operator returns a result consisting of all the characters in string_1 followed by all the characters in , string_2. Listing 3.6 shows several ways in which you can use the concatenation operator. INPUT LISTING 3.6 Use of the Concatenation Operator 1: --Demonstrate the concatenation operator 2: DECLARE 3: a VARCHAR2(30); 4: b VARCHAR2(30); 5: c VARCHAR2(30); 6: BEGIN 7: --Concatenate several string constants. 8: c := ‘Jack’ || ‘ AND ‘ || ‘Jill’; 9: DBMS_OUTPUT.PUT_LINE(c); 10: --Concatenate both string variables and constants. 11: a := ‘went up’; 12: b := ‘the hill’; 13: DBMS_OUTPUT.PUT_LINE(a || ‘ ‘ || b || ‘,’); 14: --Concatenate two string variables. 15: a := ‘to fetch a ‘; 16: b := ‘pail of water.’; 17: c := a || b; 18: DBMS_OUTPUT.PUT_LINE(c); 19: END; 20: /
Writing PL/SQL Expressions 79 Jack and Jill OUTPUT went up the hill, to fetch a pail of water. PL/SQL procedure successfully completed. The preceding code shows the concatenation operator used in several different ANALYSIS ways. Notice that you do not always have to assign the result directly to a string variable. For example, in line 13, the concatenation operator is used to create a string expression that is passed as input to the PUT_LINE procedure. Using Comparison Operators with Strings You can use any of the PL/SQL comparison operators to compare one character string to another. Strings can be compared for equality, for inequality, to see if one string is less than another, to see if one string matches a given pattern, and so on. When using charac- 3 ter strings in comparison expressions, the result depends on several things: • Character set • Datatype • Case (upper versus lower) The Effect of Character Set on String Comparisons When comparing two strings to see if one is greater or less than another, the result depends on the sort order of the underlying character set being used. In the typical ASCII environment, all lowercase letters are greater than all uppercase letters, digits are less than all letters, and the other characters fall in various places depending on their corre- sponding ASCII codes. However, if you were working in an EBCDIC environment, you would find that all the digits were greater than the letters and all lowercase letters are less than all uppercase letters, so be careful. The Datatype’s Effect on String Comparisons The underlying datatype has an effect when comparing two string variables, or NEW TERM when comparing a string variable with a constant. Remember that variables of the CHAR datatype are fixed-length and padded with spaces. Variables of the VARCHAR2 datatype are variable-length and are not automatically padded with spaces. When com- paring two CHAR datatypes, Oracle uses blank-padded comparison semantics. This means that Oracle conceptually adds enough trailing spaces to the shorter string to make it equal in length to the longer string and then does the comparison. Trailing spaces alone will not result in any differences being found between two springs. Oracle also does the same thing when comparing two string constants. However, when one of the values in a
80 Day 3 comparison is a variable-length string, Oracle uses non-padded comparison semantics. The use of non-padded comparison semantics means that Oracle does not pad either of the values with spaces, and any trailing spaces will affect the result. Listing 3.7 shows several string comparisons that illustrate this point. INPUT LISTING 3.7 Demonstration of String Comparison Semantics 1: --Demonstrate string comparision semantics 2: DECLARE 3: fixed_length_10 CHAR(10); 4: fixed_length_20 CHAR(20); 5: var_length_10 VARCHAR2(10); 6: var_length_20 VARCHAR2(20); 7: BEGIN 8: --Constants are compared using blank-padded comparison semantics, 9: --so the trailing spaces won’t affect the result. 10: IF ‘Jonathan’ = ‘Jonathan ‘ THEN 11: DBMS_OUTPUT.PUT_LINE 12: (‘Constant: ‘’Jonathan’’ = ‘’Jonathan ‘’’); 13: END IF; 14: --Fixed length strings are also compared with blank-padded 15: --comparison semantic, so the fact that one is longer doesn’t matter. 16: fixed_length_10 := ‘Donna’; 17: fixed_length_20 := ‘Donna’; 18: IF fixed_length_20 = fixed_length_10 THEN 19: DBMS_OUTPUT.PUT_LINE(‘Char: ‘’’ || fixed_length_10 || ‘’’ = ‘’’ 20: || fixed_length_20 || ‘’’’); 21: END IF; 22: --Comparison of a fixed length string and a literal also 23: --results is the use of blank-padded comparison semantics. 24: IF fixed_length_10 = ‘Donna’ THEN 25: DBMS_OUTPUT.PUT_LINE(‘Char and constant: ‘’’ 26: || fixed_length_10 || ‘’’ = ‘’’ || ‘Donna’ || ‘’’’); 27: END IF; 28: --But compare a variable length string 29: --against a fixed length, and the 30: --trailing spaces do matter. 31: var_length_10 := ‘Donna’; 32: IF fixed_length_10 = var_length_10 THEN 33: DBMS_OUTPUT.PUT_LINE(‘Char and Varchar2: ‘’’ 34: || fixed_length_10 || ‘’’ = ‘’’ 35: || var_length_10 || ‘’’’); 36: ELSE 37: DBMS_OUTPUT.PUT_LINE(‘Char and Varchar2: ‘’’ 38: || fixed_length_10 || ‘’’ NOT = ‘’’ 39: || var_length_10 || ‘’’’); 40: END IF; 41: --The maximum lengths of varchar2 strings do not matter, 42: --only the assigned values.
Writing PL/SQL Expressions 81 43: var_length_10 := ‘Donna’; 44: var_length_20 := ‘Donna’; 45: IF var_length_20 = var_length_10 THEN 46: DBMS_OUTPUT.PUT_LINE(‘Both Varchar2: ‘’’ 47: || var_length_20 || ‘’’ = ‘’’ 48: || var_length_10 || ‘’’’); 49: ELSE 50: DBMS_OUTPUT.PUT_LINE(‘Both Varchar2: ‘’’ 51: || var_length_20 || ‘’’ NOT = ‘’’ 52: || var_length_10 || ‘’’’); 53: END IF; 54: END; 55: / Constant: ‘Jonathan’ = ‘Jonathan ‘ OUTPUT Char: ‘Donna ‘ = ‘Donna Char and constant: ‘Donna ‘ = ‘Donna’ ‘ 3 Char and Varchar2: ‘Donna ‘ NOT = ‘Donna’ Both Varchar2: ‘Donna’ = ‘Donna’ PL/SQL procedure successfully completed. You can see from the output that the first three comparisons in Listing 3.7 use ANALYSIS blank-padded comparison semantics. The strings being compared are considered to be equal even though the number of trailing spaces differs in each case. The fourth comparison, however, compares a VARCHAR2 variable against a CHAR variable. Because one of the strings in question is variable-length, the trailing spaces count, and the two strings are not considered to be equal. The Effect of Case on String Comparisons PL/SQL string comparisons are always case-sensitive. The obvious ramification of this is that a lowercase string such as ‘aaa’ is not considered equal to its uppercase equivalent of ‘AAA’. But case also makes a difference when comparing two strings to see which is greater. In an ASCII environment, the letter ‘A’ will be less than the letter ‘B’. However, the letter ‘a’ will not only be greater than ‘B’; it will be greater than ‘Z’. If you need to perform case-insensitive string comparisons, use PL/SQL’s Tip built-in UPPER() function. For example: IF UPPER(‘a’) < UPPER(‘B’) THEN... You can use the LOWER() function in the same manner.
82 Day 3 Use of Comparison Operators with Dates Date comparison works pretty much as you might expect and has fewer complexities than string comparisons do. Earlier dates are considered to be less than later dates, and it follows that more recent dates are greater than earlier dates. The only complication arises from the fact that PL/SQL date variables also contain a time component. Listing 3.8 illustrates this and another potential problem to be aware of when comparing date values against each other. INPUT LISTING 3.8 Date Comparison Example 1: --Demonstrate date comparisions 2: DECLARE 3: payment_due_date DATE; 4: BEGIN 5: --In real life the payment_due date might be read from 6: --a database or calculated based on information from a database. 7: payment_due_date := TRUNC(SYSDATE); 8: --Display the current date and the payment date. 9: DBMS_OUTPUT.PUT_LINE(‘Today is ‘ || TO_CHAR(SYSDATE,’dd-Mon-yyyy’)); 10: DBMS_OUTPUT.PUT_LINE(‘Payment is due on ‘ 11: || TO_CHAR(payment_due_date,’dd-Mon-yyyy’)); 12: IF payment_due_date = SYSDATE THEN 13: DBMS_OUTPUT.PUT_LINE(‘Payment is due today.’); 14: ELSE 15: DBMS_OUTPUT.PUT_LINE(‘Payment can wait a while.’); 16: END IF; 17: --In reality, the time does not matter when speaking of a due date. 18: IF TRUNC(payment_due_date) = TRUNC(SYSDATE) THEN 19: DBMS_OUTPUT.PUT_LINE(‘Wrong! Payment is due today!’); 20: ELSE 21: DBMS_OUTPUT.PUT_LINE(‘Wrong! Payment can wait a while.’); 22: END IF; 23: END; 24: / Today is 01-Jun-1997 OUTPUT Payment is due on 01-Jun-1997 Payment can wait a while. Wrong! Payment is due today! PL/SQL procedure successfully completed. Today’s date and the payment due date both match, yet the IF statement in line ANALYSIS 12 failed to detect this. Why? Because SYSDATE is a function that returns the cur- rent date and time, with the time resolved down to the second. The payment_due_date variable will contain a time of midnight because the TRUNC function was used when the
Writing PL/SQL Expressions 83 due date was set. See line 7. The only time that line 12 would function correctly would be for one second at midnight each day. In line 18, the TRUNC function is used to truncate the time values from the two dates, resulting in a comparison that works as desired in this case. Admittedly this is a contrived example, but it does illustrate the problem. Be especially careful comparing dates read from the database. Sometimes Tip they have time components that you don’t expect. Even if programmers aren’t supposed to store a time, it’s a pretty easy mistake to make. Having the time as part of a date variable is not necessarily a bad thing. It’s just some- thing you need to be aware of, especially when comparing dates with each other. 3 Exploring Expressions When you combine values and operators to produce a result, you have an expression. You have already learned about the various datatypes available in PL/SQL and PL/SQL’s extensive collection of operators. In addition, in order to use expressions effectively in your code, you also need to understand • Operator precedence • Use of parentheses • Types of expressions • The effects of null values in an expression • Conversion between datatypes Understanding the effects of a null value on an expression is particularly important, especially when you move into retrieving data from a database. The remainder of this chapter discusses each of these items in detail. Expressions Defined Simply put, an expression is some combination of variables, operators, literals, and func- tions that returns a single value. Operators are the glue that hold an expression together and are almost always present. The other elements might not all be present in every expression. In its very simplest form, an expression might simply consist of a literal value, a variable name, or a function call. The first few entries in Table 3.9 are examples of this type of expression. More typical expressions involve two values and an operator, with the opera- tor defining the action to be taken and the result to be returned. Complex expressions
84 Day 3 can be built up by stringing several simple expressions together with various operators and function calls. Finally, the unary operators can be applied to any expression or value. TABLE 3.9 Sample Expressions Expression Comments 1000 Evaluates to one thousand some_variable_name Evaluates to the contents of the variable SYSDATE An Oracle function that returns the current date 1000 + 2000 A typical expression using a binary operator -1000 An expression using a unary operator 10 * 20 + 30 / 2 Two expressions joined together LENGTH(‘Lansing ‘ || ‘MI’) A function call evaluating a sub-expression is itself an expression 1-5**2
Writing PL/SQL Expressions 85 TABLE 3.10 Operator Precedence Precedence Operators Operation First **, NOT Exponentiation and logical negation Second +, - Arithmetic identity and negation (+ and - used as unary operators) Third *, / Multiplication and division Fourth +, -, || Addition, subtraction, and string concate- nation Fifth =, , !=, ~=, , =, LIKE, BETWEEN, IN, IS NULL 3 Sixth AND Logical conjunction Seventh OR Logical inclusion Take another look at the expression referred to in the previous section. The following list shows the steps Oracle would take to evaluate it: 1. 1-5**2
86 Day 3 Use parentheses in complex expressions, even when they are not strictly nec- Tip essary, in order to make the intended order of evaluation clear to other pro- grammers. Types of Expressions One way of classifying expressions is by the datatype of the resulting value. Using this scheme, expressions can be classified as one of these types: • Arithmetic or numeric • Boolean • String • Date Any expression returning a numeric value is referred to as an arithmetic expression, or sometimes as a numeric expression. A Boolean expression is any expression that returns a true or false value. Comparison expressions are really special cases of this type, but they are not the only way to get a true/false value. A Boolean variable—or several Boolean variables linked together with the logical operators AND, OR, and NOT—will also return a Boolean result. String expressions are those that return character strings as results, and date expressions are those that result in a date-time value. Generally speaking, you can use an expression of the appropriate datatype anywhere in your PL/SQL code where a value is required. The exception to this would be in function and procedure calls that modify their arguments. Null Values in Expressions Until now, the discussion has ignored the effect of nulls in expressions. This was done in order to concentrate on the normal function of each of the operators and also because nulls pretty much have the same effect regardless of the operation being done. What is a null? The term is best understood as referring to an unknown value. Any vari- able or expression is considered null when the value of that variable or expression is unknown. This situation can occur if you declare a variable and use it in an expression without first assigning a value. Because the variable has no assigned value, the result of the expression can’t be known. More commonly, nulls are encountered when reading data from a database. Oracle, like any other relational database, does not force you to
Writing PL/SQL Expressions 87 store a value for each column in a table. When no specific value is stored, the contents of that column are considered unknown, and the column is referred to as being null. NEW TERM The effects of nulls are particularly insidious when writing Boolean expressions, such as the WHERE clause in a SQL SELECT statement. SQL uses what is called three-valued logic. Three-valued logic says that the result of a Boolean expression can be either true, false, or NULL. Many a programmer has felt the sting of an IF statement gone awry because of an unexpected null value, and some consider three-valued logic to be more of a three-pronged pitchfork prodding them in the behind. The code in Listing 3.9 shows why nulls can cause so much grief. INPUT LISTING 3.9 Effects of Nulls on Boolean Expressions 1: --Demonstrate the effects of null values on boolean expressions. 2: DECLARE 3 3: a INTEGER; 4: b BOOLEAN; 5: n INTEGER; --this will be our null value. 6: BEGIN 7: --Assign a value to the variable A, but leave N null. 8: a := 2; 9: --Note that the test for A=N fails. 10: IF a = n THEN 11: DBMS_OUTPUT.PUT_LINE(‘a = n is true’); 12: ELSE 13: DBMS_OUTPUT.PUT_LINE(‘a = n is not true’); 14: END IF; 15: --But also note that the test for a n fails. 16: IF a n THEN 17: DBMS_OUTPUT.PUT_LINE(‘a n is true’); 18: ELSE 19: DBMS_OUTPUT.PUT_LINE(‘a n is not true’); 20: END IF; 21: --Here is an expression that many people first 22: --expect to always be true. 23: IF (a = n) OR (a n) THEN 24: DBMS_OUTPUT.PUT_LINE(‘(a = n) or (a n) is true’); 25: ELSE 26: DBMS_OUTPUT.PUT_LINE(‘(a = n) or (a n) is not true’); 27: END IF; 28: --TRUE and NULL = NULL 29: IF (a = 2) AND (a n) THEN 30: DBMS_OUTPUT.PUT_LINE(‘TRUE and NULL = TRUE’); 31: ELSE 32: DBMS_OUTPUT.PUT_LINE(‘TRUE and NULL = NULL’); 33: END IF; continues
88 Day 3 LISTING 3.9 continued 34: --TRUE or NULL = TRUE 35: IF (a = 2) OR (a n) THEN 36: DBMS_OUTPUT.PUT_LINE(‘TRUE or NULL = TRUE’); 37: ELSE 38: DBMS_OUTPUT.PUT_LINE(‘TRUE or NULL = NULL’); 39: END IF; 40: --NOT NULL = NULL 41: IF (NOT (a = n)) IS NULL THEN 42: DBMS_OUTPUT.PUT_LINE(‘NOT NULL = NULL’); 43: END IF; 44: --TIP: try this if you want a null value to be 45: --considered “not equal”. 46: --Be careful though, if BOTH A and N are NULL 47: --NVL will still return TRUE. 48: IF NVL((a n),true) THEN 49: DBMS_OUTPUT.PUT_LINE(‘The values are not equal.’); 50: ELSE 51: DBMS_OUTPUT.PUT_LINE(‘The values are equal.’); 52: END IF; 53: --TIP: a three-valued if construct. 54: b := (a n); 55: IF b THEN 56: DBMS_OUTPUT.PUT_LINE(‘a n is TRUE’); 57: ELSIF NOT b THEN 58: DBMS_OUTPUT.PUT_LINE(‘a n is FALSE’); 59: ELSE 60: DBMS_OUTPUT.PUT_LINE(‘a n is NULL’); 61: END IF; 62: END; 63: / a = n is not true OUTPUT a n is not true (a = n) or (a n) is not true TRUE and NULL = NULL TRUE or NULL = TRUE NOT NULL = NULL The values are not equal. a n is NULL PL/SQL procedure successfully completed. Listing 3.9 is a somewhat contrived example, but it illustrates very well the ANALYSIS effects of nulls on comparison expressions. Take a close look at what is going on here. The first IF statement in line 10 tests for a = n. As you might expect, this is not true, but it is important to understand that it is not false either. The second IF statement in line 16 proves this. The test there is for a n, the exact opposite of the previous
Writing PL/SQL Expressions 89 comparison, and it also is not true. Line 23 shows an extreme case, an expression which many people at first glance would expect to always be true. However, because the value of n is unknown, the truth of this expression is also unknown and it evaluates to null. There are three basic things to understand when dealing with nulls: • How nulls propagate in expressions • How the logical operators AND, OR, and NOT handle nulls • How the IF statement deals with nulls In an expression, null values propagate. For the most part, any arithmetic, date, string, or Boolean expression containing even one null value will also evaluate to null. There are some exceptions to this rule, which are described shortly. 3 Where strings are concerned, Oracle considers an empty string (‘’ for exam- Note ple) to be a null. A string of spaces, however, is not the same as an empty string, and is not null. The logical operators AND, OR, and NOT are often used to link together comparison expres- sions. Table 3.11 shows how these operators function in expressions with null values. TABLE 3.11 Three-Valued Logic Truth Table Operator Expression Result AND TRUE AND TRUE TRUE TRUE AND FALSE FALSE TRUE AND NULL NULL FALSE AND NULL FALSE NULL AND NULL NULL OR TRUE OR TRUE TRUE TRUE OR FALSE TRUE TRUE OR NULL TRUE FALSE OR NULL FALSE NULL OR NULL NULL NOT NOT TRUE FALSE NOT FALSE TRUE NOT NULL NULL
90 Day 3 Lines 28 through 43 in Listing 3.9 contain some IF statements that demonstrate how each of the logical operators operate on null values. The IF statement is the fundamental decision-making structure of PL/SQL. Give it a Boolean expression, and it evaluates that expression and makes a decision as to which piece of code to execute. However, Boolean expressions can have three values: TRUE, FALSE, and NULL. An IF statement has only two parts: the code to be executed when an expression is TRUE, and the code to be executed when it isn’t. There is a mismatch here, and it’s very important to keep in mind that the ELSE portion will be executed when the result of an expression is unknown, or in other words, when the expression is null. Lines 53 through 61 of Listing 3.9 show a way to construct an IF statement Tip that has separate execution paths for TRUE, FALSE, and NULL. There are some exceptions to the general rule that nulls propagate in expressions. Null character strings are sometimes handled as if they were zero-length strings, and PL/SQL does have some functions and operators that have been specifically designed to help you work with nulls. You can concatenate strings, even if one is null, and get the results you would expect. This is because the concatenation operator simply ignores any null strings. However, if all the strings are null, the result will be null. Also bear in mind that PL/SQL treats a zero-length VARCHAR2 string as a null value. Treating a zero-length string as a null value is an Oracle-specific behavior Note that is not specified in the ANSI standard for SQL. You can use the IS NULL operator to see whether or not a particular variable or expres- sion is null. It allows your code to detect and act on null values. You saw an example of this earlier in Listing 3.5. The IS NULL operator returns only a true or false value, never a NULL.
Writing PL/SQL Expressions 91 Always use the IS NULL operator when checking for null values. Do not use Caution the equality or inequality operators to compare a variable to null. You can code a statement such as IF some_var = NULL, but you won’t get the results you might expect. Use IF some_var IS NULL instead. The built-in NVL function allows you to specify an alternate value to be used when its argument is null. Briefly, the syntax for NVL looks like this: The Syntax for the NVL Function , SYNTAX NVL(expression, value_if_expression_is_null) Usually, when people use NVL, they simply supply a variable for the expression. However, that doesn’t have to be the case. When you invoke NVL, the result of the first 3 expression is computed. If that result is not null, then it will be returned as the result of NVL. If the result of the expression does happen to be null, then NVL will return the value of the second argument as its result. The end result is that the value_if_expression_is_null argument becomes an alternate value for expression, to , be used if expression is null. Lines 44 through 52 of Listing 3.9 show an interesting use of the NVL function to account for the possibility of the variable n being null. You can read more about NVL in Appendix B. The built-in SQL DECODE function actually treats null as a specific value instead of an unknown value. It might seem contradictory, but it’s useful. DECODE is also described in Appendix B. DO DON’T Do initialize all your variables in order to Don’t forget to think through the possi- eliminate the possibility of null values. ble implications of null values in every Do use NVL where feasible when retriev- expression you write, especially the ing values from that database in order to Boolean, including comparison and replace null values with an acceptable expressions. alternative.
92 Day 3 Converting Datatypes Sometimes you need to convert a value of one datatype to another. This is frequently true with dates and numbers, which are often converted to and from character strings. You might want to display a date, for example, so you must first convert it to a character string in the desired format. There are two ways of approaching the issue of conversion. One is to rely on Oracle to implicitly convert datatypes, which it will do automatically when it makes sense. The second and more preferred method is to code your conversions explicitly. Implicit Conversion When you mix different datatypes in an expression, Oracle will convert them for NEW TERM you when it makes sense to do so. This is referred to as implicit conversion. Listing 3.10 shows several examples of implicit conversion. INPUT LISTING 3.10 Implicit Conversion Examples 1: --Demonstrate implicit conversion 2: DECLARE 3: d1 DATE; 4: cd1 VARCHAR2(10); 5: cd2 VARCHAR2(10); 6: n1 NUMBER; 7: cn1 VARCHAR2(10); 8: cn2 VARCHAR2(10); 9: BEGIN 10: --Assign a value to this string which represents a date. 11: cd1 := ‘15-Nov-61’; 12: --Now assign the string to a date variable. 13: --The conversion is implicit. 14: d1 := cd1; 15: --Now assign that date variable to another string. 16: --Again the conversion 17: --is implicit, but this time the conversion is 18: --from a date to a string. 19: cd2 := d1; 20: --Display the two character strings to show that they are the same. 21: DBMS_OUTPUT.PUT_LINE(‘CD1 = ‘ || cd1); 22: DBMS_OUTPUT.PUT_LINE(‘CD2 = ‘ || cd2); 23: --Repeat the same example as above, but with numbers. 24: cn1 := ‘995’; 25: n1 := cn1 + .99 ; 26: cn2 := n1; 27: DBMS_OUTPUT.PUT_LINE(‘CN1 = ‘ || cn1); 28: DBMS_OUTPUT.PUT_LINE(‘CN2 = ‘ || cn2); 29: END; 30: /
Writing PL/SQL Expressions 93 CD1 = 15-Nov-61 OUTPUT CD2 = 15-NOV-61 CN1 = 995 CN2 = 995.99 PL/SQL procedure successfully completed. The code in Listing 3.10 illustrates some common implicit conversions. The first ANALYSIS assignment, in line 11, causes no conversion at all because a string is assigned to a string variable. The assignment statement in line 14, however, does represent an implic- it conversion because it must convert the string representation of the date to Oracle’s internal format before it can assign the value to d1. In line 19, that date is again convert- ed back to a string format. Lines 23 through 28 repeat the same process, but this time with a number. Implicit conversions are convenient, but can sometimes cause problems. In relying on them, you are relying on Oracle’s built-in assumptions and on default settings you might 3 not even be aware of, and which might change from one release to another. The format of a date leads to some good examples. Did you know that Oracle’s default date format varies depending on the language setting? That it can also be installation-dependent? And that it can vary between a client PC executing a Developer/2000 script and a database server? In fact, the date format can even be changed for the duration of a particular ses- sion. Figure 3.2 illustrates this by showing the same PL/SQL code succeeding once and then failing after the date format has been changed. FIGURE 3.2 The default date for- mat is changed.
94 Day 3 For the reasons just listed, it is often safer to code conversions explicitly. Explicit conver- sions also better document your code by making it clear to other programmers exactly what is happening. Explicit Conversion Oracle has several built-in functions that are designed to convert information from one datatype to another. These are shown in Table 3.12. TABLE 3.12 Conversion Functions Function Purpose TO_DATE Converts a character string to a date TO_NUMBER Converts a character string to a number TO_CHAR Converts either a number or a date to a character string Each of these functions takes three arguments: the value to be converted, a format string specifying how that conversion is to take place, and optionally a string containing language-specific parameters. These functions are described in detail on Day 6, but Listing 3.11 gives some common examples of how you can use them. INPUT LISTING 3.11 Examples of the Conversion Functions 1: --Demonstrate conversion functions 2: DECLARE 3: d1 DATE; 4: d2 DATE; 5: d3 DATE; 6: d4 DATE; 7: n1 NUMBER; 8: n2 NUMBER; 9: n3 NUMBER; 10: BEGIN 11: --Here are some common date formats which you might encounter. 12: d1 := TO_DATE(‘1/1/02’,’mm/dd/yy’); 13: d2 := TO_DATE(‘1-1-1998’,’mm-dd-yyyy’); 14: d3 := TO_DATE(‘Jan 1, 2000’,’mon dd, yyyy’); 15: --Year 2000 problems? Note the effect of using rr instead of yy. 16: d4 := TO_DATE(‘1/1/02’,’mm/dd/rr’); 17: DBMS_OUTPUT.PUT_LINE(‘d1 = ‘ || TO_CHAR(d1,’dd-Mon-yyyy’)); 18: DBMS_OUTPUT.PUT_LINE(‘d2 = ‘ || TO_CHAR(d2,’mm/dd/yyyy’)); 19: DBMS_OUTPUT.PUT_LINE(‘d3 = ‘ || TO_CHAR(d3,’Day, Month dd, yyyy’)); 20: DBMS_OUTPUT.PUT_LINE(‘d4 = ‘ || TO_CHAR(d4,’Dy, Mon dd, yyyy’)); 21: --Here are some examples of numeric conversions. 22: n1 := TO_NUMBER (‘123.99’,’999D99’);
Writing PL/SQL Expressions 95 23: n2 := TO_NUMBER (‘$1,235.95’,’$9G999D99’); 24: DBMS_OUTPUT.PUT_LINE(‘n1 = ‘ || TO_CHAR(n1,’999D99’)); 25: DBMS_OUTPUT.PUT_LINE(‘n2 = ‘ || TO_CHAR(n2,’$9G999D99’)); 26: END; 27: / d1 = 01-Jan-1902 OUTPUT d2 = 01/01/1998 d3 = Saturday , January 01, 2000 d4 = Tue, Jan 01, 2002 n1 = 123.99 n2 = $1,235.95 PL/SQL procedure successfully completed. Lines 12 through 16 show the TO_DATE function being used to convert some ANALYSIS common date formats to a date variable. Lines 17 through 20 display these dates 3 and show some more formatting possibilities. Lines 22 through 25 show some examples of conversions between numeric and character datatypes. Summary Today you have learned about writing PL/SQL expressions. You have read descriptions of each of the PL/SQL operators and seen examples of these operators in action. You have also seen how to write complex expressions and how the rules of operator prece- dence govern Oracle’s evaluation of these expressions. Remember that you can use parentheses when you need to exercise control over a calculation. Most important to remember are the effects of null, or unknown, values on expressions. This is a particular- ly important subject to keep in mind when writing comparisons for use with if state- ments. Mastering this one area will save you untold grief as you write code in the future. Q&A Q Why does the expression TRUE AND NULL evaluate to NULL, but the expression TRUE OR NULL evaluates to true? A This is a good question. To understand the answer, it might help to think in terms of null being an unknown value. The AND operator requires that both its operands be true in order to return a true result. If one of the operands is unknown, you can’t be sure that if it were known it would be true, so AND must evaluate to false in this case. Things are different, however, for the expression TRUE OR NULL. The OR operator only requires one of its operands to be true in order to return a true result. In the case of TRUE OR NULL, you do know that one operand is true.
96 Day 3 Whether the other operand is true, false, or unknown doesn’t matter at this point because you have one you know is true, and one is all you need. Q Does the IN operator let me do anything that I couldn’t do otherwise? A No, not really, but it does make your code more readable and easier to maintain. The expression x IN (1,3,4,10,30,30,40,100) is equivalent to x=1 OR x=3 OR x=4 OR x=10 OR x=30 OR x=40 OR x=100, but you will probably find the first version a bit easier to read and understand. Q You said that a statement like IF X = NULL THEN... would not work as expected, and that IF X IS NULL THEN ... should be used instead. Why? A The first expression will never be true. It will always evaluate to null because one of the operands is null, and it can never be known if two values are equal when one of the values is unknown. The second expression uses the IS NULL operator, which is designed to check for nulls. It specifically checks to see if the value of X is unknown and evaluates to true if that is the case. Q When I am comparing strings, especially when comparing a CHAR string to a VARCHAR2 string, is there a convenient way to tell PL/SQL to ignore any trail- ing spaces in the CHAR string? A Yes. Use the built-in RTRIM function. For example: IF RTRIM(char_string) = varchar2_string then... Q I’m comparing two dates and only want to know if they are in the same year. Can I use the TRUNC function to accomplish this? A Yes. By default, the TRUNC function truncates the time portion of a date, but the optional second argument enables you to specify a different point of truncation. To compare only the years, you can write: IF TRUNC(date_1,’yyyy’) = TRUNC(date_2,’yyyy’) THEN... Q Sometimes you capitalize your date format strings. Why? A When converting a date to a character string for display purposes, capitalizing parts of the format string controls whether that part of the date is capitalized. Suppose the current month is January. The expression TO_CHAR(SYSDATE,’mon’) would result in a value of ‘jan’, the expression TO_CHAR(SYSDATE,’Mon’) would result in a value of ‘Jan’, and the expression TO_CHAR(SYSDATE,’MON’) would result in a value of ‘JAN’. Q Is there any way to prevent Oracle from implicitly converting data from one type to another? A No, there is not. The only way that you can prevent implicit conversions from hap- pening is to watch what you’re doing and code all conversions explicitly.