【Oracle】数据库数据设计课设

一、根据字段及类型提示创建以下四张表

1.创建EMPS:雇员表

记录一个雇员的基本信息

插入数据

INSERT INTO EMPS VALUES (7369, 'SMITH', 'CLERK', 7902,
'17-12月-1980', 800, NULL, 20);
INSERT INTO EMPS VALUES (7499, 'ALLEN', 'SALESMAN', 7698,
'20-2月-1981', 1600, 300, 30);
INSERT INTO EMPS VALUES (7521, 'WARD', 'SALESMAN', 7698,
'22-2月-1981',  1250, 500, 30);
INSERT INTO EMPS VALUES (7566, 'JONES', 'MANAGER', 7839,
'2-4月-1981', 2975, NULL, 20);
INSERT INTO EMPS VALUES (7654, 'MARTIN', 'SALESMAN', 7698,
'28-9月-1981',  1250, 1400, 30);
INSERT INTO EMPS VALUES (7698, 'BLAKE', 'MANAGER', 7839,
'1-5月-1981', 2850, NULL, 30);
INSERT INTO EMPS VALUES (7782, 'CLARK', 'MANAGER', 7839,
'9-6月-1981',  2450, NULL, 10);

 

2.创建DEPTS:部门表

记录一个部门的具体信息

插入数据 

INSERT INTO DEPTS VALUES (10,'ACCOUNTING','NEW YORK'); 
INSERT INTO DEPTS VALUES (20,'RESEARCH','DALLAS'); 
INSERT INTO DEPTS VALUES (30,'SALES','CHICAGO');  
INSERT INTO DEPTS VALUES (40,'OPERATIONS','BOSTON');

3.创建BONUSS:奖金表

记录一个雇员的工资及奖金

4.创建SALGRADES:工资等级表
记录一个工资的等级

插入数据

INSERT INTO SALGRADES VALUES (1,700,1200); 
INSERT INTO SALGRADES VALUES (2,1201,1400); 
INSERT INTO SALGRADES VALUES (3,1401,2000); 
INSERT INTO SALGRADES VALUES (4,2001,3000); 
INSERT INTO SALGRADES VALUES (5,3001,9999);

---

二.根据第一题的四张表的完成以下sql语句

1.Oracle PL/SQL程序块

(1)编写一个程序块，从emps表中显示名为“SMITH”的雇员的薪水和职位。

declare
v_emp emp%rowtype; //该类型是表中某行的相数据类型的变量
begin 
  select * into v_emp from emps where ename='SMITH'; //从emps表中查询smith插入
dbms_output.put_line('工作是'||v_emp.job||';薪水是'||v_emp.sal); //输出显示
end;

 

(2)编写一个程序块，接受用户输入一个部门号，从depts表中显示该部门的名称与所在位置。

//传统方法进行操作
declare
v_loc depts.loc%type; //定义同类型所需的变量--所在位置
v_dname depts.dname%type;//定义同类型所需的变量--部门名称
v_deptno depts.deptno%type;
begin
v_deptno :=&部门编号;
select loc,dname intv_deptno :=&部门编号o v_loc,v_dname from depts where deptno = v_deptno;
dbms_output.put_line('所在位置是'||v_loc||';部门的名称是'||v_dname);
end;  

 

(3)编写一个程序块，利用%type属性，接受一个雇员号，从emps表中输出该雇员的整体薪水(即，薪水加佣金)。

declare
v_sal emps.sal%type;  //定义同类型所需的参数--薪水
begin
select sal+comm into v_sal from emps where empno = &雇员号; //根据雇员号匹配，把薪水+佣金放入行级变量
dbms_output.put_line('整体薪水是'||v_sal);
end;

 

2.Oracle使用游标

 (1)通过使用游标来显示depts表中的部门名称;

declare
cursor cur is select * from depts;
begin
  for v_depts in cur
    loop
      dbms_output.put_line(v_depts.dname);
     end loop;
end;

 

(2)使用loop循环,输出depts表中的所有数据;

declare
cursor ps is select * from depts;
begin 
for v_depts in ps
  loop
    dbms_output.put_line('部门编号是:'||v_depts.deptno||'部门的名称是;'||v_depts.dname||'所在位置是:'||v_depts.loc);
  end loop;
