《ORACLE数据库实验(共27页).doc》由会员分享，可在线阅读，更多相关《ORACLE数据库实验(共27页).doc（27页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、精选优质文档-倾情为你奉上ORACLE数据库实验PMQ 2015-10-292015-11-3第一次修改2016-06-14 第二次修改 现在发现，无论是什么软件。都离不开数据库的操作。又需要重新学习数据库。这次更新加了目录。专心-专注-专业第一章 Oracle 基本知识与 SQL*PLUS 环境1、创建数据库 开始程序Oracle-OraDb11g-home1配置和移植工具Database Configuration Assistant 数据库名称和密码需要设置以外，其他的选项都是直接“下一步”。 2、打开运行程序 开始运行sqlplus /nolog 3、连接数据库 conn / as s
2、ysdba;4、解锁scott用户、修改其密码（这一步只是熟悉操作，做不做没关系） Alter user scott account unlock ;conn scott / tiger ;5、修改语言美式英语： alter session set nls_language=AMERICAN;简体中文： alter session set nls_language=SIMPLIFIED CHINESE;修改语言主要是日期的格式的不一致，可以用命令select sysdate from dual;查看当前日期，并且比较两种语言的不同。这个知识在接下来实验中对表插入日期数据会涉及到。6、两张表DE
3、PT 、 EMP这两张表是接下来几次实验要用的，所以要先创建好。做实验过程中，要多数据处理，我们知道表的内容就能更好地操作了。注意：在插入数据之前一定要写命令alter session set nls_language=AMERICAN;因为插入的日期是美式日期。DROP TABLE DEPT;CREATE TABLE DEPT (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,DNAME VARCHAR2(14) ,LOC VARCHAR2(13) ) ;DROP TABLE EMP;DROP TABLE EMP;CREATE TABLE EM
4、P (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);INSERT INTO DEPT VALUES (10,ACCOUNTING,NEW YORK);INSERT INTO DEPT VALUES (20,RESEARCH,DALLAS);INSERT
5、 INTO DEPT VALUES (30,SALES,CHICAGO);INSERT INTO DEPT VALUES (40,OPERATIONS,BOSTON);INSERT INTO EMP VALUES(7369,SMITH,CLERK,7902,to_date(17-12-1980,dd-mm-yyyy),800,NULL,20);INSERT INTO EMP VALUES(7499,ALLEN,SALESMAN,7698,to_date(20-2-1981,dd-mm-yyyy),1600,300,30);INSERT INTO EMP VALUES(7521,WARD,SAL
6、ESMAN,7698,to_date(22-2-1981,dd-mm-yyyy),1250,500,30);INSERT INTO EMP VALUES(7566,JONES,MANAGER,7839,to_date(2-4-1981,dd-mm-yyyy),2975,NULL,20);INSERT INTO EMP VALUES(7654,MARTIN,SALESMAN,7698,to_date(28-9-1981,dd-mm-yyyy),1250,1400,30);INSERT INTO EMP VALUES(7698,BLAKE,MANAGER,7839,to_date(1-5-1981
7、,dd-mm-yyyy),2850,NULL,30);INSERT INTO EMP VALUES(7782,CLARK,MANAGER,7839,to_date(9-6-1981,dd-mm-yyyy),2450,NULL,10);INSERT INTO EMP VALUES(7788,SCOTT,ANALYST,7566,to_date(13-JUL-87)-85,3000,NULL,20);INSERT INTO EMP VALUES(7839,KING,PRESIDENT,NULL,to_date(17-11-1981,dd-mm-yyyy),5000,NULL,10);INSERT
8、INTO EMP VALUES(7844,TURNER,SALESMAN,7698,to_date(8-9-1981,dd-mm-yyyy),1500,0,30);INSERT INTO EMP VALUES(7876,ADAMS,CLERK,7788,to_date(13-JUL-87)-51,1100,NULL,20);INSERT INTO EMP VALUES(7900,JAMES,CLERK,7698,to_date(3-12-1981,dd-mm-yyyy),950,NULL,30);INSERT INTO EMP VALUES(7902,FORD,ANALYST,7566,to_
9、date(3-12-1981,dd-mm-yyyy),3000,NULL,20);INSERT INTO EMP VALUES(7934,MILLER,CLERK,7782,to_date(23-1-1982,dd-mm-yyyy),1300,NULL,10);第二章 数据表的创建1创建如下三个基表：S(S#，SNAME，AGE，SEX) 对应的中文为：学生(学号，姓名，年龄，性别)SC(S#，C#，GRADE) 对应的中文为：学习（学号，课程号，成绩）C(C#，CNAME，TEACHER)对应的中文为：课程（课程号，课程名，任课教师）注：以后的实验要用到这三个基本表create table
10、s(s number(10) not null primary key,sname varchar2(10),age number(3),sex varchar2(10);describe s;create table c(c number(10) not null primary key,cname varchar2(10),teacher varchar2(10);describe c;create table sc(s number(10) not null references s(s),c number(10) not null references c(c),grade numbe
11、r(3),primary key(s,c);describe sc;2生成一个数据表 PROJECTS，其字段定义如下，其中 PROJID 是主键并且要求P_END_DATE 不能比 P_START_DATE 早。字段名称 数据类型 长度PROJID NUMBER 4P_DESC VARCHAR2 20P_START_DATE DATE P_END_DATE DATE BUDGET_AMOUNT NUMBER 7,2MAX_NO_STAFF NUMBER 2create table projects(projid number(4) not null primary key,p_desc va
12、rchar2(20),p_start_date date,p_end_date date,budget_amountnumber(7,2),max_no_staffnumber(2),check(p_start_date =91;update sc set grade=1.1*gradewhere c=2 and grade =01-Jan-1983 and HIREDATE (SELECT AVG(SAL) FROM EMP) GROUP BY DEPTNO;第五章视图、索引、序列和权限设置对基本表 S 、C 和 和 SC 操作1、建立男学生的视图，属性包括学号、姓名、选修课程和成绩。cre
13、ate view m_stu(sno,sname,cname,grade)as select s.s,s.sname,ame,sc.grade from s,c,scwhere s.s=sc.s and c.c=sc.c and s.sex=M;select * from m_stu;2、在男学生视图中查询平均成绩大于 80 分的学生学号和姓名。select distinct sno,sname,AVG(grade) avg_grade from m_stuwhere sno in (select sno from m_stu) group by sno,sname having AVG(gr
14、ade) 80 ;3、撤消生成的视图。drop view m_stu;4、创建一个新用户 NEWUSER。CREATE USER NEWUSER IDENTIFIED BY MY;5、使用 GRANT 语句，把对基本表 S、C、SC 的使用权限授给 NEWUSER 用户。GRANT select,insert,delete,update on s to NEWUSER;GRANT select,insert,delete,update on c to NEWUSER;GRANT select,insert,delete,update on sc to NEWUSER;6、使用 REVOKE 语
15、句从 NEWUSER 手中收回基本表 S、C、SC 的使用权。revoke all on s from NEWUSER;revoke all on c from NEWUSER;revoke all on sc from NEWUSER;7、删除用户 NEWUSER。drop user NEWUSER;8、对基本表 S 按照 S#生成一个索引。9、对基本表 C 按照 C#生成一个索引。10、删除基本表 C 建立的索引。在S(S)和C（C）上创建索引，会产生错误，是因为在建立PRIMARY KEY约束时已经创建了索引。所以8和9出错是正常的。第六章 PL/SQL注意：在输入完成后记得加 / 符号
16、，类似 ; 的作用，代表输入结束。对基本表 EMP 操作：1、 用 PL/SQL 的存储过程删除雇员为 7788 的雇员CREATE OR REPLACE PROCEDURE fire_emp(v_emp_no IN emp.empno%type)ISBEGINDELETE FROM EMP WHERE empno = v_emp_no;END fire_emp;/EXECUT fire_emp(7788);注：%type的作用是让定义的变量v_emp与emp.empno的类型匹配。2、用带输入输出参数的存储过程查询出任意给定雇员号的的薪水。 CREATE OR REPLACE PROCEDU
17、RE query_sal(v_emp_no IN emp.empno%type,v_emp_name OUT emp.ename%type,v_emp_sal OUT emp.sal%type)ISBEGINSELECT ename, salINTO v_emp_name, v_emp_salFROM EMP WHERE empno = v_emp_no;END query_sal;/VARIABLE emp_name varchar2(15);VARIABLE emp_sal number;EXECUTE query_sal(7654,:emp_name, :emp_sal);print e
18、mp_sal;3、用函数做第 2 题。CREATE OR REPLACE FUNCTION get_sal(v_emp_no IN emp.empno%type)RETURN numberISV_emp_sal emp.sal%type := 0;BEGINSELECT sal INTO v_emp_salFROM EMP WHERE empno = v_emp_no;RETURN (v_emp_sal);END get_sal;/VARIABLE emp_sal number;EXECUTE :emp_sal := get_sal(7654)PRINT emp_sal;第七章 触发器和游标对
19、基本表 EMPDEPT 进行操作：1、用显式游标对基本表 EMP 查询信息。-显式游标就是有声明游标的，如DECLARE Cursor c1 DECLARECursor c1 ISSELECT ename, sal, hiredate FROM empWHERE deptno = 20 AND job = ANALYST;v_ename VARCHAR2(10);v_sal NUMBER(7,2);v_hiredate date;beginOPEN c1;FETCH c1 INTO v_ename, v_sal, v_hiredate;CLOSE c1;end;/2、用隐式游标对基本表 DEP
20、T 查询信息。DECLAREdno number(2);name varchar2(14);addr varchar2(13);BEGINSELECT DEPTNO,DNAME,LOC INTO dno,name,addr from DEPTwhere DEPTNO=20;end;/3、生成一个基本表 DEPT 触发器， 如果增加一个新的部门同时在基本表 EMP 中也增加一条有相同部门但雇员号为自己学号的记录。直接输入代码的话，会出现一下错误。04089：无法对sys拥有的对象创建触发器解决的办法是新建一个用户，再把SYS的EMP和DEPT两张表复制到该用户的目录。新创建一个用户（用户名pmq
21、密码是）,pmq是我的名字，请读者别复制。读者在看到pmq三个字母就替换为自己创建的用户。纯数字密码可能会提示错误，用字母+数字的密码。下面的数字2072是我的学号，意思就是在添加一个部门的时候就把我加进去。读者请更改为自己的学号，位数是4位数（表在定义的时候把员工号定义为4位数字）。conn / as sysdba;create user pmq identified by ;grant dba to username;conn pmq/CREATE TABLE pmq.DEPT AS SELECT * FROM sys.DEPT;CREATE TABLE pmq.EMP AS SELECT
22、 * FROM sys.EMP;CREATE OR REPLACE TRIGGER TRI11AFTER INSERT ON DEPTFOR EACH ROWBEGIN INSERT INTO EMP (EMPNO,DEPTNO ) values (2072,:new.DEPTNO);END;/测试数据INSERT INTO DEPT VALUES (50,COMPUTER,SHANGHAI);SELECT EMPNO,DEPTNO FROM EMP WHERE DEPTNO=50;4、生成一个基本表 EMP 触发器， 如果删除一个部门时， 同时也在基本表 DEPT 中删除该部门的相关信息。C
23、REATE OR REPLACE TRIGGER TRI22AFTER DELETE ON EMPFOR EACH ROWBEGIN DELETE FROM DEPT WHERE DEPTNO=:old.deptno; END;/试验数据delete from EMP WHERE DEPTNO=50;SELECT DEPTNO,DNAME,LOC FROM DEPT WHERE DEPTNO=50;5、删除生成的触发器。DROP TRIGGER TRI11;DROP TRIGGER TRI22;注意：(1)要理解第三题和第四题中的new和old所代表的意思和用法。大致可以理解为new是在新插入
24、数据的时候选择新数据，old是删除数据时选择被删除的数据（也就是旧的数据）。(2)for each row的含义是执行的级别是行，如果不加这一句是默认级别是表。第九章 Oracle 备份与恢复注意: -开头的语句是我解释的语句 没有-的语句是需要操作的。1、对数据库进行热备和冷备做实验之前，要知道什么是热备，什么是冷备。冷备：备份的时候把数据文件、控制文件、日志文件全部拷贝到其他地方。恢复过程就是把文件再拷回来，再把日志文件恢复。热备：备份的时候把数据文件、控制文件、日志文件全部拷贝到其他地方。恢复过程中先在备份的文件中复制回丢失的文件（更新的数据是没有的），然后利用恢复系统把文件更新。首先，
25、我要解释一下。我的oracle是安装目录为H:/oracle，大家在做的时候要按照自己的实际的目录来写代码。我在H盘新建了一个database文件夹，里面含有一个cold文件夹。coldbak.sql文件我放在了H:/database目录下。要想成功运行下面的代码，必须有正确的文件路径。冷备份的操作：1、新建一个coldbak.sql的文件，在里面写入代码： conn / as sysdba; shutdown immediate; $copy H:oracleoradatapmq*.dbf H:databasecold; $copy H:oracleoradatapmq*.ctl H:dat
26、abasecold; $copy H:oracleoradatapmq*.log H:databasecold; startup;注：其实代码的含义就是从H:oracleoradatapmq目录把所有的dbf、ctl、log文件复制到H:databasecold。2、然后在SQL 里输入：connect / as sysdba;drop table test_1;create table test_1(id int);insert into test_1 values(999);commit;H:databasecoldbak.sql;路径一定要正确， 否则无法打开文件。我的coldbak.sql是放在H盘的database文件夹中的。路径的逗号不能少。insert into test_1 values(888);commit;select * from test_1;shutdown immediate;3、手动删除H:oracleoradatapmq里的所有文件。这个的安装路径大家都有所不同。但是一定要找到oradata文件夹。里面放的是数据库的内容，我的数据库名称是pmq。里面有USERS01.DBF、UNDOTBS01.DBF等文件。