Transcript CH3_A

Enhanced Guide to Oracle 10g
Chapter 3:
Using SQL Queries to Insert,
Update, Delete, and View Data
Manipulating Data
Objectives

After completing this lesson, you should be
able to do the following:
Describe each DML statement
 Insert rows into a table
 Update rows in a table
 Delete rows from a table
 Control transactions

SQL Scripts
 Script: text file that contains a sequence of SQL
commands
 Usually have .sql extension
 To run from SQL*Plus:

Start full file path
SQL> START path_to_script_file;
@ full file path (SQL> @ path_to_script_file;)
 Extension can be omitted if it is .sql
 Path cannot contain any blank spaces

Data Manipulation Language
A
DML statement is executed when you:
 Add
new rows to a table
 Modify existing rows in a table
 Remove existing rows from a table
A
transaction consists of a collection of
DML statements that form a logical unit
of work.
Transactions

Transaction: series of action queries that represent a logical unit
of work
 consisting of one or more SQL DML commands
 INSERT, UPDATE, DELETE
 All transaction commands must succeed or none can succeed



User can commit (save) changes
User can roll back (discard) changes
Pending transaction: a transaction waiting to be committed or
rolled back


Oracle DBMS locks records associated with pending transactions
Other users cannot view or modify locked records
Adding a New Row to a Table
50 DEVELOPMENT DETROIT
New row
DEPT
DEPTNO
-----10
20
30
40
DNAME
---------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
-------NEW YORK
DALLAS
CHICAGO
BOSTON
“…insert a new row
into DEPT table…”
DEPT
DEPTNO
-----10
20
30
40
DNAME
---------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
-------NEW YORK
DALLAS
CHICAGO
BOSTON
50 DEVELOPMENT DETROIT
The INSERT Statement

Add new rows to a table by using the INSERT
statement.
INSERT INTO
VALUES

table [(column [, column...])]
(value [, value...]);
Only one row is inserted at a time with this
syntax.
Inserting New Rows
Insert a new row containing values for each
column.
 List values in the default order of the columns
in the table.
 Optionally list the columns in the INSERT
clause.

SQL> INSERT INTO
2 VALUES
1 row created.

dept (deptno, dname, loc)
(50, 'DEVELOPMENT', 'DETROIT');
Enclose character and date values within single
quotation marks.
Inserting Rows with Null Values

Implicit method: Omit the column from the
column list.
SQL> INSERT INTO
2 VALUES
1 row created.
dept (deptno, dname )
(60, 'MIS');
• Explicit method: Specify the NULL
keyword.
SQL> INSERT INTO
2 VALUES
1 row created.
dept
(70, 'FINANCE', NULL);
Inserting Special Values

The SYSDATE function records the
current date and time.
SQL> INSERT INTO
2
3
4 VALUES
5
6
1 row created.
emp (empno, ename, job,
mgr, hiredate, sal, comm,
deptno)
(7196, 'GREEN', 'SALESMAN',
7782, SYSDATE, 2000, NULL,
10);
Format Masks


All data is stored in the database in a standard
binary format
Format masks are alphanumeric text strings that
specify the format of input and output data
Table 3-1: Number format masks
 Table 3-2: Date format masks

Inserting Date Values
 Date values must be converted from
characters to dates using the
TO_DATE function and a format
mask
 Example:
Inserting Text Data
 Must be enclosed in single quotes
 Is case-sensitive
 To insert a string with a single quote, type the
single quote twice
 Example:
'Mike''s Motorcycle Shop'
Inserting Interval Values
 Year To Month Interval:
TO_YMINTERVAL(‘years-months’)
e.g. TO_YMINTERVAL(‘3-2’)
 Day To Second Interval:
TO_DSINTERVAL(‘days HH:MI:SS.99’)
e.g. TO_DSINTERVAL(‘-0 01:15:00’)
Inserting LOB Column Locators


Oracle stores LOB data in separate physical
location from other types of data
LOB locator
Structure containing information that identifies LOB
data type
 Points to alternate memory location


Create blob locator

