Display names of all countries using PL/SQL

PL/SQL While Loop: Exercise-1 with Solution

Write a PL/SQL program to display the names of all countries.

Sample Solution:

country_id		varchar(5)	
country_name		varchar(25)

PL/SQL Code:

DECLARE
v_country_namecountry.country_name%TYPE;
  CURSOR c_countries IS SELECT country_name FROM country;
BEGIN
  OPEN c_countries;
  FETCH c_countries INTO v_country_name;
  WHILE c_countries%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(v_country_name);
    FETCH c_countries INTO v_country_name;
  END LOOP;
  CLOSE c_countries;
END;

Sample Output:

Argentina
Australia
Belgium
Brazil
Canada
Switzerland
China
Germany
Denmark
Egypt
France
Israel
.....

Explanation:

The said code in Oracle's PL/SQL that retrieves the country names from the country table using a cursor and displays them one by one.

The variable v_country_name of type country.country_name is declared.

The cursor c_countries is declared to retrieve the country_name column from the country table.

The OPEN statement opens the cursor, making it ready for fetching data.

The LOOP keyword that iterates until a specific condition is met and after the completion of iteration it will exit from the loop.

The FETCH statement fetches the next row from the c_countries cursor and assigns the value to the v_country_name variable.

The DBMS_OUTPUT.PUT_LINE statement displays the value of v_country_name to the console.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: PL/SQL While Loop Exercises Home.
Next: PL/SQL program to display job titles of employees.