SQL语句PART6

Differring Constraints:
Constraints can have the following attributes: DEFFERRABLE / NOT DEFFERRABLE,  INITIALLY DEFFERRED / INITIALLY IMMEDIATE.
e.g.:
alter table dept2 add constraint dept2_id_pk primary key (department_id) deferrable initially deferred; // deferring constraint on creation.
set constraints dept2_id_pk immediate // changing a specific constraint attribute.
alter session set constraints=immediate // changing all constraints for a session.


Difference between initially deferrable and initially immediate
initially deferred: waits to check the constraints until the transaction ends.
initially immediate: checks the constraint at the end of the statement execution.


Dropping a constraint:
1. Removing the manager constraint from the EMP2 table:
alter table emp2 drop constraint emp_mgr_fk;
2. Removing the primary key constraint on the dept2 table and drop the associate foreign key constraint on the emp2.department_id column:
alter table dept2 drop primary key cascade;


Disabling Constraint
1. execute the disable clause of the alter table statement to deactivate an integrity constraint.
2. apply the cascade option to disable dependent integrity constraints.
eg: alter table emp2 disable constraint emp_dt_fk;


Enabling Constraints
1. activate an integrity constraint currently disabled in the table definition by using the enable clause.
2. a unique index is automatically created if you enable a unique key or a primary key constraint.
e.g.: alter table emp2 enable constraint emp_dt_fk;


Cascading constraints
1. used along with the drop column clauses.
2. drops all referential integrity constraints that refer to the primary key and unique keys defined on the dropped columns. 3. drops all multicolumn constraints defined on the dropped columns.
e.g.:
alter table emp2 drop column employee_id cascade constraints
alter table test1 drop (col1_pk, col2_fk, col1) cascade constraints;


e.g. foreign key example1:
create table depart(departid number primary key, deptname varchar2(200));
create table emp(empid number primary key, firstname varchar2(200), lastname varchar2(200), departid number);
alter table emp add constraint emp_dept_fk foreign key(departid) references depart(departid) on delete cascade on update cascade;
so:
table depart:
departid        departname
1                    depart1
4                    depart4
table emp:
empid        firstname        lastname        departid
1                fn1                ln1                    4
2                fn2                ln2                    1
e.g.:
update depart set departid=5 where departid=1;
result:
table depart:
departid           departname
1                    depart1
5                    depart4
table emp:
emp1                ...... departid
1                               5
2                               1


Renaming Table columns and constraints:
1. use rename column of alter table statement to rename table columns:
e.g.: alter table marketing rename column team_id to id;
2. use rename constraint clause of the aler table statement to rename any existing constraint for a table:
e.g.: alter table marketing rename constraint mktg_pk to new_mktg_pk;


Creating Indexes:
Indexes are created: 1) Automatically when PRIMARY KEY creation and UNIQUE KEY creation. 2) Manually when using CREATE INDEX statement and The CREATE TABLE statement
e.g. automatically:
CREATE TABLE NEW_EMP(employee_id NUMBER(6) PRIMARY KEY USING INDEX (CREATE INDEX emp_id_idx ON NEW_EMP(employee_id)), first_name VARCHAR2(20), last_name VARCHAR2(25));
then you can find it by :
SELECT INDEX_NAME, TABLE_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'NEW_EMP';
e.g.: manually:
CREATE INDEX upper_dept_name_idx ON dept2(UPPER(department_name));
e.g.: remove index:
DROP INDEX index_name;
** to drop an index, you must be the owner of the index or you have DROP ANY INDEX privileges.
e.g.: drop table table_name purge;
**: Normally, a table is moved into the recycle bin (as of Oracle 10g), if it is dropped. However, if the purge modifier is specified as well, the table is unrecoverably (entirely) dropped from the database.


FlashBack statement:
** to recover the dropped table from recyclebin
grammer:
FLASHBACK TABLE[schema.]table[,[ schema.]table ]...TO { TIMESTAMP | SCN } expr [ { ENABLE | DISABLE } TRIGGERS ];
e.g.:
create table new_table1(id number primary key, name varchar2(200));
drop table new_table1;
select original_name, operation, droptime FROM recyclebin;
flashback table new_table1 to before drop
 

Data Dictionary Structure
USER     User's view(what is in your schema; what you own)       
ALL        Expanded user's view (what you can access)
DBA        Database administrator's view(what is in everyone's schemas)
V$        Performance_related data
e.g.:
describe dictionary;        // dictionary structure
select * from dictionary;  // dictionary data
select * from user_objects; // displaying all objects in your own schema (objects incl.: tables,sequences,indexes,etc)