EMPTY_BLOB()
Inserting Specific Date Values

Add a new employee.
SQL> INSERT INTO
2 VALUES
3
4
1 row created.
emp
(2296,'AROMANO','SALESMAN',7782,
TO_DATE('FEB 3, 1997', 'MON DD, YYYY'),
1300, NULL, 10);
• Verify your addition.
EMPNO ENAME
JOB
MGR
HIREDATE SAL COMM DEPTNO
----- ------- -------- ---- --------- ---- ---- -----2296 AROMANO SALESMAN 7782 03-FEB-97 1300
10
Changing Data in a Table
EMP
EMPNO ENAME
7839
7698
7782
7566
...
KING
BLAKE
CLARK
JONES
JOB
...
DEPTNO
PRESIDENT
MANAGER
MANAGER
MANAGER
10
30
10
20
“…update a row
in EMP table…”
EMP
EMPNO ENAME
7839
7698
7782
7566
...
KING
BLAKE
CLARK
JONES
JOB
PRESIDENT
MANAGER
MANAGER
MANAGER
...
DEPTNO
10
30
20
10
20
The UPDATE Statement

Modify existing rows with the UPDATE
statement.
UPDATE
SET
[WHERE

table
column = value [, column = value, ...]
condition];
Update more than one row at a time, if
required.
Search Conditions
 Format:
WHERE fieldname operator expression
 Operators









Equal (=)
Greater than, Less than (>, <)
Greater than or Equal to (>=)
Less than or Equal to (<=)
Not equal (< >, !=, ^=)
LIKE
BETWEEN
IN
NOT IN
Search Condition Examples
WHERE s_name = ‘Sarah’
WHERE s_age > 18
WHERE s_class <> ‘SR’
 Text in single quotes is case sensitive
Updating Rows in a Table

Specific row or rows are modified when you
specify the WHERE clause.
SQL> UPDATE emp
2 SET
deptno = 20
3 WHERE
empno = 7782;
1 row updated.

All rows in the table are modified if you omit
the WHERE clause.
SQL> UPDATE employee
2 SET
deptno = 20;
14 rows updated.
Updating Rows:
Integrity Constraint Error
SQL> UPDATE
2 SET
3 WHERE
emp
deptno = 55
deptno = 10;
UPDATE emp
*
ERROR at line 1:
ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK)
violated - parent key not found
Removing a Row from a Table
DEPT
DEPTNO
-----10
20
30
40
50
60
...
DNAME
---------ACCOUNTING
RESEARCH
SALES
OPERATIONS
LOC
-------NEW YORK
DALLAS
CHICAGO
BOSTON
DEVELOPMENT DETROIT
MIS
“…delete a row
from DEPT table…”
DEPT
DEPTNO
-----10
20
30
40
60
...
DNAME
---------ACCOUNTING
RESEARCH
SALES
OPERATIONS
MIS
LOC
-------NEW YORK
DALLAS
CHICAGO
BOSTON
The DELETE Statement

You can remove existing rows from a table by
using the DELETE statement.
DELETE [FROM]
[WHERE
table
condition];
Deleting Rows from a Table

Specific rows are deleted when you specify the
WHERE clause.
SQL> DELETE FROM
2 WHERE
1 row deleted.

department
dname = 'DEVELOPMENT';
All rows in the table are deleted if you omit
the WHERE clause.
SQL> DELETE FROM
4 rows deleted.
department;
Deleting Rows:
Integrity Constraint Error
SQL> DELETE FROM
2 WHERE
dept
deptno = 10;
DELETE FROM dept
*
ERROR at line 1:
ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK)
violated - child record found
Database Transactions
Begin when the first executable SQL
statement is executed
 End with one of the following events:

COMMIT or ROLLBACK is issued
 DDL or DCL statement executes (automatic
commit)
 User exits
 System crashes

Advantages of COMMIT
and ROLLBACK Statements
Ensure data consistency
 Preview data changes before making changes
permanent
 Group logically related operations

Controlling Transactions

