编写一个程序以接受deptno作为参数，并显示该deptno的详细信息，还显示总薪资，员工人数，最高薪水和平均薪水? [英] Write a procedure to accept the deptno as parameter and display the details of that dept also display the total salary, no of employees, max sal and avg sal?

问题描述

编写一个程序以接受deptno作为参数并显示该dept的详细信息还显示总薪水，雇员总数，最高工资和平均工资?

Write a procedure to accept the deptno as parameter and display the details of that dept also display the total salary, no of employees, max sal and avg sal?

推荐答案

创建或替换过程EMPPRO(DEPTNOV NUMBER)
IS
光标A IS
SELECT * FROM EMP WHERE DEPTNO = DEPTNOV;
B A％ROWTYPE;
NOE NUMBER:= 0；
总号码:= 0；
AVGS NUMBER(7,2):= 0;
最大数(7,2):= 0;
开始
OPEN A;
循环
将A放入B；
当A％找不到时退出；
DBMS_OUTPUT.PUT_LINE(''EMPNO:''|| B.EMPNO);
DBMS_OUTPUT.PUT_LINE(''ENAME:''|| B.ENAME);
DBMS_OUTPUT.PUT_LINE(''JOB:''|| B.JOB);
DBMS_OUTPUT.PUT_LINE(''SA​​L:''|| B.SAL);
DBMS_OUTPUT.PUT_LINE(''HIREDATE:''|| B.HIREDATE);
DBMS_OUTPUT.PUT_LINE(''COMM:''|| B.COMM);
DBMS_OUTPUT.PUT_LINE(''**********************''));
TOT:= TOT + B.SAL;
NOE:= NOE + 1;
IF B.SAL> MAXS THEN
最大值:= B.SAL;
END IF;
END LOOP;
AVGS:= TOT/NOE;
DBMS_OUTPUT.PUT_LINE(''员工人数为NO:''|| NOE);
DBMS_OUTPUT.PUT_LINE(''TOTAL SALARY:''|| TOT);
DBMS_OUTPUT.PUT_LINE(''AVG SALARY:''|| AVGS);
DBMS_OUTPUT.PUT_LINE(''MAX SALARY:''|| MAXS);
CLOSE A;
END;

CREATE OR REPLACE PROCEDURE EMPPRO(DEPTNOV NUMBER)
IS
CURSOR A IS
SELECT * FROM EMP WHERE DEPTNO=DEPTNOV;
B A%ROWTYPE;
NOE NUMBER:=0;
TOT NUMBER:=0;
AVGS NUMBER(7,2):=0;
MAXS NUMBER(7,2):=0;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(''EMPNO :''||B.EMPNO);
DBMS_OUTPUT.PUT_LINE(''ENAME :''||B.ENAME);
DBMS_OUTPUT.PUT_LINE(''JOB :''||B.JOB);
DBMS_OUTPUT.PUT_LINE(''SAL :''||B.SAL);
DBMS_OUTPUT.PUT_LINE(''HIREDATE :''||B.HIREDATE);
DBMS_OUTPUT.PUT_LINE(''COMM :''||B.COMM);
DBMS_OUTPUT.PUT_LINE(''**********************'');
TOT:=TOT+B.SAL;
NOE:=NOE+1;
IF B.SAL>MAXS THEN
MAXS:=B.SAL;
END IF;
END LOOP;
AVGS:=TOT/NOE;
DBMS_OUTPUT.PUT_LINE(''NO OF EMPLOYEE :''||NOE);
DBMS_OUTPUT.PUT_LINE(''TOTAL SALARY :''||TOT);
DBMS_OUTPUT.PUT_LINE(''AVG SALARY :''||AVGS);
DBMS_OUTPUT.PUT_LINE(''MAX SALARY :''||MAXS);
CLOSE A;
END;

这篇关于编写一个程序以接受deptno作为参数，并显示该deptno的详细信息，还显示总薪资，员工人数，最高薪水和平均薪水?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！