end;

 

(3)使用For循环，接受一个部门号，从emps表中显示该部门的所有雇员的姓名，工作和薪水;

declare 
cursor a3 is select * from emps where deptno = &部门编号;
begin
  for v_emps in a3
  loop
    dbms_output.put_line('雇员的姓名:'||v_emps.ename||';工作:'||v_emps.job||';薪水:'||v_emps.sal);
  end loop; //退出游标
end; //关闭

 

3.Oracle存储过程

(1)创建一个过程，能向depts表中添加一个新记录

create or replace procedure d1  //or replace：已有过程则替换更新
( v_dname in  depts.dname%type,
v_deptno in depts.deptno%type,
v_loc in depts.loc%type) is
begin
  insert into depts
  values (v_deptno,v_dname,v_loc);
  commit;
end;

 

(2)从emps表中查询给定职工（提示：使用&来输入员工编号）的职工姓名和工资。

create or replace procedure d2
( v_no in emps.empno%type, //读入
v_name out emps.ename%type, //读出，out可以改变变量数值
v_sal out emps.sal%type ) is
begin
select ename,sal into v_name,v_sal from emps where empno = v_no;
dbms_output.put_line(v_name||'------'||v_sal);
end;

 

(3)创建存储过程，根据员工编号删除emps表中的相关记录。

create or replace procedure d3
(v_empno  emps.empno%type ) is  //定义同类型变量
begin 
  delete from emps where empno = v_empno;
  commit;
  dbms_output.put_line('删除员工编号为:'||v_empno); //打印
end;

 

4.Oracle存储函数

(1)创建一个函数，以员工号为参数，返回该员工的工资

create or replace function f1(v_empno emps.empno%type ) return emps.sal%type is  //创建函数，控制同类型变量
v_sal emps.sal%type;  
begin
  select sal into v_sal from emps where empno = v_empno;
  return v_sal;
end;

 

(2)创建一个函数，以部门号为参数，返回该部门的平均工资

create or replace function f2(v_deptno emps.deptno%type) return emps.sal%type is
avg_sal emps.sal%type;
begin
  select avg(sal) into avg_sal from emps where deptno = v_deptno group by deptno;
  return avg_sal;
end;

 

(3)创建一个函数，以员工号为参数，返回该员工所在的部门的平均工资

create or replace function f3(v_empno emps.empno%type) 
return emps.sal%type is
avg_sal emps.sal%type;
v_deptno emps.deptno%type;
begin 
  select deptno into v_deptno from emps where empno = v_empno; //先查员工号
  select avg(sal) into avg_sal from emps where deptno = v_deptno group by deptno;
  return avg_sal;  //返回平均薪资
end;

 

5.Oracle触发器

(1)创建触发器，比较emps表中更新的工资

create or replace trigger t1 //创建触发器
before update of sal on emps //更新薪资操作之前
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;
end; 

 

(2)创建触发器，用来记录表的删除数据

创建记录表
--首先创建一个旧的表用来删除数据
create or replace trigger t2
before delete on depts
for each row
begin
  insert into depts_delete_copy values(:old.deptno,:old.dname,:old.loc);
  dbms_output.put_line('删除成功');
end;
触发器
create or replace trigger t2
before delete on depts
for each row
begin
  insert into depts_delete_copy values(:old.deptno,:old.dname,:old.loc);
  dbms_output.put_line('删除成功');
end;

  

(3)当用户对dept表执行DML语句时，将相关信息记录到日志表

create or replace trigger t3
after delete or insert or update on depts
for each row
declare
v_type depts_log.l_type%type;
begin
  if inserting then
    v_type := 'insert';
    dbms_output.put_line('记录已经成功插入，并已记录到日志');
  elsif updating then 
    v_type := 'update';
    dbms_output.put_line('记录已经成功更新，并已记录到日志');
  elsif deleting then 
    v_type := 'delete';
    dbms_output.put_line('记录已经成功删除，并已记录到日志');
  end if;
  insert into depts_log values
  (v_type,to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
end;
创建日志记录表：
create table depts_log(
    l_type varchar2(15),
	l_time varchar2(30)
);