Oracle报告

首页
实验报告
课程名称：大型数据库管理与应用
实验项目： Oracle 数据库管理及应用
专业班级：计算机1103班
姓名：王圣林学号： 110405305 实验室号： 205 实验组号： 05 实验时间：批阅时间：
指导教师：崔文成成绩：
一、o racle数据库基础操作（写出创建语句和运行结果截图）
1.库的创建
Create database dzq1 datafile 'F:\app\product\11.2.0\dbhome_1\o radata\orcl\system01.dbf' size 300M reuse autoextend on next 102 40K maxsize unlimited extent management local sysaux datafile 'F
:\app\product\11.2.0\dbhome_1\oradata\orcl\sysaux01.dbf' size 12
0M reuse autoextend on next 10240K maxsize unlimited default tem porary tablespace temp tempfile 'F:\app\oradata\orcl\temp01.dbf'
size 20M reuse autoextend on next 640K maxsize unlimited undo t ablespace "UNDOTBS1" datafile 'F:\app\product\11.2.0\dbhome_1\o radata\orcl\undotbs01.dbf' size 200M reuse autoextend on next 51 20K maxsize unlimited logfile group 1 ('F:\app\product\11.2.0\db home_1\oradata\orcl\redo01.log') size 10240K,group 2 ('F:\app\pr oduct\11.2.0\dbhome_1\oradata\orcl\redo02.log') size 10240K,grou
p 3 ('F:\app\lenovo\product\11.2.0\dbhome_1\oradata\orcl\redo03. log') size 10240K
2.表的创建
CREATE TABLE TEST_STUDENT
(
SNO CHAR(9) PRIMARY KEY,
SNAME CHAR(20) UNIQUE,
SSEX CHAR(2),
SAGE SMALLINT,
SDEPT CHAR(20)
);
创建课程表
CREATE TABLE TEST_COURSE
(
CNO CHAR(4) PRIMARY KEY,
CNAME CHAR(40)
);
创建成绩表
CREATE TABLE TEST_SC
(
SNO CHAR(9),
CNO CHAR(4),
GRADE SMALLINT,
STATE CHAR(20),
PRIMARY KEY (SNO,CNO),
FOREIGN KEY (SNO) REFERENCES TEST_STUDENT(SNO) ON DELETE CASCADE, FOREIGN KEY (CNO) REFERENCES TEST_COURSE (CNO) ON DELETE CASCADE
);
3.视图的创建
CREATE VIEW FINALGRADE(SNO,SNAME,SSEX,SDEPT,CNAME,GRADE,STATE)
AS
SELECT
TEST_STUDENT.SNO,TEST_STUDENT.SNAME,TEST_STUDENT.SSEX,TEST_STUDENT.SD EPT,TEST_AME,TEST_SC.GRADE,TEST_SC.STATE
FROM TEST_COURSE,TEST_SC,TEST_STUDENT
WHERE TEST_O = TEST_O AND TEST_STUDENT.SNO = TEST_SC.SNO ORDER BY TEST_SC.SNO
WITH CHECK OPTION;
内容
4.游标的创建及使用
DECLARE
CURSOR per_loop_info
IS SELECT SNO,SNAME,SSEX,SAGE,SDEPT FROM TEST_STUDENT;
info_no TEST_STUDENT.SNO%TYPE;
info_name TEST_STUDENT.SNAME%TYPE;
info_sex TEST_STUDENT.SSEX%TYPE;
info_age TEST_STUDENT.SAGE%TYPE;
info_dept TEST_STUDENT.SDEPT%TYPE;
BEGIN
OPEN per_loop_info;
LOOP
FETCH per_loop_info INTO info_no,info_name,info_sex,info
_age,info_dept;
EXIT WHEN per_loop_info%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('学号：'||info_no||' 姓名：'||info_name||' 性别：'||info_sex||' 年龄：'||info_age||' 专业：'||info_dept);
END LOOP;
CLOSE per_loop_info;
END;
5.存储过程的创建及运行
CREATE PROCEDURE GRADE_INFO
AS
BEGIN
UPDATE TEST_SC SET STATE = '不及格'
WHERE GRADE < 60;
UPDATE TEST_SC SET STATE = '及格'
WHERE GRADE >= 60;
COMMIT;
END;
实现存储过程并显示
EXEC GRADE_INFO;
SELECT * FROM TEST_SC;
6.触发器的创建及运行
CREATE TRIGGER DELETE_ON_SC
AFTER DELETE ON TEST_SC
BEGIN
IF DELETING THEN
DBMS_OUTPUT.put_line('删除数据操作！');
END IF;
END;。