1 / 7

Creating and Managing Sequences and Custom Forms in SQL

This guide provides an overview of sequences in SQL, detailing their creation, usage for generating unique values, and practical implementation in custom forms. It explains the syntax for defining sequences, managing values with NEXTVAL and CURRVAL, and the step-by-step process to create a user-friendly interface by adding various interface items. Readers will learn how to manage database interactions using buttons to clear forms, save records, and manage item IDs, ensuring a smooth user experience and effective database operations.

lois-whitaker
Télécharger la présentation

Creating and Managing Sequences and Custom Forms in SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Sequences • Sequences: a database object that generates unique numbers from an arithmetic • series. Used to populated surrogate keys. • Syntax: CREATE SEQUENCE <sequence_name> • [INCREMENT BY <interval>] [START WITH <value>], • [MAXVALUE <maximum value>] [NOMAXVALUE] • [CACHE <# of values to cache>] [NOCACHE]; SQL> create sequence vendor_seq start with 1 increment by 1; Sequence created. • Using sequences • generate new sequence values: sequence_name.nextval • access the current sequence value: sequence_name.currval • undefined until the first sequence value is generated SQL> select vendor_seq.nextval from dual; NEXTVAL --------- 1 SQL> select vendor_seq.currval from dual; CURRVAL --------- 1

  2. Creating custom forms • Adding more interface items and making it more user-friendly • Step0: Connect with the database server • Step1: Create a non-database block manually (not using the wizard) • Step2: Create a canvas from object navigator • Step3: Go to the layout editor • Step4: Add interface items (buttons, text items, radio buttons, etc.) • Step5: Change the properties of the interface items from property pallette • Creating radio buttons: • Step1: Go to the layout editor • Step2: Select the radio button icon from the left hand margin and • place it on the canvas • Step3: From the property palette, change the name, label, and value • Step4: From the object navigator, select the radio group • Step5: Change the name, and set a initial value for the radio group • Creating control buttons (refer to page 190 in book) • Create buttons • Change labels • Change hints (see help property group in property palette • Create WHEN-BUTTON-PRESSED triggers

  3. Layout editor Radio button Text item Display item

  4. Trigger codes • CREATE_BUTTON (refer to page 193): --clear the form CLEAR_FORM; --assign form state to insert :GLOBAL.mode := 'INSERT'; select itemid_seq.nextval into :item_control_block.itemid_text from dual; • Explanation: • Global variables are used for form characteristics • Purpose: clear any information in the form • use the sequence itemid_seq (created previously) to generate a new • item id • :GLOBAL.mode: the form can be in various modes • enter_query, execute_query, insert, update • :item_control_block.itemid_text: an item is referred to as • :block-name.item-name • dual: a dummy table used to complete SQL syntax

  5. Trigger code • SAVE_BUTTON (refer to page 198) if :global.mode = 'INSERT' then insert into item values (:item_control_block.itemid_text, :item_control_block.itemdesc_text, :item_control_block.category_radio_group); :global.mode := 'UPDATE'; elsif :global.mode = 'UPDATE' then update item set itemdesc = :item_control_block.itemdesc_text, category = :item_control_block.category_radio_group where itemid = :item_control_block.itemid_text; end if; commit; --clear_form; • The last command (clear_form) is commented so that you can see the • record after it is saved • DELETE_BUTTON (see page 209) --delete the current record delete from ITEM where itemid = :item_control_block.itemid_text; --commit the changes commit; --clear the form clear_form;

  6. Format mask (refer to page 236) • Purpose: to display data in certain formats • item property set from property palette Testing the form • Ids (itemID, LOCID, etc.) should be display items. • Right now, the form is not ready for querying • You can only create a new record, and update a record that you just created • Once you create a record and save it, go to SQL*Plus editor and check if • the record has been posted in the database • If you get the error message: • FRM:40735: WHEN-BUTTON-PRESSED trigger raised unhandled exception • ORA-01407 • it means that you are trying to enter a null value to a non-null field • Ignore the message: FRM: 40401: No changes to save, but check if the changes • have been made from the SQL*Plus editor.

More Related