Merge

You cannot update the same row more than once in a Merge statement.
Fine-grained access control is not implemented during MERGE. If needed, use Insert and Update.

Clear Screen
------------------------------------------------------
-- Create Dept
------------------------------------------------------
Drop Table Dept Cascade Constraints;
Create Table Dept (
 Deptno              Number(2) Not Null,
 Dname               Varchar2(14),
 Loc                 Varchar2(13),
     Constraint Dept_Deptno_Pk Primary Key (Deptno) );

Insert Into Dept Values (10,'ACCOUNTING','NEW YORK');
Insert Into Dept Values (20,'RESEARCH','DALLAS');
Insert Into Dept Values (30,'SALES','CHICAGO');
Insert Into Dept Values (40,'OPERATIONS','BOSTON');

------------------------------------------------------
-- Create Dept2 - holds records to be inserted/updated
------------------------------------------------------
Drop Table Dept2 Cascade Constraints;

Create Table Dept2 As Select * From Dept Where 1=2;

Insert Into Dept2 Values (10,'ACCOUNTING'  ,'PITTSBURGH');
Insert Into Dept2 Values (40,'OPERATIONS'  ,'BOSTON');
Insert Into Dept2 Values (50,'HOUSEKEEPING','WASHINGTON DC');
Commit;

------------------------------------------------------
-- Display the table data before the Merge
------------------------------------------------------
Prompt Table: Dept -- Before Merge
Select * From Dept Order By Deptno;

Prompt
Prompt Table: Dept2
Select * From Dept2 Order By Deptno;

------------------------------------------------------
-- Merge Dept2 into Dept
------------------------------------------------------
Merge Into Dept D1
Using (Select Deptno, Dname, Loc
         From Dept2) D2
   On (D1.Deptno = D2.Deptno)
 When     Matched Then Update Set D1.Loc = D2.Loc
                       Delete Where (Loc = 'BOSTON')
 When Not Matched Then Insert (D1.Deptno, D1.Dname, D1.Loc)
                       Values (D2.Deptno, D2.Dname, D2.Loc);
Commit;

------------------------------------------------------
-- Show dept table data after merge
------------------------------------------------------
Prompt
Prompt Table: Dept -- After Merge
Select * From Dept Order By Deptno;

DML/DDL

T A B L E S P A C E S

Create

CREATE TABLESPACE tablespacename
DATAFILE datfile_name filesize; -- size 100m autoextend on;

T A B L E S

Create with Primary Key

CREATE TABLE table
(
 field1 VARCHAR2(1) CONSTRAINT pk_constraint PRIMARY KEY
 ,field2 NUMBER NOT NULL
 ,field3 DATE
);

Create with Composite Primary Key

CREATE TABLE table
(
 field1 VARCHAR2(5),
  field2 NUMBER(7,2),
  field3 DATE       ,
    CONSTRAINT pk_table1 PRIMARY KEY (field1, field2)
        USING INDEX TABLESPACE tablespace
);

Create Foreign Key

CREATE TABLE table
(
 field1 VARCHAR2(5) CONSTRAINT pk_constraint PRIMARY KEY,
  field2 VARCHAR2(5) REFERENCES table2 (field2) ON DELETE SET NULL,
  field3 VARCHAR2(5)
);

Create Partitioned Table By Range

CREATE TABLE table
(
  field1 VARCHAR2(5)  CONSTRAINT pk_constraint PRIMARY KEY,
   field2 NUMBER(7,2),
   field3 DATE      
)
PARTITION BY RANGE (field2)
(
  PARTITION part1 VALUES LESS THAN ('value')  TABLESPACE tablespace
  PARTITION part2 VALUES LESS THAN (MAXVALUE) TABLESPACE tablespace
);

Create Partitioned Table By Hash

• Placement is determined by hashing the partition key.
• Decreases I/O contention by a greater distribution of data.

CREATE TABLE table
(
 field1 VARCHAR2(1) CONSTRAINT pk_constraint PRIMARY KEY
,field2 NUMBER NOT NULL
,field3 DATE
)
PARTITION BY HASH (field2)
(
 PARTITION part1 TABLESPACE tablespace1
,PARTITION part2 TABLESPACE tablespace2
);

Create Subpartition

CREATE TABLE table
(
  field1 VARCHAR2(1) CONSTRAINT pk_constraint PRIMARY KEY
 ,field2 NUMBER NOT NULL
 ,field3 DATE
)
PARTITION BY RANGE (field2)
 SUBPARTITION BY HASH (field3) SUBPARTITIONS number
 (
   SUBPARTITION part1 VALUES LESS THAN ('value')  TABLESPACE tablespace
  ,SUBPARTITION part2 VALUES LESS THAN (MAXVALUE) TABLESPACE tablespace
 );

