Transcript Ch. 14: SQL*PLUS  Advanced Formatting

Ch. 14: SQL*PLUS

Advanced Formatting, pp. 256-270
•
•
•
•
•
•
•
•
•

break on column/row/report
compute avg/count/max/min/num/sum/std/var
ttitle, btitle left/right/center
NEW_VALUE
sql.pno
noprint
skip n/page
set termout on/off
fold_after, fold_before (p.276)
Variables, pp. 270-273
•
•
•
•
Define without parameters it shows defined variables.
Define with a variable name it sets that variable.
Variables can also be defined with a ACCEPT command.
‘&’ signifies the start of a variable name, ‘.’ ends a variable name.
Select company from STOCK
where company = ‘&xCompany’;
1
Ch. 14: Spooled Substitution of Variables

Spooled Substitution – making the output of a SQL script define
the value for a variable.
set heading off
spool slave.sql
select 'define myvar = '||item
from LEDGER
where rate = (select max(rate) from LEDGER)
/
spool off
@slave
2
Ch. 15: Changing Data






Insert
Update
Merge (new in 9i)
Delete
Rollback
Commit
3
Ch. 15: Insert


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.
4
Ch. 15: Insert 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 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’);
5
Ch. 15: Insert with Append Hint

To improve insert performance, especially for large subquery inserts,
you can use the /*+ APPEND */ hint.
Insert /*+ APPEND */ into mytable (col1)
select mycol from othertable;



Normally insert looks for freespace inside currently allocated blocks on
disk.
Append overrides this behavior and appends data to the table by
allocating new data blocks.
Hints are non-ANSI standard. This only works in Oracle.
6
Ch. 15: Rollback, Commit, Autocommit
 Rollback allow you to
reverse changes.
 Commit make your
changes permanent.
 Example
from 8i book:
7
Ch. 15: Transactions, Savepoint
 Transactions begin when:
•
•
•
a SQL satatement 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’;
 Commits occur when:
•
•
•
SAVEPOINT blake_sal
Commit (explicit)
quit/exit a program (implicit)
DDL (implicit)
UPDATE emp
SET sal = 1500
WHERE ename = ’CLARK’;
SAVEPOINT clark_sal
 Rollbacks occur when:
•
•
•
SELECT SUM(sal) FROM emp;
Rollback (explicit)
Abnormal program
termination (implicit)
Database crash (implicit)
ROLLBACK TO SAVEPOINT
blake_sal;
UPDATE emp
SET sal = 1300
WHERE ename = ’CLARK’;
COMMIT;
8
Ch. 15: Delete, Update Examples
 The following statement deletes
all rows from a table named
TEMP_ASSIGN.
 The following statement gives null
commissions to all employees with
the job TRAINEE:
UPDATE emp
SET comm = NULL
WHERE job = ’TRAINEE’;
DELETE FROM temp_assign;
 The following statement deletes
from the employee table all
sales staff who made less than
$100 commission last month:
 The following statement promotes
JONES to manager of Department
20 with a $1,000 raise:
UPDATE emp
SET job = ’MANAGER’,
sal = sal + 1000,
deptno = 20
WHERE ename = ’JONES’;
DELETE FROM emp
WHERE JOB = ’SALESMAN’
AND COMM < 100;
9
Ch. 15: Update Example
 More complex example with multiple subqueries.
UPDATE emp a
SET deptno =
(SELECT deptno
FROM dept
WHERE loc = ’BOSTON’),
(sal, comm) =
(SELECT 1.1*AVG(sal), 1.5*AVG(comm)
FROM emp b
WHERE a.deptno = b.deptno)
WHERE deptno IN
(SELECT deptno
FROM dept
WHERE loc = ’DALLAS’
OR loc = ’DETROIT’);
10
Ch. 15: Multi-table Inserts
 Multi-table inserts are a new 9i feature.
 WHEN clause indicates which VALUES clause to use.
 ALL or FIRST indicates to evaluate all or only the first
WHEN/VALUE clause per row.
Insert ALL|FIRST
when (condition1) then
insert clause1
when (condition2) then
insert clause2
when (condition3) then
insert clause3
else
insert clause4
Subquery;
11
Ch. 15: Merge
 Merge is a new 9i feature.
 Combines the features of an INSERT with UPDATE.
 Based on the conditions you specify in the WHEN clause,
