发布时间 : 星期三 文章《Oracle数据库》实验指导书级更新完毕开始阅读a4a24957336c1eb91a375de2
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(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 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','jian',21,'F'); --创建记录表
CREATE TABLE old_employee AS SELECT * FROM employee; DESC old_employee; --创建触发器
CREATE OR REPLACE TRIGGER tig_old_emp AFTER DELETE ON employee --
FOR EACH ROW --语句级触发，即每一行触发一次 BEGIN
INSERT INTO old_employee
VALUES(:old.id,:old.name,:old.age,:old.sex); --:old代表旧值 END; /
--下面进行测试 DELETE employee;
SELECT * FROM old_employee;
5
5、创建触发器，比较emp表中更新的工资。 CREATE OR REPLACE TRIGGER sal_emp BEFORE UPDATE ON emp FOR EACH ROW BEGIN
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('工资未作任何变动'); 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 foo Begin
If user not in (‘DONNY’) then
Raise_application_error(-20001, ‘You don’t have access to modify this table.’); End if; End; /
即使SYS，SYSTEM用户也不能修改foo表。
写存储过程，显示所指定雇员名所在的部门名和位置。
CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE, pdname OUT dept.dname%TYPE,ploc OUT dept.loc%TYPE) AS BEGIN
SELECT dname,loc INTO pdname,ploc FROM emp,dept
WHERE emp.deptno=dept.deptno AND emp.ename=pename; END; /
VARIABLE vdname VARCHAR2(14); VARIABLE vloc VARCHAR2(13);
EXECUTE DeptMesg('SMITH',:vdname，:vloc);
6
PRINT vdname vloc;
1、 定义一个为修改职工表（emp）中某职工工资的存储过程子程序，职工名作为形参,若该职工名在职工表中查找不到，就在屏幕上提示“查无此人”然后结束子程序的执行；否则若工种为MANAGER的，则工资加＄1000；工种为SALESMAN，工资加＄500；工种为ANALYST，工资加＄200，否则工资加＄100。
create or replace procedure xggz(name varchar2) is
k_job emp.job%type; addsal emp.sal%type; begin
select 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; else
addsal:=100; end if;
update emp set sal=sal+addsal where ename=name; exception
when no_data_found then
dbms_output.put_line(‘查无此人”); end; 2、 通过dept表查询出所有部门号,对每个部门雇员的工资进行调整，将工资高于（包含$2000）$2000的雇员每人增加$500,将工资低于$2000的雇员每人增加到$2000。但应注意雇员工资调整后不应大于$10000,否则显示出错信息,并退出程序。并统计显示各部门人数及工资调整后的总和。 declare
cursor dept_cursor is
select distinct deptno from dept;
cursor emp_cursor(v_deptno emp.deptno%type)is select ename,sal from emp where deptno=v_deptno; allsal number(5):=0; allemp number(3):=0; err exception; begin
for deptrec in dept_cursor loop
for emprec in emp_cursor(deptrec.deptno) loop
if emprec.sal>=2000 then emprec.sal:=emprec.sal+500; else emprec.sal:=2000; end if;
if emprec.sal>10000 then raise err;
7
end if;
updata emp set sal=emprec.sal where ename=emprec.ename; allsal:=allsal+emprec.sal;
allemp:=emp_cursor%rowcount; end loop;
dbms_output.put_line(deptrec.deptno,allemp,allsal); allsal:=0; end loop; commit work; exception when err then
dbms_output.put_line('发现有职工的工资大于10000'); rollback work; when others then
dbms_output.put_line('发现其他类型错误'); rollback work; end;
8