oracle练习_oracle --按部门编号从小到大的顺序输出雇员名字、工资以及工资与平均工资的差-CSDN博客

【训练1】将雇员从一个表复制到另一个表。
步骤1：创建一个结构同EMP表一样的新表EMP1：
CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2;
步骤2：通过指定雇员编号，将雇员由EMP表移动到EMP1表：

    Sql代码   
    
  
 SET SERVEROUTPUT ON   
 DECLARE  
 v_empno NUMBER(5):=7788;  
 emp_rec emp%ROWTYPE;  
 BEGIN  
  SELECT * INTO emp_rec FROM emp WHERE empno=v_empno;  
  DELETE FROM emp WHERE empno=v_empno;  
 INSERT INTO emp1 VALUES emp_rec;  
  IF SQL%FOUND THEN  
   COMMIT;  
   DBMS_OUTPUT.PUT_LINE('雇员复制成功！');  
  ELSE   
   ROLLBACK;  
   DBMS_OUTPUT.PUT_LINE('雇员复制失败！');  
  END IF;  
 END;  

执行结果为：
雇员复制成功！
PL/SQL 过程已成功完成。
步骤2：显示复制结果：
SELECT empno,ename,job FROM emp1;
执行结果为：

    Sql代码   
    
  
 EMPNO ENAME      JOB  
 ------------- -------------- ----------------  
     7788  SCOTT      ANALYST  

说明：emp_rec变量是根据emp表定义的记录变量，SELECT...INTO...语句将整个记录传给该变量。INSERT语句将整个记录变量插入emp1表，如果插入成功(SQL%FOUND为真)，则提交事务，否则回滚撤销事务。试修改雇员编号为7902，重新执行以上程序。
【训练2】输出雇员工资，雇员工资用不同高度的*表示。
输入并执行以下程序：

    Sql代码   
    
  
 SET SERVEROUTPUT ON   
 BEGIN  
  FOR re IN (SELECT ename,sal FROM EMP)  LOOP  
   DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*'));  
  END LOOP;  
 END;  

输出结果为：

    Sql代码   
    
  
 SMITH       ********  
 ALLEN           ****************  
 WARD        *************  
 JONES           ******************************  
 MARTIN      *************  
 BLAKE       *****************************  
 CLARK           *****************************  
 SCOTT           ******************************  
 KING            **************************************************  
 TURNER      ***************  
 ADAMS       ***********  
 JAMES           **********  
 FORD            ******************************  
 MILLER          *************  
          执行结果为：  
         PL/SQL 过程已成功完成。  

说明：第一个rpad函数产生对齐效果，第二个rpad函数根据工资额产生不同数目的*。该程序采用了隐式的简略游标循环形式。
【训练3】编写程序，格式化输出部门信息。
输入并执行如下程序：

    Sql代码   
    
  
 SET SERVEROUTPUT ON   
         DECLARE  
          v_count number:=0;  
          CURSOR dept_cursor IS SELECT * FROM dept;  
         BEGIN  
           DBMS_OUTPUT.PUT_LINE('部门列表');  
 DBMS_OUTPUT.PUT_LINE('---------------------------------');  
          FOR Dept_record IN dept_cursor LOOP     
          DBMS_OUTPUT.PUT_LINE('部门编号：'|| Dept_record.deptno);  
          DBMS_OUTPUT.PUT_LINE('部门名称：'|| Dept_record.dname);  
             DBMS_OUTPUT.PUT_LINE('所在城市：'|| Dept_record.loc);  
 DBMS_OUTPUT.PUT_LINE('---------------------------------');  
       v_count:= v_count+1;  
         END LOOP;  
          DBMS_OUTPUT.PUT_LINE('共有'||to_char(v_count)||'个部门！');  
         END;  

输出结果为：

    Sql代码   
    
  
 部门列表  
 ------------------------------------  
 部门编号：10  
 部门名称：ACCOUNTING  
 所在城市：NEW YORK  
 ------------------------------------  
 部门编号：20  
 部门名称：RESEARCH  
 所在城市：DALLAS  
 ...  
 共有4个部门！  
 PL/SQL 过程已成功完成。  

