oracle从入门到精通第五篇(视图|表空间|索引|存储过程|触发器|复制表)

视图：view

假表，用查询结果动态生成一张表
视图是编译后将查询结果保存在数据库中，下次在查询的时候不用编译，可以直接从视图中获取数据。

创建视图

CREATE VIEW v_name AS SELECT
student.NAME stuname,
school.NAME 
FROM
	student
	JOIN school ON student.sid = school.id;
1
2
3
4
5
6

查看视图：

select * from v_name;
1

删除视图：

drop view v_name;
1

为什么使用视图？
节省编译时间，提高查询效率
屏蔽原表中的字段，避免没有权限的用户查看
简单视图的数据能够动态来源于原表，复杂视图可以手动编译（alter view 视图名 compile;）

表空间：tablespace

在一段内存中因为多数存储的是表，所以称为表空间

Oracle当中每个用户都有属于自己的表空间
用户的表空间
普通用户的表空间
系统用户的表空间

为什么要给普通用户创建属于自己的表空间？
项目中很多时候会遇到多个项目访问同一个数据库，多个用户在访问同一个数据库的时候会产生资源争用问题，给不同的用户指定不同的表空间，就可以解决资源争用问题。

创建表空间：

create tablespace table_user   
datafile 'D:\table_user.dbf'     -- 文件位置
size 5M                                   -- 初始大小
autoextend on next 5M         -- 下一次拓展多少
maxsize 100M;                        -- 最大值
1
2
3
4
5

创建用户指定默认的表空间：

create user ET191201 identified by etoak
default tablespace table_user;
1
2

创建用户未指定表空间：

create user ET191202 identified by etoak;
1

创建用户以后修改指定表空间：

alter user ET191202 default tablespace table_user;
1

删除表空间：

删除表空间后，原先指向该表空间的用户仍然指向该表空间
需要通过alter user命令将用户的表空间重新指定到有效表空间

drop tablespace table_user including contents and datafiles;
1

索引

数据库会在具有唯一约束的列上自动添加唯一性索引

创建索引：

create index 索引名 on 表名(列名);
create index ind_name on student(salary);
1
2

删除索引：

drop index ind_name;
1

如何查看索引：

select table_name,index_name,uniqueness,status from user_indexes
where table_name = 'STUDENT';
1
2

索引类型：

普通索引：normal

create index 索引名 on 表名(列名);
create index ind_name on student(salary);
1
2

唯一性索引：unique

create unique index 索引名 on 表名(列名);
create unique index ind_cid on student(cid);
1
2

位图（分类）索引：bitmap

数据量比较大，基数比较小

create bitmap index 索引名 on 表名(列名);
create bitmap index ind_sid on student(sid);
1
2

函数索引：

create index 索引名 on 表名(函数(列名));
create index ind_length on student(length(name));
1
2

创建索引的优缺点：
能够更快的查询数据，有效的提高查询效率
数据量多，查询多，增删改少的列适合添加索引
增删改数据的时候，数据库会浪费资源去维护索引

存储过程：procedure

在服务器端能够被一个或者多个程序调用的sql语句集

创建存储过程：

create procedure 存储过程名(参数名 in 参数类型,参数名 out 参数类型)
as
变量名 变量类型 := 值;
begin 
sql语句集;
end;
1
2
3
4
5
6

存储过程可以没有参数，如果没有参数的话则过程名之后不要出现括号
传入参数用in表示，传出参数用out表示
存储过程没有返回值，而是通过传出参数进行返回数据
可以多个传入参数，也可以有多个传出参数

练习：
如果传入参数是1，则返回你好
如果传入参数是2，则返回再见

create procedure pro_hi(cr in number,cc out varchar)
as
begin
if cr = 1 then cc := '你好';
else if cr = 2 then cc := '再见';
end if;
end if;
end;
/
1
2
3
4
5
6
7
8
9

调用存储过程：

declare 变量 变量类型 := 初始值;
begin 
sql 语句集;
end;
1
2
3
4

开启输出行：
set serveroutput on;

declare val varchar2(20) := '';
begin
pro_hi(1,val);
dbms_output.put_line(val);
pro_hi(2,val);
dbms_output.put_line(val);
end;
/
1
2
3
4
5
6
7
8

触发器：trigger

for each row：行级触发器

创建触发器：

create or replace trigger 触发器名
before/after insert/delete/update on 表名
for each row
begin
sql语句集;
end;
1
2
3
4
5
6

Oracle通过触发器调用序列来实现主键自增
1.创建表

create table test(id number(5) primary key,name varchar2(20))
1

2.创建序列

create sequence seq_test;
1

3.创建触发器

create or replace trigger tri_id
before insert on test
for each row
begin
select seq_test.nextval
into:new.id
from dual;
end;
/
1
2
3
4
5
6
7
8
9

4.插入数据

insert into test (name) values('橘子');
insert into test (name) values('苹果');
1
2

5.查询数据

select * from test;
1

通过触发器实现对表内数据操作的监测
1.创建日志表

create table dept_log(name varchar2(20),time date);
1

2.创建触发器

create or replace trigger tri_dept_log
before insert or delete or update on dept
declare val varchar2(20);
begin
if inserting then val := '增加数据';
elsif deleting then val := '删除数据';
elsif updating then val := '修改数据';
end if;
insert into dept_log values(val,sysdate);
end;
/
1
2
3
4
5
6
7
8
9
10
11

3.操作数据

insert into dept values(60,'教学部','山东易途');
update dept set loc = '中国易途' where deptno = 60;
delete from dept where deptno = 60;
1
2
3

4.查看日志表

select * from dept_log;
1

复制表：

1.复制表结构和表数据

create table dept2 as select * from dept;
1

2.复制表结构不复制表数据

create table dept3 as select * from dept where 1 = 2;
1

3.复制表，复制指定的几个列

create table dept4 as select deptno,dname from dept;
1

4.复制表，将新表中表的列名更改为其他名称

create table dept5(deptno1,dname1) as select deptno,dname from dept;
1

5.向已经存在的表中插入数据

insert into dept3(deptno,dname) select deptno,dname from dept;
1