对oracle感兴趣的请进!(综合应用)

Question

案例介绍(希望大家做做,对自己有好处的)
某数据库有三张表，是关于某公司员工资料、薪水,部门和工作地点信息的，它们分别是emp表, dept表，和location表. 三张表的结构如下：
Emp: 用于存储员工的工号，姓名，工资信息。Column name Date type Is null Primary Key comment
-----------       ----  ----- ----  ------------       -------
Emp_id Number(5) False Yes
Emp_name Varchar2(20) False
Emp_salary Number(6)
Dept_id Number(5) False Foreign KeyDept： 用于记录部门信息，部门名称Column name Date type Is null Primary Key comment
-----------       ----  ----- ----  ------------       -------
Dept_id Number(5) False Yes
Dept_name Varchar2(20) False
Loc_id Number(5) False Foreign KeyLocation： 用于记录办公地点信息，办公地点名称Column name Date type Is null Primary Key comment
-----------       ----  ----- ----  ------------       -------
Loc_id Number(5) False Yes
Loc_name Varchar2(20) False 1、按照上表结构建立相应的表并插入下面的测试数据.
   (我已经为大家写出来了,方便大家做下面的题目)
   初始化数据：create table a_location 
(
  loc_id number(5) primary key,
  loc_name varchar(20)
);insert into a_location values(01001,'buildinga');
insert into a_location values(01005,'buildingb');
insert into a_location values(01006,'buildingc');create table a_dept
(
       dept_id number(5) primary key,
       dept_name varchar2(20),
       loc_id number(5) 
);
alter table
  a_dept add constraint fk1 foreign key (loc_id)
  references a_location(loc_id);insert into a_dept values(001,'HR',01005);
insert into a_dept values(002,'ADMIN',01001);
insert into a_dept values(003,'TR',01005);
insert into a_dept values(004,'MARKETING',01005);
insert into a_dept values(005,'IT',01001);
   
create table a_emp
(
   emp_id number(5) primary key,
   emp_name varchar2(20),
   emp_salary number(6),
   dept_id number(5)
);   
alter table a_emp add constraint a foreign key(dept_id) references a_dept(dept_id);insert into a_emp values(00101,'aaron',4200,'005');
insert into a_emp values(00203,'clara',3600,'002');
insert into a_emp values(00507,'chris',2500,'005');
insert into a_emp values(00045,'sam',1500,'005');
insert into a_emp values(00406,'jack',2200,'004');2. 创建一个存储过程来计算每个BUILDING 里上班的总员工数, 如果有没有员工工作的BUILDING, 用异常来显示一条错误信息.3．创建并应用一个修改工资的游标，为指定部门的员工按照一定规则进行加薪。规则如下：
原工资 <= 2000 , 加薪25% ;
 2000<原工资 <= 3000 , 加薪15%
   3000< 原工资 <=5000 , 加薪8% ；
5000 > 原工资  , 加薪4%
要求: 需要有一个为单个员工进行加薪的函数. 该函数有两个IN的参数, 员工编号和加薪幅度, 并返回加薪后的总薪水. 
需要有一个为单个部门内的所有员工进行加薪的存储过程. 该过程有一个IN的参数, 部门名字, 和一个OUT 的参数, 该部门的总薪资. 该过程将通过一个带参数的游标来调用为单个员工加薪的函数来为每个部门员工计算薪水.4．创建一个触发器记录上题中员工ID， 加薪前和加薪后的薪资。 5. 因为BUILDINGA 要装修, 将BUILDINGA 里的所有员工搬去BUILDINGC里面, 并给每个员工500圆的补贴费. 该补贴费由部门来发放, 所以需要知道每个部门应该付出的补贴费.

Answer 1