说明：该程序中将字段内容垂直排列。V_count变量记录循环次数，即部门个数。
【训练4】已知每个部门有一个经理，编写程序，统计输出部门名称、部门总人数、总工资和部门经理。
输入并执行如下程序：

    Sql代码   
    
  
 SET SERVEROUTPUT ON   
 DECLARE  
  v_deptno number(8);  
  v_count number(3);  
  v_sumsal number(6);  
  v_dname  varchar2(15);  
 v_manager  varchar2(15);  
  CURSOR list_cursor IS  
    SELECT deptno,count(*),sum(sal) FROM emp group by deptno;  
 BEGIN  
   OPEN list_cursor;   
   DBMS_OUTPUT.PUT_LINE('----------- 部 门 统 计 表 -----------');  
 DBMS_OUTPUT.PUT_LINE('部门名称   总人数  总工资   部门经理');  
   FETCH list_cursor INTO v_deptno,v_count,v_sumsal;   
   WHILE list_cursor%found LOOP    
  SELECT dname INTO v_dname FROM dept  
     WHERE deptno=v_deptno;  
     SELECT ename INTO v_manager FROM emp   
     WHERE deptno=v_deptno and job='MANAGER';  
 DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_char(v_count),8)  
       ||rpad(to_char(v_sumsal),9)||v_manager);  
     FETCH list_cursor INTO v_deptno,v_count,v_sumsal;   
     END LOOP;  
         DBMS_OUTPUT.PUT_LINE('--------------------------------------');  
         CLOSE list_cursor;  
         END;  

输出结果为：

    Sql代码   
    
  
 -------------------- 部 门 统 计 表 -----------------  
         部门名称     总人数  总工资     部门经理  
         ACCOUNTING    3      8750       CLARK  
         RESEARCH      5     10875       JONES  
         SALES             6      9400       BLAKE  
         -------------------------------------------------------------  
         PL/SQL 过程已成功完成。   

说明：游标中使用到了起分组功能的SELECT语句，统计出各部门的总人数和总工资。再根据部门编号和职务找到部门的经理。该程序假定每个部门有一个经理。
【训练5】为雇员增加工资，从工资低的雇员开始，为每个人增加原工资的10%，限定所增加的工资总额为800元，显示增加工资的人数和余额。
输入并调试以下程序：

    Sql代码   
    
  
 SET SERVEROUTPUT ON   
 DECLARE   
   V_NAME CHAR(10);  
   V_EMPNO NUMBER(5);  
   V_SAL NUMBER(8);  
   V_SAL1 NUMBER(8);  
   V_TOTAL NUMBER(8) := 800;     --增加工资的总额  
 V_NUM NUMBER(5):=0;     --增加工资的人数  
          CURSOR emp_cursor IS   
           SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC;  
         BEGIN  
          OPEN emp_cursor;  
         DBMS_OUTPUT.PUT_LINE('姓名      原工资  新工资');   
         DBMS_OUTPUT.PUT_LINE('---------------------------');   
          LOOP  
             FETCH emp_cursor INTO V_EMPNO,V_NAME,V_SAL;  
 EXIT WHEN emp_cursor%NOTFOUND;  
          V_SAL1:= V_SAL*0.1;  
             IF V_TOTAL>V_SAL1 THEN  
             V_TOTAL := V_TOTAL - V_SAL1;  
             V_NUM:=V_NUM+1;  
     DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||  
         TO_CHAR(V_SAL+V_SAL1,'99999'));  
              UPDATE EMP SET SAL=SAL+V_SAL1  
              WHERE EMPNO=V_EMPNO;  
          ELSE  
 DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||TO_CHAR(V_SAL,'99999'));  
          END IF;  
         END LOOP;  
         DBMS_OUTPUT.PUT_LINE('---------------------------');  
         DBMS_OUTPUT.PUT_LINE('增加工资人数:'||V_NUM||' 剩余工资：'||V_TOTAL);    
          CLOSE emp_cursor;   
          COMMIT;  
          END;  

输出结果为：

    Sql代码   
    
  
 姓名        原工资  新工资  
         ---------------------------------------------  
 SMITH       1289   1418  
 JAMES       1531   1684  
 MARTIN      1664   1830  
 MILLER          1730   1903  
 ALLEN           1760   1936  
 ADAMS       1771   1771  
 TURNER      1815   1815  
 WARD        1830   1830  
 BLAKE       2850   2850  
 CLARK       2850   2850  
 JONES           2975   2975  
 FORD            3000   3000  
 KING            5000   5000  
 -----------------------------------------------  
 增加工资人数：5 剩余工资：3  
 PL/SQL 过程已成功完成。  