Oracle数据库实验应用
Oracle数据库实验应用
授权:
- create user tempUser identified by sa
- default tablespace users
- temporary tablespace temp quota unlimited on users;
- create profile tempprofile LIMIT
- SESSIONS_PER_USER 3
- CPU_PER_CALL 2000
- IDLE_TIME 15
- LOGICAL_READS_PER_CALL 200
- FAILED_LOGIN_ATTEMPTS 2;
- --为用户指定配置文件
- alter user tempUser profile tempprofile;
- --为用户授的权限
- grant create session to tempUser;
- grant select on student to tempUser;
- connect tempUser/sa;
- --撤销用户权限
- revoke select on system.student from tempUser;
- revoke create session from tempUser;
视图:
- create or replace view Student_Math as
- select * from student
- where sclass in (
- select c.name from class c ,department d
- where c.department=d.did
- and d.DNAME='数学系')
- with check opition;
- )
- create or replace view department_Class as select d.dnam,c,cname
- from class c,department d
- where c.department=d.did;
- select text from user_views where view_name=UPPER('Student_Math');
序列:
- drop table sequence_table ;
- create table sequence_table(
- id number,
- op varchar2(20)
- )
- /
- drop sequence Incr_id;
- create sequence Incr_id
- start with 100
- increment by 1
- nomaxvalue
- nocycle
- order;
- /
- create or replace trigger Incr_trigger
- before insert on sequence_table
- for each row
- declare
- next_no number;
- begin
- select Incr_id.nextval
- into next_no
- from dual;
- :NEW.ID :=next_no ;
- end;
- /
- --test
- insert into sequence_table(op) values('INSERT');
- select * from sequence_table;
- --查看序列状态
- select * from user_sequences where sequence_name=UPPER('Incr_id');
- --事物的隔离级别为 read committed
- set transaction isolation level read committed;
- select sname from student;
- --要是不提交就会报上一个事物还没有处理
- commit;
- --Serializable
- set transaction isolation level serializable;
- select sname,age from student;
- update student set age=23
- where sname='李小龙';
- --rollback;
- rollback;
- select sname,age from student;
- --savepoint section 存储点
|
评论暂时关闭