Database Programming with PL/SQL
4-2: Conditional Control: Case Statements
Practice Activities
Vocabulary
An expression that selects a result and returns it into a variable. - CASE expression
Shows the results of all possible combinations of two conditions. - Logic Tables
A block of code that performs actions based on conditional tests. - CASE statement
Try It / Solve It
1. Write a PL/SQL block:
A. To find the number of airports from the countries table for a supplied country_name. Based on this number, display a customized message as follows:
# Airports Message
0–100 There are 100 or fewer airports.
101–1,000 There are between 101 and 1,000 airports.
1001–1,0000 There are between 1,001 and 10,000 airports.
> 10,000 There are more than 10,000 airports.
No value in database The number of airports is not available for this country
Use a CASE statement to process your comparisons.
You can use the following code to get started:
DECLARE
v_country_name countries.country_name%TYPE := '<country_name>';
v_airports countries.airports%TYPE;
BEGIN
SELECT airports INTO v_airports
FROM wf_countries
WHERE country_name = v_country_name;
CASE
WHEN ...
…
END CASE;
END;
DECLARE
v_country_name wf_countries.country_name%TYPE := 'Canada';
v_airports wf_countries.airports%TYPE;
v_string VARCHAR2(60);
BEGIN
SELECT airports INTO v_airports
FROM wf_countries
WHERE country_name = v_country_name;
v_string :=
CASE
WHEN v_airports < 101 THEN 'There are 100 or fewer airports.'
WHEN v_airports < 1001 THEN 'There are between 101 and 1,000 airports'
WHEN v_airports < 10001 THEN 'There are between 1,001 and 10,000 airports'
WHEN v_airports > 10000 THEN 'There are more than 10,000 airports.'
ELSE 'The number of airports is not available for this country.'
END;
DBMS_OUTPUT.PUT_LINE(v_string);
END;
B. Test your code for the following countries and confirm the results.
On the second line, v_country_name wf_countries.country_name%TYPE := 'Canada';
Replace 'Canada' with the other country names, one by one and check the result.
(Japan, Malaysia, Mongolia, Navassa Island, Romania, United States of America).
2. Write a PL/SQL block:
A. To find the amount of coastline for a supplied country name. Use the countries table. Based on the amount of coastline for the country, display a customized message as follows: Length of Coastline Message
0 no coastline
< 1,000 a small coastline
< 10,000 a mid-range coastline
All other values a large coastline
Use a CASE expression.
Use the following code to get started:
DECLARE
v_country_name countries.country_name%TYPE := '<country name>';
v_coastline countries.coastline %TYPE;
v_coastline_description VARCHAR2(50);
BEGIN
SELECT coastline INTO v_coastline
FROM countries
WHERE country_name = v_country_name;
v_coastline_description :=
CASE
...
END;
DBMS_OUTPUT.PUT_LINE('Country ' || v_country_name || ' has '
|| v_coastline_description);
END;
DECLARE
v_country_name wf_countries.country_name%TYPE := 'Canada';
v_coastline wf_countries.coastline %TYPE;
v_coastline_description VARCHAR2(50);
BEGIN
SELECT coastline INTO v_coastline
FROM wf_countries
WHERE country_name = v_country_name;
v_coastline_description :=
CASE
WHEN v_coastline = 0 THEN 'no coastline'
WHEN v_coastline < 1000 THEN 'a small coastline'
WHEN v_coastline < 10000 THEN 'a mid-range coastline'
ELSE 'a large coastline'
END;
DBMS_OUTPUT.PUT_LINE('Country ' || v_country_name || ' has ' || v_coastline_description);
END;
B. Test your code for the following countries and confirm the results.
On the second line, v_country_name wf_countries.country_name%TYPE := 'Canada';
Replace 'Canada' with the other country names, one by one and check the result.
(Grenada, Jamaica, Japan, Mongolia, Ukraine).
3. Use a CASE statement:
A. Write a PL/SQL block to select the number of countries using a supplied currency name. If the number of countries is greater than 20, display “More than 20 countries”. If the number of countries is between 10 and 20, display “Between 10 and 20 countries”. If the number of countries is less than 10, display “Fewer than 10 countries”. Use a CASE statement.
DECLARE
v_currency wf_countries.currency_code%TYPE := 'USD';
v_result VARCHAR2(50);
v_count NUMBER(3);
BEGIN
SELECT count(country_id) INTO v_count
FROM wf_countries
WHERE currency_code = v_currency;
v_result :=
CASE
WHEN v_count < 10 THEN 'Fewer than 10 countries'
WHEN v_count < 21 THEN 'Between 10 and 20 countries'
ELSE 'More than 20 countries'
END;
DBMS_OUTPUT.PUT_LINE(v_result);
END;
B. Test your code using the following data:
On the second line, v_currency wf_countries.currency_code%TYPE := 'USD';
Replace 'USD' with the other currencies, one by one and check the result.
(CHF for Swiss franc and Eur for Euro).
4. Examine the following code.
A. What do you think the output will be? Test your prediction by running the code.
DECLARE
x BOOLEAN := FALSE;
y BOOLEAN;
v_color VARCHAR(20) := 'Red';
BEGIN
IF (x OR y)
THEN v_color := 'White';
ELSE
v_color := 'Black';
END IF;
DBMS_OUTPUT.PUT_LINE(v_color);
END;
Black
B. Change the declarations to x and y as follows. What do you think the output will be? Test your
prediction by running the code again.
x BOOLEAN ;
y BOOLEAN ;
Black
C. Change the declarations to x and y as follows. What do you think the output will be? Test your
prediction by running the code again.
x BOOLEAN := TRUE;
y BOOLEAN := TRUE;
White
D. Experiment with changing the OR condition to AND.
The results will be the same because, for A and B we have a null and every comparison with null is false. null or something, is false. null and someting, is false. For C, we have both true so both true or true, true and true gives the same result.
0 Comments