如何从预言机中的函数返回对象输出具有 DBMS 输出的对象

Question

我为员工详细信息编写了一个函数和对象。该函数返回特定员工的对象，我想通过返回对象的 DBMS 输出显示结果。但我做不到。请帮我解决这个问题。

类型：

CREATE OR REPLACE TYPE EMP_DETAILS_OBJ AS OBJECT(
EMPID NUMBER,
ENAME VARCHAR2(100),
DESIGNATION VARCHAR2(100),
DEPARTMENT VARCHAR2(50),
JOIN_DATE DATE,
COUNTRY VARCHAR2(50),
REGION VARCHAR2(30)
)
/
CREATE OR REPLACE TYPE EMP_DETAILS_TABLE IS TABLE OF EMP_DETAILS_OBJ
/

功能：

CREATE OR REPLACE FUNCTION GETEMPLOYEE_INFO(SEARCH_EMPLOYEE_ID NUMBER)
RETURN EMP_DETAILS_TABLE IS
EMPLOYEEID NUMBER;
EMPLOYEE_NAME VARCHAR2(100);
DESIGNATION VARCHAR2(100);
DEPARTMENT VARCHAR2(50);
JOIN_DATE DATE;
COUNTRY VARCHAR2(50);
REGION VARCHAR2(30);
EMP_DETAILS EMP_DETAILS_TABLE := EMP_DETAILS_TABLE();
BEGIN
EMP_DETAILS.EXTEND();
SELECT EMP.EMPLOYEE_ID,
EMP.FIRST_NAME||' '||EMP.LAST_NAME,
EMPJOB.JOB_TITLE,
DEPT.DEPARTMENT_NAME,
EMP.HIRE_DATE,
CNTRY.COUNTRY_NAME,
RGN.REGION_NAME
INTO EMPLOYEEID, EMPLOYEE_NAME, DESIGNATION, DEPARTMENT, JOIN_DATE,
COUNTRY, REGION
FROM EMPLOYEES EMP
LEFT OUTER JOIN JOBS EMPJOB ON EMPJOB.JOB_ID = EMP.JOB_ID
LEFT OUTER JOIN DEPARTMENTS DEPT ON DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID
LEFT OUTER JOIN LOCATIONS LOC ON LOC.LOCATION_ID = DEPT.LOCATION_ID
LEFT OUTER JOIN COUNTRIES CNTRY ON CNTRY.COUNTRY_ID = LOC.COUNTRY_ID
LEFT OUTER JOIN REGIONS RGN ON RGN.REGION_ID = CNTRY.REGION_ID
WHERE EMP.EMPLOYEE_ID = SEARCH_EMPLOYEE_ID ;
EMP_DETAILS(1) := EMP_DETAILS_OBJ(EMPLOYEEID, EMPLOYEE_NAME, DESIGNATION,
DEPARTMENT, JOIN_DATE, COUNTRY, REGION);
RETURN EMP_DETAILS;
END;
/

调用：

SET SERVEROUTPUT ON;
DECLARE 
--EMP_DETAILS EMP_DETAILS_TABLE :=EMP_DETAILS_TABLE();
EMP_OBJ EMP_DETAILS_OBJ;
BEGIN
EMP_OBJ :=GETEMPLOYEE_INFO(100);
--EMP_OBJ :=EMP_DETAILS_OBJ(EMP_DETAILS.EMPID,EMP_DETAILS.ENAME,EMP_DETAILS.DESIGNATION,EMP_DETAILS.DEPARTMENT,EMP_DETAILS.JOIN_DATE,EMP_DETAILS.COUNTRY,EMP_DETAILS.REGION);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID: '||EMP_OBJ.EMPID);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_NAME: ');
DBMS_OUTPUT.PUT_LINE('DESIGNATION: ');
DBMS_OUTPUT.PUT_LINE('DEPARTMENT: ');
DBMS_OUTPUT.PUT_LINE('JOIN DATE: '||EMP_DETAILS.JOIN_DATE);
DBMS_OUTPUT.PUT_LINE('COUNTRY: '||EMP_DETAILS.COUNTRY);
DBMS_OUTPUT.PUT_LINE('REGION: '||EMP_DETAILS.REGION); 
END;
/
SELECT GETEMPLOYEE_INFO(100) FROM DUAL;

Answer 1

该函数返回特定员工的对象

不，它没有。它返回一个包含单个对象的表。如果使函数返回对象：

