LOWER(): Oracle PL/SQL LOWER Function Examples

PL/SQL String Functions: LOWER()

Exercise 1:

Write a PL/SQL block to convert the last name of each employee in the employees table to lowercase and display the result.

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_lower_last_nameVARCHAR2(50);
BEGIN
  FOR emp IN (SELECT last_name FROM employees) LOOP
v_lower_last_name := LOWER(emp.last_name);
    DBMS_OUTPUT.PUT_LINE('Lowercase last name: ' || v_lower_last_name);
  END LOOP;
END;
/

Sample Output:

Lowercase last name: king        
Lowercase last name: kochhar
Lowercase last name: de haan
Lowercase last name: hunold
Lowercase last name: ernst
Lowercase last name: austin
Lowercase last name: pataballa
Lowercase last name: lorentz
Lowercase last name: greenberg
Lowercase last name: faviet
Lowercase last name: chen
Lowercase last name: sciarra
Lowercase last name: urman
Lowercase last name: popp
.......

Explanation:

The said code in Oracle's PL/SQL that demonstrate the conversion of last names of the employees from 'employees' table to lowercase.

The variable v_lower_last_name of type VARCHAR2 is declared.

The FOR loop which iterates through each row in the 'employees' table and retrieves the current employee's last name and assigned to the variable v_lower_last_name after converting it to lowercase using the LOWER function.

Tthe DBMS_OUTPUT.PUT_LINE statement displays the lowercase last name for each employee.

Flowchart:

Exercise 2:

Create a PL/SQL procedure to update the email of a specific employee identified by their employee ID to lowercase.

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 PROCEDURE update_email_to_lowercase(
p_employee_id IN employees.employee_id%TYPE
) AS
v_emailemployees.email%TYPE;
BEGIN
  SELECT email INTO v_email FROM employees WHERE employee_id = p_employee_id;
v_email := LOWER(v_email);
  UPDATE employees SET email = v_email WHERE employee_id = p_employee_id;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Email updated to lowercase successfully.');
END;
/
To execute the procedure:
BEGIN
update_email_to_lowercase(p_employee_id => 100); END;
/

Sample Output:

Email updated to lowercase successfully

Explanation:

The said code in Oracle's PL/SQL defines a procedure that updates the email of an employee to lowercase.

The procedure takes an input parameter p_employee_id of the same data type as the employee_id column in the 'employees' table.

A local variable v_email is declared with the same data type as the email column in the 'employees' table.

Inside the procedure, a SELECT statement retrieves the email associated with the provided p_employee_id and assign it to the v_email variable.

The v_email variable is then converted to lowercase using the LOWER function.

An UPDATE statement is executed to set the email of the employee with the provided p_employee_id to the lowercase version stored in the v_email variable.

A COMMIT statement is included to save the changes made by the UPDATE statement.

The DBMS_OUTPUT.PUT_LINE statement displays a success message indicating that the email has been updated to lowercase.

Flowchart:

Exercise 3:

Write a PL/SQL function to return the number of employees whose job title, converted to lowercase, matches a specific input job title.

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 count_employees_with_job_title(
p_job_title IN employees.job_id%TYPE
) RETURN NUMBER AS
v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM employees WHERE LOWER(job_id) = LOWER(p_job_title);
  RETURN v_count;
END;
/
To execute the function:
DECLARE
v_result NUMBER;
BEGIN
v_result := count_employees_with_job_title(p_job_title => 'ST_CLERK');  DBMS_OUTPUT.PUT_LINE('Number of employees with the job title: ' || v_result);
END;
/

Sample Output:

Number of employees with the job title: 20

Explanation:

The said code in Oracle's PL/SQL defines a function that calculates and returns the count of employees with a specific job title.

The code accepts an input parameter p_job_title of the same data type as the job_id column in the 'employees' table.

A local variable v_count of type NUMBER is declared to store the count of employees.

Inside the function the SELECT statement that counts the number of rows in the 'employees' table where the job_id is equal to the provided p_job_title. The result of the COUNT(*) query is assigned to the v_count variable using the INTO clause.

The function then returns the value stored in the v_count variable.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: SUBSTR() Functions.
Next: RPAD() Function.