数据库系统之Stored PL/SQL function项目练习-2

关于Stored PL/SQL的介绍可以看我的这篇文章：
https://blog.youkuaiyun.com/Jifu_M/article/details/112443652
关于Stored PL/SQL procedure的项目练习可以看我的这篇文章：
https://blog.youkuaiyun.com/Jifu_M/article/details/112450422

项目介绍

实现一个存储的PL/SQL函数DEPTPROJECT，它接受一个department number作为参数，并查找该部门持有的所有项目编号、职务和预算，以及该部门中处理该项目的员工编号和姓名。

函数必须返回一个字符串，其中包含部门编号、名称、项目编号、部门的职务和预算，以及部门中每个项目的员工编号和名称。如果一个部门有多个项目，这些项目必须按预算的降序排列。在项目中工作的员工必须按姓名的升序排列。

为所有部门执行存储的PL/SQL函数DEPTPROJECT。样本打印输出的片段如下所示:

使用SQL * Plus命令来列出包括已处理的所有SQL语句的清单：
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
SET SERVEROUTPUT ON

本项目包含以下表：

CREATE TABLE Project (
	P#		    NUMBER(10)	NOT NULL, /* Project number		*/
	PTitle		VARCHAR2(30)	NOT NULL, /* Project title		*/
	Sponsor		VARCHAR2(30),		  /* Project sponsor name	*/
	D#		    NUMBER(5)	NOT NULL, /* Department number		*/
	Budget		NUMBER(10,2)	NOT NULL, /* Project budget		*/
	CONSTRAINT Project_PK PRIMARY KEY(P#),
	CONSTRAINT Project_FK FOREIGN KEY (D#) REFERENCES Department(D#),
	CONSTRAINT Project_CK UNIQUE (PTitle)
);

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 PRIMARY KEY(D#),
	CONSTRAINT Department_CK UNIQUE(DName)
);

CREATE TABLE WorksOn (
	E#		       CHAR(5)		NOT NULL, /* Employee number		*/
	P#		       NUMBER(10)	NOT NULL, /* Project number		*/
	Hours		   NUMBER(3,1)	NOT NULL, /* Working hours per week	*/
	CONSTRAINT WorksOn_PK PRIMARY KEY(E#, P#),
	CONSTRAINT WorksOn_FK1 FOREIGN KEY(E#) REFERENCES Employee(E#),	
	CONSTRAINT WorksOn_FK2 FOREIGN KEY(P#) REFERENCES Project(P#)
);


CREATE TABLE Dependent (
	E#		     CHAR(5)		   NOT NULL, /* Employee number  		*/
	DName		VARCHAR2(30)	   NOT NULL, /* Dependent name		*/
	Sex		    CHAR,			             /* Dependent sex, M-Male, F-Female */
	DOB		    DATE,			            /* Date of birth		*/
	Relationship	VARCHAR2(10),		    /* Relationship with the employee */
	CONSTRAINT Dependent_PK PRIMARY KEY(E#, DName),
	CONSTRAINT Dependent_FK FOREIGN KEY(E#) REFERENCES Employee(E#),
	CONSTRAINT Dependent_CK CHECK (Relationship IN ('SON', 'DAUGHTER', 'SPOUSE', 'OTHER'))
);

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#)
);

项目实现

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

CREATE OR REPLACE FUNCTION DEPTPROJECT (d_num department.d#%TYPE)
RETURN VARCHAR2 IS 
p_num project.p#%TYPE;
p_title project.ptitle%TYPE;
p_budget project.budget%TYPE; 
e_name employee.name%TYPE;
e_num employee.E#%TYPE;
e_dept employee.d#%TYPE;
str VARCHAR2(300);

BEGIN

    FOR z IN (SELECT project.p#, project.ptitle, project.budget INTO p_num,p_title,p_budget
    FROM project WHERE project.d#=d_num ORDER BY budget DESC)
    LOOP
    
    DBMS_OUTPUT.PUT_LINE('  Project: '||z.p#||' '||z.ptitle||' '||z.budget);
    
        FOR x IN(SELECT employee.e#,employee.name,employee.d# INTO e_num,e_name,e_dept
                    FROM employee inner join workson ON employee.e#=workson.e#
                    WHERE workson.p#=z.p# ORDER BY e_name ASC)
        LOOP
        IF e_dept = d_num THEN
        DBMS_OUTPUT.PUT_LINE('      '||x.e#||' '||x.name);
        END IF;
        END LOOP;
        
    END LOOP;   
       
    RETURN str;
    
END;
/

--使用创建的函数
DECLARE
pro_p number;

BEGIN

    FOR y IN (SELECT department.d#,department.dname FROM department ORDER BY d# ASC)
    LOOP
    
        SELECT COUNT(project.p#) INTO pro_p FROM project WHERE d#=y.d#;
        IF pro_p=0 then
                DBMS_OUTPUT.PUT_LINE('Department: '||y.d#||' '||y.dname);
        ELSE 
                DBMS_OUTPUT.PUT_LINE('Department: '||y.d#||' '||y.dname);
                DBMS_OUTPUT.PUT_LINE(DEPTPROJECT (y.d#));
        END IF;
    END LOOP;
    
END;

运行结果如下：

Department: 1 SALES
  Project: 1001 Computation 25000

Department: 2 ACCOUNTING
Department: 3 GAMES
  Project: 1003 Racing car 225000
  Project: 1002 Study methods 15000

Department: 4 HUMAN RESOURCES
Department: 5 SPORTS
  Project: 1005 Swimming 125000
  Project: 1004 Football 35000

Department: 6 COMPUTING
Department: 7 SUPPORT