Create Partitioned Table By List

CREATE TABLE table
(
  field1 VARCHAR2(5)  CONSTRAINT pk_constraint PRIMARY KEY
  ,field2 NUMBER(7,2)
  ,field3 DATE      
)
PARTITION BY LIST (field2)
(
  PARTITION part1 VALUES ('value1','value2') TABLESPACE tablespace1
 ,PARTITION part2 VALUES ('value3','value4') TABLESPACE tablespace2
);

Create Index-Organized Table

• Sorted by primary key.
• All data is stored in the index.
• If 'Where' always uses primary key
• Use only if the data is very static
• Most effective when primary key is most of the table
• You can use the 'move online'* option of 'alter table'

CREATE TABLE table
(
 field1 VARCHAR2(5) CONSTRAINT pk_constraint PRIMARY KEY,
  field2 VARCHAR2(5) ,
  field3 VARCHAR2(5)
)
ORGANIZATION INDEX

Copy Some Rows & Columns (but not constraints) (not allowed if LONG type)

CREATE TABLE table2
AS SELECT field1, field2, field3+field4 as field34
     FROM table1
    WHERE table1.field = value;

Copy Structure Only

CREATE TABLE table2
AS SELECT *
    FROM table1
   WHERE 1 = 2;

Copy Table without Generating Redo Log

CREATE TABLE table2
NOLOGGING
AS SELECT *
    FROM table1;

Drop (2 ways)

DROP TABLE table [CASCADE CONSTRAINTS] PURGE;

DROP TABLE table;
PURGE TABLE table;

