[ Pobierz całość w formacie PDF ]
.It performs the comparison in oneof two ways, depending on the types of the two variables:f&If a comparison is made between two CHAR variables, then PL/SQL uses a blank-paddingcomparison.With this approach, PL/SQL blank-pads the shorter of the two values out to thelength of the longer value.It then performs the comparison.So with the above example, ifcompany_name is declared CHAR(30) and parent_company_name is declared CHAR(35),4.2.3 Character Datatypes 155 [Appendix A] What's on the Companion Disk?then PL/SQL adds five spaces to the end of the value in company_name and then performsthe comparison.Note that PL/SQL does not actually change the variable's value.It copies thevalue to another memory structure and then modifies this temporary data for the comparison.f&If at least one of the strings involved in the comparison is variable-length, then PL/SQLperforms a nonblank-padding comparison.It makes no changes to any of the values, uses theexisting lengths, and performs the comparison.This comparison analysis is true ofevaluations which involve more than two variables as well, as may occur with the INoperator:IF menu_selection NOT IN(save_and_close, cancel_and_exit, 'OPEN_SCREEN')THEN.If any of the four variables (menu_selection, the two named constants, and the single literal) isdeclared VARCHAR2, then exact comparisons without modification are performed to determine ifthe user has made a valid selection.Note that a literal like OPEN_SCREEN is always considered afixed-length CHAR datatype.These rules can make your life very complicated.Logic which looks perfectly correct may not operate asexpected if you have a blend of fixed-length and variable-length data.Consider the following fragment:DECLAREcompany_name CHAR (30) DEFAULT 'PC HEAVEN';parent_company_name VARCHAR2 (25) DEFAULT 'PC HEAVEN';BEGINIF company_name = parent_company_nameTHEN-- This code will never be executed.END IF;END;The conditional test will never return TRUE because the value company_name has been padded to the lengthof 30 with 21 spaces.To get around problems like this, you should always RTRIM your CHAR values whenthey are involved in any kind of comparison or database modification.It makes more sense to use RTRIM (to remove trailing spaces) than it does to use RPAD (to padvariable-length strings with spaces).With RPAD you have to know what length you wish to pad thevariable-length string to get it in order to match the fixed-length string.With RTRIM you just get rid of allthe blanks and let PL/SQL perform its nonblank-padding comparison.It was easy to spot the problem in this anonymous PL/SQL block because all the related statements are closetogether.In the real world, unfortunately, the variables' values are usually set in a much less obvious mannerand are usually in a different part of the code from the conditional statement which fails.So if you have to usefixed-length variables, be on the lookout for logic which naively believes that trailing spaces are not an issue.4.2.3.3 The LONG datatypeA variable declared LONG can store variable-length strings of up to 32760 bytes -- this is actually sevenfewer bytes than allowed in VARCHAR2 type variables! The LONG datatype for PL/SQL variables is quitedifferent from the LONG datatype for columns in the Oracle Server.The LONG datatype in Oracle7 can storecharacter strings of up to two gigabytes or 231-1 bytes; this large size makes the LONG column a possiblerepository of multimedia information, such as graphics images.As a result of these maximum length differences, you can always insert a PL/SQL LONG variable value into aLONG database column, but you cannot select a LONG database value larger than 32760 bytes into a PL/SQL4.2.3 Character Datatypes 156 [Appendix A] What's on the Companion Disk?LONG variable.In the Oracle database, there are many restrictions on how the LONG column can be used in a SQL statement;for example:"A table may not contain more than one single LONG column."You may not use the LONG column in a GROUP BY, ORDER BY, WHERE, or CONNECT BYclause."You may not apply character functions (such as SUBSTR, INSTR, or LENGTH), to the LONGcolumn.PL/SQL LONG variables are free of these restrictions.In your PL/SQL code you can use a variable declaredLONG just as you would a variable declared VARCHAR2.You can apply character functions to the variable.You can use it in the WHERE clause of a SELECT or UPDATE statement.This all makes sense given that, atleast from the standpoint of the maximum size of the variables, there is really little difference betweenVARCHAR2 and LONG in PL/SQL.Given the fact that a VARCHAR2 variable actually has a higher maximum length than the LONG and has norestrictions attached to it, I recommend that you always use the VARCHAR2 datatype in PL/SQL programs.LONGs have a place in the RDBMS, but that role is not duplicated in PL/SQL.This makes some sense sinceyou will very rarely want to manipulate truly enormous strings within your program using such functions asSUBSTR or LENGTH or INSTR.4.2.3.4 The RAW datatypeThe RAW datatype is used to store binary data or other kinds of raw data, such as a digitized picture orimage.A RAW variable has the same maximum length as VARCHAR2 (32767 bytes), which must also bespecified when the variable is declared.The difference between RAW and VARCHAR2 is that PL/SQL willnot try to interpret raw data.Within the Oracle RDBMS this means that Oracle will not perform character setconversions on RAW data when it is moved from one system (based, for example, on 7-bit ASCII) to anothersystem.Once again, there is an inconsistency between the PL/SQL maximum length for a RAW variable (32767) andthe RDBMS maximum length (255).As a result, you cannot insert more than 255 bytes of your PL/SQLRAW variable's value into a database column.You can, on the other hand, insert the full value of a PL/SQLRAW variable into a column with type LONG RAW, which is a two-gigabyte container for raw data in thedatabase.4.2.3.5 The LONG RAW datatypeThe LONG RAW datatype stores raw data of up to 32760 bytes and is just like the LONG datatype exceptthat the data in a LONG RAW variable is not interpreted by PL/SQL
[ Pobierz całość w formacie PDF ]