文档库

最新最全的文档下载
当前位置:文档库 > 《Oracle数据库》实验报告

《Oracle数据库》实验报告

实验二Oracle数据库开发环境下PL/SQL编程(2学时)

【实验目的】

(1)掌握PL/SQL 的基本使用方法。

(2)在SQL*PLUS环境下运行PL/SQL的简单程序。

(3)应用PL/SQL 解决实际问题

【实验内容与步骤】

一、实验内容:

1、用PL/SQL实现:输入eno的值,显示emp表中对应记录的内容。

《Oracle数据库》实验报告

2、用PL/SQL完成:读入三个数,计算并输出它们的平均值及三个数的乘积。

《Oracle数据库》实验报告

3、对职工表emp中的雇员SCOTT提高奖金,若工种为MANAGER,则奖金提高其原来的20%;若工种为SALESMAN,则奖金提高其原来的15%;若工种为ANALYST,则奖金提高其原来的10%,

其它都按原来的7%提高。

《Oracle数据库》实验报告

4、用PL/SQL块实现下列操作

公司为每个职工增加奖金:若职工属于30号部门,则增加$150;若职工属于20号部门,

则增加$250;若职工属于10号部门,则增加$350。(提示:游标请自行阅读相关内容)

《Oracle数据库》实验报告

DECLARE

addcomm http://www.wendangku.net/doc/2c59cfdf3c1ec5da50e270d8.htmlm%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 emprec.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表均为复制后的表)

CREATE OR REPLACE TRIGGER del_emp_deptno

BEFORE DELETE ON dept

FOR EACH ROW

BEGIN

DELETE FROM emp WHERE deptno=:OLD.deptno;

END;

《Oracle数据库》实验报告

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_trigger

AFTER DELETE OR INSERT OR UPDA TE ON test

DECLARE

v_type test_log.l_type%TYPE;

BEGIN

IF INSERTING THEN --INSERT触发

v_type := 'INSERT';

DBMS_OUTPUT.PUT_LINE('记录已经成功插入,并已记录到日志'); ELSIF UPDATING THEN --UPDA TE触发

v_type := 'UPDATE';

DBMS_OUTPUT.PUT_LINE('记录已经成功更新,并已记录到日志'); ELSIF DELETING THEN

v_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;

/

《Oracle数据库》实验报告

--下面我们来分别执行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;

《Oracle数据库》实验报告

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_info

AFTER INSERT OR UPDA TE OR DELETE ON emp

DECLARE

CURSOR cur_emp IS

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

V ALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);

END LOOP;

END;

/

《Oracle数据库》实验报告

--对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;

《Oracle数据库》实验报告

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');

《Oracle数据库》实验报告

--创建记录表

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,:http://www.wendangku.net/doc/2c59cfdf3c1ec5da50e270d8.html,:old.age,:old.sex); --:old代表旧值END;

/

《Oracle数据库》实验报告

--下面进行测试

DELETE employee;

SELECT * FROM old_employee;

《Oracle数据库》实验报告

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';

《Oracle数据库》实验报告

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表。

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

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;

/

V ARIABLE vdname V ARCHAR2(14);

V ARIABLE vloc V ARCHAR2(13);

EXECUTE DeptMesg('SMITH',:vdname,:vloc);

PRINT vdname vloc;

《Oracle数据库》实验报告

8.定义一个为修改职工表(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=’ANAL YST’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;

《Oracle数据库》实验报告

9.通过dept表查询出所有部门号,对每个部门雇员的工资进行调整,将工资高于(包含$2000)$2000的雇员每人增加$500,将工资低于$2000的雇员每人增加到$2000。但应注意雇员工资调整后不应大于$10000,否则显示出错信息,并退出程序。并统计显示各部门人数及工资调整后的总和。

实验四PL/SQL子程序(2学时)

【实验目的】

(1)掌握创建PL/SQL子程序的方法。

(2) 掌握利用PL/SQL子程序提高代码重用性的方法。

【实验内容】

一、实验内容

1、设计一个过程子程序,根据输入职工所在的部门号和职工名来修改该职工的工资;若该

职工的部门号为10,则工资加$100;若部门号为20,则工资加¥300;否则工资加¥200.

(设部门号与职工名作为过程的输入形式参数)

《Oracle数据库》实验报告

程序的代码如下:

CREATE OR REPLACE PROCEDURE rais_sal(dept_no integer,v_name varchar2) IS

addsal real;

v_sal number;

salary_mis EXCEPTION;

BEGIN

select sal into v_sal from emp

where 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;

update emp

set sal=sal+addsal

where ename=v_name and deptno=dept_no;

COMMIT WORK;

EXCEPTION

WHEN 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后,可以给出本过程名,表示过程到此结束*/ 过程调用为一个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 emp

where empno= v_empno;

RETURN(sum_sal);

EXCEPTION

WHEN 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(‘未知错误!’);

ELSE

dbms_output.put_line('工资总额为:'||y_sum);

END IF;

在主调函数中可以对y_sum的值进行判断。如果是-1,说明未找到该职工,不能计算;如果是-2,说明有其他错误,否则就是要计算该职工的年收入总额。

《Oracle数据库》实验报告

3、写过程子程序,以雇员名为参数,显示所指定雇员名所在的部门名和位置,并调用这个

子程序。

《Oracle数据库》实验报告

4、写函数子程序,以部门号为参数,计算该部门职工的总人数,并调用这个子程序。CREATE OR REPLACE FUNCTION empsum(v_deptno integer)

RETURN real IS

v_sum real;

BEGIN

select COUNT(*) INTO v_sum from emp where deptno=v_deptno;

return(v_sum);

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('没有找到部门!');

return(-1);

WHEN Others THEN

dbms_output.put_line('发现其他错误!');

return(-2);

END empsum;

y_sum number;

BEGIN

y_sum:= empsum(20);