add

About Me

My photo
Oracle Apps - Techno Functional consultant

Friday, June 29

Oracle DML, DDL and Constraint Statements


DML Statements

Add a new row to the table:

INSERT INTOdepartments
            (location, dname, department_id)
VALUES
            (‘Beijing’, ‘Public Relations’, 50);

INSERT INTO employees
VALUES
            (9013, USER, 'MANAGER', 7788, ADD_MONTHS (SYSDATE, -36), DEFAULT, '', NULL);

INSERT INTO employees_copy
VALUES
            (SELECT empno, ename, job, mgr, hiredate  --?
             FROM employees);

Modifies existing rows in the table:

UPDATE departments
SET    dname = 'Engineering',
       location = DEFAULT
WHERE  department_id = 50;

UPDATE employees
SET    ename = (SELECT ename FROM emp WHERE empno = 7844),
       mgr = DEFAULT
WHERE  empno = 7944;

Removes existing rows from the table

DELETE FROMdepartments/ DELETE FROM departments
WHERE  department_id = 50;

Conditionally inserts / updates data in a table

MERGE INTOdepartments
WHERE  department_id = 50;

DDL Statements

Creates a table

CREATE TABLEdepartments_copy
            (department_id           NUMBER(2),
             dname                        VARCHAR2(14) DEFAULT‘Administration’);

Modifies table structures

ALTER TABLEdepartments_copy
ADD / MODIFY/ DROP COLUMN / SETUNUSED ...
...

Adding a Column

ALTER TABLEdepartments_copy
ADD    (dcategory VARCHAR2(9) DEFAULT ‘General’,
              mgr_head  NUMBER(5)      DEFAULT12345);

Modifying a Column

ALTER TABLEdepartments_copy
MODIFY (dcategory VARCHAR2(30) DEFAULT ‘Private’,
              mgr_head      VARCHAR2(10) DEFAULT‘None’);

Renaming a Column

ALTER TABLEdepartments_copy
RENAME COLUMN (dcategory TO dept_cat);

Dropping a Column[s]

ALTER TABLEdepartments_copy
DROP COLUMN     dcategory;/ DROPCOLUMN (mgr_head [, dcategory, ...]);

SET UNUSED Option

ALTER TABLEdepartments_copy
SET UNUSED (dcategory, mgr_head);

ALTER TABLEdepartments_copy
DROP UNUSED COLUMNS;

Removes the rows and table structure

DROP TABLEdepartments_copy;

Changes the name of a table

RENAME [TABLE] departments_copy TO detail_dept;
OR
ALTER TABLEdepartments_copy
RENAME TOdetail_dept;

Removes all rows from a table

TRUNCATE TABLE detail_dept;  --Releasesthe storage space,                             cannot rollback.
OR
DELETE         detail_dept;               --Do not release the storage space,                   can-be rollback.

Explicit Locking for all SQL statements except SELECT

SELECT * FROM detail_dept
FOR UPDATENOWAIT;

Add Comments to a column/ table/ view (Delete Comments by setting it to empty string i.e. ‘’.)

COMMENT ON TABLE departments_copy          /           COMMENT ON COLUMNdepartments_copy.dname
IS‘Copy of Departments table’;                    IS‘This column has Department Names’;
USER_TAB_COMMENTS                                                            USER_COL_COMMENTS

CONSTRAINTS (using CREATE TABLE)

                                                                         Column-Level only
NOT NULL Constraints
CREATE TABLE employees
            (employee_id  NUMBER(2),
             last_name                   VARCHAR2(25) NOT NULL,          --Oracle Server Named Cons.
             salary              NUMBER(8,2),
             commission_pct         NUMBER(2,2),
             hire_date                    DATE             CONSTRAINT emp_hire_date_nn
                                                                         NOT NULL    --User Named Cons.
            );

                                                                         Column-Level
             Table-Level
