Database Tutorial

Course Tutorial Site

Site Admin

CIT325: Lab 5 Instructions

without comments

Lab #5: Oracle Assignment

Objectives

The lab is designed to teach you how to work with cursor loops, SQL collections, and an UPDATE statement in a range for loop. The anonymous block PL/SQL program should achieve these objectives:

  • Learn how to instantiate an empty collection.
  • Learn how to extend memory space in a collection one element at a time.
  • Learn how to assign values to a composite data type inside a collection.
  • Learn how to use the values of a composite data type to set values and filter values in a WHERE clause.

Business Scenario

Application development involves writing and testing software. After you master basic programming structures, iteration, and collection, you begin writing programs that work with data from relational databases.

The lab is designed to teach you how to write a small PL/SQL program that teaches you how to work with cursors that read data from the database. You will write a small parameterized cursor program in the lab.

Help Section

The following is an set of sample programs and steps that address concepts in the lab. The instructor should review these in classroom or in a Google Hangout with students. Click the Instructional Material link to see the preparation lesson material.

Instructional Material →

The following code shows you how to loop across a range of values.

  • How to create and assign values to a collection, and how to use the collection to insert rows into a table of the Video store model. 

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

    -- Drop table collection.
DROP TYPE common_lookup_tab;
 
-- Drop object type.
DROP TYPE common_lookup_obj;
 
-- Create object type.
CREATE OR REPLACE 
  TYPE common_lookup_obj IS OBJECT
  ( common_lookup_table    VARCHAR2(30)
  , common_lookup_column   VARCHAR2(30)
  , common_lookup_type     VARCHAR2(30)
  , common_lookup_code     VARCHAR2(8)
  , common_lookup_meaning  VARCHAR2(255));
/
 
-- Create collection of object type.
CREATE OR REPLACE
  TYPE common_lookup_tab IS TABLE OF common_lookup_obj;
/
 
-- Declare anonymous block to write to and use a collection.
DECLARE
  /* Declare input values. */
  lv_common_lookup_table    VARCHAR2(30) := 'ITEM';
  lv_common_lookup_column   VARCHAR2(30) := 'ITEM_TYPE';
  lv_common_lookup_type     VARCHAR2(30) := 'BLU-RAY';
  lv_common_lookup_code     VARCHAR2(8);
  lv_common_lookup_meaning  VARCHAR2(255) := 'Blu-ray';
 
  /* Declare empty collection. */
  lv_common_lookup_tab  COMMON_LOOKUP_TAB := common_lookup_tab();
BEGIN
  /* Implement assignment of variables inside a loop, which mimics
     how you would handle them if they were read from a cursor loop. */
  FOR i IN 1..1 LOOP
    lv_common_lookup_tab.EXTEND;
    lv_common_lookup_tab(lv_common_lookup_tab.COUNT) := 
       common_lookup_obj( lv_common_lookup_table
                        , lv_common_lookup_column
                        , lv_common_lookup_type
                        , lv_common_lookup_code
                        , lv_common_lookup_meaning );
  END LOOP;
 
  /* Insert the values from the collection into a table. */
  FOR i IN 1..lv_common_lookup_tab.COUNT LOOP
    INSERT INTO common_lookup
    VALUES
    ( common_lookup_s1.NEXTVAL
    , lv_common_lookup_table
    , lv_common_lookup_column
    , lv_common_lookup_type
    , lv_common_lookup_code
    , lv_common_lookup_meaning
    , 1
    , SYSDATE
    , 1
    , SYSDATE );
  END LOOP;
  /* Make insert permanent. */
  COMMIT;
END;
/

    You can use the following query to verify the record insert:

    COL common_lookup_table   FORMAT A12
COL common_lookup_column  FORMAT A12
COL common_lookup_type    FORMAT A12
SELECT  common_lookup_table
,       common_lookup_column
,       common_lookup_type
FROM    common_lookup
WHERE   common_lookup_type = 'BLU-RAY';

    It prints:

    Lookup	     Lookup	  Lookup
TABLE	     COLUMN	  TYPE
------------ ------------ ------------
ITEM	     ITEM_TYPE	  BLU-RAY

Students may want or need supplemental articles that let them review tips and techniques. The following is a function that checks for valid dates inside strings. You should run this type of check after you rule out a number and alphanumeric string.

The lab has one part.

Lab Description

[25 points] Click the Lab Instructions link to open the instructions inside the current webpage.

Lab Instructions →

You need to write an anonymous block PL/SQL program that uses one collection. The collection is a table of the following structure, and this same structure is also the structure of a new table – the RATING_AGENCY table:

Object Type: STRUCT
Member Name Member Type
rating_agency_id NUMBER
rating VARCHAR2(8)
rating_agency VARCHAR2(4)

