《Oracle数据库》实验报告

实验二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%，其它都按原来的7%提高。

4、用PL/SQL块实现下列操作公司为每个职工增加奖金：若职工属于30号部门，则增加＄150；若职工属于20号部门，则增加＄250；若职工属于10号部门，则增加＄350。

（提示：游标请自行阅读相关内容）DECLAREaddcomm m%type;CURSOR emp_cursor IS select deptno from emp;BEGINFOR emprec IN emp_cursor LOOPIF emprec.deptno=30 THEN addcomm:=150;ELSIF emprec.deptno=20 THEN addcomm:=250;ELSIF emprec.deptno=10 THEN addcomm:=350;END IF;Update empset 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表均为复制后的表）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 V ARCHAR2(20),t_age NUMBER(2),t_sex CHAR);--创建记录测试表CREATE TABLE test_log(l_user V ARCHAR2(15),l_type V ARCHAR2(15),l_date V ARCHAR2(30));--创建触发器CREATE OR REPLACE TRIGGER test_triggerAFTER DELETE OR INSERT OR UPDA TE ON testDECLAREv_type test_log.l_type%TYPE;BEGINIF INSERTING THEN --INSERT触发v_type := 'INSERT';DBMS_OUTPUT.PUT_LINE('记录已经成功插入，并已记录到日志'); ELSIF UPDATING THEN --UPDA TE触发v_type := 'UPDATE';DBMS_OUTPUT.PUT_LINE('记录已经成功更新，并已记录到日志'); ELSIF DELETING THENv_type := 'DELETE';DBMS_OUTPUT.PUT_LINE('记录已经成功删除，并已记录到日志'); END IF;INSERT INTO test_log V ALUES(user,v_type,TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));END;/--下面我们来分别执行DML语句INSERT INTO test V ALUES(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表中每个部门的总人数和总工资。

--创建映射表CREATE TABLE dept_salASSELECT 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 UPDA TE OR DELETE ON empDECLARECURSOR cur_emp ISSELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno;BEGINDELETE 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_salV ALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);END LOOP;END;/--对emp表进行DML操作INSERT INTO emp(empno,deptno,sal) V ALUES('123','10',10000); SELECT * FROM dept_sal;DELETE EMP WHERE empno=123;SELECT * FROM dept_sal;4、创建触发器，它记录表的删除数据--创建表CREATE TABLE employee(id V ARCHAR2(4) NOT NULL,name V ARCHAR2(15) NOT NULL,age NUMBER(2) NOT NULL,sex CHAR NOT NULL);DESC employee;--插入数据INSERT INTO employee V ALUES('e101','zhao',23,'M');INSERT INTO employee V ALUES('e102','jian',21,'F');--创建记录表CREATE TABLE old_employee ASSELECT * FROM employee;DESC old_employee;--创建触发器CREATE OR REPLACE TRIGGER tig_old_empAFTER DELETE ON employee --FOR EACH ROW --语句级触发，即每一行触发一次BEGININSERT INTO old_employeeVALUES(:old.id,:,:old.age,:old.sex); --:old代表旧值END;/--下面进行测试DELETE employee;SELECT * FROM old_employee;5、创建触发器，比较emp表中更新的工资。

CREATE OR REPLACE TRIGGER sal_emp BEFORE UPDATE ON empFOR EACH ROWBEGINIF :OLD.sal > :NEW.sal THENDBMS_OUTPUT.PUT_LINE('工资减少'); ELSIF :OLD.sal < :NEW.sal THENDBMS_OUTPUT.PUT_LINE('工资增加');ELSEDBMS_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 table foo(a number);Create trigger biud_fooBefore insert or update or deleteOn fooBeginIf user not in (‘DONNY’) thenRaise_application_error(-20001, ‘You don’t have access to modify this table.’);End if;End;/即使SYS，SYSTEM用户也不能修改foo表。

7.写存储过程，显示所指定雇员名所在的部门名和位置。

CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,pdname OUT dept.dname%TYPE,ploc OUT dept.loc%TYPE) ASBEGINSELECT dname,loc INTO pdname,plocFROM emp,deptWHERE emp.deptno=dept.deptno AND emp.ename=pename;END;/V ARIABLE vdname V ARCHAR2(14);V ARIABLE vloc V ARCHAR2(13);EXECUTE DeptMesg('SMITH',:vdname,:vloc);PRINT vdname vloc;8.定义一个为修改职工表（emp）中某职工工资的存储过程子程序，职工名作为形参,若该职工名在职工表中查找不到，就在屏幕上提示“查无此人”然后结束子程序的执行；否则若工种为MANAGER的，则工资加＄1000；工种为SALESMAN，工资加＄500；工种为ANALYST，工资加＄200，否则工资加＄100。