Using ORACLE to Add, View, and Update Data

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