Exploring Oracle PL/SQL code: looping, procedure, and function examples on LENGTH()

PL/SQL String Functions: LENGTH()

Exercise 1:

Write a PL/SQL block to calculate the length of each employee's last name in the employees table.

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_length NUMBER;
BEGIN
  FOR emp IN (SELECT last_name FROM employees) LOOP
v_length := LENGTH(emp.last_name);
    DBMS_OUTPUT.PUT_LINE('Length of last name ' || emp.last_name || ': ' || v_length);
  END LOOP;
END;
/

Sample Output:

Length of last name King        : 12
Length of last name Kochhar     : 12
Length of last name De Haan     : 12
Length of last name Hunold      : 12
Length of last name Ernst       : 12
Length of last name Austin      : 12
Length of last name Pataballa   : 12
Length of last name Lorentz     : 12
Length of last name Greenberg   : 12
Length of last name Faviet      : 12
Length of last name Chen        : 12
Length of last name Sciarra     : 12
Length of last name Urman       : 12
Length of last name Popp        : 12
Length of last name Raphaely    : 12
.....

Explanation:

The said code in Oracle's PL/SQL retrieves the last names of all employees from the 'employees' table and calculates the length of each last name.

A variable v_length is declared of type NUMBER. It is used to store the length of each employee's last name.

The FOR loop iterates over the result set that is the last names of all employees from the 'employees' table returned by the SELECT statement.

A loop variable emp that represents each row fetched from the result set in each iteration of the loop.

Then it calculates the length of the current employee's last name and assigns it to the variable v_length.

DBMS_OUTPUT.PUT_LINE displays the output in the console.

Flowchart:

Exercise 2:

Create a PL/SQL procedure to calculate the length of the job title for a specific employee identified by their employee ID.

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 calculate_job_title_length(
p_employee_id IN employees.employee_id%TYPE
) AS
v_job_titleemployees.job_id%TYPE;
v_length NUMBER;
BEGIN
  SELECT job_id INTO v_job_title FROM employees WHERE employee_id = p_employee_id;
v_length := LENGTH(v_job_title);
  DBMS_OUTPUT.PUT_LINE('Length of job title: ' || v_length);
END;
/
To execute procedure:
BEGIN
  -- Call the procedure and pass the employee ID as the parameter
calculate_job_title_length(p_employee_id => 125); -- Replace 125 with the desired employee ID
END;
/

Sample Output:

Length of job title: 8

Explanation:

The said code in Oracle's PL/SQL defines a procedure that calculates the length of a job title for a specific employee.

The procedure "calculate_job_title_length" takes one input parameter, p_employee_id, which represents the employee ID.

Two variables v_job_title the same datatype of job_id and v_length the NUMBER type are declared to store the job title of the employee and the length of the job title respectively.

The SELECT statement retrieves the job_id of the employee with the specified employee_id and assigns it to the v_job_title variable.

The LENGTH() function calculates the length of the job title stored in the v_job_title variable and assigns it to the v_length variable.

The DBMS_OUTPUT.PUT_LINE displays the length of the job title in the console.

Flowchart:

Exercise 3:

Write a PL/SQL function to return the maximum length of the department names in the departments table.

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

PL/SQL Code:

CREATE OR REPLACE FUNCTION get_max_department_length RETURN NUMBER AS
v_max_length NUMBER := 0;
v_length NUMBER;
BEGIN
  FOR dept IN (SELECT department_name FROM departments) LOOP
v_length := LENGTH(dept.department_name);
    IF v_length>v_max_length THEN
v_max_length := v_length;
    END IF;
  END LOOP;

  RETURN v_max_length;
END;
/
To execute the function:
DECLARE
v_result NUMBER;
BEGIN
  -- Call the function and store the result in the variable v_result
v_result := get_max_department_length();

  -- Do something with the result
  DBMS_OUTPUT.PUT_LINE('Maximum department name length: ' || v_result);
END;

Sample Output:

Maximum department name length: 15

Explanation:

The said code in Oracle's PL/SQL defines a function that calculates and returns the maximum length of department names in the "departments" table.

The function "get_max_department_length" and it doesn't take any input parameters.

Two variables v_max_length of NUMBER type and initialized to 0 to store the maximum length of department names and v_length of NUMBER type to calculate the length of each department name.

The FOR loop iterates over the result set that is the department names from the 'departments' table returns by the SELECT statement.

A loop variable dept that represents each row fetched from the result set in each iteration of the loop.

The LENGTH() then calculates the length of the current department name and assigns it to the v_length variable.

The IF statement checks whether the current department name's length is greater than the current maximum length and updates to the new maximum length if true.

The LOOP continues to iterate over all the department names in the result set.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: CHR() Functions.
Next: CONCAT() Function.