In this tutorial we will learn about Strings in PL/SQL covering various types of strings, syntax of declaring a string variable and then using it in a PL/SQL code block.
Just like in any other programming language, string in PL/SQL is a combination of characters which can be numbers, special characters, alphabets or all together.
PL/SQL offers us the liberty of creating variable length strings where it is optional to provide the size of the string.
In PL/SQL there are basically three different types of strings, they are:
A basic literal is also a string when enclosed in a quotation marks, for example:
'This is a string literal'
To include a single quote in the string text, you will have to use two single quotes. For example, 'You think it' 's your choice'.
For declaring a string variable, we have to specify the datatype and length value which defines the maximum length of the string.
We can use datatypes like CHAR, VARCHAR2, CLOB etc, where each of these is used to define a different type of string.
First let's see how to declare a variable length string variable. We use VARCHAR2 datatype for it.
name is the name of the variable, VARCHAR2 is the datatype and 10 is the maximum length that the string can take.
Now, let's see how can we declare a fixed length string variable. We use CHAR datatype for this. While declaring this type of string, you may or may not provide the size/length of the string because if you do not provide it, Oracle Database automatically uses a maximum length required. For example,
approved CHAR(1) := 'Y' approved CHAR := 1
Let's take an example of PL/SQL code where we will be using multiple strings to understand the concept better.
In the code example below we will be using all 3 types of strings.
DECLARE s_name VARCHAR2(20); s_age NUMBER(2); s_address CLOB; s_gender CHAR(1); BEGIN s_name := 'Sherlock Holmes'; s_age := 21; s_address := '221B, Baker Street, London'; s_gender := 'M'; IF s_gender = 'M' THEN dbms_output.put_line(s_name || ' is a student at Studytonight'); dbms_output.put_line('He is ' || s_age || ' years old.'); dbms_output.put_line('He lives at ' || s_address); ELSE dbms_output.put_line(s_name || ' is a student at Studytonight'); dbms_output.put_line('She is ' || s_age || ' years old.'); dbms_output.put_line('She lives at ' || s_address); END IF; END; /
Sherlock Holmes is a student at Studytonight He is 21 years old. He lives at 221B, Baker Street, London
You can find the list and usage examples of all the string functions in the official documentation at Oracle's website here: String Functions