ch 14 sql plus l.
Skip this Video
Loading SlideShow in 5 Seconds..
Ch. 14: SQL*PLUS PowerPoint Presentation
Download Presentation
Ch. 14: SQL*PLUS

Loading in 2 Seconds...

play fullscreen
1 / 23

Ch. 14: SQL*PLUS - PowerPoint PPT Presentation

  • Uploaded on

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

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Ch. 14: SQL*PLUS' - bishop

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
ch 14 sql plus
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
    • 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 STOCKwhere company = ‘&xCompany’;


ch 14 spooled substitution of variables
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


where rate = (select max(rate) from LEDGER)


spool off



ch 15 changing data
Ch. 15: Changing Data
  • Insert
  • Update
  • Merge (new in 9i)
  • Delete
  • Rollback
  • Commit


ch 15 insert
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, ... )


  • Column names are optional, but then the values must be in order of the columns.


ch 15 insert examples
Ch. 15: Insert Examples
  • The following statement inserts a row into the DEPT table:



  • 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’);


ch 15 insert with append hint
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.


ch 15 rollback commit autocommit
Ch. 15: Rollback, Commit, Autocommit
  • Rollback allow you to reverse changes.
  • Commit make your changes permanent.
  • Example from 8i book:


ch 15 transactions savepoint
Ch. 15: Transactions, Savepoint
  • Transactions begin when:
    • a SQL satatement is first issued,
    • immediately after a commit,
    • immediately after a rollback.
  • Commits occur when:
    • Commit (explicit)
    • quit/exit a program (implicit)
    • DDL (implicit)
  • Rollbacks occur when:
    • Rollback (explicit)
    • Abnormal program termination (implicit)
    • Database crash (implicit)
  • SAVEPOINT allows you to rollback to a specific point in a transaction. Example:UPDATE empSET sal = 2000WHERE ename = ’BLAKE’;SAVEPOINT blake_salUPDATE empSET sal = 1500WHERE ename = ’CLARK’;SAVEPOINT clark_salSELECT SUM(sal) FROM emp;ROLLBACK TO SAVEPOINT blake_sal;UPDATE empSET sal = 1300WHERE ename = ’CLARK’;COMMIT;


ch 15 delete update examples
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 empWHERE JOB = ’SALESMAN’ AND COMM < 100;

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:UPDATE emp SET job = ’MANAGER’, sal = sal + 1000, deptno = 20 WHERE ename = ’JONES’;

Ch. 15: Delete, Update Examples


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


OR loc = ’DETROIT’);

Ch. 15: Update Example


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|FIRSTwhen (condition1) theninsert clause1

when (condition2) theninsert clause2

when (condition3) theninsert clause3


insert clause4


Ch. 15: Multi-table Inserts


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.


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


insert (C1.City, C1.SampleDate, C1.Noon)

values (C2.City, C2.SampleDate, C2.Noon);

Ch. 15: Merge


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.

Ch. 17: DECODE


ch 17 case
Ch. 17: CASE

select distinct

CASE Category

when ‘A’ then ‘Adult’

when ‘F’ then ‘Fiction’

when ‘N’ then ‘Non-Fiction’

when ‘C’ then ‘Children’

else Category



  • CASE, new in 9i, works just like DECODE
  • Uses WHEN, THEN, ELSE, and END keywords

select distinct


‘A’, ‘Adult’,

‘F’, ‘Fiction’,

‘N’, ‘Non-Fiction’,

‘C’, ‘Children’,




ch 18 basic create table
Ch. 18: Basic CREATE TABLE
  • Syntax:CREATE TABLEtablename(column_a type NOT NULL,column_b type, ...);
  • Data Types:
    • CHAR(n) character data of fixed size up to 2000 characters.
    • VARCHAR2(n) variable length character data up to 4000 characters.
    • NUMBER integer data
    • NUMBER(n) integer data, length n
    • NUMBER(m,n) floating point data, m = total length of digits, n = digits after decimal point
    • DATE date/time data
    • NOT NULL null constraint, requires a value for row to exist


ch 18 other create table clauses
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


ch 18 constraints
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.


ch 18 constraint example
Ch. 18: Constraint Example

SQL> desc customer

Name Null? Type

----------------------- -------- ----




SQL> desc state2

Name Null? Type

----------------------- -------- ----



SQL> alter table state2

2 add constraint PK_State2

3 primary key (state_code);

Table altered.

SQL> alter table customer

2 add constraint cust_another

3 foreign key (state)

4 references

5 state2(state_code);

Table altered.


ch 18 drop vs truncate table
  • DroppingTables:

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.


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 empADD (thriftplan NUMBER(7,2),loancode CHAR(1) NOT NULL);ALTER TABLE empMODIFY (thriftplan NUMBER(9,2));alter table emp drop column thriftplan;



ch 18 views
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.
    • The read only clause prevents update/insert/delete of rows in a view.


ch 18 creating a table from another table
Ch. 18: Creating a Table from Another Table
  • Includes the data:

create table RAIN as

select City, Precipitation


  • Does not include the data, only the table definition:

create table RAIN as

select City, Precipitation


where 1=2;


ch 18 new features
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.