PL/SQL program to display location ids and cities

PL/SQL While Loop: Exercise-3 with Solution

Write a PL/SQL program to display the location IDs and cities of all locations along with the proper heading.

Sample Solution:

location_id			integer
street_address			varchar(50)
postal_code			varchar(13)
city				varchar(25)
state_province			varchar(25)
country_id			varchar(2)

PL/SQL Code:

DECLARE
v_location_idlocations.location_id%TYPE;
v_citylocations.city%TYPE;
  CURSOR c_locations IS SELECT location_id, city FROM locations;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Location ID | City');
  DBMS_OUTPUT.PUT_LINE('-------------------');
  OPEN c_locations;
  FETCH c_locations INTO v_location_id, v_city;
  WHILE c_locations%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_location_id || '       | ' || v_city);
    FETCH c_locations INTO v_location_id, v_city;
  END LOOP;
  CLOSE c_locations;
END;
/

Sample Output:

Location ID | City
-------------------
1000 | Roma
1100 | Venice
1200 | Tokyo
1300 | Hiroshima
1400 | Southlake
1500 | South San
1600 | South Brun
1700 | Seattle
1800 | Toronto
1900 | Whitehorse
2000 | Beijing
2100 | Bombay
2200 | Sydney
.....

Explanation:

The said code in Oracle's PL/SQL that retrieves the location IDs and cities from the 'locations' table and displays them with a proper heading.

The variable v_location_id of datatypelocations.location_id and the v_city of datatypelocations.city are declared to hold the location_id and city value from the 'locations' table.

The FOR loop retrieves each row from the 'locations' table and assigns the location_id and city values to the declared variables v_location_id and v_city respectively.

The DBMS_OUTPUT.PUT_LINE displays the location ID and city with a formatting heading "Location ID | City".

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: PL/SQL program to display job titles of employees.
Next: PL/SQL Program to Display Employee Information.