Oracle视图+序列+触发器+访问控制

一、视图

--创建简单视图(查询一个表)

CREATE or replace  VIEW emp_view as
  SELECT e.EMPLOYEE_ID,e.FIRST_NAME,e.SALARY from EMPLOYEES e;

CREATE or replace  VIEW emp_view(emp_id,e_name,e_salary)
  as
    SELECT e.EMPLOYEE_ID,e.FIRST_NAME,e.SALARY from EMPLOYEES e;

--查询视图
SELECT * from emp_view ev;

--创建复杂视图(查询多个表)
CREATE VIEW stu_score_view
 AS
  select STU."s_id",STU."s_name",s."score" from student stu,SC s
where STU."s_id"=s."s_id"

--简单视图的DML操作(复杂视图不可进行DML操作)
DELETE from emp_view e where e.emp_id=106;

二、序列

--创建一个序列
CREATE sequence emp_seq
 INCREMENT by 1
 start with 10
 maxvalue 20
 nocycle
 nocache
--使用序列
insert into SC values(emp_seq.nextval,2,10);

--创建索引
CREATE index sname_index
on STUDENT("s_name","s_sex");
--删除索引
drop index sname_index;

三、触发器
--触发器
--语法
create or replace Trigger tname
timing
event1 [or event2,event3....]
on TABLE
trigger_body

--复制student表的结构
create table stu_copy
as select * from student where 1=2;

--定义触发器
create or replace trigger student_dml_trigger
before
DELETE
on STUDENT
for each ROW
BEGIN
insert into stu_copy("s_id","s_name","s_sex","s_age","s_birthday")
values(:old."s_id",:old."s_name",:old."s_sex",:old."s_age",:old."s_birthday");
end;

四、访问控制

--创建用户
CREATE USER damon
IDENTIFIED by damon 123;
--授权用户
grant create session,create table
to damon;
--修改用户密码
alter user damon identified by 123;
--创建角色
create role developer;
--给角色授权
grant create session,create table,create view,create sequence,create procedure to developer;
--角色赋予
grant developer to damon;

--对象权限
--hr  用户给Damon用户授权查看
grant select
on employees
to damon;
--登录Damon 查看
select * from hr.employees;
--只有查看权限
delete from hr.employees where employee_id=100;

--撤销授权
revoke select
on employees
from damon;