An Entity Relationship Model (ERD) would look like the following for the ITEM and RATING_AGENCY tables. However, you do not need to create a full RATING_AGENCY table for the lab. All you need in your RATING_AGENCY table are the three columns described in the STRUCT referenced above.

The lab requires you to implement:

  • A new RATING_AGENCY table and RATING_AGENCY_S sequence that starts with a value of 1001.
  • Add a new RATING_AGENCY_ID column to the ITEM table.
  • A SQL structure or composite object type, as qualified above.
  • A SQL collection, as a table of the composite object type.
  • An anonymous block PL/SQL program that:
    • Declare a local cursor to read the contents of the RATING_AGENCY table.
    • A cursor for loop that reads the cursor contents and assigns them to a local variable of the SQL collection data type.
    • A range for loop that reads the collection contents and updates the RATING_AGENCY_ID column in the item table by checking the ITEM_RATING and ITEM_RATING_AGENCY column values with the members of the collection’s composite object type.

Test Case

Click the Test Case Instructions link to open the test case instructions inside the current webpage.

Test Case Instructions →

The apply_plsql_lab5.sql script should test for several things. For example, your test case should check for:

  • Like Lab 2.1, you need to re-run the setup code with each test. That means you should include the following at the beginning of your apply_plsql_lab5.sql script: 

    @/home/student/Data/cit325/lib/cleanup_oracle.sql
@/home/student/Data/cit325/lib/Oracle12cPLSQLCode/Introduction/create_video_store.sql
  • You can create the new RATING_AGENCY table by using the data from the ITEM table, like this: 

    CREATE TABLE rating_agency AS
  SELECT rating_agency_s.NEXTVAL AS rating_agency_id
  ,      il.item_rating AS rating
  ,      il.item_rating_agency AS rating_agency
  FROM  (SELECT DISTINCT
                i.item_rating
         ,      i.item_rating_agency
         FROM   item i) il;

    After creating the table, you should be able to check it’s contents with the following query:

    SELECT * FROM rating_agency;

    The query prints:

    Rating             Rating
Agency ID Rating   Agency   
--------- -------- ------
     1001 PG       MPAA
     1002 T        ESRB
     1003 R        MPAA
     1004 NR       MPAA
     1005 PG-13    MPAA
     1006 E10+     ESRB
     1007 G        MPAA

  • You add the RATING_AGENCY_ID column to the ITEM table. After adding the RATING_AGENCY_ID column to the ITEM table, you should be able to modified ITEM table with this query: 

    SET NULL ''
COLUMN table_name   FORMAT A18
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'ITEM'
ORDER BY 2;

    It prints:

    TABLE_NAME         COLUMN_ID COLUMN_NAME            NULLABLE DATA_TYPE
------------------ --------- ---------------------- -------- ------------
ITEM                       1 ITEM_ID                NOT NULL NUMBER(22)
ITEM                       2 ITEM_BARCODE           NOT NULL VARCHAR2(20)
ITEM                       3 ITEM_TYPE              NOT NULL NUMBER(22)
ITEM                       4 ITEM_TITLE             NOT NULL VARCHAR2(60)
ITEM                       5 ITEM_SUBTITLE                   VARCHAR2(60)
ITEM                       6 ITEM_DESC              NOT NULL CLOB
ITEM                       7 ITEM_PHOTO                      BFILE
ITEM                       8 ITEM_RATING            NOT NULL VARCHAR2(8)
ITEM                       9 ITEM_RATING_AGENCY     NOT NULL VARCHAR2(4)
ITEM                      10 ITEM_RELEASE_DATE      NOT NULL DATE
ITEM                      11 CREATED_BY             NOT NULL NUMBER(22)
ITEM                      12 CREATION_DATE          NOT NULL DATE
ITEM                      13 LAST_UPDATED_BY        NOT NULL NUMBER(22)
ITEM                      14 LAST_UPDATE_DATE       NOT NULL DATE
ITEM                      15 RATING_AGENCY_ID                NUMBER(22)

  • Implement your anonymous PL/SQL block to read the RATING_AGENCY reference cursor into a collection, add row-by-row the values of the reference cursor into a local variable of the collection table, and then read the collection in a range for loop and update the RATING_AGENCY_ID column for rows where the ITEM_RATING and IETM_RATING_AGENCY values match the respective values in the collection.

    You should be able to run the following query to verify the results of your anonymous PL/SQL block:

    SELECT   rating_agency_id
,        COUNT(*)
FROM     item
WHERE    rating_agency_id IS NOT NULL
GROUP BY rating_agency_id
ORDER BY 1;

    It prints:

    RATING_AGENCY_ID   COUNT(*)
---------------- ----------
	    1001	 26
	    1002	  7
	    1003	  7
	    1004	  3
	    1005	 45
	    1006	  3
	    1007	  2

Written by michaelmclaughlin

December 15th, 2016 at 11:31 am

Posted in