Working with Strings（使用Oracle字符串）(二)

q'[This isn't a date]'

A string variable is an identifier declared with a string datatype and then assigned a value (which could be a literal or an expression).

Declaring String Variables

To work with strings in your PL/SQL programs, you declare variables to hold the string values. To declare a string variable, you must select from one of the many string datatypes Oracle Database offers, including CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes that are prefixed with an ”N” are “national character set” datatypes, which means they are used to store Unicode character data. (Unicode is a universal encoded character set that can store information in any language using a single character set.)

To declare a variable-length string, you must provide the maximum length of that string. The following code declares a variable, using the VARCHAR2 datatype, that will hold a company name, which cannot (in this declaration) have more than 100 characters:

DECLARE

l_company_name VARCHAR2(100);

You must provide the maximum length; if you leave it out, Oracle Database raises a compile error, as shown below:

SQL> DECLARE

2 l_company_name VARCHAR2;

3 BEGIN

4 l_company_name :=

'Oracle Corporation';

5 END;

6 /

l_company_name VARCHAR2;

*

ERROR at line 2:

ORA-06550: line 2, column 21:

PLS-00215: String length constraints

must be in range (1 .. 32767)

To declare a fixed-length string, use the CHAR datatype:

DECLARE

l_yes_or_no CHAR(1) := 'Y';

With CHAR (unlike with VARCHAR2) you do not have to specify a maximum length for a fixed-length variable. If you leave off the length constraint, Oracle Database automatically uses a maximum length of 1. In other words, the two declarations below are identical:

DECLARE

l_yes_or_no1 CHAR(1) := 'Y';

l_yes_or_no2 CHAR := 'Y';

If you declare a CHAR variable with a length greater than 1, Oracle Database automatically pads whatever value you assign to that variable with spaces to the maximum length specified.

Finally, to declare a character large object, use the CLOB datatype. You do not specify a maximum length; the length is determined automatically by Oracle Database and is based on the database block size. Here is an example:

DECLARE

l_lots_of_text CLOB;

So, how do you determine which datatype to use in your programs Here are some guidelines:

If your string might contain more than 32,767 characters, use the CLOB (or NCLOB) datatype.

If the value assigned to a string always has a fixed length (such as a U.S. Social Security number, which always has the same format and length, NNN-NN-NNNN), use CHAR (or NCHAR).

Otherwise (and, therefore, most of the time), use the VARCHAR2 datatype (or NVACHAR2, when working with Unicode data).

Using the CHAR datatype for anything but strings that always have a fixed number of characters can lead to unexpected and undesirable results. Consider the following block, which mixes variable and fixed-length strings:

DECLARE

l_variable VARCHAR2 (10) := 'Logic';

l_fixed CHAR (10) := 'Logic';

BEGIN

IF l_variable = l_fixed

THEN

DBMS_OUTPUT.put_line ('Equal');

ELSE

DBMS_OUTPUT.put_line ('Not Equal');

END IF;

END;

At first glance, you would expect that the word “Equal” would be displayed after execu