Ch. 14: SQL*PLUS

1. 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 STOCKwhere company = ‘&xCompany’; 1

2. 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

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

4. 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

5. 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

6. 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

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

8. 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; 8

9. 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 9

10. 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’); Ch. 15: Update Example 10

11. 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 else insert clause4 Subquery; Ch. 15: Multi-table Inserts 11

12. 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); Ch. 15: Merge 12

13. 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 13

14. 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 END from BOOKSHELF; • 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; 14

15. 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 15

16. 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

17. 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

18. Ch. 18: Constraint Example SQL> desc customer Name Null? Type ----------------------- -------- ---- ID NUMBER NAME VARCHAR2(40) STATE CHAR(2) SQL> desc state2 Name Null? Type ----------------------- -------- ---- STATE_CODE NOT NULL CHAR(2) STATE_NAME VARCHAR2(50) 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. 18

19. 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. 19

20. 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: ALTER TABLE 20

21. 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

22. 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

23. 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