进度记录【Day 14】Oracle触发器、账号与权限、存储结构

Day 14

• 内容比较简略，详细的内容通读文件。

触发器

• 触发器首先是一个特殊的存储过程
• 写一个函数或者存储过程的定义，代码“主动”调用这个存储过程
• dbms自动对触发器的调用
• 一般来说设定对表进行DML操作，这就是触发条件

create [or replace] trigger 名称 [before/after] insert [or delete or update] on 表名 
[for each row]
declare

begin
		
end;

• 在删除学生表数据时备份删除的数据

create or replace trigger backHist after delete on student
for each row
begin
	insert into backStu(sid,sname,sage,ssex,snativeplace,smajor,sclass)
	values(:OLD.sid,:OLD.sname,:OLD.sage,:OLD.ssex,:OLD.snativeplace,:OLD.smajor,:OLD.sclass);
end;

• 创建一张学生最高分历史表，表中用于记录学生的最高分选课记录(sid,cid,cmark)；
  同时创建一张课程最高分历史表，表中用于记录课程的最高分选课记录(sid,cid,cmark)；
  当对学生的成绩表进行修改成绩的时候，请将新的最高分选课信息分别更新到上述两张表中。

create table histStu
as select sid,cid,cmark from student;

create table histCourse 
as select sid,cid,cmark from student;

create or replace trigger recordHist after insert or update or delete on mark
for each row
declare
	stuCid	mark.cid%type;
	stuMark	mark.cmark%type;
	corSid 	mark.sid%type;
	corMark	mark.cmark%
begin
	select cid,cmark into stuCid,stuMark from (
		select * from mark where sid=:OLD.sid order by cmark desc
	) where rownum=1
	update histStu(sid,cid,cmark) values(:OLD.sid,stuCid,stuMark);

	select sid,cmark into corSid,corMark from (
		select * from mark where cid=:OLD.cid order by cmark desc
	) where rownum=1;
	update histCor(sid,cid,cmark) values(corSid,:OLD.cid,corMark);
end;

账号与权限

• 创建账号

create user 用户名
create user 用户名 identified by "密码"
default tablespace 表空间名
temporary tablespace 临时表空间名
profile default
quota 50m on account;
-- 此时该用户没有任何权限，无法访问、修改数据库
-- 如果账号用create user 用户名 直接创造，该账号处于锁定状态，需要unlock，没有密码则需要设定密码
alter user 用户名 account unlock
alter user 用户名 identified by "密码" account unlock
-- 锁定账户
alter user 用户名 account lock

• 权限管理：grant to、revoke from

-- 将session权限给用户：建立对话关系
grant create session to 用户名;
-- 将查找某个表的权限给用户
grant select on 表名 to 用户名;
-- DBA用户收回某用户的连接数据库的权限）（系统权限只能由DBA用户回收）
revoke connect from 用户名;
-- 权限传递
-- 如果取消该权限，对于这个用户使用with grant option授权的用户来说，同样还会取消这些用户的权限
grant create to 用户名 with grant/admin option;

• 删除账号

drop user 用户名;			//用户没有建任何实体
drop user 用户名 CASCADE;	//将用户及其所建实体全部删除
-- 当前连接的用户不能删除

• 命令行客户端进入数据库

>sqlplus hr/123456  -- 在命令行登录hr用户
>conn /as sysdba    -- 连接数据库，如果要实现表格创建、删除，需要以SYSDBA连接

-- 上面两步可以直接简化为下面的
> conn hr/123456@XE as sysdba

• 表空间：创建用户时可以指定表空间
• 分为默认表空间和临时表空间，一般的查询语句一般缓存在默认表空间，当查询为笛卡尔积之类的需要较大内存空间时，会使用临时表空间；临时表空间是一个动态的过程，不需要时会消失。
• 登录的方式不同，进入的表空间也不同。（以SYSDBA登录之类的）

-- 创建默认表空间：服务器端创建。
create tableplace 表空间名
datafile '路径'
size 100M;
-- 创建临时表空间
create temporary tableplace 表空间名
tempfile '路径名'
size 50m
autoextend on
next 50m maxsize 20480m
extend management local;
-- 查看用户的表空间[从字典中查]
select username,default_tablespace,temporary_tablespace from dba_users;

• profile是一个文件，这个文件中保存的是用户的配置和一些账号相关的数据

角色

• 创建账号后给各种权限比较复杂，将多个权限首先赋予一个角色，然后将这个角色授予某个账号
• 可以将这个角色在此授予更多的账号，也就是说只需要授予权限一次

create role develop
create role tester
create role producer
grant create session,select or update or insert on student to develop
grant create session,select or update or delete on student to tester

grant develop to 用户名
grant tester to 用户名

• 系统的三个主要角色

CONNECT
拥有访问数据库的权限

RESOURCE
拥有访问数据库中数据的权限（增删改查）

DBA
拥有对数据库中表结构进行操作的权限

• 系统提供的特殊账号：PUBLIC 表示所有账号

grant select on hr.student to public
-- 之后所有人都能访问该表

• 查询拥有系统权限的用户

select * from DBA_SYS_PRIVS

存储结构

• 逻辑结构——物理结构
  

逻辑结构

• DB是由多个DB instance构成
  一个DB instance是由一个或多个TableSpace构成的
  而一个TableSpace是由至少一个DB File构成
• 磁盘最小的存储单位是512个字节，称为一个扇区
• 数据库中最小的存储单位是block（块），8K
• block逻辑上连续，物理空间上不连续
• 顺序：表空间-段-区-块

表空间

• 表空间是一个逻辑概念，物理上对应一个或多个数据文件datafile或临时文件tempfile，逻辑上表空间是存储段的容器。
• 多个用户可能共用一个表空间，为了区分用户，在表空间中每个用户都有一个对应的方案，用于保存单个用户的信息。

游标

• 隐形游标

for line in (select * from student where ssex='男') loop
	line
end loop;

• 显示游标：结果是多行的查询语句
• 查询前10名员工的信息

DECLARE
   CURSOR c_cursor 
   IS SELECT first_name || last_name, Salary 
   FROM EMPLOYEES 
   WHERE rownum<11;   
   v_ename  EMPLOYEES.first_name%TYPE;
   v_sal    EMPLOYEES.Salary%TYPE;   
BEGIN
  OPEN c_cursor;
  FETCH c_cursor INTO v_ename, v_sal;
  WHILE c_cursor%FOUND LOOP
     DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) );
     FETCH c_cursor INTO v_ename, v_sal;
  END LOOP;
  CLOSE c_cursor;
END;