oracle实验教案10.docx

1、oracle实验教案10oracle数据库教案 单位： 计算机学院 教师： 汤海蓉 时间： 2012.9 课程名称 oracle数据库 使用教材 oracle 10g数据库管理应用与开发标准教程 清华大学出版社 专业班级 计算机学院信管10级 授课时数 共48课时（理论：32课时，实验：16课时） 授课教师 汤海蓉 授课时间 2012学年 下学期 主要参考文献 oracle教程与实验 胡明庆等编著 清华大学出版社 教 学 进 度 表课程名称Oracle数据库实验 总学时数42 其中:理论课学时32 _ 实验(实习)学时10 使用教材Oracle10g数据库管理应用与开发标准教程 周次教学内容（

2、含理论和实践）学时作业布置6SQL*PLUS练习2完成实验报告8Oracle数据库开发环境下PL/SQL编程2完成实验报告10PL/SQL触发器与存储过程2完成实验报告12PL/SQL子程序与Oracle安全管理2完成实验报告14Oracle综合实例设计设计分析2完成实验报告实验一 SQL*PLUS练习 （2学时）【实验目的】 (1) 了解Oracle的工作环境和基本使用方法。(2) 练习标准SQL的数据操作，查询命令及其查询优化。(3) 学会使用高级SQL命令，排序、分组、自连接查询等。(4) 学会使用SQL*PLUS命令显示报表，存储到文件等。【实验内容】一、 准备使用SQL*PLUS1.

3、 进入SQL*PLUS2. 退出SQL*PLUS3. 显示表结构命令DESCRIBE SQLDESCRIBE emp使用DESCRIBE（缩写DESC）可以列出指定表的基本结构，包括各字段的字段名以及类型、长度、是否非空等信息。4. 使用SQL*PLUS显示数据库中EMP表的内容 输入下面的查询语句：SQLSELECT * FROM emp;按下回车键执行查询5. 执行命令文件START或命令将指定文件调入SQL缓冲区中，并执行文件内容。SQL 文件名（文件后缀缺省为.SQL）或SQLSTART 文件名文件中每条SQL语句顺序装入缓冲区并执行。二、 数据库命令有关表、视图等的操作1. 创建表e

