1 / 26

Using ORACLE to Add, View, and Update Data

Learn how to insert, view, and update data in Oracle using SQL commands. Understand transactions, locking, and sequences.

Download Presentation

Using ORACLE to Add, View, and Update Data

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. Using ORACLE to Add,View, and Update Data INFSY 445 Fall 2005

  2. INSERT • Add a row into a table • INSERT INTO followed by the names of columns that will contain data • If you only enter data for some columns, the column names must be provided in the INSERT INTO clause

  3. INSERT INSERT INTO acctmanager VALUES (‘L500’, ‘Mandy Lopex’, ’01-OCT-02’, NULL); • NULL may be used to indicate no value for a column • ‘’ may be used to indicate no value for a column

  4. INSERT • May use INSERT INTO with a subquery • VALUES clause is not included • Data is derived from the results of the subquery INSERT INTO acctmanager SELECT amid, amname, amedate, region FROM account WHERE amedate <= ’01-OCT-02’;

  5. UPDATE • Update record in one table at a time UPDATEtablename SETcolumn = new data value WHEREsearch condition; UPDATE JANE SET STATE = ‘PA’ WHERE STATE = ‘Pa’;

  6. UPDATE • Values within single quotes are case sensitive • CHAR data type stores blank space. When testing a column, you must account for those blank spaces. • fixed-length text up to 2,000 characters • default size is 1 • Unused size will be filled with spaces to the right of the data to the size of the column WHERE CATEGORY = ‘U’ vs. WHERE CATEGORY = ‘U ‘

  7. UPDATE • Date format in Oracle is DD-MON-YY • TO_DATE • Function which facilitates data entry of date • Oracle will automatically convert date to default format for internal storage • INSERT INTO JANE • VALUES (‘Jane Kochanov, ‘123 Any Street’, Harrisburg, Pa, 101, TO_DATE(’02/01/2001’, ‘MM/DD/YYYY’);

  8. UPDATE UPDATE acctmanager SET amname = UPPER (amname); • Ensure all values in column are upper case

  9. Transaction Control Statements • Data Manipulation Language (DML) • Modify data in tables • Changes are not permanently saved to the table when SQL statement executes • Transaction control statements • Save modified data • Undo changes made in error • All work is held in the Database Buffer • Rows are locked and unavailable to other users

  10. COMMIT • Inserts, updates, and deletes are not permanent changes until COMMIT issued • Commit automatic when you EXIT SQL*Plus • COMMIT is automatic when CREATE or ALTER table is issued COMMIT;

  11. ROLLBACK • Undo in database ROLLBACK; • Can be used with savepoints which mark the beginning of a series of transactions SAVEPOINT jane_save; INSERT….. ROLLBACK TO jane_save;

  12. ROLLBACK • Oracle crashes, a ROLLBACK will automatically occur after Oracle restarts • Any operation not committed will be undone • Even when SAVEPOINT is used, a COMMIT command still needed to update any changes

  13. DELETE • Delete rows from a table • May not specify any column names • If you omit the WHERE clause in DELETE statement, all rows will be deleted from the table

  14. DELETE DELETE FROMtablename WHEREsearch condition; DELETE FROM JANE WHERE ACCT_NUM = 101;

  15. Table Locks • Prevents two users from changing same record • Oracle locks row(s) being changed • Other users can view data in table

  16. Sequences

  17. Sequences • Sequence generates sequential integers • Used to generate unique primary keys • Used for internal control such as purchase orders, invoices, etc.

  18. Sequences CREATE SEQUENCE sequencename INCREMENT BY value START WITH value MAXVALUE value | NOMAXVALUE MINVALUE value | NOMINVALUE CYCLE | NOCYCLE ORDER | NOORDER CACHE value | NOCACHE ;

  19. Sequences • Name convention _seq • INCREMENT BY • Default is 1 • May be any number positive or negative • START WITH • Default is 1

  20. Sequences • MINVALUE and MAXVALUE • CYCLE and NOCYCLE • Reissue values from sequence once min or max value has been reached • NOCYCLE is default

  21. Sequences • ORDER and NOODER • Used in application cluster environments where multiple users may request sequence values at same time during large transactions • ORDER = return sequence values in same order in which request were received • NOORDER is default

  22. Sequences • CACHE and NOCACHE • Use CACHE option for organization transactions require large number of sequential numbers during a session • Oracle pre-generate a set of values stored in memory • Default is CACHE 20 • If pre-generated values are not used, they are lost until the next CYCLE; gaps in sequence

  23. Sequences CREATE SEQUENCE order_ordernumber_seq INCREMENT BY 1 START WITH 1021 NOCACHE NOCYCLE;

  24. Sequences • Verify settings on a sequence SELECT * FROM user_sequences;

  25. Sequences INSERT INTO ORDERS VALUES (orders_ordernumber.nextval, 1010, ‘06-APR-03’, ….. • NEXTVAL used to generate next sequence value

  26. Sequences • Sequences may be changes using ALTER SEQUENCE command • Sequence may be deleted using DROP SEQUENCE command

More Related