Querying the Dictionary views:
tables info.:         USER_TABLES
columns info. :    USER_TAB_COLUMNS
constraint info.:     USER_CONSTRAINTS (constraints definition on your table), USER_CONS_COLUMNS (columns owned by you and specified in constraints).
view info.:         USER_VIEWS
sequences info.:     USER_SEQUENCES
index info.:         USER_INDEXES (provide information about indexes), USER_IND_COLUMNS (columns comprising your indexes and columns of indexes on your tables.)
synonym info.:     user_synonyms
** adding comments to a table
comment on table table1 is 'content...';
comment on column table1.col1 is 'contenting...';
comment info.:     ALL_COL_COMMENTS, USER_COL_COMMENTS, ALL_TAB_COMMENTS, USER_TAB_COMMENTS


Insert using  subqueries
e.g.
1. insert into(select p.nprocid,p.cprocname,p.ndocsortid
from wf_procname p join wf_docsort d on(d.ndocsortid=p.ndocsortid) and p.nprocid=1) values(1000,'abc',2)  // ok
2. insert into(select nprocid,cprocname
from wf_procname join wf_docsort using(ndocsortid) where nprocid=1) values(1001,'abcd') // ok
3. insert into(select nprocid,cprocname,ndocsortid
from wf_procname p join wf_docsort d using(ndocsortid) where nprocid=1) values(1002,'bcd',3) // error: ORA-01776: 无法通过联接视图修改多个基表
4. insert into(select nprocid,cprocname,ndocsortid
from wf_procname join wf_moduleinfo using(nmoduleinfoid) where nprocid=1) values(1002,'bcd',3) //error: ORA-01779: 无法修改与非键值保存表对应的列
5. insert into(select nprocid,cprocname
from wf_procname join wf_docsort using(ndocsortid)) values(1003,'bcd3') // ok
6. insert into(select nprocid,cprocname
from wf_procname join wf_docsort using(ndocsortid) where nprocid=11000) values(1004,'bcd3') // ok, even there is no nprocid=11000, still can be inserted into the table
7. insert into(select nprocid,cprocname
from wf_procname join wf_docsort using(ndocsortid) where nprocid=11000 with check option) values(1004,'bcd3') // error: ORA-01733: 此处不允许虚拟列
INSERT INTO ( SELECT location_id, city, country_id FROM loc WHERE country_id IN (SELECT country_id
FROM countries NATURAL JOIN regions WHERE region_name = 'Europe') WITH CHECK OPTION ) VALUES (3600, 'Washington', 'US');
prohibits from changing rows which is not in subquery


Multiple inserts
grammer:
INSERT [conditional_insert_clause]
[insert_into_clause values_clause] (subquery)
** conditional_insert_clause:
[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[ELSE] [insert_into_clause values_clause]
e.g.: (without conditional_insert_clause)
create table temp_t1(docsortid number primary key, docsortname varchar2(200));
create table temp_t2(moduleid number primary key, docsortkey varchar2(200));
create table temp_t3(nmoduleinfoid number, docsortname varchar2(200));
--drop table temp_t1 purge;
--drop table temp_t2 purge;
--drop table temp_t3 purge;
insert all into temp_t1 values(ndocsortid,cname)
              into temp_t2 values(nmoduleinfoid,key)
           into temp_t3 values(nmoduleinfoid,cname)
select ndocsortid,nmoduleinfoid, cname, key from wf_docsort where ndocsortid=1
e.g.: (with conditional_insert_clause)
INSERT ALL
WHEN ndocsortid >1 THEN 
INTO temp_t1 VALUES(ndocsortid, cname)
WHEN upper(key)='JIAOBAN' THEN
INTO temp_t2 VALUES(nmoduleinfoid, key)
WHEN NMODULEINFOID=4 THEN
INTO TEMP_T3 VALUES(NMODULEINFOID,CNAME)
select * from wf_docsort
e.g.: (conditional_insert_clause: insert first...)
INSERT first
WHEN ndocsortid <3 THEN
INTO temp_t1 VALUES(ndocsortid, cname)
WHEN ndocsortid between 3 and 5 then
INTO temp_t2 VALUES(ndocsortid, key)
else
INTO TEMP_T3 VALUES(ndocsortid,CNAME)
select * from wf_docsort
e.g.: (pivoting insert)
INSERT ALL
INTO temp_t1 VALUES (ndocsortid,cname)
into temp_t1 values (ndocsortid, key)
select * from wf_docsort;
** alter table temp_t1 drop constraint sys_c0036486 (drop primary key)