数据库系统之Stored PL/SQL procedure项目练习-1

关于Stored PL/SQL的介绍可以看我的这篇文章：
https://blog.csdn.net/Jifu_M/article/details/112443652

项目介绍

实现一个存储的PL/SQL过程deptememployees来列出部门、每个部门的经理、每个部门的员工总数以及每个部门的员工编号和姓名。

部门信息按部门号从小到大的顺序排列。每个部门的员工必须按姓名升序列出。
执行存储的PL/SQL过程DEPTEMPLOYEES。下面给出了预期示例打印输出的一个片段。

使用SQL * Plus命令来列出包括已处理的所有SQL语句的清单：

SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 100
SET SERVEROUTPUT ON

本项目包含两个表：

CREATE TABLE Department (
	D#		   NUMBER(5)	     NOT NULL, /* Department number		*/
	DName		VARCHAR2(30)	 NOT NULL, /* Department name		*/
	Manager#	CHAR(5)		     NOT NULL, /* Department manager number	*/
	MSDate		DATE, 			           /* Manager start date     	*/
	CONSTRAINT Department_PK PRIMA RY KEY(D#),
	CONSTRAINT Department_CK UNIQUE(DName)
);

CREATE TABLE Employee (
	E#		    CHAR(5)		    NOT NULL, /* Employee number    	*/
	Name		VARCHAR2(30)	NOT NULL, /* Employee name		*/
	DOB		    Date,			          /* Date of birth		*/
	Address		VARCHAR2(50),		      /* Home address		*/
	Sex		    CHAR,			          /* M-Male, F-Female		*/
	Salary		NUMBER(7,2),		      /* Salary			*/
	Super#		CHAR(5),		          /* Supervisor number		*/
	D#		   NUMBER(5), 		           /* Department number		*/
	CONSTRAINT Employee_PK PRIMARY KEY(E#),
	CONSTRAINT Employee_FK1 FOREIGN KEY (Super#) REFERENCES Employee(E#),
	CONSTRAINT Employee_FK2 FOREIGN KEY (D#) REFERENCES Department (D#)
);

项目实现

SPOOL C:\123--将代码运算结果保存为文档，后面跟着文档保存路径
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 100
SET SERVEROUTPUT ON
/* 创建 procedure DEPTEMPLOYEES*/
CREATE OR REPLACE PROCEDURE  DEPTEMPLOYEES
  AS
  /* 设新变量 Totalnumber*/
  Total_number  NUMBER;
  
BEGIN
/* 使用for循环在DEPARTMENT和EMPLOYEE表中按照升序查找D#、DNAME和NAME*/
FOR A IN    (SELECT  D.D#, D.DNAME, E.NAME
            FROM DEPARTMENT D JOIN EMPLOYEE E
            ON D.MANAGER#=E.E#
            ORDER BY D# ASC)
LOOP
/* 打印第一行 */
DBMS_OUTPUT.PUT_LINE('Department'||' '|| A.D#||' '|| A.DNAME||' '||'managed by'||' '|| A.NAME);
/* 数 E# */
SELECT COUNT(E#) INTO  Total_number
                 FROM EMPLOYEE E
                 WHERE E.D#=A.D#;
/* 打印第二行 */
DBMS_OUTPUT.PUT_LINE('Total number of employees: '||Total_number );
/* 使用for循环在EMPLOYEE中升序查找E#和NAME*/
FOR B IN (SELECT E.E#, E.NAME 
          FROM EMPLOYEE E
          WHERE E.D#=A.D# 
          ORDER BY E.NAME ASC)
LOOP
/* 使用循环打印剩下的行 */
DBMS_OUTPUT.PUT_LINE(B.E#||' '||B.NAME);

END LOOP;

END LOOP;
END  DEPTEMPLOYEES;
/
EXECUT DEPTEMPLOYEES;

运行结果如下：

Department 1 SALES managed by Alvin
Total number of employees: 5
00110 Alvin
00103 Ami
00109 Michael
00101 Peter
00107 Wendy
Department 2 ACCOUNTING managed by Alice
Total number of employees: 2
00120 Alice
00125 Angela
Department 3 GAMES managed by Bob
Total number of employees: 2
00150 Bob
00105 Robert
Department 4 HUMAN RESOURCES managed by Carl
Total number of employees: 2
00136 Aban
00200 Carl
Department 5 SPORTS managed by Douglass
Total number of employees: 2
00250 Douglass
00187 Eadger
Department 6 COMPUTING managed by Fidlar
Total number of employees: 3
00315 Fidlar
00327 James
00352 Judy
Department 7 SUPPORT managed by Helmus
Total number of employees: 6
00531 Harry
00338 Helmus
00360 Jack
00342 Jason
00456 Johnson
00310 Kelly