PL-SQL-Strings – Variable Declaration and initialization

PL-SQL-Strings- Learn how to declare string variables and initialization of values into them.

PL-SQL-Strings

A string is a collection of characters with in a single quote. This characters can be any kind of symbols and letters such as ‘A’,’B’..,

There are 3 kinds of strings available in PL/SQL.

• Fixed-length strings – programmers specify the length while declaring the string.
• Variable-length strings – for variable length , you can use from zero length to max length of 32,767 characters.
• Character large objects (CLOBs) – this are variable length, you can use up to 128 Tera Bytes.

Strings can be literals or variables. A string literal begins and ends with a single quotation mark.

Example 

 'This is a string literal'

To include a single quote inside a string literal

'this isn''t a string literal '
or
q'[this isn't a string literal]'

String Variables Declaration

Syntax

variable_name data_type(size)

Example

name varchar2(20); -- variable length string declaration
gender char(1); -- fixed length string declaration

String data types

• CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB are string data types.
• The data types prefixed with ‘N’ are national character set data types which stores unicode characters.

Example program to demonstrate the string variable declaration and initialize values.

DECLARE
  --Declaring variable length variable
  FullName VARCHAR2(100);
  --Declaring fixed length variable
  Gender CHAR(1);
BEGIN
  --initializinng value to fixed to length variable
  FULLNAME :='JOHN FEDRICK';
  --initializing value to fixed length variable
  Gender :='M';
  DBMS_OUTPUT.PUT_LINE('I am '||FULLNAME);
  IF gender='M' THEN
    DBMS_OUTPUT.PUT_LINE('I am Male');
  ELSE
    DBMS_OUTPUT.PUT_LINE('I am Female');
  END IF;
END;

Output

I am JOHN FEDRICK
I am Male

Example program to demonstrate how to display a string with single quote

Method -1

DECLARE
  --Declaring variable length variable
  FullName VARCHAR2(100);
  --Declaring fixed length variable
  Gender CHAR(1);
BEGIN
  --initializinng value to fixed to length variable
  FULLNAME :='JOHN FEDRICK';
  --initializing value to fixed length variable
  Gender :='M';
  --use '' when we want to display single quote
  DBMS_OUTPUT.PUT_LINE('He isn''t '||FULLNAME);
  IF gender='M' THEN
    DBMS_OUTPUT.PUT_LINE('He isn''t Male');
  ELSE
    DBMS_OUTPUT.PUT_LINE('She isn''t Female');
  END IF;
END;

Method -2

DECLARE
  --Declaring variable length variable
  FullName VARCHAR2(100);
  --Declaring fixed length variable
  Gender CHAR(1);
BEGIN
  --initializinng value to fixed to length variable
  FULLNAME :='JOHN FEDRICK';
  --initializing value to fixed length variable
  Gender :='M';
  --use '' when we want to display single quote
  DBMS_OUTPUT.PUT_LINE(q'[He isn't ]'||FULLNAME);
  IF gender='M' THEN
    DBMS_OUTPUT.PUT_LINE(q'[He isn't Male]');
  ELSE
    DBMS_OUTPUT.PUT_LINE(q'[She isn't Female]');
  END IF;
END;

Output

He isn’t JOHN FEDRICK
He isn’t Male