Working with Strings（使用Oracle字符串）

Working with Strings（使用Oracle字符串）
Part 3 in a series of articles on understanding and using PL/SQL
Every application needs data. That seems rather obvious, doesn’t it? An application is almost always built on top of database tables. Those tables are full of different kinds of data. And the programs you write—whether they are in PL/SQL or another language—manipulate that data. It is, therefore, extremely important for you to be aware of the different datatypes supported by PL/SQL and how you can work with those datatypes.
Take the Challenge!
Each of my PL/SQL 101 articles offers a quiz to test your knowledge of the information provided in the article. The quiz questions are shown below and also at PL/SQL Challenge (plsqlchallenge.com), a Website that offers online quizzes for the PL/SQL language. You can read and answer the quiz here, and then check your answers in the next issue. If, however, you take the quiz at PL/SQL Challenge, you will be entered into a raffle to win your choice of an e-book from O’Reilly Media (oreilly.com).
Question 1
What will be displayed after executing this block?
BEGIN
sys.DBMS_OUTPUT.put_line (
INSTR ('steven feuerstein'
, 'e'
, -1
, 2));
END;
Question 2
True or false: When assigning a literal value to a string, that value may not contain within it any single quotes.
Question 3
What will be displayed after executing this block?
BEGIN
DBMS_OUTPUT.put_line (
'REPLACE='
|| REPLACE ('steven feuerstein'
, 'e'
, NULL));
DBMS_OUTPUT.put_line (
'TRANSLATE='
|| TRANSLATE ('steven feuerstein'
, 'e'
, NULL));
END;
As you might expect, there is an awful lot to learn about datatypes, and not all of that knowledge can fit into a single article. So I will start with one of the most common types of data: strings. Very few database tables and programs do not contain strings—strings such as a company name, address information, descriptive text, and so on. As a result, you quite often need to do the following:
Declare string variables and constants
Manipulate the contents of a string (remove characters, join together multiple strings, and so on)
Move string data between PL/SQL programs and database tables
This article gives you the information you need to begin working with strings in your PL/SQL programs.
What Is a String?
A string, also referred to as character data, is a sequence of selected symbols from a particular set of characters. In other words, the symbols in a string might consist of English letters, such as ”A” or ”B.” They might also consist of Chinese characters, such as 字串.
There are three kinds of strings in PL/SQL:
Fixed-length strings. The string is right-padded with spaces to the length specified in the declaration. (See ”Declaring String Variables,” to see padding in action.)
Variable-length strings. A maximum length for the string is specified (and it must be no greater than 32,767), but no padding takes place.
Character large objects (CLOBs).CLOBs are variable-length strings that can be up to 128 terabytes.
Strings can be literals or variables. A string literal begins and ends with a single quotation mark:
'This is a string literal'
If you need to embed a single quote inside a string literal, you can type in two single quotes right next to one another, as in:
'This isn''t a date'
You can also use the “q” character to indicate an alternative terminating character for the literal:
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)