4、mployee 例1 定义一个人事信息管理系统中存放职工基本信息的一张表。可输入如下命令：SQLCREATE TABLE employee (empno number(6) PRIMARY KEY, /* 职工编号 name varchar2(10) NOT NULL, /* 姓名deptno number(2) DEFAULT 10, /* 部门号salary number(7,2) CHECK(salarycreate table emp2 asselect * from empwhere 1=2;在命令的where子句中给出1=2，表示条件不可能成立，因而只能复制表结构，而不能复制任何数

5、据到新表中去。另外，还可以复制一个表的部分列定义或部分列定义及其数据。三、 Oracle数据库数据查询 1、单表查询2、多表查询四、 SQL*PLUS常用命令表1 常用报表格式化名命令命令定义Btitle为报表的每一页设置底端标题Column设置列的标题和格式Compute让SQL*PLUS计算各种值Remark将某些字标记为注释Set linesize设置报表的行宽字符数Set newpage设置报表各页之间的行数Spool使SQL*PLUS将输出写入文件中Start使SQL*PLUS执行一个sql文件Ttitle设置报表每页的头标题Break让SQL*PLUS进行分组操作例3 建立一个批命

6、令文件对查询到的数据以报表的形式输出并将其保存到指定的文件中。处理方法：利用SQL*PLUS语言工具（也可以使用其他文本编辑器）建立批命令的.SQL文件。在“SQL”提示符下，使用EDIT命令在”E:”中建立SCGB.SQL文件。SCGB.SQL文件中的命令组如下：SQLEDIT E: SCGB.SQL SET echo off SET pagesize 30 SET linesize 75 TTITLE2008年4月10号CE公司职员基本情况登记表RPage: FORMAT 99-SQL.PNO SKIP 1 CE= BTITLE COL 60 制标单位 TAB 3 人事部 COLUMN e

7、mpno heading 职工|编号 COLUMN ename format a10 heading 姓 名COLUMN job heading 工 种COLUMN sal format $99,990 heading 工 资COLUMN comm Like sal heading 奖 金COLUMN deptno format 9999 heading 部门|编号COLUMN hiredate heading 参加工作时间 SPOOL e:sjbb /*在E盘中建立格式报表输出文件，默认属性为LST BREAK on deptno skip 1 COMPUTE sum of sal comm

8、 on deptno SELECT empno,ename,job,hiredate,sal,comm,deptno from empORDER BY deptno,sal;SPOOL off /*终止SPOOL功能，关闭其文件。注意，此命令不可省，否则将建立空文件。五、 实验内容1、以cs+学号为用户名创建用户，并授予用户创建数据对象的权限。2、复制emp表，复制表名为emp_学号，然后将emp表中工资低于$2000的职工插入到复制的表中。3、对复制的emp表插入一行只包含有职工号,职工名,工资与部门号四个数据项值的记录。4、在复制的emp表中将雇员ALLEN提升为经理，工资增至2500，奖

9、（佣 ）金增加40%。5、删除复制的emp表中工资低于500的记录行。6、列出10号部门中既不是经理，也不是秘书的职工的所有信息。7、查找出部门所在地是CHICAGO的部门的职工姓名、工资和工种。8、统计各部门中各工种的人数、工资总和及奖金总和。9、查找出工资比其所在部门平均工资高的职工姓名、工种与工资情况。10、创建一个批文件，制作一张职工情况登记表，并保存到一个磁盘文件中。要求：有表头、表尾及制表时间。登记表中包括姓名、工种、工资、部门名称和部门所在地。实验二 Oracle数据库开发环境下PL/SQL编程 （2学时）【实验目的】 (1)掌握 PL/SQL 的基本使用方法。(2)在SQL*P

10、LUS环境下运行PL/SQL的简单程序。 (3)应用 PL/SQL 解决实际问题 【实验内容与步骤】 PL/SQL块中的可执行部分是由一系列语句组成的（包括对数据库进行操作的SQL语句，PL/SQL语言的各种流程控制语句等）。在块中对数据库查询，增、删、改等对数据的操作是由SQL命令完成的。在PLSQL块中，可以使用SQL的数据查询命令，数据操纵命令和事务控制命令。可使用全部SQL函数。PLSQL中的SQL语句，可使用SQL的比较操作等运算符。但不能使用数据定义语句。在PLSQL块中使用SELECT语句时注意几点：（1）SELECT语句必须含有INTO子句。（2）INTO子句后的变量个数和位置

11、及数据类型必须和SELECT命令后的字段名表相同。（3）INTO子句后可以是简单类型变量或组合类型变量。（4）SELECT语句中的WHERE条件可以包含PL/SQL块中定义的变量及表达式，但变量名不要同数据库表列名相同。（5）在未使用显式游标的情况下，使用SELECT语句必须保证只有一条记录返回，否则会产生异常情况。实验内容：1、用PL/SQL实现：输入eno的值，显示emp表中对应记录的内容。2、用PL/SQL完成：读入三个数，计算并输出它们的平均值及三个数的乘积。3、对职工表emp中的雇员SCOTT提高奖金，若工种为MANAGER,则奖金提高其原来的20%;若工种为SALESMAN,则奖金

12、提高其原来的15%;若工种为ANALYST,则奖金提高其原来的10%，其它都按原来的7%提高。4、用PL/SQL块实现下列操作公司为每个职工增加奖金：若职工属于30号部门，则增加150；若职工属于20号部门，则增加250；若职工属于10号部门，则增加350。 实验三 PL/SQL触发器 （1学时）【实验目的】(1) 了解触发器的类型。(2) 掌握PL/SQL触发器的使用方法。【实验内容】触发器注意事项（1）触发器可以声明为在对记录进行操作之前，在之前(检查约束之前和 INSERT，UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT， UPDATE 或 DELET

13、E 操作)触发.。（2）一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次。（3）SELECT 并不更改任何行，因此不能创建 SELECT 触发器。这种场合下规则和视图更适合。（4）触发器和某一指定的表格有关，当该表格被删除时，任何与该表有关的触发器同样会被删除。（5）在一个表上的每一个动作只能有一个触发器与之关联。（6）在一个单独的表上，最多只能创建三个触发器与之关联，一个INSERT触发器，一个DELETE触发器和一个UPDATE触发器。1. 实例讲解Oracle数据库自带的几个触发器Oracle数据库自带的几个触发器（最简单触发器格式）示例如下： -crea

14、te or replace trigger MDSYS.sdo_drop_userafter drop on DATABASEdeclare stmt varchar2(200);BEGIN if dictionary_obj_type = USER THEN stmt := DELETE FROM SDO_GEOM_METADATA_TABLE | WHERE SDO_OWNER = | dictionary_obj_name | ; EXECUTE IMMEDIATE stmt; end if;end;-create or replace trigger SYS.aurora$server

15、$startup after startup on database call dbms_java.server_startup- create or replace trigger SYS.JIS$ROLE_TRIGGER$ after drop on database when (ora_dict_obj_type=ROLE)begin sns_context.role_dropped(ora_dict_obj_name); http_security_cascade.principal_dropped(ora_dict_obj_name); end;-删除前备份数据的器Create Or

16、 Replace Trigger YSPJ.T_Bill_reMain_DelBefore delete On bill_remainFOR EACH ROWBeginInsert into BILL_REMAIN_TIGERValues(:old.BILL_REMAINID,:old.BILL_TYPEID,:old.REMAIN_NUM,:old.ADD_TIME,:old.ORG_ID,:old.STATE,:old.BILL_ID,删除记录,Sysdate,user);End;二、 实验内容：1、编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该

17、部门的所有雇员。（要求：emp表、dept表均为复制后的表）2、创建触发器，当用户对test表执行DML语句时，将相关信息记录到日志表。-创建测试表CREATE TABLE test(t_id NUMBER(4),t_name VARCHAR2(20),t_age NUMBER(2),t_sex CHAR);-创建记录测试表CREATE TABLE test_log(l_user VARCHAR2(15),l_type VARCHAR2(15),l_date VARCHAR2(30);-创建触发器CREATE OR REPLACE TRIGGER test_triggerAFTER DELET

18、E OR INSERT OR UPDATE ON testDECLAREv_type test_log.l_type%TYPE;BEGINIF INSERTING THEN -INSERT触发 v_type := INSERT; DBMS_OUTPUT.PUT_LINE(记录已经成功插入，并已记录到日志);ELSIF UPDATING THEN -UPDATE触发 v_type := UPDATE; DBMS_OUTPUT.PUT_LINE(记录已经成功更新，并已记录到日志);ELSIF DELETING THEN v_type := DELETE; DBMS_OUTPUT.PUT_LINE(

19、记录已经成功删除，并已记录到日志);END IF;INSERT INTO test_log VALUES(user,v_type, TO_CHAR(sysdate,yyyy-mm-dd hh24:mi:ss);END;/-下面分别执行DML语句INSERT INTO test VALUES(101,zhao,22,M);UPDATE test SET t_age = 30 WHERE t_id = 101;DELETE test WHERE t_id = 101;-然后查看效果SELECT * FROM test;SELECT * FROM test_log;3、创建触发器，它将映射emp表中

20、每个部门的总人数和总工资。-创建映射表CREATE TABLE dept_sal AS SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno;DESC dept_sal;-创建触发器CREATE OR REPLACE TRIGGER emp_infoAFTER INSERT OR UPDATE OR DELETE ON empDECLARE CURSOR cur_emp IS SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) A

21、S total_sal FROM emp GROUP BY deptno;BEGIN DELETE dept_sal; -触发时首先删除映射表信息 FOR v_emp IN cur_emp LOOP -DBMS_OUTPUT.PUT_LINE(v_emp.deptno | v_emp.total_emp | v_emp.total_sal); -插入数据 INSERT INTO dept_sal VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal); END LOOP;END;/-对emp表进行DML操作INSERT INTO emp(emp

22、no,deptno,sal) VALUES(123,10,10000);SELECT * FROM dept_sal;DELETE EMP WHERE empno=123;SELECT * FROM dept_sal;4、创建触发器，它记录表的删除数据-创建表CREATE TABLE employee( id VARCHAR2(4) NOT NULL, name VARCHAR2(15) NOT NULL, age NUMBER(2) NOT NULL, sex CHAR NOT NULL);DESC employee;-插入数据INSERT INTO employee VALUES(e101

23、,zhao,23,M);INSERT INTO employee VALUES(e102,jian,21,F);-创建记录表CREATE TABLE old_employee AS SELECT * FROM employee;DESC old_employee;-创建触发器CREATE OR REPLACE TRIGGER tig_old_empAFTER DELETE ON employee -FOR EACH ROW -语句级触发，即每一行触发一次BEGIN INSERT INTO old_employee VALUES(:old.id,:old.name,:old.age,:old.s

24、ex); -:old代表旧值END;/-进行测试DELETE employee;SELECT * FROM old_employee;5、创建触发器，比较emp表中更新的工资。CREATE OR REPLACE TRIGGER sal_empBEFORE UPDATE ON empFOR EACH ROWBEGIN IF :OLD.sal :NEW.sal THEN DBMS_OUTPUT.PUT_LINE(工资减少); ELSIF :OLD.sal :NEW.sal THEN DBMS_OUTPUT.PUT_LINE(工资增加); ELSE DBMS_OUTPUT.PUT_LINE(工资未作

25、任何变动); END IF; DBMS_OUTPUT.PUT_LINE(更新前工资 ： | :OLD.sal); DBMS_OUTPUT.PUT_LINE(更新后工资 ： | :NEW.sal);END;/-执行UPDATE查看效果UPDATE emp SET sal = 3000 WHERE empno = 7788;6、需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。Create table foo(a number);Create trigger biud_foo Before insert or update or delete On fooBegin If use

26、r not in (DONNY) thenRaise_application_error(-20001, You dont have access to modify this table.);End if;End;/即使SYS，SYSTEM用户也不能修改foo表。实验四 PL/SQL存储过程 （1学时）【实验目的】(1)了解存储过程的使用方法。(2)掌握存储过程的使用方法。【实验内容】1、 写存储过程，显示所指定雇员名所在的部门名和位置。CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,pdname OUT dept.dna

27、me%TYPE,ploc OUT dept.loc%TYPE) ASBEGINSELECT dname,loc INTO pdname,ploc FROM emp,deptWHERE emp.deptno=dept.deptno AND emp.ename=pename;END;/VARIABLE vdname VARCHAR2(14);VARIABLE vloc VARCHAR2(13);EXECUTE DeptMesg(SMITH,:vdname，:vloc);PRINT vdname vloc;2、 定义一个为修改职工表（emp）中某职工工资的存储过程子程序，职工名作为形参,若该职工名在

28、职工表中查找不到，就在屏幕上提示“查无此人”然后结束子程序的执行；否则若工种为MANAGER的，则工资加1000；工种为SALESMAN，工资加500；工种为ANALYST，工资加200，否则工资加100。create or replace procedure xggz(name varchar2) isk_job emp.job%type; addsal emp.sal%type;beginselect job into k_job from emp where ename=name;if k_job=MANAGER then addsal:=1000;elsif k_job=SALESMAN then addsal:=500;elsif k_job=ANALYST then addsal:=200;elseaddsal:=100;end if;update emp set sal=sal+addsal where ename=name;exceptionwhen no_data_found thendbms_output.put_line(查无此人”);end;3、 通过dept表查询出所有部门号,对每个部门雇员的工资进行调整，将工资高于（包含$2000）$2000的雇员每人增加$500,将工资低于$