INSERT
COMMIT
Transaction
UPDATE
Savepoint A
INSERT
DELETE
Savepoint B
ROLLBACK to Savepoint B
ROLLBACK to Savepoint A
ROLLBACK
Implicit Transaction Processing

An automatic commit occurs under the
following circumstances:
DDL statement is issued
 DCL statement is issued
 Normal exit from SQL*Plus, without explicitly
issuing COMMIT or ROLLBACK


An automatic rollback occurs under an
abnormal termination of SQL*Plus or a
system failure.
State of the Data Before
COMMIT or ROLLBACK
The previous state of the data can be recovered.
 The current user can review the results of the DML
operations by using the SELECT statement.
 Other users cannot view the results of the DML
statements by the current user.
 The affected rows are locked; other users cannot
change the data within the affected rows.

State of the Data After COMMIT
Data changes are made permanent in the
database.
 The previous state of the data is permanently
lost.
 All users can view the results.
 Locks on the affected rows are released; those
rows are available for other users to manipulate.
 All savepoints are erased.

Committing Data

Make the changes.
SQL> UPDATE emp
2 SET
deptno = 10
3 WHERE
empno = 7782;
1 row updated.
• Commit the changes.
SQL> COMMIT;
Commit complete.
State of the Data After ROLLBACK

Discard all pending changes by using the
ROLLBACK statement.
Data changes are undone.
 Previous state of the data is restored.
 Locks on the affected rows are released.

SQL> DELETE FROM
14 rows deleted.
SQL> ROLLBACK;
Rollback complete.
employee;
Savepoints
 Used to mark
individual sections
of a transaction
 You can roll back
a transaction to a
savepoint
Rolling Back Changes
to a Marker
Create a marker in a current transaction by using the
SAVEPOINT statement.
 Roll back to that marker by using the ROLLBACK
TO SAVEPOINT statement.

SQL> UPDATE...
SQL> SAVEPOINT update_done;
Savepoint created.
SQL> INSERT...
SQL> ROLLBACK TO update_done;
Rollback complete.
Truncating Tables

Removes all table data without saving any
rollback information
Advantage: fast way to delete table data
 Disadvantage: can’t be undone


Syntax:
TRUNCATE TABLE tablename;
Summary
Statement
Description
INSERT
Adds a new row to the table
UPDATE
Modifies existing rows in the table
DELETE
Removes existing rows from the table
COMMIT
Makes all pending changes permanent
SAVEPOINT
Allows a rollback to the savepoint marker
ROLLBACK
Discards all pending data changes
Sequences
 Sequential list of numbers that is
automatically generated by the database
 Used to generate values for surrogate
keys
Creating New Sequences

CREATE SEQUENCE command
DDL command
 No need to issue COMMIT command

General Syntax Used to Create a
New Sequence
Creating Sequences
 Syntax:
CREATE SEQUENCE sequence_name
[optional parameters];
 Example:
CREATE SEQUENCE f_id_sequence
START WITH 200;
Viewing Sequence Information

Query the SEQUENCE Data Dictionary View:
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

Pseudocolumn Examples
Pseudocolumn
Name
Output
CURRVAL
Most recently retrieved sequence
value
Next value in a sequence
NEXTVAL
SYSDATE
USER
Current system date from
database server
Username of current user
Using Pseudocolumns
 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
With Sequences
 Accessing the next value in a sequence:
sequence_name.NEXTVAL
 Inserting a new record using a sequence:
INSERT INTO my_faculty VALUES
(f_id_sequence.nextval, ‘Professor Jones’);
Object Privileges


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_name
TO user1, user 2, …;

Revoking object privileges:
REVOKE privilege1, privilege2, …
ON object_name
FROM user1, user 2, …;
Examples of Object Privileges
Object Type
Privilege
Description
Table, Sequence
ALTER
Allows user to change object’s structure using the
ALTER command
Table, Sequence
DROP
Allows user to drop object
Table, Sequence
SELECT
Allows user to view object
Table
INSERT,
UPDATE,
DELETE
Allows user to insert, update, delete table data
Any database
object
ALL
Allows user to perform any operation on object
Granting and Revoking Object
Privileges