I am a new software developer and would like you to help me since I am new with PL/SQL programming language. How can you create lookup tables which must have a drop-down menu with categories? Thanks for your support in advance.
To support a drop-down menu for categories, I would create a table containing a unique identifier, the menu name, a sort order, a flag for whether this item should initially be the one highlighted when the menu is displayed, and the category string. For example:
CREATE TABLE dropdown_category (
,initial_focus NUMBER(1) -- 0 or 1
,CONSTRAINT pk_dropdown_category PRIMARY KEY (dropdown_category_id)
,CONSTRAINT uk_dropdown_category UNIQUE (dropdown_menu_name, category_name)
,CONSTRAINT ck_initial_focus CHECK (initial_focus = 0 OR initial_focus = 1)
You can use a sequence object and a row-level Before-Insert trigger to provide values for the id.
The sort order is intended to be within a single menu, and I wouldn't expect each menu to have more than say, thousands of entries. Likewise, the category name should be unique within a menu.
If you want, you could use triggers to enforce the business rule that each drop-down menu (by name) can have exactly zero or one row with the initial_focus flag set. To implement this, you would need:
- A row-level Before Insert or Update trigger to accumulate in a packaged collection the menu names and id's that have the flag set; remember a single DML statement can affect multiple rows.
- A PL/SQL package to provide an API for initializing, adding, resetting and testing a private-global packaged collection that captures these id's.
- A statement-level After Insert or Update trigger to test the id's captured, and raise an exception if there's more than one set to 1 for a menu name.
- A statement-level Before Insert or Update trigger to reset (delete the entries in) the packaged collection.
This complexity arises because row-level triggers can't query the table they're operating against; you'll get the dreaded "Mutating Table" exception if you attempt to do so.
Some database designers don't care for the idea that every different kind of drop-down menu requires its own table. They try to design a generic "table within a table" that can store many kinds of drop-down menus with varying numbers of fields. This sounds great in theory. However, in practice, it doesn't scale. When you try to accommodate varying numbers and types of menu columns in your table, you find yourself writing complex pivot queries to extract the different sets of columns, and performance greatly suffers.
Don't be tempted to cram more than a single entity into a physical table. A category drop-down isn't the same entity as a country code/name drop-down; which is different from a state code/name drop-down. Even though the latter two have similar structures, logically they are different entities, and deserving of their own tables.
Have a PL/SQL question for Dan Clamage? Send an e-mail to email@example.com
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading
Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically. Continue Reading