Database Programming with PL/SQL
6-2: Indexing Tables of Records
Practice Activities
Vocabulary
A set of occurrences of the same kind of data - Collection
A collection which is based on a single field or column; for example, on the last_name column of EMPLOYEES - INDEX BY table
A collection which is based on a composite record type; for example, on the whole DEPARTMENTS row - INDEX BY table of records
Try It / Solve It
1. PL/SQL collections:
A. In your own words, describe what a PL/SQL collection is.
A collection is a set of similar datas saved as variables.
B. Which of the following are collections and which are not?
1. A list of all employees’ last names
2. The character value “Chang”
3. The populations of all countries in Europe
4. All the data stored in the employees table about a specific employee.
1 and 3 are collections, 2 and 4 are not.
C. What is the difference between an INDEX BY table and a database table such as EMPLOYEES or
COUNTRIES?
An INDEX BY table have mandatory a primary key that is usually a Binary_integer
D. Describe the difference between an INDEX BY table and an INDEX BY table of records.
Index by have only one column and Index by table have a row of columns .
E. Look at the following code. Describe the difference between t_pops and v_pops_tab. Is v_pops_tab an
INDEX BY table or an INDEX BY table of records? How do you know?
DECLARE
TYPE t_pops IS TABLE OF countries.population%TYPE
INDEX BY BINARY_INTEGER;
v_pops_tab t_pops;
t_pops is Indexy by table
v_pops_tab is Index by table of records
2. INDEX BY tables of countries in South America:
A. Write and execute an anonymous block that declares and populates an INDEX BY table of countries in
South America (region_id = 5). The table should use country_id as a primary key, and should store the
country names as the element values. The data should be stored in the table in ascending sequence of
country_id. The block should not display any output. Save your code.
DECLARE
TYPE t_country_names IS
TABLE OF wf_countries.country_name%TYPE
INDEX BY BINARY_INTEGER;
v_country_names t_country_names;
CURSOR country_curs IS
SELECT country_id, country_name
FROM wf_countries
WHERE region_id = 5
ORDER BY country_id;
v_country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs; LOOP
FETCH country_curs INTO v_country_rec;
EXIT WHEN country_curs%NOTFOUND;
v_country_names(v_country_rec.country_id) := v_country_rec.country_name;
END LOOP;
CLOSE country_curs;
END;
B. Modify the block so that after populating the INDEX BY table, it uses a FOR loop to display the contents
of the INDEX BY table. You will need to use the FIRST, LAST, and EXISTS table methods. Execute the
block and check the displayed results. Save your code.
DECLARE
TYPE t_country_names IS
TABLE OF wf_countries.country_name%TYPE
INDEX BY BINARY_INTEGER;
v_country_names t_country_names;
CURSOR country_curs IS
SELECT country_id, country_name
FROM wf_countries
WHERE region_id = 5
ORDER BY country_id;
v_country_rec country_curs%ROWTYPE;
BEGIN OPEN country_curs;
LOOP FETCH country_curs INTO v_country_rec;
EXIT WHEN country_curs%NOTFOUND;
v_country_names(v_country_rec.country_id) := v_country_rec.country_name;
END LOOP;
CLOSE country_curs;
FOR i IN v_country_names.FIRST .. v_country_names.LAST LOOP
IF v_country_names.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(i || ' ' || v_country_names(i));
END IF;
END LOOP;
END;
C. Modify the block again so that instead of displaying all the contents of the table, it displays only the first
and last elements and the number of elements in the INDEX BY table. Execute the block and check the
displayed results.
DECLARE
TYPE t_country_names IS
TABLE OF wf_countries.country_name%TYPE
INDEX BY BINARY_INTEGER;
v_country_names t_country_names;
CURSOR country_curs IS
SELECT country_id, country_name
FROM wf_countries
WHERE region_id = 5
ORDER BY country_id;
v_country_rec country_curs%ROWTYPE;
BEGIN
OPEN country_curs; LOOP
FETCH country_curs INTO v_country_rec;
EXIT WHEN country_curs%NOTFOUND;
v_country_names(v_country_rec.country_id) := v_country_rec.country_name;
END LOOP;
CLOSE country_curs;
DBMS_OUTPUT.PUT_LINE(v_country_names.FIRST || ' ' || v_country_names(v_country_names.FIRST));
DBMS_OUTPUT.PUT_LINE(v_country_names.LAST || ' ' || v_country_names(v_country_names.LAST));
DBMS_OUTPUT.PUT_LINE('Number of countries is: ' || v_country_names.COUNT);
END;
3. INDEX BY tables of records:
A. Write and execute an anonymous block that declares and populates an INDEX BY table of records
containing employee data. The table of records should use the employee id as a primary key, and each
element should contain an employee’s last name, job id, and salary. The data should be stored in the
INDEX BY table of records in ascending sequence of employee id. The block should not display any
output.
Hint: declare a cursor to fetch the employee data, then declare the INDEX BY table as cursorname%ROWTYPE. Save your code.
DECLARE
CURSOR emp_curs IS
SELECT employee_id, last_name, job_id, salary
FROM employees
ORDER BY employee_id;
v_emp_rec emp_curs%ROWTYPE;
TYPE t_emp_data IS
TABLE OF emp_curs%ROWTYPE
INDEX BY BINARY_INTEGER;
v_emp_data t_emp_data;
BEGIN
OPEN emp_curs; LOOP
FETCH emp_curs INTO v_emp_rec;
EXIT WHEN emp_curs%NOTFOUND;
v_emp_data(v_emp_rec.employee_id) := v_emp_rec;
END LOOP;
CLOSE emp_curs;
END;
B. Modify the block so that after populating the table of records, it uses a FOR loop to display to display
the contents. You will need to use the FIRST, LAST and EXISTS table methods. Execute the block and
check the displayed results. Save your code.
DECLARE
CURSOR emp_curs IS
SELECT employee_id, last_name, job_id, salary
FROM employees
ORDER BY employee_id;
v_emp_rec emp_curs%ROWTYPE;
TYPE t_emp_data IS
TABLE OF emp_curs%ROWTYPE
INDEX BY BINARY_INTEGER;
v_emp_data t_emp_data;
BEGIN
OPEN emp_curs; LOOP
FETCH emp_curs INTO v_emp_rec;
EXIT WHEN emp_curs%NOTFOUND;
v_emp_data(v_emp_rec.employee_id) := v_emp_rec;
END LOOP;
CLOSE emp_curs;
FOR i IN v_emp_data.FIRST .. v_emp_data.LAST LOOP
IF v_emp_data.EXISTS(i) THEN
DBMS_OUTPUT.PUT_LINE(v_emp_data(i).employee_id || ' ' || v_emp_data(i).last_name || ' ' || v_emp_data(i).job_id || ' ' || v_emp_data(i).salary);
END IF;
END LOOP;
END;
0 Comments