UNIQUE Constraints
CREATE TABLE employees
            (employee_id  NUMBER(2)  UNIQUE,        --Oracle Server Named Cons.
             last_name                   VARCHAR2(25) NOT NULL,
             nic_no                        NUMBER(14),
             passport_no    NUMBER(7),
             cell_no                        NUMBER(11)   CONSTRAINTemp_cell_no_uk
                                                                         UNIQUE,        --User Named Cons.
             UNIQUE(nic_no),
             CONSTRAINTemp_passport_no_uk UNIQUE (passport_no)
            );

PRIMARY KEY Constraints
CREATE TABLE employees
            (CONSTRAINT emp_passport_no_pk PRIMARY KEY (passport_no),
             employee_id   NUMBER(2)  PRIMARY KEY, --Oracle Server Named Cons.
             last_name                   VARCHAR2(25) NOT NULL DISABLE,
             nic_no                        NUMBER(14),
             passport_no    NUMBER(7),
             sal                               NUMBER(5)  CHECK(sal>5000) DISABLE,
             cell_no                        NUMBER(11)            CONSTRAINT emp_cell_no_pk
                                                                         PRIMARY KEY, --User Named Cons.
             PRIMARY KEY (nic_no)
            );

FOREIGN KEY Constraints
CREATE TABLE employees
            (manager_id                NUMBER(2)  REFERENCESemployees(employee_id),
                                                                                                  --Oracle Server Named Cons.
             employee_id   NUMBER(2)  PRIMARY KEY,
             department_id,
             location_id                             CONSTRAINT emp_location_id_fk
                                                                         REFERENCESlocation,
                                                                                                  --User Named Cons.
             last_name                   VARCHAR2(25) NOT NULL,
             test_id                        NUMBER(2),
             CONSTRAINTemp_department_id_fk
             FOREIGN KEY (department_id) REFERENCESdepartment
             ON DELETE CASCADE / ON DELETE SET NULL
             FOREIGN KEY (test_id) REFERENCESemployees(employee_id),
            );

CHECK Constraints
CREATE TABLE employees
            (employee_id  NUMBER(2), CHECK (employee_id > 5000),
                                                                                                  --Oracle Server Named Cons.
             salary              NUMBER(8,2)  CONSTRAINTemp_salary_min_chk
                                                                         CHECK(salary > 0)
                                                                         DISABLE       --User Named Cons.
             CHECK(employee_id > 5000),
             CONSTRAINTemp_salary_min_chk CHECK(salary > 0)
            );

CONSTRAINTS (using ALTER TABLE)

Adding/ Dropping/ Disabling/ Enabling Constraints (Using Constraint Name)
ALTER TABLE employees
ADD/ DROP/ DISABLE/ ENABLE CONSTRAINTSYS_C003553;

Adding Constraints (Disabled Explicitly) – Table-Level only except NOT NULL
ALTER TABLE employees
MODIFY (manager_id                      NOT NULL
                                                            DISABLE);  --Oracle Server Named Cons.
ALTER TABLE employees
MODIFY (manager_id                      CONSTRAINTemp_manager_id_nn
                                                            NOT NULL
                                                            DISABLE);  --User Named Cons.
ALTER TABLE employees
ADD                                                   UNIQUE(manager_id)
                                                            DISABLE;                   --Oracle Server Named Cons.
ALTER TABLE employees
ADD                                                   CONSTRAINT emp_manager_id_uk
                                                            UNIQUE(manager_id); --User Named Cons.
ALTER TABLE employees
ADD                                                   PRIMARY KEY(manager_id)
                                                            DISABLE;                   --Oracle Server Named Cons.
ALTER TABLE employees
ADD                                                   CONSTRAINT emp_manager_id_pk
                                                            PRIMARY KEY(manager_id)
                                                            DISABLE;                   --User Named Cons.
