Scribd Logo
Upload

Welcome to Scribd!

  • 240 views

    Database Programming With PL/SQL 6-2: Practice Activities: Indexing Tables of Records

    Uploaded by

    Абзал Калдыбеков

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Database Programming With PL/SQL 6-2: Practice Activities: Indexing Tables of Records

    Uploaded by

    Абзал Калдыбеков
    240 views2 pages

    Original Title

    PLSQL_6_2_Practice.pdf

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    240 views2 pages

    Database Programming With PL/SQL 6-2: Practice Activities: Indexing Tables of Records

    Uploaded by

    Абзал Калдыбеков

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 2

    Database Programming with PL/SQL

    6-2: Indexing Tables of Records


    Practice Activities
    Vocabulary
    Identify the vocabulary word for each definition below.

    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

    A collection which is based on a composite record type; for example,


    on the whole DEPARTMENTS row

    Try It / Solve It
    1. PL/SQL collections:
    A. In your own words, describe what a PL/SQL collection is.

    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.

    C. What is the difference between an INDEX BY table and a database table such as EMPLOYEES or
    COUNTRIES?

    D. Describe the difference between an INDEX BY table and an INDEX BY table of records.

    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;
    Copyright © 2020, 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.
    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.

    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.

    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.

    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.

    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.

    Copyright © 2020, 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.

    You might also like