itbis373 database development lecture 3a - chapter 3: using sql queries to insert, update, delete,...
TRANSCRIPT
ITBIS373 Database Development
Lecture 3a - Chapter 3: Using SQL Queries to Insert, Update, Delete, and View Data
Guide to Oracle 10g 2
Lesson A Objectives
After completing this lesson, you should be able to: Run a script to create database tables automatically Insert data into database tables Create database transactions and commit data to the
database Create search conditions in SQL queries Update and delete database records and truncate
tables
Guide to Oracle 10g 3
Lesson A Objectives (continued)
Create and use sequences to generate surrogate key values automatically
Grant and revoke database object privileges
Guide to Oracle 10g 4
Using Scripts to Create Database Tables
Script Text file that contains one or more SQL
commands Run a script
Type start at SQL prompt Blank space Full path and filename of script file
Guide to Oracle 10g 5
Running a script:SQL> START path_to_script_file; Path cannot contain any blank spaces
SQL Scripts
Guide to Oracle 10g 6
Using SQL*PLUS to Insert Data After successfully running the script to create the tables, you
already to begin adding data to them. In business setting, programs called forms are used to automate the data entry process. Program developers who create forms often use the SQL INSERT statement in the form program code to insert data into tables.
Guide to Oracle 10g 7
Using the INSERT Command
Basic syntax for inserting into every column:INSERT into tablename
VALUES (column1_value, column2_value, … );
Basic syntax for inserting into selected columnsINSERT into tablename (columnname1, columnname2, … );
VALUES (column1_value, column2_value, … );
Guide to Oracle 10g 8
Using the INSERT Command (continued)
Ensure all foreign keys that new row references have already been added to database
Guide to Oracle 10g 9
Guide to Oracle 10g 10
Guide to Oracle 10g 11
Inserting Selected Table Fields
Guide to Oracle 10g 12
Format Models
Also called format mask Used to specify different output format from
default For NUMBER data types
9 represents digit For DATE/TIMESTAMP data types
Choose formats for year day, date, etc.
Guide to Oracle 10g 13
Inserting Date and Interval Values
Inserting values into DATE columns Use TO_DATE function to convert string to DATE Syntax
TO_DATE('date_string', 'date_format_model')
Inserting values into INTERVAL columns Syntax
TO_YMINTERVAL('years-months') TO_DSINTERVAL('days HH:MI:SS.99')
Guide to Oracle 10g 14
Inserting Date Values
Guide to Oracle 10g 15
Guide to Oracle 10g 16
Guide to Oracle 10g 17
Guide to Oracle 10g 18
Creating Transactions and Committing New Data
When you create a new table or update the structure of an existing table, the DBMS changes the rows immediately and makes the change visible to other users. This is not the case when you insert, update, or delete data rows. The commands for operations that add, update, or delete data are called action queries.
All three action queries need to succeed, or non of them should succeed.
After the user enters all of the action queries in a transaction, he or she can either COMMIT (save) all of the changes or ROLL BACK (discard) all of the changes
Guide to Oracle 10g 19
The purpose of transaction processing is enable every user to see a consistent view of the database. To achieve this consistency, a user cannot view or update data values that are part of another user’s uncommitted transaction because these uncommitted transactions, which are called pending transactions, might be rolled back.
The Oracle DBMS implements transaction processing by locking data rows associated with pending transactions. When the DBMS locks a row, other users cannot view or modify the row. When the user commits the transaction, the DBMS releases the lock on the rows, and other users can view and update the rows again.
Guide to Oracle 10g 20
A transaction starts when you type one or more DML commands in SQL*Plus
A transaction ends when you issue either the COMMIT or ROLLBACK command SQL>COMMIT;
SQL>ROLLBACK;
Transactions
Guide to Oracle 10g 21
Committing and Rolling Back Data
COMMIT Makes transaction command changes permanent
in the database and visible to other users ROLLBACK
Rolls back transaction command changes and restores database to its state before the transaction
Guide to Oracle 10g 22
Guide to Oracle 10g 23
Used to mark individual sections of a transaction
You can roll back a transaction to a savepoint
Savepoint
Guide to Oracle 10g 24
Syntax:UPDATE tablename
SET column1 = new_value,
column2 = new_value, …
WHERE search_condition; Records can be updated in only one
table at a time Can update multiple records if they all
match the search condition
Updating Records
Guide to Oracle 10g 25
Guide to Oracle 10g 26
The general syntax of a SQL search condition is:
WHERE columnname comparison_operator search_expression
Search Conditions
Guide to Oracle 10g 27
Guide to Oracle 10g 28
Defining Search Expressions
NUMBER example WHERE f_id = 1
Character data example WHERE s_class = 'SR'
DATE example WHERE s_dob = TO_DATE('01/01/1980', ‘MM/DD/YYYY')
Guide to Oracle 10g 29
Guide to Oracle 10g 30
Creating Complex Search Conditions
A complex search condition combines multiple search conditions using the AND,OR, and NOT logical operators.
EX The following complex search condition matches all rows in which
BLDG_CODE is ‘CR’ and the capacity is greater than 50: WHERE bldg_code =‘CR’ AND capacity >50 The following search condition matches all course section rows that
meet either on Tuesday and Thursday or on Monday, Wednesday, and Friday (at Northwoods University):
WHERE day=‘MW’ OR day =‘UTH’
Guide to Oracle 10g 31
Deleting Table Rows You use the SQL DELETE action to remove specific
rows from a database table, and you truncate the table to remove all of the table rows.
The SQL DELETE Action QueryThe general syntax for DELETE action query is: DELETE FROM tablename WHERE search condition;EX In the following set of steps, you delete Tammy Jones from the STUDENT
table. You specify ‘Tammy’ and ‘Jones’ in the action query’s search condition.
Guide to Oracle 10g 32
Guide to Oracle 10g 33
Deletes multiple records if search condition specifies multiple records
If search condition is omitted, all table records are deleted
You can’t delete a record if it contains a primary key value that is referenced as a foreign key
Deleting Records
Guide to Oracle 10g 34
Truncating Tables
When you need to delete all of the rows in a table quickly, you can truncate the table, which means you remove all of the table data without saving any rollback information.
TRUNCATE TABLE tablename;
You cannot truncate a table that has foreign key constraints enabled.
Guide to Oracle 10g 35
Ex:Ex: You truncate the LOCATION table to delete all of its rows. Recall that the LOC_ID column in the LOCATION table is a foreign key in both the FACULTY table and the COURSE_SECTION table, so you must first disable the LOC_ID foreign key constraints in the FACULTY and COOURSE_SECTION tables. Then you truncate the LOCATION table.
Guide to Oracle 10g 36
Guide to Oracle 10g 37
Sequential list of numbers that is automatically generated by the database
Used to generate values for primary key identifier
Has no real relationship to row to which it is assigned other than to identify it uniquely
Surrogate key values automatically generated using a sequence.
Sequences
Guide to Oracle 10g 38
Guide to Oracle 10g 39
Creating New Sequences
CREATE SEQUENCE command DDL command No need to issue COMMIT command
Guide to Oracle 10g 40
General Syntax Used to Create a New Sequence
Guide to Oracle 10g 41
Guide to Oracle 10g 42
Viewing Sequence Information
Query the SEQUENCE Data Dictionary View:
Guide to Oracle 10g 43
Using Sequences
CURRVAL Returns most recent sequence value retrieved
during the current user session. NEXTVAL
Next available sequence value sequence_name.NEXTVAL
Guide to Oracle 10g 44
Guide to Oracle 10g 45
Using Sequences (continued)
DUAL Simple table in system user schema More efficient to retrieve pseudocolumns from
DUALSELECT sequence_name.NEXTVAL
FROM DUAL;
DBMS uses user sessions To ensure that all sequence users receive unique
sequence numbers
Guide to Oracle 10g 46
Pseudocolumns
Acts like a column in a database query Actually a command that returns a specific
values Used to retrieve:
Current system date Name of the current database user Next value in a sequence
Guide to Oracle 10g 47
Pseudocolumn Examples
Pseudocolumn
Name
Output
CURRVAL Most recently retrieved sequence value
NEXTVAL Next value in a sequence
SYSDATE Current system date from database server
USER Username of current user
Guide to Oracle 10g 48
- Retrieving the current system date:SELECT SYSDATE
FROM DUAL;
- Retrieving the name of the current user:SELECT USER
FROM DUAL;
- DUAL is a system table that is used with pseudocolumns
Using Pseudocolumns
Guide to Oracle 10g 49
EX To create the ITEMID_SEQUENCE SQL> CREATE SEQUENCE itemid_sequence START WITH 996 NOMAXVALUE NOCACHE; sequence created To access the next value in a sequence and use that value when
you insert a new data record, use the following general command: INSERT INTO <table name> VALUES(<owner user name>_<sequence name>.NEXTVAL,<field2
data value>,<field3 data value>,… This command assumes that the primary key associated with
the sequence is the first table field.
Guide to Oracle 10g 50
SQL> INSERT INTO item VALUES(itemid_sequence.NEXTVAL, ’Heavey duty day pack’, ‘outdoor gear’); SQL> Select itemid, itemdesc From item; ITEMID ITEMDESC 894 ------- 897 ------- 559 ------- 786 ------ 996 Heavy duty day pack
Guide to Oracle 10g 51
SQL > Insert into item values(item_sequence.nextval,’mountain parka’,’clothing’) ITEMID ITEMDESC 894 ------- 897 ------- 559 ------- 786 ------ 996 Heavy duty day pack 997 Mountain parka
SQL> select itemid_sequence.nextval from dual; Nextval 998
Guide to Oracle 10g 52
Deleting Sequences
To delete a sequence from the database, you use the DROP SEQUENCE DDL command.
Ex: to drop LOC_ID_SEQUENCE, you use the command
DROP SEQUENCE loc_id_sequence;
Guide to Oracle 10g 53
Permissions that you can grant to other users to allow them to access or modify your database objects
Granting object privileges:GRANT privilege1, privilege2, …ON object_nameTO user1, user 2, …;
Revoking object privileges:REVOKE privilege1, privilege2, …ON object_nameFROM user1, user 2, …;
Object Privileges
Guide to Oracle 10g 54
Examples of Object Privileges
Privilege Description
ALTER Allows user to change object’s structure using the ALTER command
DROP Allows user to drop object
SELECT Allows user to view object
INSERT, UPDATE, DELETE
Allows user to insert, update, delete table data
ALL Allows user to perform any operation on object
Guide to Oracle 10g 55
Guide to Oracle 10g 56
Guide to Oracle 10g 57