In Oracle PL/SQL, the VARCHAR data type is used to store character strings of variable length. A VARCHAR column can store alphanumeric data and symbols, such as letters, numbers, and special characters, up to a maximum length specified during declaration.
The syntax for declaring a VARCHAR variable or column is as follows:
DECLARE -- declares a VARCHAR variable with a maximum length of 50 characters my_var VARCHAR(50); BEGIN -- code block here END;
In the example above, my_var is a VARCHAR variable that can store up to 50 characters. The maximum length can range from 1 to 4000 bytes.
One advantage of using VARCHAR over CHAR (fixed-length character string) data type is that it takes up less storage space. Since VARCHAR only stores the characters entered plus 1 or 2 bytes for overhead, it is more space-efficient compared to CHAR, which always reserves the maximum length specified, regardless of the actual data length.
To manipulate VARCHAR data, PL/SQL provides built-in functions and operators such as CONCAT, SUBSTR, INSTR, LENGTH, and others.
Here’s an example that shows how to concatenate two VARCHAR variables:
DECLARE first_name VARCHAR(50) := 'John'; last_name VARCHAR(50) := 'Doe'; full_name VARCHAR(100); BEGIN full_name := CONCAT(first_name, ' ', last_name); DBMS_OUTPUT.PUT_LINE(full_name); -- prints "John Doe" END;
In the example above, the CONCAT function is used to concatenate the first and last name variables with a space in between. The resulting string is stored in the full_name variable and then printed to the console using the DBMS_OUTPUT.PUT_LINE procedure.
In summary, VARCHAR is a data type used in Oracle PL/SQL to store variable-length character strings. It is space-efficient, flexible, and can be manipulated using various built-in functions and operators.