Class notes pl sql (sql01)

PL/SQL - Strings
The string in PL/SQL is actually a sequence of characters with an optional size
specification. The characters could be numeric, letters, blank, special characters or
a combination of all. PL/SQL offers three kinds of strings −
 Fixed-length strings − In such strings, programmers specify the length while
declaring the string. The string is right-padded with spaces to the length so
specified.
 Variable-length strings − In such strings, a maximum length up to 32,767,
for the string is specified and no padding takes place.
 Character large objects (CLOBs) − These are variable-length strings that
can be up to 128 terabytes.
PL/SQL strings could be either variables or literals. A string literal is enclosed within
quotation marks. For example,
'This is a string literal.' Or 'hello world'
To include a single quote inside a string literal, you need to type two single quotes
next to one another. For example,
'this isn''t what it looks like'

Declaring String Variables
Oracle database provides numerous string datatypes, such as CHAR, NCHAR,
VARCHAR2, NVARCHAR2, CLOB, and NCLOB. The datatypes prefixed with
an 'N' are 'national character set' datatypes, that store Unicode character data.
If you need to declare a variable-length string, you must provide the maximum
length of that string. For example, the VARCHAR2 data type. The following example
illustrates declaring and using some string variables −
DECLARE
name varchar2(20);
company varchar2(30);
introduction clob;
choice char(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 Smith
Infotech
Hello! I'm John Smith from Infotech.

PL/SQL procedure successfully completed
To declare a fixed-length string, use the CHAR datatype. Here 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 required. The
following two declarations are identical −
red_flag CHAR(1) := 'Y';
red_flag CHAR := 'Y';

PL/SQL String Functions and Operators
PL/SQL offers the concatenation operator (||) for joining two strings. The following
table provides the string functions provided by PL/SQL −

S.No Function & Purpose



1 ASCII(x);
Returns the ASCII value of the character x.


2 CHR(x);
Returns the character with the ASCII value of x.


3 CONCAT(x, y);
Concatenates the strings x and y and returns the appended string.


4 INITCAP(x);
Converts the initial letter of each word in x to uppercase and returns that string.


5 INSTR(x, find_string [, start] [, occurrence]);
Searches for find_string in x and returns the position at which it occurs.


6 INSTRB(x);
Returns the location of a string within another string, but returns the value in bytes.

7
LENGTH(x);