• Author / Uploaded
• imhereforanswers2

Citation preview

www.oracle.com/academy

Database Programming with PL/SQL 6-2: Indexing Tables of Records Practice Activities Vocabulary Identify the vocabulary word for each definition below.

INDEX BY table

INDEX BY table of records Try It / Solve It

A set of occurrences of the same kind of data A collection which is based on a single field or column; for example, on the last_name column of EMPLOYEES

is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m

COLLECTION

A collection which is based on a composite record type; for example, on the whole DEPARTMENTS row

1. PL/SQL collections:

A. In your own words, describe what a PL/SQL collection is. Son muhas ocurrencias que presentan el mismo tipo de dato

B. Which of the following are collections and which are not?

sh

Th

1. A list of all employees’ last names COLLECTION 2. The character value “Chang” DATA TYPE SCALAR 3. The populations of all countries in Europe COLLECTION 4. All the data stored in the employees table about a specific employee. STRUCTURE

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

This study source was downloaded by 100000822525108 from CourseHero.com on 03-31-2021 08:06:03 GMT -05:00

https://www.coursehero.com/file/47922795/PLSQL-6-2-Practicepdf/

C. What is the difference between an INDEX BY table and a database table such as EMPLOYEES or COUNTRIES? En el INDEX TABLE los datos se guardan en una variable y la información es temporal, en cambio la otra se almacena en una Base de datos D. Describe the difference between an INDEX BY table and an INDEX BY table of records. INDEX BY solo usa tipos de datos SCALARES e INDEX BY TABLE OF RECORDS usa datos compuestos

is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m

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 BINARY_INTEGER; v_pops_tab t_pops;

INDEX BY

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.

sh

Th

DECLARE TYPE tipo_country IS TABLE OF countries.country_name%TYPE INDEX BY BINARY_INTEGER; v_tabla_index_co tipo_country; CURSOR country_cur IS SELECT country_id, country_name FROM countries WHERE region_id = 5 ORDER BY country_id; v_country country_cur%ROWTYPE; BEGIN OPEN country_cur; LOOP FETCH country_cur INTO v_country;

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

This study source was downloaded by 100000822525108 from CourseHero.com on 03-31-2021 08:06:03 GMT -05:00

https://www.coursehero.com/file/47922795/PLSQL-6-2-Practicepdf/

EXIT WHEN country_cur%NOTFOUND; v_tabla_index_co(v_country.country_id) := v_country.country_name; END LOOP; CLOSE country_cur; 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.

is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m

DECLARE TYPE tipo_country IS TABLE OF countries.country_name%TYPE INDEX BY BINARY_INTEGER; v_tabla_index_co tipo_country; CURSOR country_cur IS SELECT country_id, country_name FROM countries WHERE region_id = 5 ORDER BY country_id; v_country country_cur%ROWTYPE; BEGIN OPEN country_cur; LOOP FETCH country_cur INTO v_country; EXIT WHEN country_cur%NOTFOUND; v_tabla_index_co(v_country.country_id) := v_country.country_name; END LOOP; CLOSE country_cur; FOR i IN v_tabla_index_co.FIRST .. v_tabla_index_co.LAST LOOP IF v_tabla_index_co.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE(i || ' ' || v_tabla_index_co(i)); END IF; END LOOP; END;

sh

Th

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 tipo_country IS TABLE OF countries.country_name%TYPE INDEX BY BINARY_INTEGER;

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

This study source was downloaded by 100000822525108 from CourseHero.com on 03-31-2021 08:06:03 GMT -05:00

https://www.coursehero.com/file/47922795/PLSQL-6-2-Practicepdf/

is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m

v_tabla_index_co tipo_country; CURSOR country_cur IS SELECT country_id, country_name FROM countries WHERE region_id = 5 ORDER BY country_id; v_country country_cur%ROWTYPE; BEGIN OPEN country_cur; LOOP FETCH country_cur INTO v_country; EXIT WHEN country_cur%NOTFOUND; v_tabla_index_co(v_country.country_id) := v_country.country_name; END LOOP; CLOSE country_cur; DBMS_OUTPUT.PUT_LINE('Primer elemento: ' || ' ' || v_tabla_index_co.FIRST || ' ' || v_tabla_index_co(v_tabla_index_co.FIRST)); DBMS_OUTPUT.PUT_LINE('Ultimo elemento: ' || ' ' ||v_tabla_index_co.LAST || ' ' || v_tabla_index_co(v_tabla_index_co.LAST)); DBMS_OUTPUT.PUT_LINE('Cantidad de elementos en la tabla INDEX BY: ' || v_tabla_index_co.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 cursor-name%ROWTYPE. Save your code.

sh

Th

DECLARE CURSOR employee_cur IS SELECT employee_id, last_name, job_id, salary FROM employees ORDER BY employee_id; v_employee employee_cur%ROWTYPE; TYPE t_employee IS TABLE OF employee_cur%ROWTYPE INDEX BY BINARY_INTEGER; v_datos_employee t_employee;

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

This study source was downloaded by 100000822525108 from CourseHero.com on 03-31-2021 08:06:03 GMT -05:00

https://www.coursehero.com/file/47922795/PLSQL-6-2-Practicepdf/

BEGIN OPEN employee_cur; LOOP FETCH employee_cur INTO v_employee; EXIT WHEN employee_cur%NOTFOUND; v_datos_employee(v_employee.employee_id) := v_employee; END LOOP; CLOSE employee_cur; 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.

sh

Th

is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m

DECLARE CURSOR employee_cur IS SELECT employee_id, last_name, job_id, salary FROM employees ORDER BY employee_id; v_employee employee_cur%ROWTYPE; TYPE t_employee IS TABLE OF employee_cur%ROWTYPE INDEX BY BINARY_INTEGER; v_datos_employee t_employee; BEGIN OPEN employee_cur; LOOP FETCH employee_cur INTO v_employee; EXIT WHEN employee_cur%NOTFOUND; v_datos_employee(v_employee.employee_id) := v_employee; END LOOP; CLOSE employee_cur; FOR i IN v_datos_employee.FIRST .. v_datos_employee.LAST LOOP IF v_datos_employee.EXISTS(i) THEN DBMS_OUTPUT.PUT_LINE(v_datos_employee(i).employee_id || ' ' || v_datos_employee(i).last_name || ' ' || v_datos_employee(i).job_id || ' ' || v_datos_employee(i).salary); END IF; END LOOP; END;

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

This study source was downloaded by 100000822525108 from CourseHero.com on 03-31-2021 08:06:03 GMT -05:00

https://www.coursehero.com/file/47922795/PLSQL-6-2-Practicepdf/

is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m sh

Th

Copyright © 2016, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

This study source was downloaded by 100000822525108 from CourseHero.com on 03-31-2021 08:06:03 GMT -05:00

https://www.coursehero.com/file/47922795/PLSQL-6-2-Practicepdf/ Powered by TCPDF (www.tcpdf.org)