Embedding Single Quotes Inside a String

The trickiest part of working with string literals comes when you need to include a single quote inside a string literal (as part of the literal itself). Generally, the rule is that you write two single quotes next to each other inside a string if you want the literal to contain a single quote in that position. The following table shows the literal in one column and the resulting "internal" string in the second column:

Literal Actual value
'There''s no business like show business.' There's no business like show business.
'"Hound of the Baskervilles"' "Hound of the Baskervilles"
'NLS_LANGUAGE=''ENGLISH''' NLS_LANGUAGE='ENGLISH'
'''' '
'''hello''' 'hello'
'''''' ''

Here's a summary of how to embed single quotes in a literal:

· To place a single quote inside the literal, put two single quotes together.

· To place a single quote at the beginning or end of a literal, put three single quotes together.

· To create a string literal consisting of one single quote, put four single quotes together.

· To create a string literal consisting of two single quotes together, put six single quotes together.

Two single quotes together is not the same as a double quote character. A double quote character does not have any special significance inside a string literal. It is treated the same as a letter or number.

Numeric Literals

Numeric literals can be integers or real numbers (a number that contains a fractional component). Note that PL/SQL considers the number 154.00 to be a real number, even though the fractional component is zero and the number is actually an integer. Internally, integers and reals have a different representation, and there is some small overhead involved in converting between the two.

You can also use scientific notation to specify a numeric literal. Use the letter "E" (upper- or lowercase) to multiply a number by 10 to the nth power—for example, 3.05E19, 12e-5.

Boolean Literals

Oracle provides two literals to represent Boolean values: TRUE and FALSE. These values are not strings; you should not put quotes around them. Use Boolean literals to assign values to Boolean variables, as in:

DECLARE enough_money BOOLEAN; -- Declare a Boolean variableBEGIN enough_money := FALSE; -- Assign it a valueEND;

You do not, on the other hand, need to refer to the literal value when checking the value of a Boolean expression. Instead, just let that expression speak for itself, as shown in the conditional clause of the following IF statement:

DECLARE enough_money BOOLEAN;BEGIN IF enough_money THEN ...

Наши рекомендации