CREATE OR REPLACE FUNCTION GETEMPLOYEE_INFO(SEARCH_EMPLOYEE_ID NUMBER)
RETURN EMP_DETAILS_OBJ IS
EMPLOYEEID NUMBER;
EMPLOYEE_NAME VARCHAR2(100);
DESIGNATION VARCHAR2(100);
DEPARTMENT VARCHAR2(50);
JOIN_DATE DATE;
COUNTRY VARCHAR2(50);
REGION VARCHAR2(30);
BEGIN
SELECT EMP.EMPLOYEE_ID,
EMP.FIRST_NAME||' '||EMP.LAST_NAME,
EMPJOB.JOB_TITLE,
DEPT.DEPARTMENT_NAME,
EMP.HIRE_DATE,
CNTRY.COUNTRY_NAME,
RGN.REGION_NAME
INTO EMPLOYEEID, EMPLOYEE_NAME, DESIGNATION, DEPARTMENT, JOIN_DATE,
COUNTRY, REGION
FROM EMPLOYEES EMP
LEFT OUTER JOIN JOBS EMPJOB ON EMPJOB.JOB_ID = EMP.JOB_ID
LEFT OUTER JOIN DEPARTMENTS DEPT ON DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID
LEFT OUTER JOIN LOCATIONS LOC ON LOC.LOCATION_ID = DEPT.LOCATION_ID
LEFT OUTER JOIN COUNTRIES CNTRY ON CNTRY.COUNTRY_ID = LOC.COUNTRY_ID
LEFT OUTER JOIN REGIONS RGN ON RGN.REGION_ID = CNTRY.REGION_ID
WHERE EMP.EMPLOYEE_ID = SEARCH_EMPLOYEE_ID ;
RETURN EMP_DETAILS_OBJ(EMPLOYEEID, EMPLOYEE_NAME, DESIGNATION,
DEPARTMENT, JOIN_DATE, COUNTRY, REGION);
END;
/

也就是说，删除EMP_DETAILS声明，并直接返回一个新对象(而不是将该对象分配给表并返回表(。

然后你的匿名块就可以工作了，引用名称也固定了：

SET SERVEROUTPUT ON;
DECLARE 
EMP_OBJ EMP_DETAILS_OBJ;
BEGIN
EMP_OBJ := GETEMPLOYEE_INFO(100);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID: ' ||EMP_OBJ.EMPID);
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_NAME: ' ||EMP_OBJ.ENAME);
DBMS_OUTPUT.PUT_LINE('DESIGNATION: ' ||EMP_OBJ.DESIGNATION);
DBMS_OUTPUT.PUT_LINE('DEPARTMENT: ' ||EMP_OBJ.DEPARTMENT);
DBMS_OUTPUT.PUT_LINE('JOIN DATE: ' ||EMP_OBJ.JOIN_DATE);
DBMS_OUTPUT.PUT_LINE('COUNTRY: ' ||EMP_OBJ.COUNTRY);
DBMS_OUTPUT.PUT_LINE('REGION: ' ||EMP_OBJ.REGION); 
END;
/
EMPLOYEE_ID: 100
EMPLOYEE_NAME: Steven King
DESIGNATION: President
DEPARTMENT: Executive
JOIN DATE: 17-JUN-87
COUNTRY: United States of America
REGION: Americas
PL/SQL procedure successfully completed.

您可以通过创建对象作为查询的一部分来进一步简化函数，因此您不需要每个列/字段的局部变量：

CREATE OR REPLACE FUNCTION GETEMPLOYEE_INFO(SEARCH_EMPLOYEE_ID NUMBER)
RETURN EMP_DETAILS_OBJ IS
L_EMP_DETAILS_OBJ EMP_DETAILS_OBJ;
BEGIN
SELECT EMP_DETAILS_OBJ(EMP.EMPLOYEE_ID,
EMP.FIRST_NAME||' '||EMP.LAST_NAME,
EMPJOB.JOB_TITLE,
DEPT.DEPARTMENT_NAME,
EMP.HIRE_DATE,
CNTRY.COUNTRY_NAME,
RGN.REGION_NAME)
INTO L_EMP_DETAILS_OBJ
FROM EMPLOYEES EMP
LEFT OUTER JOIN JOBS EMPJOB ON EMPJOB.JOB_ID = EMP.JOB_ID
LEFT OUTER JOIN DEPARTMENTS DEPT ON DEPT.DEPARTMENT_ID = EMP.DEPARTMENT_ID
LEFT OUTER JOIN LOCATIONS LOC ON LOC.LOCATION_ID = DEPT.LOCATION_ID
LEFT OUTER JOIN COUNTRIES CNTRY ON CNTRY.COUNTRY_ID = LOC.COUNTRY_ID
LEFT OUTER JOIN REGIONS RGN ON RGN.REGION_ID = CNTRY.REGION_ID
WHERE EMP.EMPLOYEE_ID = SEARCH_EMPLOYEE_ID ;
RETURN L_EMP_DETAILS_OBJ;
END;
/