用Java编辑员工信息_编写一个函数来显示基于Oracle中特定部门的员工信息？

这些是我创建的表的列表，并为创建的表插入值：

CREATE TABLE DEPARTMENT

(DEPARTMENT_ID NUMBER PRIMARY KEY,

DEPARTMENT_NAME VARCHAR(30) NOT NULL

);

CREATE TABLE JOBS

(JOB_ID NUMBER PRIMARY KEY,

JOB_TITLE VARCHAR(35) NOT NULL,

MIN_SALARY DECIMAL NOT NULL,

MAX_SALARY DECIMAL NOT NULL

);

CREATE TABLE EMPLOYEES

(EMPLOYEE_ID NUMBER PRIMARY KEY,

FIRST_NAME VARCHAR(20) NOT NULL,

LAST_NAME VARCHAR(25) NOT NULL,

EMAIL VARCHAR(25) NOT NULL,

PHONE_NUMBER VARCHAR(20) NOT NULL,

HIRE_DATE DATE NOT NULL,

JOB_ID NUMBER NOT NULL,

SALARY DECIMAL NOT NULL,

DEPARTMENT_ID NUMBER NOT NULL,

CONSTRAINT emp_job_fk FOREIGN KEY(JOB_ID) REFERENCES JOBS(JOB_ID),

CONSTRAINT emp_department_fk FOREIGN KEY(DEPARTMENT_ID) REFERENCES DEPARTMENT(DEPARTMENT_ID)

);

INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)

VALUES(1,'IT');

INSERT INTO DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME)

VALUES(2,'Sales');

INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)

VALUES (1,'IT Administrator',250000.00,50000.00);

INSERT INTO JOBS (JOB_ID,JOB_TITLE,MIN_SALARY,MAX_SALARY)

VALUES (2,'Salesman',200000.00,40000.00);

INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)

VALUES (1,'Tony','Starc','starc@gmail.com','0123456789',TO_DATE('15/1/2008','DD/MM/YYYY'),1,45000.00,1);

INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)

VALUES (2,'Bruce','Wayne','bruce@gmail.com','0123456788',TO_DATE('15/1/2009','DD/MM/YYYY'),1,40000.00,1);

INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)

VALUES (3,'Larry','Ellison','larry@gmail.com','0123456787',TO_DATE('15/1/2010','DD/MM/YYYY'),1,30000.00,1);

INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)

VALUES (4,'Steve','Jobs','steve@gmail.com','0123456786',TO_DATE('15/1/2011','DD/MM/YYYY'),2,35000.00,2);

INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)

VALUES (5,'Remy','Lebeau','remy@gmail.com','0123456785',TO_DATE('15/1/2012','DD/MM/YYYY'),2,30000.00,2);

INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,DEPARTMENT_ID)

VALUES (6,'Clark','Kent','clark@gmail.com','0123456784',TO_DATE('15/1/2013','DD/MM/YYYY'),2,20000.00,2);

现在在我的任务中，我被要求解决以下问题：

Write a function called fn_emps_per_dept_jc450912 to retrieve EMPLOYEE_ID,FIRST_NAME,LAST_NAME AND JOB_TITLE with a given DEPARTMENT_ID. This function should have DEPARTMENT_ID as input parameter and this function should have EMPLOYEE_ID,FIRST_NAME,LAST_NAME AND JOB_TITLE as output parameters. It should return TRUE if found and FALSE if not found.

为了显示特定部门员工的信息，我编写了以下功能：

CREATE OR REPLACE FUNCTION fn_emps_per_dept_jc450912 (f_dept_id NUMBER)

RETURN SYS_REFCURSOR

AS

emp_details SYS_REFCURSOR;

BEGIN

OPEN emp_details

FOR

SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,JOB_TITLE

FROM EMPLOYEES,JOBS,DEPARTMENT

WHERE DEPARTMENT.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID

AND JOBS.JOB_ID = EMPLOYEES.JOB_ID

AND EMPLOYEES.DEPARTMENT_ID = f_dept_id;

RETURN emp_details;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

DBMS_OUTPUT.PUT_LINE('Department not available');

END fn_emps_per_dept_jc450912;

VARIABLE e REFCURSOR

EXECUTE :e := fn_emps_per_dept_jc450912(1);

PRINT e;

EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_TITLE

----------- -------------------- ------------------------- ----------------------------

1 Tony Starc IT Administrator

2 Bruce Wayne IT Administrator

3 Larry Ellison IT Administrator

然后我尝试插入一个无效的部门号码：

VARIABLE e REFCURSOR

EXECUTE :e := fn_emps_per_dept_jc450912(5);

PRINT e;

我有以下3个问题：

为什么空白？可能的错误在哪里？

这个问题要求 EMPLOYEE_ID,FIRST_NAME,LAST_NAME AND JOB_TITLE should be the output parameters . 但我正在返回 EMPLOYEE_ID,FIRST_NAME,LAST_NAME AND JOB_TITLE 作为 SYS_REFCURSOR . 那么根据输出参数的问题，我的方法是否正确？

我需要修复哪部分代码？如何重写这个功能？