Transcript Chapter 1: Sharing Knowlege and Success

Chapter 15: Changing Data: insert, update, delete
•
•
•
•
•
Insert
Rollback
Commit
Update
Delete
Insert Statement
–
–
Allows you to insert a row or rows into tables or views.
Syntax1:
INSERT INTO tablename / viewname
(column1, column2, ... )
VALUES (value1, value2, ... )
–
Syntax2:
INSERT INTO tablename / viewname
(column1, column2, ... )
subquery
–
Column names are optional, but then the values must be
in order of the columns.
CSCI N311: Oracle Database Programming
5-1
Chapter 15: Changing Data: insert, update, delete
• Examples:
– The following statement inserts a row into the DEPT
table:
INSERT INTO dept
VALUES (50, ’PRODUCTION’, ’SAN FRANCISCO’)
–
The following statement inserts a row with six columns
into the EMP table. One of these columns is assigned
NULL and another is assigned a number in scientific
notation:
INSERT INTO emp (empno, ename, job, sal,
comm, deptno) VALUES
(7890, ’JINKS’, ’CLERK’, 1.2E3, NULL, 40)
The following statement has the same effect:
INSERT INTO (select empno, ename, job, sal,
comm, deptno from emp) VALUES
(7890, ’JINKS’, ’CLERK’, 1.2E3, NULL, 40)
•
The following statement copies managers and presidents
or employees whose commission exceeds 25% of their
salary into the BONUS table:
INSERT INTO bonus
SELECT ename, job, sal, comm
FROM emp
WHERE comm > 0.25 * sal
OR job IN (’PRESIDENT’, ’MANAGER’)
CSCI N311: Oracle Database Programming
5-2
Chapter 15: Changing Data: insert, update, delete
• Rollback / Commit:
– These commands allow you to reverse changes, or make
your changes permanent.
– Example from book:
CSCI N311: Oracle Database Programming
5-3
Chapter 15: Changing Data: insert, update, delete
• Notes about Commit :
– No special privileges are required for you to commit your
work.
– When sqlplus terminates or any other program which
save data to Oracle, an implicit commit is issued for any
open transactions.
– Transactions begin when:
–
–
–
–
a SQL statement is first issued,
immediately after a commit,
immediately after a rollback.
Savepoint allows you to rollback to a specific point in a
transaction. Example:
UPDATE emp
SET sal = 2000
WHERE ename = ’BLAKE’
SAVEPOINT blake_sal
UPDATE emp
SET sal = 1500
WHERE ename = ’CLARK’
SAVEPOINT clark_sal
SELECT SUM(sal) FROM emp
ROLLBACK TO SAVEPOINT blake_sal
UPDATE emp
SET sal = 1300
WHERE ename = ’CLARK’
COMMIT
CSCI N311: Oracle Database Programming
5-4
Chapter 15: Changing Data: insert, update, delete
• Delete:
– The following statement deletes all rows from a table
named TEMP_ASSIGN.
DELETE FROM temp_assign
–
The following statement deletes from the employee table
all sales staff who made less than $100 commission last
month:
DELETE FROM emp
WHERE JOB = ’SALESMAN’
AND COMM < 100
–
The following statement has the same effect:
DELETE FROM (select * from emp)
WHERE JOB = ’SALESMAN’
AND COMM < 100
CSCI N311: Oracle Database Programming
5-5
Chapter 15: Changing Data: insert, update, delete
• Update:
– The following statement gives null commissions to all
employees with the job TRAINEE:
UPDATE emp
SET comm = NULL
WHERE job = ’TRAINEE’
–
The following statement promotes JONES to manager of
Department 20 with a $1,000 raise (assuming there is
only one JONES):
UPDATE emp
SET job = ’MANAGER’,
sal = sal + 1000,
deptno = 20
WHERE ename = ’JONES’
CSCI N311: Oracle Database Programming
5-6
Additional UPDATE Examples
– Set the number of sets won to zero for all
competitors resident in Stratford.
UPDATE MATCHES
SET
WON = 0
WHERE PLAYERNO IN
( SELECT PLAYERNO
FROM
PLAYERS
WHERE TOWN = ‘Stratford’)
– Set all penalties lower than average to $10.
UPDATE PENALTIES
SET
AMOUNT = 10
WHERE AMOUNT <
( SELECT AVG(AMOUNT)
FROM
PENALTIES)
– Sets # of sets won and lost for match 2 same as
match 1.
UPDATE MATCHES
SET ( WON, LOST ) =
( SELECT WON, LOST FROM MATCHES
FROM MATCHES WHERE MATCHNO = 1 )
WHERE MATCHNO = 2
CSCI N311: Oracle Database Programming
5-7
Chapter 15: Changing Data: insert, update, delete
• Notes for Insert / Update / Delete:
–
For you to insert / update / delete values in a table, the
table must be in your own schema or you must have
INSERT / UPDATE / DELETE privilege on the table.
–
For you to insert / update / delete values in the base table
of a view, the owner of the schema containing the view
must have INSERT / UPDATE / DELETE privilege on
the base table. Also, if the view is in a schema other than
your own, you must have INSERT / UPDATE / DELETE
privilege on the view.
–
You cannot insert / update / delete rows in a view if the
view’s defining query contains one of the following
constructs:
–
–
–
–
–
join
set operator
GROUP BY clause
group function
DISTINCT operator
CSCI N311: Oracle Database Programming
5-8
Update and Delete Questions
– Increase all penalties which are higher than the
average by 20%.
– Update player #95’s data to reflect the fact that he
has moved to the same address (street and town)
as player #6.
– Delete all players who live in the same town as
player #44, but keep the data about player #44.
CSCI N311: Oracle Database Programming
5-9
Chapter 18: Creating, Dropping, and Altering
Tables and Views
• Creating a Table:
– Basic Syntax:
CREATE TABLE tablename
(column_a type NOT NULL,
column_b type,
... );
– Data Types:
–
CHAR(n)
–
VARCHAR2(n)
–
–
–
NUMBER
NUMBER(n)
NUMBER(m,n)
–
DATE
character data of fixed size
up to 2000 characters.
variable length character data
up to 4000 characters.
integer data
integer data, length n
floating point data,
m = total length of digits,
n = digits after decimal point
date/time data
– Uncommon Data Types:
–
LONG
–
–
RAW
LONG RAW
Similar to VARCHAR2 but
Oracle’s character functions
cannot operate on this type.
holds binary data, up to 255 bytes
holds large binary data,
eg. sound, video, etc.
CSCI N311: Oracle Database Programming
5-10
Chapter 18: Creating, Dropping, and Altering
Tables and Views
Constraint Clauses:
– Candidate Keys:
Column(s) which uniquely identify rows in a table. This
could be used as the primary key.
– Primary Keys:
Column(s) used to specify and enforce uniqueness among
rows in a table.
create table employee (
employee_id
varchar(8) not null,
column x, ....,
primary key (employee_id) ) ;
–
Foreign Keys:
Column(s) used to reference rows in another table. These
values are actually the primary keys in the other table.
alter table employee
add constraint emp02
foreign key (dept_id)
references department(dept_id);
–
Check Constraint:
A constraint that forces a set of values (domain) for a
column.
CSCI N311: Oracle Database Programming
5-11
Chapter 18: Creating, Dropping, and Altering
Tables and Views
Creating a Table from a Table:
Includes the data:
create table RAIN as
select City, Precipitation
from TROUBLE;
Does not include the data, only the table definition:
create table RAIN as
select City, Precipitation
from TROUBLE
where 1=2;
CSCI N311: Oracle Database Programming
5-12
Chapter 18: Creating, Dropping, and Altering
Tables and Views
Constraint Examples:
SQL> desc customer
Name
Null?
------------------------------- -------ID
NAME
STATE
Type
---NUMBER
VARCHAR2(40)
CHAR(2)
SQL> desc state2
Name
Null?
------------------------------- -------STATE_CODE
NOT NULL
STATE_NAME
Type
---CHAR(2)
VARCHAR2(50)
SQL> alter table state2
2 add constraint PK_State2
3 primary key (state_code);
Table altered.
SQL>
2
3
4
alter table customer
add constraint cust_anotherone
foreign key (state)
references state2(state_code);
Table altered.
CSCI N311: Oracle Database Programming
5-13
Chapter 18: Creating, Dropping, and Altering
Tables and Views
Dropping Tables (p. 375):
DROP TABLE tablename;
–
Dropping a table removes the table,
both it’s data and definition.
Truncating Tables (p. 321, 375):
TRUNC TABLE tablename;
–
Truncating a table removes only the data.
–
Table’s definition is still available.
Altering Tables:
Tables can be altered to:
–
change column definitions,
–
add new columns,
–
enable or disable constraints constraints,
–
or change storage parameters.
Problem: Adding NOT NULL to column definitions.
ALTER TABLE emp
ADD (thriftplan NUMBER(7,2),
loancode CHAR(1) NOT NULL);
ALTER TABLE emp
MODIFY (thriftplan NUMBER(9,2));
CSCI N311: Oracle Database Programming
5-14
Chapter 18: Creating, Dropping, and Altering
Tables and Views
Views
– Special considerations about views:
• You cannot insert if the underlying table has any NOT
NULL columns that are not in the view.
• You cannot insert/update if any one of the view’s
columns referenced in the insert/update contains
functions or calculations.
• You cannot insert/update/delete if the view contains
group by, distinct, or a reference to rownum.
– View Stability:
• dropping the underlying tables of a view prevents the
view from working.
• altering the underlying tables of a view are OK.
– Order By in Views:
• ORDER BY does not work in views!
• GROUP BY still consolidates rows in views,
a side affect of GROUP BY is ordering rows.
– READ ONLY:
• The read only clause prevents update/insert/delete of
rows in a view.
CSCI N311: Oracle Database Programming
5-15
Chapter 18: Creating, Dropping, and Altering
Tables and Views
New with Oracle8:
– Index-Only Tables: stores data as if all data is in an
index. Faster access.
– Partitioned Tables: breaks up data in a table into
partitions, faster for access for huge tables, easier to
manage load/backup/recover.
CSCI N311: Oracle Database Programming
5-16