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 t