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 存储点

存储过程：

set serveroutput on; create or replace procedure PrintStudentInfo is begin for s_current in (select * from student) loop dbms_output.put(s_current.sno || '\'); dbms_output.put(s_current.sname || '\'); dbms_output.put_line(s_current.age); end loop; end PrintStudentInfo; / exec PrintStudentInfo; create or replace procedure GetStudentInfo(ID in varchar2) is type StudentInfo is record( sID student.sno%type, sName student.sname%type, className course.Cname%type ); v_studentInfo StudentInfo ; begin select s.sno,s.sname,c.cname into v_studentInfo from student s join course c on s.sclass=c.sid where s.sno=ID; dbms_output.put(v_studentInfo.SID || '\'); dbms_output.put(v_studentInfo.Sname || '\'); dbms_output.put(v_studentInfo.className || '\'); exception when no_data_found then dbms_output.put_line('not found this student'); end GetStudentInfo; / declare studentID varchar2(20):='804'; begin GetStudentInfo(id=>studentID); end; /
触发器：

drop table student_log; create table student_log( who varchar2(30), ActionType varchar2(10), ModelDate timestamp ); --触发 create or replace trigger InsertLog_trigger after insert on student begin insert into student_log values(user,'INSERT',sysdate); end; / insert into student(sno,age,sname) values(222190912,23,'李小龙'); select * from student_log; alter table student_log add Student_ID varchar2(10); alter trigger insertlog_trigger disable; create or replace trigger insert_row_log_trigger before insert on student referencing new as new_value for each row begin insert into student_log values(user,'INsert',sysdate,:new_value.SNO) ; end; / insert into student(sno,age,sname) values(32565132,231,'成长伦'); select * from student_log; --test

备份：

-- exp system/manager@ORCL file=d:\daochu.dmp log=d:\logs\log.log full=y imp system/sa@ORCL full=y file=d:\daochu.dmp log=d:\logs\log.log ignore=y