oracle之plSql学习

ORACLE 测试

一、完成下列SQL语句

1．查询员工姓名（last_name或first_name），hire_date , department_id
   满足以下条件：雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空

SELECT last_name, hire_date, department_id
FROM employees
WHERE hire_date > to_date('1997-1-1', 'yyyy-mm-dd') AND department_id in (80, 90, 110) AND commission_pct is not null
 
2．查询员工所在的部门名称、部门地址、姓名、工作、工资其中员工所在部门的部门名称为’Executive’

SELECT department_name, city, last_name, job_id, salary
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE department_name = 'Executive'

3．查询公司各员工工作的年数，工作的月份数（sysdate-hire_date为工作的天数），并按工作年数的降序排序。

SELECT round(months_between(sysdate, hire_date) / 12, 2) years
FROM employees
ORDER BY years desc

4．查询员工号、姓名、雇用时间、工资其中员工的管理者为’De Haan’

SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE manager_id in (
 SELECT employee_id
 FROM employees
 WHERE last_name = 'De Haan'
)

5．将公司中工资小于自己所在部门平均工资的员工工资更新为原工资的1.1倍

UPDATE employees e SET salary = salary * 1.1
WHERE salary < (SELECT avg(salary) FROM employees WHERE department_id = e.department_id)

6．查询在90或60号部门工作的员工的last_name, job_id

SELECT last_name, job_id
FROM employees
WHERE department_id IN (90, 60)

7．查询员工姓名、工资和税金，其中税金的换算关系为
   Salary             tax _rate
   0— 2999           salary*0.10
   3000—5999         salary*0.15
   6000 — 8999       salary*0.20
   9000 — 11999      salary*0.30
    〉12000           salary*0.40

SELECT last_name, salary, decode(trunc(salary / 3000), 0, salary * 0.1,
                                                       1, salary * 0.15,
                                                       2, salary * 0.2,
                                                       3, salary * 0.3,
                                 salary * 0.4) tax

FROM employees

二、完成下列存储过程

创建存储过程updatesalary(deptid number)
其中参数deptid为部门id ， 根据输入的部门id输出出该部门各员工的雇佣时间和工资，并根据以下条件对工资进行更新
hire_date                                      salary
hire_date < 1995                               salary = salary*1.2
hire_date >=1995 and hire_date <= 1998         salary = salary*1.15
hire_date > 1998 and hire_date <= 2001         salary = salary *1.10
hire_date > 2001                               salary = salary * 1.05

CREATE OR REPLACE PROCEDURE updatesalary(deptid number)
IS
 
  CURSOR emp_sal_cursor IS SELECT hire_date hd, salary sal
  FROM employees
  WHERE department_id = deptid
  FOR UPDATE;

  temp number(3, 2) := 0;

BEGIN
 
  FOR es_r IN emp_sal_cursor LOOP
   
    dbms_output.put_line('hire_date: ' || es_r.hd || ', salar: ' || es_r.sal);
   
   
    IF es_r.hd < to_date('1995-1-1', 'yyyy-mm-dd') THEN
      temp := 1.2;
    ELSIF es_r.hd < to_date('1999-1-1', 'yyyy-mm-dd') THEN
      temp := 1.15;
    ELSIF es_r.hd < to_date('2002-1-1', 'yyyy-mm-dd') THEN
      temp := 1.10;
    ELSE
      temp := 1.05;
    END IF; 
   
    UPDATE employees SET salary = salary * temp WHERE CURRENT OF emp_sal_cursor;
   
   
  END LOOP;

END;

三、完成下列函数

创建函数 Deptcount  (p_deptno  number default 10 ，p_emp_count out number)  return number
根据输入参数p_deptno（部门id）求出该部门的部门人数(p_emp_count)
如果函数正常执行               则函数返回值为0；
如果该部门人数超过100          则函数返回值为1；
如果出现其他错误               则函数返回值为2

CREATE OR REPLACE FUNCTION Deptcount(p_deptno NUMBER DEFAULT 10, p_emp_count OUT NUMBER)
RETURN number
IS

 too_many_emps EXCEPTION;

BEGIN

 SELECT count(employee_id) INTO p_emp_count
 FROM employees
 WHERE department_id = p_deptno;

 IF p_emp_count > 100 THEN
  RAISE too_many_emps;
 ELSE
  RETURN 0;
 END IF;

EXCEPTION
 WHEN too_many_emps  THEN RETURN 1;
 WHEN OTHERS THEN RETURN 2;

END;

四、创建触发器 dmltrigger 在对表 myemp 进行 insert , update 或 delete 操作前触发，
    如果是insert操作， 则将插入的数据存入 myempback 表中
    如果是update操作， 则将更新前的数据存入 myempback 表中
    如果是delete操作 ，则将删除的数据存入 myempback 表中
   
    注意:
    1. 可以利用 INSERTING, UPDATING, DELETING 识别实际触发触发器的语句类型 如: if inserting then ...
    2. 通过查看 user_users 表可以得到当前登录 oracle 数据库用户的详细信息
  
  myemp: CREATE TABLE myemp AS SELECT employee_id, last_name FROM employees;
 
  以下是myempback 表的结构
  CREATE TABLE myempback
  (  
      userid         VARCHAR2(20)  --执行操作者的用户名
    , Dmldate        date  DEFAULT SYSDATE  -- 操作时间
    , employee_id    number(6)  -- 员工id
    , last_name      VARCHAR2(20)  --名
  )

 

CREATE OR REPLACE TRIGGER dmltrigger
BEFORE INSERT OR UPDATE OR DELETE
ON myemp
FOR EACH ROW

DECLARE
 --存放当前登录用户的用户名
 username VARCHAR2(50);

BEGIN
 SELECT USERNAME INTO username FROM user_users;

 IF INSERTING THEN
  INSERT INTO myempback VALUES(username , sysdate, :new.employee_id, :new.last_name);
 ELSE
  INSERT INTO myempback VALUES(username , sysdate, :old.employee_id, :old.last_name);
 END IF;

END;