Oracle SQL基礎(3)-索引/觸發器/視圖操作，oraclesql

Oracle SQL基礎(3)-索引/觸發器/視圖操作，oraclesql

本文只列出索引，觸發器，視圖的簡單操作語句

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;