首页 > 资讯 > Oracle > 正文

Oracle SQL基础(3)-索引/触发器/视图操作

15-12-03 来源：[db:作者]

收藏我要投稿

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;

点击复制链接与好友分享!回本站首页