create or replace PACKAGE BODY PS_list IS /* The maximum number of items allowed in a list. */ max_item_count CONSTANT NUMBER := 100; /* Maximum length of an item in the list. */ max_item_length CONSTANT NUMBER := 100; /* Variable to server as %TYPE declaration source later in package. */ std_item VARCHAR2(100); /* The three different table types and actual tables. */ TYPE list_tabtype IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; TYPE list_name_tabtype IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER; TYPE list_length_tabtype IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER; list_table list_tabtype; name_table list_name_tabtype; length_table list_length_tabtype; /* Total number of lists defined in the package. */ number_of_lists NUMBER := 0; /*--------------- Private Modules -------------------------*/ FUNCTION name_row (list_in IN VARCHAR2) RETURN BINARY_INTEGER /* || The name_row function searches through the list names and returns || the row in which name is found. This same row in length_table || contains the number of items in the list. */ IS no_match BOOLEAN := TRUE; list_int VARCHAR2(60) := UPPER (list_in); return_value BINARY_INTEGER := 1; BEGIN /* || Loop through the names table until I find a match or have run out || of rows to check. */ WHILE no_match AND return_value <= number_of_lists LOOP no_match := name_table (return_value) != list_int OR name_table (return_value) IS NULL; IF no_match THEN return_value := return_value + 1; END IF; END LOOP; IF no_match THEN RETURN 0; ELSE RETURN return_value; END IF; END; PROCEDURE assert_existence (list_in IN VARCHAR2, row_out IN OUT BINARY_INTEGER) /* || Assert that the list exists. If it doesn't raise the appropriate || the PS_list exception. If the list does exist, return the name row. || Inside the package, I use the name row in place of the list name || as a "handle" for the list. */ IS BEGIN row_out := name_row (list_in); IF row_out = 0 THEN RAISE PS_list.list_undefined; ELSIF name_table (row_out) IS NULL THEN RAISE PS_list.list_undefined; END IF; END; PROCEDURE assert_room_in_list (name_row_in IN BINARY_INTEGER) /* || Second assertion routine: makes sure there's room for other row. || It's all based on max_item_count so if you change that value, || you have larger or smaller lists. */ IS BEGIN IF length_table (name_row_in) = max_item_count THEN RAISE PS_list.list_full; END IF; END; FUNCTION starting_row (name_row_in IN BINARY_INTEGER) RETURN BINARY_INTEGER /* || The programmer thinks in terms of the 1st or Nth item in list. || In the table, however, that item is in the 1st or Nth position || within the range of rows for that list. This function returns the || starting row in the table for a particular list (notice I don't pass || in the list name, but its row in the reference table. || || Examples of computation (with max_item_count = 100): || || Name Row Description Starting Row || 1 First list created (1-1)*100 + 1 = 1 || 5 Fifth list created (5-1)*100 + 1 = 401 */ IS BEGIN RETURN (name_row_in-1)*max_item_count + 1; END; FUNCTION adjusted_row (name_row_in IN BINARY_INTEGER, position_in IN NUMBER) RETURN BINARY_INTEGER /* || I have to do a lot of sequential reading through and assignments in || the list_table. Again, it will be necessary to translate from the || item position according to the programmer (user of the package) and || the row for that item in the table. I need to adjust the position as || perceived by my user to the row in the list_table. I use the || starting_row function to perform the initial shift and then adjust || as necessary to get the right value. || || Examples of computation (with max_item_count = 100): || || Name Row Position Adjusted Row || 1 1 (1-1)*100 + 1 + 1 - 1 = 1 || 5 12 (5-1)*100 + 1 + 12 - 1 = 412 */ IS BEGIN RETURN starting_row(name_row_in) + position_in - 1; END; PROCEDURE set_item (name_row_in IN BINARY_INTEGER, position_in IN NUMBER, item_in IN VARCHAR2) /* || Sets the value of an item in the list, using adjusted_row to perform || the shift from position_in to actual row. */ IS BEGIN list_table (adjusted_row(name_row_in, position_in)) := item_in; END; PROCEDURE set_length (name_row_in IN BINARY_INTEGER, length_in IN NUMBER) /* || Sets the number of items in the list. */ IS BEGIN length_table (name_row_in) := length_in; END; FUNCTION nitems (name_row_in IN BINARY_INTEGER) RETURN NUMBER /* || Private version of nitems based on name_row for internal use. || Need to let programmers obtain the nitems value by passing the list name. || Inside the package, however, I do not want to constantly incur the || overhead of reading through name_table. So once I have name row, || this version of nitems obtains the value directly. */ IS BEGIN RETURN length_table (name_row_in); END; /*----------------------- Public Modules ---------------------------- */ FUNCTION nitems (list_in IN VARCHAR2) RETURN NUMBER IS name_index BINARY_INTEGER; BEGIN assert_existence (list_in, name_index); RETURN length_table (name_index); END; PROCEDURE appenditem (list_in IN VARCHAR2, item_in IN VARCHAR2) /* || Add an item to the end of the list. I can now put to use all of the || private modules and thereby keep the code in my public modules very || concise and readable. */ IS -- The new number of items in the list. next_avail_position NUMBER; name_index BINARY_INTEGER; BEGIN -- Does the list exist and is there room? assert_existence (list_in, name_index); assert_room_in_list (name_index); next_avail_position := nitems (list_in) + 1; set_item (name_index, next_avail_position, item_in); set_length (name_index, next_avail_position); END; PROCEDURE destroy (list_in IN VARCHAR2) /* || Erase all signs of existence of list. With PL/SQL tables, only || thing I can do is set rows to NULL and remove the || entries from the reference tables. */ IS name_index BINARY_INTEGER := name_row (list_in); BEGIN IF name_index > 0 THEN FOR item_index IN starting_row (name_index) .. adjusted_row (name_index, nitems (name_index)) LOOP list_table (item_index) := NULL; END LOOP; name_table (name_index) := NULL; /* ================ Commented Out =============================== 11/95 DO NOT SHIFT elements in table. This throws off all the other lists. FOR list_index IN name_index + 1 .. number_of_lists LOOP name_table (list_index-1) := name_table (list_index); length_table (list_index-1) := length_table (list_index); END LOOP; number_of_lists := number_of_lists - 1; ================ Commented Out =============================== */ END IF; END; PROCEDURE deleteitem (list_in IN VARCHAR2, item_in IN VARCHAR2) /* || Remove the item from the list which matches the incoming value. || Then shift the rest of the list down so there are no gaps. || The shift-down is performed by looping through the values in the || list. Once I find the match, I copy the next-item into the previous || list table row. */ IS curr_item std_item%TYPE; shift_down BOOLEAN := FALSE; number_of_items NUMBER; name_index BINARY_INTEGER; BEGIN assert_existence (list_in, name_index); number_of_items := nitems (name_index); FOR item_index IN starting_row (name_index) .. adjusted_row (name_index, number_of_items) LOOP -- Get the current list element. curr_item := list_table (item_index); IF shift_down THEN -- If match previously found for item, shift value. list_table (item_index-1) := curr_item; ELSE -- Does the current item match the input item? shift_down := curr_item = item_in; END IF; END LOOP; -- If there was a shift, shorten the list by 1. IF shift_down THEN set_length (name_index, number_of_items - 1); END IF; END; PROCEDURE deleteitem (list_in IN VARCHAR2, position_in IN NUMBER) /* || Delete an item at the specified position. This is simpler than the || delete by value. Just start at the specified position and copy the || next item into the row containing the current item. */ IS number_of_items NUMBER; name_index BINARY_INTEGER; BEGIN assert_existence (list_in, name_index); number_of_items := nitems (name_index); -- If removing the last item, handle it separately and easily. IF position_in = number_of_items THEN list_table (number_of_items) := NULL; set_length (name_index, number_of_items - 1); ELSIF position_in BETWEEN 1 AND number_of_items THEN -- Shift each item down a notch in the list and clean up. FOR item_index IN adjusted_row (name_index, position_in) .. adjusted_row (name_index, number_of_items)-1 LOOP list_table (item_index) := list_table (item_index + 1); END LOOP; list_table (number_of_items) := NULL; set_length (name_index, number_of_items - 1); END IF; END; FUNCTION getitem (list_in IN VARCHAR2, position_in IN NUMBER) RETURN VARCHAR2 /* || Return the item specified by the input position. */ IS name_index BINARY_INTEGER; BEGIN assert_existence (list_in, name_index); IF position_in BETWEEN 1 AND nitems (name_index) THEN RETURN list_table (adjusted_row (name_index, position_in)); ELSE RETURN NULL; END IF; END; FUNCTION getposition (list_in IN VARCHAR2, item_in IN VARCHAR2) RETURN NUMBER /* || Return the position of the item which matches the input item. || Loop through the values in the list until I find a match. || I return zero if no match is found. */ IS item_index NUMBER; last_row BINARY_INTEGER; still_searching BOOLEAN := TRUE; name_index BINARY_INTEGER; BEGIN assert_existence (list_in, name_index); item_index := starting_row (name_index); last_row := adjusted_row (name_index, nitems (name_index)); WHILE still_searching AND item_index <= last_row LOOP still_searching := list_table (item_index) != item_in; item_index := item_index + 1; END LOOP; IF still_searching THEN RETURN 0; ELSE RETURN item_index - 1; END IF; END; PROCEDURE insertitem (list_in IN VARCHAR2, position_in IN NUMBER, item_in IN VARCHAR2) /* || Insert an item at specified position. I have to make room for || item at that position by shifting all the values to the next row. */ IS number_of_items NUMBER := nitems (list_in); name_index BINARY_INTEGER; BEGIN -- Make sure list exists and there is room on the list for another. assert_existence (list_in, name_index); assert_room_in_list (name_index); IF position_in BETWEEN 1 AND number_of_items THEN -- Starting at the end of the list, move the valuse forward. FOR item_index IN REVERSE adjusted_row (name_index, position_in) .. adjusted_row (name_index, number_of_items) LOOP list_table (item_index+1) := list_table (item_index); END LOOP; /* Items shifted, so insert new value and resize list. */ set_item (name_index, position_in, item_in); set_length (name_index, number_of_items+1); ELSE RAISE PS_list.out_of_bounds; END IF; END; PROCEDURE make (list_in IN VARCHAR2) /* || To create a new list I add the name and length of 0 to the reference || tables. */ IS name_index BINARY_INTEGER := name_row (list_in); BEGIN -- If the list already exists, just ignore the request. IF name_index > 0 THEN NULL; ELSE number_of_lists := number_of_lists + 1; name_table (number_of_lists) := UPPER (list_in); length_table (number_of_lists) := 0; END IF; END; PROCEDURE prependitem (list_in IN VARCHAR2, item_in IN VARCHAR2) /* || Insert an item at the beginning of the list. */ IS BEGIN insertitem (list_in, 1, item_in); END; PROCEDURE replaceitem (list_in IN VARCHAR2, position_in IN NUMBER, item_in IN VARCHAR2) /* || Change the value of an item in the list -- only if the list exists || and if the position is valid. All it takes is a call to that || convenient private module, set_item. */ IS name_index BINARY_INTEGER; BEGIN assert_existence (list_in, name_index); IF position_in BETWEEN 1 AND nitems (name_index) THEN set_item (name_index, position_in, item_in); ELSE RAISE PS_list.out_of_bounds; END IF; END; PROCEDURE display (list_in IN VARCHAR2) IS name_index BINARY_INTEGER; BEGIN assert_existence (list_in, name_index); FOR list_ind IN 1 .. nitems (name_index) LOOP DBMS_OUTPUT.PUT_LINE (getitem (list_in, list_ind)); END LOOP; END; END PS_list; /