exec sql declare emp_cur cursor for 
select * from a_emp ;
if(sqlca.sqlcode != 1403)
{
printf("not found!\n");
return 0;
}
exec sql open emp_cur;
if()//判断是否打开游标成功
{
}
do
{ fetch emp_cur into :a_emp;
if()//FETCH数据到表结构是否成功
{}
if(a_emp.emp_salary <= 2000)
{ 
a_emp.emp_salary *= 0.25;
} els if(a_emp.emp_salary <= 3000)
{ 
a_emp.emp_salary *= 0.15;
}
else if(a_emp.emp_salary <= 5000)
{ 
a_emp.emp_salary *= 0.08;
}
else 
{ 
a_emp.emp_salary *= 0.04;
}
}while( (sqlca.sqlcode>=0&&sqlca.sqlcode!=1403)||sqlca.sqlcode==-1405 )
exec sql close emp_cur;

Answer 2

OracleDBA群35903017，欢迎加入。

Answer 3

加了这个群   OracleDBA群35903017，欢迎加入。  怎么没反映?

Answer 4

create or replace procedure proa(pv_did d.did%type)
as
cursor cur is 
select e.did,sum(e.esal)
from e,
   (select e.*
   from e
   where e.did in (
                    select d.did
    from d,l
    where d.lid=l.lid and d.did=pv_did
   )
   )
group by e.did;
v_esal e.esal%type;
v_did e.did%type;
begin
open cur;
loop
fetch cur into v_did,v_esal;
if cur%notfound
then 
raise_application_error(-20001,'该位置无部门');
else
dbms_output.put_line('部门:'||v_did||'的总工资是：'||v_esal);
end if;
end loop;
close cur;
end proa;
/

Answer 5

第3题
CREATE OR REPLACE TRIGGER AU_E
before UPDATE ON E FOR EACH ROW
begin
insert into tri values (:old.eid,:old.esal,:new.esal);
end;

Answer 6

select e.did,d.dname,count(*)*500 from e,d,l
where l.lid=1001 and d.lid=l.lid and e.did=d.did
group by e.did,d.dname
第5题,E代表员工表,D代表部门表,L代表位置表.

Answer 7

刚学，做了第一题，觉得不是很好，请大家批评指正：
create or replace procedure countallstaff
IS
       num_a number;
       num_b number;
       num_c number;
       no_staff_a EXCEPTION;
       no_staff_b EXCEPTION;
       no_staff_c EXCEPTION;
BEGIN
     select count(1) into num_a from a_emp where emp_name in (select emp_name from a_emp     where dept_id in (select dept_id from a_dept where loc_id=(select loc_id from     a_location where loc_name='buildinga')));
     select count(1) into num_b from a_emp where emp_name in (select emp_name from a_emp     where dept_id in (select dept_id from a_dept where loc_id=(select loc_id from     a_location where loc_name='buildingb')));
     select count(1) into num_c from a_emp where emp_name in (select emp_name from a_emp     where dept_id in (select dept_id from a_dept where loc_id=(select loc_id from     a_location where loc_name='buildingc')));     if num_a>0 then dbms_output.put_line('There are '|| num_a || ' staff in building A!');
     else RAISE no_staff_a; end if;     if num_b>0 then dbms_output.put_line('There are '|| num_b || ' staff in building B!');
     else RAISE no_staff_b; end if;
     
     if num_c>0 then dbms_output.put_line('There are '|| num_c || ' staff in building C!');
     else RAISE no_staff_c; end if;
     
EXCEPTION
         WHEN no_staff_a
         THEN dbms_output.put_line('There is no staff in building A!');
         WHEN no_staff_b
         THEN dbms_output.put_line('There is no staff in building B!');
         WHEN no_staff_c
         THEN dbms_output.put_line('There is no staff in building C!');
END;

Answer 8

单个员工加薪的函数：create or replace function salary_add (salary_emp_id IN a_emp.emp_id%TYPE, salary_rate IN NUMBER)
       RETURN NUMBER
IS
new_salary NUMBER(6,2);
CURSOR cr1 IS select emp_salary from a_emp where emp_id = salary_emp_id;
employee_rec cr1%ROWTYPE;
BEGIN
     OPEN cr1;
     FETCH cr1 INTO employee_rec;
     new_salary := employee_rec.emp_salary + employee_rec.emp_salary*salary_rate;
     RETURN new_salary;            --Employ's total salary after promotion
