Database Programming with PL/SQL
8-3: Passing Parameters
Practice Activities
Vocabulary
Returns a value to the caller - OUT Parameter
Provides values for a subprogram to process - IN Parameter
Lists the actual parameters in arbitrary order and uses the association operator ( ‘=>' which is an equal and an arrow together) to associate a named formal parameter with its actual parameter - Named notation
Lists some of the actual parameters as positional (no special operator) and some as named (with the => operator) - Combination Notation
Lists the actual parameters in the same order as the formal parameters - Positional Notation
Supplies an input value, which may be returned as a modified value - IN OUT Parameter
Try It / Solve It
1. Name the three modes for parameters and indicate which mode is the default mode. Which mode
cannot be modified inside the procedure?
IN is the default mode and can not be modified insite the procedure because is a constant
OUT
IN OUT
2. Procedures:
A. Create a procedure that receives a country_id as an IN parameter and returns the name and
population of that country as OUT parameters. Include an exception handler to trap the
NO_DATA_FOUND exception if the country does not exist. The procedure should not display
the returned values; this will be done in the next step. Name your procedure find_area_pop.
Save your code.
CREATE OR REPLACE PROCEDURE find_area_pop
(p_country_id IN wf_countries.country_id%TYPE, p_country_name OUT wf_countries.country_name%TYPE,p_country_population OUT wf_countries.population%TYPE) IS
BEGIN
SELECT country_name, population INTO p_country_name, p_country_population
FROM wf_countries
WHERE country_id = p_country_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No country found');
END find_area_pop;
B. Test your procedure by creating and executing an anonymous block which invokes the
procedure and displays the returned OUT values. Save your code. Run the block twice, with
country_ids 2 (Canada) and 10 (does not exist).
DECLARE
v_country_name wf_countries.country_name%TYPE;
v_country_population wf_countries.population%TYPE;
BEGIN
find_area_pop (2, v_country_name, v_country_population);
DBMS_OUTPUT.PUT_LINE(v_country_name || ' ' || v_country_population);
END;
DECLARE
v_country_name wf_countries.country_name%TYPE;
v_country_population wf_countries.population%TYPE;
BEGIN
find_area_pop (10, v_country_name, v_country_population);
DBMS_OUTPUT.PUT_LINE(v_country_name || ' ' || v_country_population);
END;
C. Retrieve your procedure code and modify it to add a third OUT parameter which is the
population density of the country, using the formula: density = (population / area). You will
need to modify your SELECT statement to fetch the area column value into a local variable.
Save your modified code.
CREATE OR REPLACE PROCEDURE find_area_pop
(p_country_id IN wf_countries.country_id%TYPE, p_country_name OUT wf_countries.country_name%TYPE,p_country_population OUT wf_countries.population%TYPE, p_density OUT NUMBER) IS
BEGIN
SELECT country_name, population, population / area INTO p_country_name, p_country_population, p_density
FROM wf_countries
WHERE country_id = p_country_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No country found');
END find_area_pop;
D. Test your modifed procedure using country_id 2. You will need to modify your calling
anonymous block to declare and pass a fourth actual parameter to receive the population
density from the procedure. Save your code.
DECLARE
v_country_name wf_countries.country_name%TYPE;
v_country_population wf_countries.population%TYPE;
v_density NUMBER(3);
BEGIN
find_area_pop (2, v_country_name, v_country_population, v_density);
DBMS_OUTPUT.PUT_LINE(v_country_name || ' ' || v_country_population || ' ' || v_density);
END;
3. Create a procedure which accepts an integer as an IN OUT parameter and returns the square of
that integer, for example the square of 4 is 16. Save your code. Test your procedure from an
anonymous block three times, using integer values 4, 7, and –20 (negative 20).
CREATE OR REPLACE PROCEDURE square
(p_square IN OUT NUMBER) IS
BEGIN
p_square := p_square * p_square;
END square;
DECLARE
nr NUMBER(20);
BEGIN
nr := 4;
square(nr);
DBMS_OUTPUT.PUT_LINE(nr);
END;
DECLARE
nr NUMBER(20);
BEGIN
nr := 7;
square(nr);
DBMS_OUTPUT.PUT_LINE(nr);
END;
DECLARE
nr NUMBER(20);
BEGIN
nr := -20;
square(nr);
DBMS_OUTPUT.PUT_LINE(nr);
END;
4. List the three methods of passing parameters to a procedure.
Positional, Named and Combination
A. Retrieve your anonymous block from question 2D and modify its call to find_area_pop to pass
the four parameters using named notation. Test your block, again using country_id 2
(Canada). If you have forgotten the p_ names of the procedure’s formal parameters, how can
you refresh your memory?
describe find_area_pop; to refresh our memories
DECLARE
v_country_name wf_countries.country_name%TYPE;
v_country_population wf_countries.population%TYPE;
v_density NUMBER(3);
BEGIN
find_area_pop (p_country_name => v_country_name, p_density => v_density, p_country_population => v_country_population,p_country_id => 2);
DBMS_OUTPUT.PUT_LINE(v_country_name || ' ' || v_country_population || ' ' || v_density);
END;
B. Modify the anonymous block from the previous step to pass the FIRST two parameters using
named notation and the LAST two using positional notation. Test the block again. What
happens?
DECLARE
v_country_name wf_countries.country_name%TYPE;
v_country_population wf_countries.population%TYPE;
v_density NUMBER(3);
BEGIN
find_area_pop (v_country_population, v_density, p_country_id => 2, p_country_name => v_country_name);
DBMS_OUTPUT.PUT_LINE(v_country_name || ' ' || v_country_population || ' ' || v_density);
END;
Error
C. Correct the problem in the previous step by modifying the anonymous block again to pass the
first two parameters using positional notation and the last two using named notation. Test the
block again.
DECLARE
v_country_name wf_countries.country_name%TYPE;
v_country_population wf_countries.population%TYPE;
v_density NUMBER(3);
BEGIN
find_area_pop (2, v_country_name, p_density => v_density, p_country_population => v_country_population);
DBMS_OUTPUT.PUT_LINE(v_country_name || ' ' || v_country_population || ' ' || v_density);
END;
5. In your own words, describe the purpose of the DEFAULT option for parameters and state the two
syntax options for providing the default value in the procedure header.
Default can be declared with := or DEFAULT
CREATE PROCEDURE def(v_a NUMBER := 0, v_b NUMBER DEFAULT 0)
It is useful when the call is missing those variables.
6. Find the country_id of your own country by executing a suitable SELECT…FROM countries….
Then retrieve your find_area_pop procedure from question 2C. Modify the code to use your
country_id as a default value for the country_id IN parameter. Save your code. Then retrieve your
anonymous block from question 2D and modify it so that it does NOT pass the country_id to the
procedure. Test the block and check that your country’s details are returned and displayed. If your
modified anonymous block does not work, correct it so it will.
SELECT country_id
FROM wf_countries
WHERE country_name = 'Romania';
country_id = 40
CREATE OR REPLACE PROCEDURE find_area_pop
(p_country_id IN wf_countries.country_id%TYPE DEFAULT 40, p_country_name OUT wf_countries.country_name%TYPE,p_country_population OUT wf_countries.population%TYPE, p_density OUT NUMBER) IS
BEGIN
SELECT country_name, population, population / area INTO p_country_name, p_country_population, p_density
FROM wf_countries
WHERE country_id = p_country_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No country found');
END find_area_pop;
DECLARE
v_country_name wf_countries.country_name%TYPE;
v_country_population wf_countries.population%TYPE;
v_density NUMBER(3);
BEGIN
find_area_pop (p_country_name => v_country_name, p_density => v_density, p_country_population => v_country_population);
DBMS_OUTPUT.PUT_LINE(v_country_name || ' ' || v_country_population || ' ' || v_density);
END;
0 Comments