Oracle在创建存储过程时出错.ORA-22095:无法访问非嵌套表项中的行

Question

大家好，我需要在Oracle中使用动态数据透视来挑选记录。我已经完成了我的查询，该查询使用"；AntotsPivoting；https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/.

当我在过程中添加查询时，它会给出错误：ORA-22095:无法访问非嵌套表项中的行。

示例表脚本：

CREATE TABLE DEPARTMENT(DEPT_ID NUMBER PRIMARY KEY, DEPT_NAME VARCHAR2(25))
CREATE TABLE EMPLOYEE(EMP_ID NUMBER, EMP_NAME VARCHAR(100), DEPT_ID NUMBER , FOREIGN KEY(DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID))

INSERT INTO DEPARTMENT(DEPT_ID, DEPT_NAME)
SELECT  1, 'HR' FROM DUAL
UNION ALL SELECT 2, 'OPS' FROM DUAL
UNION ALL SELECT 3, 'MKT' FROM DUAL
UNION ALL SELECT 4, 'FIN' FROM DUAL
UNION ALL SELECT 5, 'IT' FROM DUAL
UNION ALL SELECT 6, 'SERV' FROM DUAL
COMMIT;

INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, DEPT_ID)
SELECT 1, 'A', 1 FROM DUAL
UNION ALL SELECT 1, 'A', 1 FROM DUAL
UNION ALL SELECT 2, 'B', 1 FROM DUAL
UNION ALL SELECT 3, 'C', 2 FROM DUAL
UNION ALL SELECT 4, 'D', 3 FROM DUAL
UNION ALL SELECT 5, 'E', 3 FROM DUAL
UNION ALL SELECT 6, 'F', 4 FROM DUAL
UNION ALL SELECT 7, 'G', 4 FROM DUAL
UNION ALL SELECT 8, 'H', 4 FROM DUAL
UNION ALL SELECT 9, 'I', 5 FROM DUAL
UNION ALL SELECT 10, 'J', 5 FROM DUAL
UNION ALL SELECT 11, 'K', 1 FROM DUAL
UNION ALL SELECT 12, 'L', 1 FROM DUAL
COMMIT;

示例查询脚本运行良好：

SELECT D.DEPT_NAME, COUNT(E.EMP_ID) TOTAL_EMP
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_ID= E.DEPT_ID
GROUP BY D.DEPT_NAME
;

动态枢轴示例运行良好：

select * from table( pivot(  '
SELECT D.DEPT_NAME, COUNT(E.EMP_ID) TOTAL_EMP
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_ID= E.DEPT_ID
GROUP BY D.DEPT_NAME' ) )
;

出现错误的添加过程：

CREATE OR REPLACE PROCEDURE GET_EMPLOYEE (P_RESULT OUT SYS_REFCURSOR)
IS
BEGIN 
OPEN P_RESULT FOR
select * from table( pivot(  '
SELECT D.DEPT_NAME, COUNT(E.EMP_ID) TOTAL_EMP
FROM DEPARTMENT D
LEFT JOIN EMPLOYEE E ON D.DEPT_ID= E.DEPT_ID
GROUP BY D.DEPT_NAME' ) )
;

END
;

Answer 1

您创建了Pivot函数吗？

请浏览下面已经回答的链接。

链接