The PL/SQL string is actually an optional sequence of dimension-specification characters. A character can be a combination of numbers, letters, blanks, special characters, or all. PL/SQL provides three types of strings:
fixed-length string : In such a string, the programmer specifies the length while declaring the string. The string is the right padding space to reach the specified length.
variable length string : In such a string, the maximum length can be up to 32,767, specified for the string, and does not need to be populated.
character Large object (CLOB) : This is a variable-length string that can reach 128 megabytes.
The PL/SQL string can be a variable or literal value. String literals are enclosed inside the quotation marks. For example: ' This is a string literal. yiibai.com ' Or ' Hello World '
Text that includes single quotes in the string, requires two single quotes to be entered adjacent to each other, such as: ' This isn ' t what it looks like '
Declaration of a String variable
The Oracle database provides a number of string data types, such as: Char,nchar,varchar2,nvarchar2,clob and NCLOB. The data type preceded by an ' N ' is the "national character set" data type, which stores Unicode character data.
If you need to declare a variable-length string, you must provide the maximum length of the string. For example, the VARCHAR2 data type. The following example illustrates the declaration and use of some string variables:
DECLARE name VARCHAR2 ( -); Company VARCHAR2 ( -); Introduction Clob; ChoiceChar(1); BEGIN Name:='John Smith'; Company:='Infotech'; Introduction:='hello! I"'m John Smith from Infotech.'; Choice:='y'; IF Choice='y'Then dbms_output.put_line (name); Dbms_output.put_line (company); Dbms_output.put_line (Introduction); END IF; END; //When the above code is executed at the SQL prompt, it produces the following result:John Smithinfotech Hello! I'm John Smith from Infotech.PL/sql procedure successfully completed
Two, PL/SQL string functions and operators
PL/SQL provides the join operator (| | ) is used to connect two strings. The following table provides the string functionality (functions) provided with PL/sql:
|
functions and uses |
1 |
ASCII (x); Returns the ASCII value of the character X |
2 |
CHR (x); returns the ASCII value of the character X |
3 |
CONCAT (x, y); Concatenate strings x and Y, and return additional strings |
4 |
initcap (x); Each word is converted to uppercase in the first letter x, and the string is returned |
5 |
INSTR (x, find_string [, start] [, occurrence]); Search find_string in X and return to where it appears |
6 |
INSTRB (x); Returns the position of a string in another string, but returns the value in bytes |
7 |
LENGTH (x); Returns the number of characters in X |
8 |
LENGTHB (x); Returns the length of a string of bytes for a single-byte character set |
9 |
LOWER (x); converts an x to a lowercase letter and returns the string |
10 |
lpad (x, Width [, pad_string]); X fills the left with a space, and the total length of the string reaches the wide character |
11 |
LTRIM (x [, trim_string]); Left trim character from X |
12 |
NANVL (x, value); If x matches the special value of Nan (not a number) it returns its value, otherwise it returns X |
13 |
nls_initcap (x); The same initcap function, but it can be specified using a different sort method nlssort |
14 |
nls_lower (x); Similarly, the difference is that it can be specified using a different sorting method nlssort lower function |
15 |
nls_upper (x); The same, except that it can use different sorting methods to specify the Nlssort upper function |
16 |
nlssort (x); The method of changing the sorted character. This parameter must be specified before any NLS function; otherwise, the default sort is used |
17 |
NVL (x, value); Returns NULL if x is null; Otherwise, X is returned |
18 |
NVL2 (x, value1, value2); If x is not NULL, return value1; If x is null, the value2 is returned |
19 |
REPLACE (x, search_string, replace_string); Search x for search_string and replace with replace_string it |
20 |
rpad (x, Width [, pad_string]); Fill x to the right |
21st |
RTRIM (x [, trim_string]); Trim from X Right |
22 |
SOUNDEX (x); Returns a string containing the phonetic representation of X |
23 |
SUBSTR (x, start [, length]); Returns a child of x starting at the position specified by start. Optional length is substring |
24 |
SUBSTRB (x); Systems with single-byte characters in bytes instead of characters except for the same substr |
25 |
TRIM ([Trim_char from) x); Trim x characters from left and right |
26 |
UPPER (x); X is converted to uppercase and returns the string |
(vi) PL/SQL string