Database Programming with PL/SQL
8-2: Using Parameters in Procedures
Practice Activities
Vocabulary
Pass or communicate data between the caller and subprogram. - Parameters
The actual value assigned to a parameter. - Argument
Can be literal values, variables, or expressions that are provided in the parameter list of a called subprogram. - Actual parameter
A parameter name declared in the procedure heading. - Formal Paramter
Try It / Solve It
1. In your own words, describe parameters and the purpose they serve in PL/SQL subprograms.
Parameters are variables passed from the caller program to the subprogram.
Parameters are used to make procedures more flexible, so that it can be used for more than one purpose or more than one piece of data.
2. Using the COUNTRIES table:
A. Create a procedure that accepts a country_id as a parameter and displays the name of the
country and its capitol city. Name your procedure get_country_info. Save your procedure
definition for later use.
CREATE OR REPLACE PROCEDURE get_country_info
(p_country_id IN wf_countries.country_id%TYPE) IS
v_country_id wf_countries.country_id%TYPE;
v_capitol wf_countries.capitol%TYPE;
BEGIN
SELECT country_id, capitol INTO v_country_id, v_capitol
FROM wf_countries
WHERE country_id = p_country_id;
DBMS_OUTPUT.PUT_LINE(v_country_id || ' ' || v_capitol);
END get_country_info;
B. Execute your procedure from an anonymous block, using country_id 90.
BEGIN
get_country_info(90);
END;
C. Re-execute the procedure from the anonymous block, this time using country_id 95. What happens?
ORA-01403: no data found
D. Retrieve your procedure code from Saved SQL and modify it to trap the NO_DATA_FOUND exception in an exception handler. Execute the modified procedure using country_id 95 again. Now what happens?
CREATE OR REPLACE PROCEDURE get_country_info
(p_country_id IN wf_countries.country_id%TYPE) IS
v_country_id wf_countries.country_id%TYPE;
v_capitol wf_countries.capitol%TYPE;
BEGIN
SELECT country_id, capitol INTO v_country_id, v_capitol
FROM wf_countries
WHERE country_id = p_country_id;
DBMS_OUTPUT.PUT_LINE(v_country_id || ' ' || v_capitol);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is no country with that id');
END get_country_info;
BEGIN
get_country_info(95);
END;
3. In your own words, describe what a formal parameter is and what an actual parameter is. Also,
name three variations for an actual parameter.
The formal parameter is a parameter declared in the procedure heading.
The actual parameter is the parameter or value in the calling environment.
Actual parameters can be literal values, variables or expressions.
4. Procedure Exercise:
A. Write a procedure that displays the number of countries in a given region whose highest elevations
exceed a given value. The procedure should accept two formal parameters, one for a region_id and the
other for an elevation value for comparison. Use DBMS_OUTPUT.PUT_LINE to display the results in a message. Save your procedure code.
CREATE OR REPLACE PROCEDURE get_no_countries
(p_region_id IN wf_countries.region_id%TYPE, p_highest_elevation IN wf_countries.highest_elevation%TYPE) IS
v_count NUMBER(3);
BEGIN
SELECT count(country_id) INTO v_count
FROM wf_countries
WHERE region_id = p_region_id AND highest_elevation > p_highest_elevation;
DBMS_OUTPUT.PUT_LINE('The number of countries with elevation higher than ' || p_highest_elevation || ' for region ' || p_region_id || ' is ' || v_count);
END get_no_countries;
B. Execute your procedure using the value 5 for the region_id and 2000 for the highest elevation.
BEGIN
get_no_countries(5,2000);
END;
C. DESCRIBE your procedure to check the names and datatypes of its formal parameters.
DESCRIBE get_no_countries;
D. Retrieve your procedure code from Saved SQL and modify it to accept a third formal parameter of
datatype CHAR. The procedure should display a count of the number of countries in a given region
whose highest elevations exceed a given value and whose country name starts with a given alphabetic
character. Your SELECT statement should include a WHERE condition to compare the first character
of each country’s name with the third parameter value (Hint: use SUBSTR). Save your work again and
DESCRIBE the modified procedure.
CREATE OR REPLACE PROCEDURE get_no_countries
(p_region_id IN wf_countries.region_id%TYPE, p_highest_elevation IN wf_countries.highest_elevation%TYPE,p_first_letter CHAR) IS
v_count NUMBER(3);
BEGIN
SELECT count(country_id) INTO v_count
FROM wf_countries
WHERE region_id = p_region_id AND highest_elevation > p_highest_elevation AND SUBSTR(country_name,1,1) = p_first_letter;
DBMS_OUTPUT.PUT_LINE('The number of countries with elevation higher than ' || p_highest_elevation || ' for region ' || p_region_id || ' is ' || v_count);
END get_no_countries;
E. Write an anonymous block which declares three variables to store actual parameter values for the
region_id, elevation, and area, and then executes the procedure passing these values. Execute the
block using values 5, 2000, and ‘B’.
DECLARE
v_region_id wf_countries.region_id%TYPE;
v_elevation wf_countries.highest_elevation%TYPE;
v_letter CHAR;
BEGIN
v_region_id := 5;
v_elevation := 2000;
v_letter := 'B';
get_no_countries(v_region_id, v_elevation, v_letter);
END;
F. Modify your anonymous block to use the same actual parameter values but pass them to the procedure in a different order: (5, ‘B’, 2000). Execute the block. What happens and why?
DECLARE
v_region_id wf_countries.region_id%TYPE;
v_elevation wf_countries.highest_elevation%TYPE;
v_letter CHAR;
BEGIN
v_region_id := 5;
v_elevation := 2000;
v_letter := 'B';
get_no_countries(v_region_id, v_letter, v_elevation);
END;
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
It couldn't implicitly convert B to a numeric value.
0 Comments