ALTER TABLE employees
ADD                                                   FOREIGN KEY(manager_id) REFERENCES employees
                                                            DISABLE;                   --Oracle Server Named Cons.
ALTER TABLE employees
ADD                                                   CONSTRAINT emp_manager_id_fk
                                                            FOREIGN KEY(manager_id) REFERENCESemployees
                                                            DISABLE;                   --User Named Cons.
ALTER TABLE employees
ADD   (                                               CHECK         (duty_pct < 10));                                                                                                                                                           --Oracle Server Named Cons.
ALTER TABLE employees
ADD   (                                               CONSTRAINT emp_duty_chk
                                                            CHECK         (duty_pct < 10));
                                                                                      --User Named Cons.

ALTER TABLE employees                                                   --Add a new column with Cons.
ADD   (duty_pct        NUMBER(2,2)           CHECK         (duty_pct < 10)
                                                            NOT NULL,                --Oracle Server Named Cons.

             department_id,                       CONSTRAINT emp_department_id_fk
                                                            FOREIGN KEY(department_id) REFERENCESdepartments
                                                            DISABLE);                  --User Named Cons.

Dropping/ Disabling Constraints
ALTER TABLE employees
DROP    PRIMARY KEY/ UNIQUE (email) [CASCADE];/
DISABLE PRIMARY KEY/ UNIQUE (email) [CASCADE];

Dropping Table/ Column having Constraints defined
DROP TABLE employees/ DROP COLUMN employee_id
CASCADE CONSTRAINTS; --To Drop an object other than Cons., tell Oracle to effect on its Cons. also,
                                          if there any exist, using CONSTRAINTS keyword after CASCADE.
Enabling Constraints
ALTER TABLE employees
ENABLE PRIMARY KEY/ UNIQUE (email);

Renaming Constraints
ALTER TABLE employees
RENAME CONSTRAINTcust_fname_nn TOcust_firstname_nn;

CONSTRAINTS (using CREATE TABLE)

PRIMARY KEY Constraints
CREATE TABLE employee
            (CONSTRAINT emp_employee_id_pk PRIMARY KEY (employee_id),
             NOT NULL(salary)
             CHECK    (salary>=5000)    DISABLE,
             UNIQUE   (email)                             DISABLE,
             employee_id   NUMBER(4),
             manager_id                                        REFERENCES employee
                                                                         ON DELETE CASCADE,
             last_name                   VARCHAR2(25) DEFAULT   ‘Public User’
                                                                         NOT NULL
                                                                         DISABLE,
             email               VARCHAR2(25) DEFAULT   ‘user@kingtest.com.uk’, --???
             department_id,                                   FOREIGN KEY (department_id)
                                                                         REFERENCESdepartments
                                                                         ON DELETE SET NULL,
             salary              NUMBER(5)
            );

Other Database Objects

INDEXES

Creating an Index

CREATE INDEX emp_sal_01                                                         --Non-Unique Index
ON emp (sal);

CREATE UNIQUE INDEXemp_empno_01                                 --Unique Index
ON emp (empno);

CREATE UNIQUE INDEXemp_empno_ename_indx_01           --Composite Index
ON emp (empno, ename);

CREATE INDEX emp_ename_reverse_indx                                  --Reverse Key Index
ON emp (ename) REVERSE;

CREATE BITMAP INDEXemp_deptno_indx_01                       --Bitmap Index
ON emp (deptno);

CREATE INDEX ixd_emp_01                                                        --Function-Based Index
ON emp (sal * 1.08);

Renaming an Index

ALTER INDEX upper_ix
RENAME TO upper_name_ix;

Dropping an Index

DROP INDEX upper_ix;

SYNONYMS

Creating a Synonym

CREATE SYNONYM my_private_synonym                                 --Private Synonym by Default
FOR emp;

CREATE PUBLIC SYNONYM like_this                          --Public Synonym
FOR scott.emp;

Dropping a Synonym

DROP SYNONYM emp_sal;