END;

Answer 9

加薪的存储过程：create or replace procedure addsalary (salary_dept_name IN a_dept.dept_id%TYPE, total_salary_dept OUT NUMBER)
IS
CURSOR cr2 IS select emp_id,emp_name, emp_salary from a_emp where dept_id=salary_dept_name;
employ_record cr2%ROWTYPE;
newsalary number(6,2);
BEGIN
     OPEN cr2;
     total_salary_dept := 0;
     LOOP
         FETCH cr2 INTO employ_record;
         EXIT WHEN cr2%NOTFOUND;
         IF employ_record.emp_salary <= 2000
            THEN newsalary := salary_add(employ_record.emp_id,0.25);
         END IF;
         IF employ_record.emp_salary > 2000 AND employ_record.emp_salary <= 3000
            THEN newsalary := salary_add(employ_record.emp_id,0.15);
         END IF;
         IF employ_record.emp_salary > 3000 AND employ_record.emp_salary <= 5000
            THEN newsalary := salary_add(employ_record.emp_id,0.08);
         END IF;
         IF employ_record.emp_salary > 5000
            THEN newsalary := salary_add(employ_record.emp_id,0.04);
         END IF;
     
         DBMS_OUTPUT.PUT_LINE('before promotion, employee ' || employ_record.emp_name ||' ''s salary is ' ||employ_record.emp_salary);
         UPDATE a_emp SET (EMP_SALARY) = newsalary where emp_id = employ_record.emp_id;  
         DBMS_OUTPUT.PUT_LINE('after promotion, employee ' || employ_record.emp_name ||' ''s salary is ' ||newsalary);
         total_salary_dept := total_salary_dept + newsalary;
         commit;
     END LOOP;
END;

Answer 10

按部门加薪存储过程：
create or replace procedure addsalary_dept
IS
CURSOR cr3 IS select dept_id,dept_name,loc_id from a_dept;
dept_id_rec cr3%ROWTYPE;
total_salary_dept NUMBER(7,2);
BEGIN
     OPEN cr3;
     LOOP
          total_salary_dept :=0;
     FETCH cr3 INTO dept_id_rec;
     EXIT WHEN cr3%NOTFOUND;
           --call procedure ADDSALARY to compute new salary
           ADDSALARY (dept_id_rec.dept_id,total_salary_dept);
           DBMS_OUTPUT.PUT_LINE('The total salary of department ' || dept_id_rec.dept_name || ' is ' || total_salary_dept);
     END LOOP;
END;记录加薪人员的触发器(需要建个表存放结果)：
CREATE OR REPLACE TRIGGER after_salary_change
       AFTER UPDATE
       ON a_emp
       FOR EACH ROW
DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
     INSERT INTO a_updated_emp
            VALUES(:NEW.emp_id,:OLD.emp_salary,:NEW.emp_salary,SYSDATE);
     COMMIT;
END;

Answer 11

把第一题改了改，csdn竟然不允许连续发帖超过三次，只好换个号发了create or replace procedure countallstaff
IS
  CURSOR cur IS
         select * from a_location;
  loc_rec cur%ROWTYPE;
  num_staff_in_building NUMBER;         
       no_staff_a EXCEPTION;
       no_staff_b EXCEPTION;
       no_staff_c EXCEPTION;
BEGIN     OPEN cur;
     LOOP
     FETCH cur into loc_rec;
     EXIT WHEN cur%NOTFOUND;
         select count(1) into num_staff_in_building from a_emp where emp_name in (select emp_name from a_emp
         where dept_id in (select dept_id from a_dept where loc_id=(select loc_id from
         a_location where loc_name=loc_rec.loc_name)));
         IF num_staff_in_building > 0 THEN dbms_output.put_line('There are '|| num_staff_in_building || ' staff in ' || loc_rec.loc_name);
         ELSE
         RAISE_APPLICATION_ERROR(-20001, 'There is no staff in '|| loc_rec.loc_name);
         END IF;
     END LOOP;
     CLOSE cur;
END;