Concatenation of strings in PL/SQL

In PL/SQL, there are multiple ways to concatenate strings:

Using the Concatenation Operator ||: The most common method of concatenating strings in PL/SQL is to use the || operator.

DECLARE
    str1 VARCHAR2(50) := 'Hello, ';
    str2 VARCHAR2(50) := 'World!';
    result VARCHAR2(100);
BEGIN
    result := str1 || str2;
    DBMS_OUTPUT.PUT_LINE(result);  -- Outputs: Hello, World!
END;
/

Using the CONCAT Function: Oracle provides a CONCAT function, which takes two string arguments and returns their concatenation.
```
DECLARE
    str1 VARCHAR2(50) := 'Hello, ';
    str2 VARCHAR2(50) := 'World!';
    result VARCHAR2(100);
BEGIN
    result := CONCAT(str1, str2);
    DBMS_OUTPUT.PUT_LINE(result);  -- Outputs: Hello, World!
END;
/
```
Note: The CONCAT function only takes two arguments. If you want to concatenate more than two strings using CONCAT, you will have to nest calls, like CONCAT(CONCAT(str1, str2), str3).

Using CHR for Special Characters: Sometimes, you might want to include special characters, such as a newline (CHR(10)) or carriage return (CHR(13)).

DECLARE
    str1 VARCHAR2(50) := 'Hello,';
    str2 VARCHAR2(50) := 'World!';
    result VARCHAR2(100);
BEGIN
    result := str1 || CHR(10) || CHR(13) || str2; -- Concatenating with newline and carriage return
    DBMS_OUTPUT.PUT_LINE(result);  
END;
/

Choose the method that best fits your needs. In most cases, the || operator is the most straightforward and flexible option for string concatenation in PL/SQL.

PL/SQL concatenate strings example:

Using the concatenation operator || to concatenate two strings:

DECLARE
   first_name VARCHAR2(50) := 'John';
   last_name VARCHAR2(50) := 'Doe';
   full_name VARCHAR2(100);
BEGIN
   full_name := first_name || ' ' || last_name;
   DBMS_OUTPUT.PUT_LINE('Full Name: ' || full_name);
END;
/

Using the CONCAT function in PL/SQL:

The CONCAT function can also be used for string concatenation:

DECLARE
   first_name VARCHAR2(50) := 'John';
   last_name VARCHAR2(50) := 'Doe';
   full_name VARCHAR2(100);
BEGIN
   full_name := CONCAT(first_name, ' ', last_name);
   DBMS_OUTPUT.PUT_LINE('Full Name: ' || full_name);
END;
/

How to concatenate strings in Oracle SQL:
In Oracle SQL, you can use the || operator for string concatenation:
```
SELECT first_name || ' ' || last_name AS full_name
FROM employees;
```

Concatenating strings in PL/SQL stored procedures:

When creating stored procedures, you can use string concatenation to build dynamic SQL queries or compose output:

CREATE OR REPLACE PROCEDURE generate_greeting(p_name VARCHAR2) IS
   v_greeting VARCHAR2(100);
BEGIN
   v_greeting := 'Hello, ' || p_name || '!';
   DBMS_OUTPUT.PUT_LINE(v_greeting);
END generate_greeting;
/

String manipulation in PL/SQL for concatenation:

PL/SQL offers various string manipulation functions for concatenation. For example:

DECLARE
   first_name VARCHAR2(50) := 'John';
   last_name VARCHAR2(50) := 'Doe';
   full_name VARCHAR2(100);
BEGIN
   full_name := CONCAT(CONCAT(first_name, ' '), last_name);
   DBMS_OUTPUT.PUT_LINE('Full Name: ' || full_name);
END;
/

Concatenating variables and literals in PL/SQL:

Combining variables and literals using the || operator:

DECLARE
   greeting_prefix VARCHAR2(20) := 'Hello, ';
   user_name VARCHAR2(50) := 'Alice';
   full_greeting VARCHAR2(100);
BEGIN
   full_greeting := greeting_prefix || user_name || '!';
   DBMS_OUTPUT.PUT_LINE(full_greeting);
END;
/