《Oracle数据库》实验报告

1、实验二Oracle数据库开发环境下PL/SQL编程 （2学时）【实验目的】 (1)掌握 PL/SQL 的基本使用方法。(2)在SQL*PLUS环境下运行PL/SQL的简单程序。 (3)应用 PL/SQL 解决实际问题 【实验内容与步骤】 一、 实验内容：1、用PL/SQL实现：输入eno的值，显示emp表中对应记录的内容。2、用PL/SQL完成：读入三个数，计算并输出它们的平均值及三个数的乘积。3、对职工表emp中的雇员SCOTT提高奖金，若工种为MANAGER,则奖金提高其原来的20%;若工种为SALESMAN,则奖金提高其原来的15%;若工种为ANALYST,则奖金提高其原来的10%，其它

2、都按原来的7%提高。4、用PL/SQL块实现下列操作公司为每个职工增加奖金：若职工属于30号部门，则增加150；若职工属于20号部门，则增加250；若职工属于10号部门，则增加350。（提示：游标请自行阅读相关内容）DECLAREaddcomm m%type; CURSOR emp_cursor IS select deptno from emp;BEGIN FOR emprec IN emp_cursor LOOP IF emprec.deptno=30 THEN addcomm:=150; ELSIF emprec.deptno=20 THEN addcomm:=250;ELSIF emp

3、rec.deptno=10 THEN addcomm:=350; END IF;Update emp set comm=comm+ addcomm where deptno= emprec.deptno;END LOOP;COMMIT WORK;END;实验三PL/SQL触发器和存储过程 （2学时）【实验目的】(1)了解触发器的类型。（2）掌握PL/SQL触发器的使用方法。(3)了解存储过程的使用方法。(4)掌握存储过程的使用方法。【实验内容】实验内容：1、编写一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。（要求：emp表、dept表均为

4、复制后的表）CREATE OR REPLACE TRIGGER del_emp_deptnoBEFORE DELETE ON deptFOR EACH ROWBEGINDELETE FROM emp WHERE deptno=:OLD.deptno;END;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_us

5、er   VARCHAR2(15),l_type   VARCHAR2(15),l_date   VARCHAR2(30);-创建触发器CREATE OR REPLACE TRIGGER test_triggerAFTER DELETE OR INSERT OR UPDATE ON testDECLAREv_type test_log.l_type%TYPE;BEGINIF INSERTING THEN -INSERT触发   v_type := 'INSERT'   DBMS_

6、OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志');ELSIF UPDATING THEN -UPDATE触发   v_type := 'UPDATE'   DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志');ELSIF DELETING THEN   v_type := 'DELETE'   DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志');END IF

7、;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 *

8、 FROM test;SELECT * FROM test_log;3、创建触发器，它将映射emp表中每个部门的总人数和总工资。-创建映射表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

9、   SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS 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&#

10、160;   VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal); END LOOP;END;/-对emp表进行DML操作INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);SELECT * FROM dept_sal;DELETE EMP WHERE empno=123;SELECT * FROM dept_sal;4、创建触发器，它记录表的删除数据-创建表CREATE TABLE employee( id &#

11、160; 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','zhao',23,'M');INSERT INTO employee VALUES('e102',

12、'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.age,:old.sex); -:old代表旧

13、值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('

14、;工资增加'); ELSE   DBMS_OUTPUT.PUT_LINE('工资未作任何变动'); 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 ta

15、ble foo(a number);Create trigger biud_foo Before insert or update or delete On fooBegin If user not in (DONNY) thenRaise_application_error(-20001, You dont have access to modify this table.);End if;End;/即使SYS，SYSTEM用户也不能修改foo表。7. 写存储过程，显示所指定雇员名所在的部门名和位置。CREATE OR REPLACE PROCEDURE DeptMesg(pename em

16、p.ename%TYPE,pdname OUT dept.dname%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;8.定义一

17、个为修改职工表（emp）中某职工工资的存储过程子程序，职工名作为形参,若该职工名在职工表中查找不到，就在屏幕上提示“查无此人”然后结束子程序的执行；否则若工种为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=MANA

18、GER 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;9.通过dept表查询出所有部门号,对每个部门雇员的工资进行调整，将工资高于（包含$2000）$2000的雇员每人增加$500,将工资低于$

19、2000的雇员每人增加到$2000。但应注意雇员工资调整后不应大于$10000,否则显示出错信息,并退出程序。并统计显示各部门人数及工资调整后的总和。实验四 PL/SQL子程序 （2学时）【实验目的】(1)掌握创建PL/SQL子程序的方法。(2) 掌握利用PL/SQL子程序提高代码重用性的方法。【实验内容】一、 实验内容1、 设计一个过程子程序，根据输入职工所在的部门号和职工名来修改该职工的工资；若该职工的部门号为10，则工资加$100；若部门号为20，则工资加￥300；否则工资加￥200.（设部门号与职工名作为过程的输入形式参数）程序的代码如下：CREATE OR REPLACE PROCE

20、DURE rais_sal(dept_no integer,v_name varchar2) IS addsal real; v_sal number; salary_mis EXCEPTION;BEGINselect sal into v_sal from empwhere ename=v_name and deptno=dept_no;IF v_sal IS NULL THEN RAISE salary_mis;elsif dept_no=10 then addsal:=100;elsif dept_no=20 then addsal:=300;else addsal:=200;END I

21、F;update emp set sal=sal+addsalwhere ename=v_name and deptno=dept_no;COMMIT WORK;EXCEPTIONWHEN NO_DATA_FOUND THEN dbms_output.put_line(没有找到职工|v_name);WHEN salary_mis THEN dbms_output.put_line(v_name|工资是空的，有错误！);WHEN Others THEN dbms_output.put_line(发现其他错误！);END rais_sal; /*在END后，可以给出本过程名，表示过程到此结束*/过

22、程调用为一个PL/SQL语句，其语句调用形式为：rais_sal(10,SCOTT);2、 定义一个函数子程序，根据输入的职工号计算该职工的年收入总额。程序如下：CREATE OR REPLACE FUNCTION compsumal(v_empno number)RETURN real IS sum_sal real;BEGIN select 12*(sal+nvl(comm,0) INTO sum_sal from empwhere empno= v_empno;RETURN(sum_sal);EXCEPTIONWHEN NO_DATA_FOUND THEN dbms_output.put_line('没有找到职工!');return(-1);WHEN Others THEN dbms_output.put_line('发现其他错误！'); return(-2);END compsumal;可用如下PL/SQL赋值语句调用上述函数：DECLARE y_sum emp.sal%TYPE;BEGIN y_sum:= compsumal(7788);IF y_sum=-1 THEN dbms_output.put_line(未找到该职工，不能计算！);ELSIF y_sum=-2 THEN dbms_output.put_line(未知错误！);E