Statement that enables PL/SQL to perform actions selectively based on conditions
ID: 3747038 • Letter: S
Question
Statement that enables PL/SQL to perform actions selectively based on conditions.
Control structures – Repetition statements that enable you to execute statements in a PL/SQL block repeatedly.
An expression with a TRUE or FALSE value that is used to make a decision. An expression that determines a course of action based on conditions and can be used outside a PL/SQL block in a SQL statement.
1.What is the purpose of a conditional control structure in PL/SQL?
List the three categories of control structures in PL/SQL.
1.Write a PL/SQL block to find the population of a given country in the countries table. Display a message indicating whether the population is greater than or less than 1 billion (1,000,000,000). Test your block twice using India (country_id = 91) and United Kingdom (country_id = 44). India’s population should be greater than 1 billion, while United Kingdom’s should be less than 1 billion.
1.Modify the code from the previous exercise so that it handles all the following cases:
A.Population is greater than 1 billion.
B.Population is greater than 0.
C.Population is 0.
D.Population is null. (Display: No data for this country.)
Run your code using the following country ids. Confirm the indicated results.
China (country_id = 86): Population is greater than 1 billion.
United Kingdom (country_id = 44): Population is greater than 0.
Antarctica (country_id = 672): Population is 0.
Europa Island (country_id = 15): No data for this country.
DECLARE
v_country_id countries.country_name%TYPE := <a value>; v_ind_date countries.date_of_independence%TYPE; v_natl_holiday countries.national_holiday_date%TYPE;
BEGIN
SELECT date_of_independence, national_holiday_date
INTO v_ind_date, v_natl_holiday
FROM countries
WHERE country_id = v_country_id;
IF v_ind_date IS NOT NULL THEN DBMS_OUTPUT.PUT_LINE('A');
ELSIF v_natl_holiday IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('B');
ELSIF v_natl_holiday IS NULL AND v_ind_date IS NULL THEN
DBMS_OUTPUT.PUT_LINE('C');
END IF;
END;
What would print if the country has an independence date equaling NULL and a national holiday date equaling NULL?
What would print if the country has an independence date equaling NULL and a national holiday date containing a value?
What would print if the country has an independence date equaling a value and a national holiday date equaling NULL?
Explanation / Answer
------------------------------------------------------------------
1.What is the purpose of a conditional control structure in PL/SQL?
List the three categories of control structures in PL/SQL.
Sol:
selection structure :Test and Executes a condition in a sequence mode. It may be an variable or Boolean expression.
Ex: IF ELSE
Iteration structure: Executes a sequence of statements repeatedly as long as a condition holds true.
Ex: FOR LOOP
Sequence structure: executes a sequence of statements in the order in which they occur.
Ex: IF THEN ELSEIF
--------------------------------------------------------------------
1.Write a PL/SQL block to find the population of a given country in the countries table.
Display a message indicating whether the population is greater than or less than 1 billion (1,000,000,000).
Test your block twice using India (country_id = 91) and United Kingdom (country_id = 44).
India’s population should be greater than 1 billion, while United Kingdom’s should be less than 1 billion.
-----
DECLARE
v_country_id countries.country_id%TYPE ;
v_country_name countries.country_name%TYPE ;
v_pOPULATION countries.population%TYPE;
BEGIN
SELECT country_name, population INTO v_country_name, v_pOPULATION
FROM countries WHERE country_id = v_country_id;
IF v_pOPULATION > 1000000000 THEN
DBMS_OUTPUT.PUT_LINE(country_name|| ' population greater than 1 billion');
ELSIF v_pOPULATION < 1000000000 THEN
DBMS_OUTPUT.PUT_LINE(country_name|| ' population less than 1 billion');
END IF;
END;
-----
1.Modify the code from the previous exercise so that it handles all the following cases:
A.Population is greater than 1 billion.
B.Population is greater than 0.
C.Population is 0.
D.Population is null. (Display: No data for this country.)
Run your code using the following country ids. Confirm the indicated results.
China (country_id = 86): Population is greater than 1 billion.
United Kingdom (country_id = 44): Population is greater than 0.
Antarctica (country_id = 672): Population is 0.
Europa Island (country_id = 15): No data for this country.
-----
DECLARE
v_country_id countries.country_id%TYPE ;
v_country_name countries.country_name%TYPE ;
v_pOPULATION countries.population%TYPE;
BEGIN
SELECT country_name, population INTO v_country_name, v_pOPULATION
FROM countries WHERE country_id = v_country_id;
IF v_pOPULATION > 1000000000 THEN
DBMS_OUTPUT.PUT_LINE('Population is greater than 1 billion.');
ELSIF v_pOPULATION < 1000000000 AND v_pOPULATION > 0 THEN
DBMS_OUTPUT.PUT_LINE('Population is greater than 0.');
ELSIF v_pOPULATION =0 THEN
DBMS_OUTPUT.PUT_LINE('Population is 0.');
ELSE
DBMS_OUTPUT.PUT_LINE('Population is null. (Display: No data for this country.)');
END IF;
END;
-----
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.