作业内容：

1、创建employees表，包含employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id。

2、编写PL/SQL程序，实现如下功能：输入一个员工号，修改该员工的工资。如果该员工为10号部门，则工资增加100元；若为20号部门，则工资增加140元；若为30号部门，则工资增加200元；否则增加300元。

3、编写PL/SQL程序，实现如下功能：利用for循环统计并输出各个部门的平均工资。

不多说废话，直接上代码：

步骤一：建表

CREATE TABLE employees (
  EMPLOYEE_ID numeric(6) NOT NULL primary key,
  FIRST_NAME varchar2(20) DEFAULT NULL,
  LAST_NAME varchar2(25) NOT NULL,
  EMAIL varchar2(25) NOT NULL,
  HIRE_DATE date NOT NULL,
  JOB_ID varchar2(10) NOT NULL,
  SALARY decimal(8,2) DEFAULT NULL,
  DEPARTMENT_ID numeric(4) DEFAULT NULL
);

步骤二：声明日期格式为美国，并加入数据

 alter session set nls_date_language='american';

insert into employees
values(024226,'c','xy','qfwy@qfwys.cn','23-Aug-2001','01','1000','10');
insert into employees
values(024219,'l','cb','lcb@qfwys.cn','24-Aug-2001','02','10000','400');
insert into employees
values(024203,'c','x','cx@qfwys.cn','22-Aug-2001','03','10','10');

步骤三：编写程序

DECLARE
  v_deptno employees.department_id%type;
  v_increment NUMBER(4);
  v_empno  employees.employee_id%type;
BEGIN
  v_empno:=&x;
  SELECT department_id INTO v_deptno FROM employees 
  WHERE employee_id=v_empno;
  CASE v_deptno
    WHEN 10 THEN v_increment:=100;
    WHEN 20 THEN v_increment:=140;
    WHEN 30 THEN v_increment:=200;
    ELSE  v_increment:=300;
 END CASE;
 UPDATE employees SET salary=salary+v_increment 
 WHERE employee_id=v_empno; 
END;
/

步骤四：打开输出，编写程序

set serveroutput on

DECLARE
  CURSOR c_dept_stat IS 
  SELECT department_id,avg(salary) avgsal
  FROM employees GROUP BY department_id;
BEGIN
  FOR v_dept IN c_dept_stat LOOP
    DBMS_OUTPUT.PUT_LINE(v_dept.department_id||' '||v_dept.avgsal);
  END LOOP;
END;
/