Truncate (can't rollback) (also see delete rows)

TRUNCATE TABLE table;

Rename (2 ways)

RENAME table1 TO table2; 

ALTER TABLE table RENAME TO table;

Comment

COMMENT ON TABLE table IS 'some table comment';

V I E W S

Create

CREATE [OR REPLACE] VIEW view
 AS SELECT *
     FROM table1
   [WITH READ ONLY];

CREATE [OR REPLACE] VIEW view
(
 field1
, field2
)
 AS SELECT fielda, fieldb
      FROM table1; 

Drop

DROP VIEW view;

C O N S T R A I N T S

Add

ALTER TABLE table
ADD CONSTRAINT pk_constraint PRIMARY KEY (field);
ADD CONSTRAINT fk_constraint FOREIGN KEY (field) REFERENCES table2 (field);
ADD CONSTRAINT uk_constraint UNIQUE      (field);
ADD CONSTRAINT ck_constraint CHECK       (field = value);
MODIFY (field NOT NULL);

Disable

ALTER TABLE table
DISABLE PRIMARY KEY [CASCADE];
DISABLE CONSTRAINT uk_constraint;

Drop

ALTER TABLE table
DROP PRIMARY KEY CASCADE;
DROP CONSTRAINT constraint;
DROP UNIQUE (field);
MODIFY (field NULL);

Enable

ALTER TABLE table
ENABLE [NOVALIDATE] PRIMARY KEY;
ENABLE CONSTRAINT uk_constraint;

I N D E X E S

• a B*-tree is typical - matches column values to their related RowIDs

Create

CREATE UNIQUE INDEX index
ON table1(field1,field2)         /* if multi-column, put most accessed column first */
[REVERSE];

Add Primary Key

ALTER TABLE table
ADD PRIMARY KEY (field);

Bitmap Index

• Use only if data infrequently updated
• Use only in batch processing
• Only on column with just a few values
• i.e.,

         Y 00010011
        N 11101100

CREATE BITMAP INDEX indexname ON table(column);

Create Index for Subpartition

CREATE INDEX index ON table1(field1)
  LOCAL         /* Create a separate index for each partition of table */
(
 PARTITION part1 TABLESPACE tablespace,
  PARTITION part2 TABLESPACE tablespace
);

Drop

DROP INDEX index;

Rebuild

• Rebuilt from the index so there must be enough space for original and new.
• 'Online' allows rebuild while being accessed.
  

ALTER INDEX index REBUILD [ONLINE]
STORAGE (INITIAL 10m NEXT 5m PCTINCREASE 0)
TABLESPACE tablespace; 

C O L U M N S

Add

ALTER TABLE table  ADD (field data_type);

Add a Not Null Column (3 steps)

ALTER TABLE table
 ADD (field data_type);
UPDATE table
 SET column =(
             SELECT column
               FROM table2
              WHERE table1.key = table2.key
            );
ALTER TABLE table
 MODIFY (field NOT NULL);

Drop (option 1 - 2 steps)

ALTER TABLE table  SET UNUSED COLUMN field;
ALTER TABLE table DROP UNUSED COLUMNS;

Drop (option 2)

ALTER TABLE table
DROP COLUMN field1        ; -- one column
DROP        (field1, field2); -- more than one column

Rename

ALTER TABLE table RENAME COLUMN current_column TO new_column;

Change Length

ALTER TABLE table  MODIFY (field data_type);

Decrease Length (4 steps)

CREATE TABLE table2 (key, column)
AS
  SELECT key, column
    FROM table1;

UPDATE table1
  SET column = NULL;

ALTER TABLE table1
 MODIFY (column VARCHAR2(5));

UPDATE table
  SET column =(SELECT column
                 FROM table2
                WHERE table1.key = table2.key); 

Comment

COMMENT ON COLUMN table.field IS 'column comment';

D A T A ( D M L )

Insert (2 ways)

INSERT INTO table
VALUES ('xyz',123);

INSERT /*+ APPEND */ INTO table      -- hint is optional
  SELECT  field1, field2
    FROM table1;
COMMIT;                              -- set autocommit off/on/#

Change

UPDATE table
  SET field1 = 'value' ,
       field2 =  value
WHERE key = value;
COMMIT;

Delete All Rows

DELETE FROM table;
ROLLBACK;

Delete Some Rows

DELETE FROM table where field1 = value;
COMMIT;

R E C Y C L E B I N

Purge

PURGE RECYCLEBIN;

C O M M I T / R O L L B A C K

Note: Exit, Quit or any DDL statement force a commit.
If the machine goes down, an automatic rollback occurs.
Set autocommit off;  -- preferred

Savepoint

Insert ...
Delete ...
Savepoint A;

Insert ...
Delete ...
Savepoint B;

Select ...
Rollback to savepoint B;

Use Variables to Insert Data

In this example, a user is asked for three kinds of variables: Character, Date & Number. They are then inserted into an 'INSERT INTO' statement. (In entering the date, the year is assumed to be the current one.) After insertion, a SELECT is performed to show the user the data entered as it is in the table.

Set Verify Off
Set Feedback Off
Prompt Please Enter...

--+------------------------------------------------------------------
--| Ask for data
--+------------------------------------------------------------------

Accept Who                 Prompt 'Name: '
Accept Month   Format 99   Prompt 'Month: '
Accept Day     Format 99   Prompt 'Day: '
Accept Amount              Prompt 'Amount: '

--+- 1 --------------------------------------------------------------
--| Insert the record based on the values typed (Assume current year)
--+------------------------------------------------------------------

Insert Into TestData Values
     (
        Test_Seq.NextVal
        ,
        Upper('&Who')
        ,
        &Day
        || '-'
        || Decode(&Month, 1,'JAN',  2,'FEB',  3,'MAR',  4,'APR',
                          5,'MAY',  6,'JUN',  7,'JUL',  8,'AUG',
                          9,'SEP', 10,'OCT', 11,'NOV', 12,'DEC',
                  '???')
        || '-'
        || To_Char(Sysdate,'YY')
        ,
        &Amount
     );
Commit;

--+- 2 --------------------------------------------------------------
--| Get the current value
--+------------------------------------------------------------------

Set Term Off

Column CurrVal New_Value CurrVal

Select Test_Seq.Currval CurrVal
 From Dual;

--+- 3 --------------------------------------------------------------
--| Display the record just entered.
--+------------------------------------------------------------------

Set Term On

Select *
 From Test_Data
Where Test_Id = &CurrVal
;
Undefine Who Month Day Amount
Set Feedback On
Set Verify On

Oracle documentation:

Accept,

Dates

A date needs to be entered either in the standard format (dd-mon-yy) as shown in the first example:

INSERT INTO EMP VALUES(1234,'MICKEY','CEO',7249,
                       '10-JUL-08',
                       1000,500,10
                      );

or you must use the TO_DATE function which tells the system the format of the date.

INSERT INTO EMP VALUES(1235,'MINNEY','DBA',7249,
                       TO_DATE('10/07/2008','MM/DD/YYYY'),
                       1000,500,10
                      );

Use To_Char to display a date in a format other than dd-mon-yy

Select To_Char(Sysdate,'mm/dd/yyyy') Today
  From Dual;


Today
----------
05/29/2008