Oracle SQL基礎(3)-索引/觸發器/視圖操作
1.索引
a.創建
create index idx_name on fdh_client_info(name);--普通索引（單列索引）
create unique index uni_idx_id on fdh_client(id); --唯一索引
create index union_idx_name_addr on fdh_client(name, address);--聯合索引
b.查詢索引
select * from user_indexes;
select * from all_indexes;
c.重建索引
alter index idx_name rebuild online;--重建索引時不鎖表
alter index idx_name rebuild tablespace tablespace_name;--重建時指定索引存儲的表空間
d.釋放索引中無用空間
alter index idx_name deallocate unused;
e.整理索引碎片
alter index idx_name coalesce;
f.刪除索引
drop index idx_name;
2. 觸發器
以下語句學習自慕課網（http://www.imooc.com/learn/414）
a.創建語句級觸發器(沒有 for each row)
執行安全檢查：禁止在非工作時間插入員工信息
create or replace trigger security_emp
before insert on emp
begin
if to_char(sysdate, 'day') in ('星期六', '星期日') or
to_number(to_char(sysdate, 'hh24')) not between 9 and 17 then --9點到18點
raise_application_error(-20001, '非工作時間禁止插入新員工');
end if;
end;
b.創建行級觸發器
數據檢查：漲後的工資不能比漲前少(偽記錄變量:old和:new分別表示更新前後的那一條記錄)
create or replace trigger check_salary
before update
on emp
for each row --行級觸發器
begin
if :new.sal < :old.sal then
raise_application_error(-20002, '漲後的工資不能比漲前少. '
|| '漲後的工資:' || :new.sal || ' 漲前的工資:' || :old.sal);
end if;
end;
c.數據庫審計（員工漲後薪水大於6000，審計員工信息）
-- 創建薪水審計表
create table audit_emp_sal(
empno number(4, 0),
ename varchar2(10),
newsal number(7,2),
incdate date
)
--創建員工測試表
create table emp_2 as select * from emp;
--數據庫審計:漲後薪水大於6000,其員工信息插入審計表
create or replace trigger do_audit_emp_sal
after update
on emp_2
for each row
begin
if :new.sal > 6000 then
insert into audit_emp_sal
values (:new.empno, :new.ename, :new.sal, sysdate);
end if;
end;
d.數據庫備份和同步
--創建備份表
create table emp_back as select * from emp;
--數據庫的備份和同步(利用觸發器進行同步備份)
create or replace trigger sync_emp_sal
after update
on emp
for each row
begin
update emp_back b set b.sal = :new.sal where b.empno = :new.empno;
end;
3.視圖
視圖本身不包含數據，存儲的是一條select語句的查詢結果；視圖是基於若干表或視圖的邏輯表，這裡的表稱作基表，通過視圖可以查詢或修改基表的數據。
視圖分為簡單視圖和復雜視圖；簡單視圖從單表獲取數據，不包含函數和數據組，可以執行DML操作，復雜視圖相反。
a.語法
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
注：FORCE：不管基表是否存在ORACLE都會自動創建該視圖；
NOFORCE：只有基表都存在ORACLE才會創建該視圖：
alias：為視圖產生的列定義的別名；
subquery：一條完整的SELECT語句，可以在該語句中定義別名；
WITH CHECK OPTION ： 插入或修改的數據行必須滿足視圖定義的約束；
WITH READ ONLY ： 該視圖上不能進行任何DML操作
b.示例
create or replace view dept_statistics
(name,minsal,maxsal,avgsal)
as select d.dname,min(e.sal),max(e.sal),avg(e.sal)
from emp e,dept d
where e.deptno=d.deptno
group by d.dname;
