CONCAT(): Oracle PL/SQL concatenation examples

PL/SQL String Functions: CONCAT()

Exercise 1:

Write a PL/SQL block to concatenate the first name and last name of each employee in the employees table and display the full name.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DECLARE
v_full_nameVARCHAR2(100);
BEGIN
  FOR emp IN (SELECT first_name, last_name FROM employees) LOOP
v_full_name := emp.first_name || ' ' || emp.last_name;
    DBMS_OUTPUT.PUT_LINE('Full Name: ' || v_full_name);
  END LOOP;
END;
/

Sample Output:

Full Name: Steven      King        
Full Name: NeenaKochhar
Full Name: Lex         De Haan
Full Name: Alexander   Hunold
Full Name: Bruce       Ernst       
Full Name: David       Austin      
Full Name: ValliPataballa
Full Name: Diana       Lorentz     
Full Name: Nancy       Greenberg   
Full Name: Daniel      Faviet
Full Name: John        Chen      
.....

Explanation:

The said code in Oracle's PL/SQL that retrieves data from the 'employees' table and concatenates the "first_name" and "last_name" columns to form a full name.

A variable v_full_name of VARCHAR2 type declares to store concatenated name of maximum length of 100 characters.

The FOR loop iterates over the result set obtained from the SELECT statement.

The SELECT statement retrieves the first_name and last_name columns from the employees table and store to loop variable emp.

Inside the loop, the first_name and last_name values are concatenated using the || operator and assigned to the v_full_name variable.

The DBMS_OUTPUT.PUT_LINE procedure prints the full name to the console along with the text 'Full Name: '.

Flowchart:

Exercise 2:

Create a PL/SQL procedure to concatenate the job title and department name for a specific employee identified by their employee ID and display the result.

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

CREATE OR REPLACE PROCEDURE concatenate_job_title_department(
p_employee_id IN employees.employee_id%TYPE,
p_department_id IN departments.department_id%TYPE
) AS
v_job_titleemployees.job_id%TYPE;
v_department_namedepartments.department_name%TYPE;
v_resultVARCHAR2(100);
BEGIN
  SELECT job_id INTO v_job_title FROM employees WHERE employee_id = p_employee_id;
  SELECT department_name INTO v_department_name FROM departments WHERE department_id = p_department_id;
v_result := v_job_title || ' - ' || v_department_name;
  DBMS_OUTPUT.PUT_LINE('Concatenated Result: ' || v_result);
END;
/
To execute procedure:
DECLARE
employee_idemployees.employee_id%TYPE := 125; -- Provide the employee ID you want to pass
department_iddepartments.department_id%TYPE := 90; -- Provide the department ID you want to pass
BEGIN
concatenate_job_title_department(employee_id, department_id);
END;
/

Sample Output:

Concatenated Result: ST_CLERK - Executive

Explanation:

The said code in Oracle's PL/SQL defines a procedure that concatenates the job title and department of an employee based on the given employee ID and department ID.

The procedure has two input parameters, the p_employee_id and the p_department_id .

The three variables v_job_title of employees job_id TYPE, v_department_name of departments department_name TYPE, and v_result of VARCHAR2 TYPE with a maximum length of 100 characters are declares.

The first SELECT statement retrieves the job ID from the employees table based on the specifies employee ID. The retrieved value is stored in the v_job_title variable.

The second SELECT statement retrieves the department name from the departments table based on the specified department ID. The retrieved value is stored in the v_department_name variable.

The v_result variable is assigned the concatenated value of v_job_title, a hyphen (' - '), and v_department_name.

The DBMS_OUTPUT.PUT_LINE procedure displays the concatenated result with the text 'Concatenated Result: ' to the console.

Flowchart:

Exercise 3:

Write a PL/SQL function to concatenate the first name, last name, and email of an employee identified by their employee ID and return the result.

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

CREATE OR REPLACE FUNCTION concatenate_employee_details(
p_employee_id IN employees.employee_id%TYPE
) RETURN VARCHAR2 AS
v_first_nameemployees.first_name%TYPE;
v_last_nameemployees.last_name%TYPE;
v_emailemployees.email%TYPE;
v_resultVARCHAR2(100);
BEGIN
  SELECT first_name, last_name, email INTO v_first_name, v_last_name, v_email
  FROM employees WHERE employee_id = p_employee_id;

v_result := v_first_name || ' ' || v_last_name || ' - ' || v_email;
  RETURN v_result;
END;
/
To execute function:
DECLARE
employee_idemployees.employee_id%TYPE := 123; -- Provide the employee ID you want to pass
result VARCHAR2(100);
BEGIN
result := concatenate_employee_details(employee_id);
  DBMS_OUTPUT.PUT_LINE('Concatenated Result: ' || result);
END;
/

Sample Output:

Concatenated Result: Julia       Nayer        - JNAYER

Explanation:

The said code in Oracle's PL/SQL defines a function that concatenates the first name, last name, and email of an employee based on the specified employee ID.

The takes an input parameter p_employee_id of type employee_id of employees table and it specifies the employee for which the details will be concatenated.

The four variables v_first_name of type first_name, v_last_name of type last_name, and v_email of type email of the table employees, and v_result of type VARCHAR2 with a maximum length of 100 characters are declares.

The SELECT statement retrieves the first name, last name, and email from the employees table based on the specified employee ID and stored in the variables v_first_name, v_last_name, and v_email.

The v_result variable is assigned the concatenated value of v_first_name, a space ,v_last_name,a hyphen (' - '), and v_email.

The RETURN statement is used to return the value of v_result from the function.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: LENGTH() Functions.
Next: INSTR() Function.