Oracle takes the source data (table, view, subquery) and
updates the existing rows. If the conditions are not met, a new
row is inserted.
MERGE into COMFORT C1
USING (select City, SampleDate, Noon from COMFORT) C2
ON (C1.City=C2.City and C1.SampleDate=C2.SampleDate)
WHEN MATCHED THEN update set C1.Noon = C2.Noon
WHEN NOT MATCHED THEN
insert (C1.City, C1.SampleDate, C1.Noon)
values (C2.City, C2.SampleDate, C2.Noon);
12
Ch. 17: DECODE
 Decode is Oracle non-standard SQL. Extremely powerful, yet
underutilized.
 Decode works similar to if/then/else
DECODE (value, if1, then1, if2, then2,…, default )
 Common use illustrated in the text:
• Aggregating groups of data into a single column.
• Flip a table on its side, ie rows become columns.
• Dividing data into sections based on row number. This is done in
conjunction with the MOD function.
• Complex computations that require if/then logic.
13
Ch. 17: CASE
 CASE, new in 9i, works just like DECODE
 Uses WHEN, THEN, ELSE, and END keywords
select distinct
Decode(Category,
‘A’, ‘Adult’,
‘F’, ‘Fiction’,
‘N’, ‘Non-Fiction’,
‘C’, ‘Children’,
Category)
from BOOKSHELF;
select distinct
CASE Category
when ‘A’ then
when ‘F’ then
when ‘N’ then
when ‘C’ then
else Category
END
from BOOKSHELF;
14
‘Adult’
‘Fiction’
‘Non-Fiction’
‘Children’
Ch. 18: Basic CREATE TABLE
 Syntax:
CREATE TABLE tablename
(column_a
column_b type,
...
);
 Data Types:
• CHAR(n)
• VARCHAR2(n)
• NUMBER
• NUMBER(n)
• NUMBER(m,n)
• DATE
• NOT NULL
type NOT NULL,
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
null constraint, requires a value for row to exist
15
Ch. 18: Other CREATE TABLE clauses









Appendix shows all CREATE TABLE clauses pp. 990-1002
Object tables – Ch. 32 **
XML types – Ch. 41***
STORAGE clause – specifies how the data is stored in the database,
Ch. 20 and 40
VARRAYs and Nested Tables – Ch. 31**
LOB (Large OBject) parameters – Ch. 32**
Partition clauses – Ch. 18*
External Tables – Ch. 25 ***
And many others
* new in Oracle8
** new in Oracle8i
*** new in Oracle9i
16
Ch. 18: Constraints
 Candidate Keys:
Column(s) which uniquely identify rows in a table. Also called unique constraint.
 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, ....,
constraint emp_PK 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 emp_dept_FK
foreign key (dept_id)
references department(dept_id);
 Check Constraint:
A constraint that forces a set of values (domain) for a column. NOT NULL
columns automatically get a system generated check constraint.
17
Ch. 18: Constraint Example
SQL> desc customer
Name
Null?
----------------------- -------ID
NAME
STATE
SQL> desc state2
Name
Null?
----------------------- -------STATE_CODE
NOT NULL
STATE_NAME
Type
---NUMBER
VARCHAR2(40)
CHAR(2)
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
5
alter table customer
add constraint cust_another
foreign key (state)
references
state2(state_code);
Table altered.
18
Ch. 18: DROP vs. TRUNCATE TABLE
 Dropping Tables:
DROP TABLE tablename;
• Dropping a table removes the table,
• both the table’s data and definition are removed.
 Truncating Tables:
TRUNC TABLE tablename;
• Truncating a table removes only the data,
• the table’s definition is still available.
19
Ch. 18: ALTER TABLE
 Tables can be altered to:
• change column definitions,
• add new columns (without NOT NULL),
• enable or disable constraints constraints,
• change storage parameters,
• drop columns as of 8i.
ALTER TABLE emp
ADD (thriftplan NUMBER(7,2),
loancode CHAR(1) NOT NULL);
ALTER TABLE emp
MODIFY (thriftplan NUMBER(9,2));
alter table emp drop column thriftplan;
20
Ch. 18: 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.
• INSTEAD OF triggers (Ch. 28, p. 511) get around the above limitation.
 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 before Oracle 8i.
• 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.
21
Ch. 18: Creating a Table from Another 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;
22
Ch. 18: New Features
 New with Oracle8:
• Index-Only Tables – entire table is stored as an index.
• Partitioned Tables – table split into separate physical parts. This
serves 3 purposes:
 Improved query performance on very large tables.
 Easier to manage loads and deletes on very large tables.
 Backup and/or recovery faster.
 New with Oracle9i:
• Online Table Redefinition – allows for changes to table structure
while being accessed.
23