Oracle SQL创建函数 – 混淆编译错误

我正在尝试创建一个函数,它将从几个表中返回一行包含多个计数.这是我到目前为止所拥有的：

CREATE OR REPLACE TYPE RESULT_ROW is OBJECT
(LOC_TABLE_ENTRY_KY VARCHAR2(50),LOCATION_NAME VARCHAR2(50),A_ASSIGN_CNT VARCHAR2(50),B_ASSIGN_CNT VARCHAR2(50),C_ASSIGN_CNT VARCHAR2(50),D_ASSIGN_CNT VARCHAR2(50),E_ASSIGN_CNT VARCHAR2(50),F_ASSIGN_CNT VARCHAR2(50),G_ASSIGN_CNT VARCHAR2(50),H_ASSIGN_CNT VARCHAR2(50));
/
CREATE OR REPLACE TYPE RESULT_TABLE AS TABLE OF RESULT_ROW;
/
CREATE OR REPLACE FUNCTION LOCATION_RULE_LOOKUP(P_LOCATION_VAR IN NUMBER)
RETURN RESULT_TABLE
IS
OUT_REC RESULT_TABLE;
BEGIN
WITH LOC AS
(SELECT LOC_TABLE_ENTRY_KY,LOCATION_NAME
FROM LOCATION_CODE
WHERE LOC_TABLE_ENTRY_KY = P_LOCATION_VAR
),ONE AS
(SELECT COUNT(*) AS A_ASSIGN_CNT
FROM COLLECTOR_ASSIGNMENT
WHERE LOCATION_CODE     = P_LOCATION_VAR
AND FUNCTION_STATE_CODE = '   '
OR FUNCTION_STATE_CODE  = '***'
),TWO AS
(SELECT COUNT(*) AS B_ASSIGN_CNT
FROM COMM_PLAN_ASSGN_RULE
WHERE LOCATION_CODE = P_LOCATION_VAR
),THREE AS
(SELECT COUNT(*) AS C_ASSIGN_CNT
FROM INPUT_TRANS_ASGN
WHERE LOCATION_CODE = P_LOCATION_VAR
),FOUR AS
(SELECT COUNT(*) AS D_ASSIGN_CNT
FROM RECALL_DAYS_ASSGN_RULE
WHERE LOCATION_CODE = P_LOCATION_VAR
),FIVE AS
(SELECT COUNT(*) AS E_ASSIGN_CNT
FROM SCRIPT_VIEW_ASSIGNMENT
WHERE LOCATION_CODE     = P_LOCATION_VAR
AND FUNCTION_STATE_CODE = '   '
OR FUNCTION_STATE_CODE  = '***'
AND SCRIPT_TYPE         = 'V'
),SIX AS
(SELECT COUNT(*) AS F_ASSIGN_CNT
FROM SCRIPT_VIEW_ASSIGNMENT
WHERE LOCATION_CODE     = P_LOCATION_VAR
AND FUNCTION_STATE_CODE = '   '
OR FUNCTION_STATE_CODE  = '***'
AND SCRIPT_TYPE         = 'D'
),SEVEN AS
(SELECT COUNT(*) AS G_ASSIGN_CNT
FROM TSR_ASSGN_RULE
WHERE LOCATION_CODE = P_LOCATION_VAR
),EIGHT AS
(SELECT COUNT(*) AS H_ASSIGN_CNT
FROM TRAN_STATE_ASSGN_RULE
WHERE LOCATION_CODE = P_LOCATION_VAR
)
SELECT * INTO OUT_REC
FROM ONE,TWO,THREE,FOUR,FIVE,SIX,SEVEN,EIGHT,LOC;
RETURN OUT_REC;
END LOCATION_RULE_LOOKUP;

但无论我做什么使它看起来像现有的函数示例,无论是在这里还是在其他站点上,它都不接受BEGIN块中的任何内容. BEGIN块内的SQL有效;我可以运行所有那些WITH AS SELECT等,它会给我一行作为结果与我正在寻找的计数.但是这个函数不会编译.根据我放置空格或分号的位置,错误会发生变化.目前的错误：

Error(7,3): PL/SQL: SQL Statement ignored
Error(62,3): PL/SQL: ORA-00947: not enough values

救命？ d：

此外,您需要按正确的顺序放置FROM子句,LOC在ONE到EIGHT之前;基于表达式的相对位置执行强制转换,而不是基于字段别名. (或者,您可以更改SELECT *以按正确的顺序明确标识字段,而不是依赖于FROM子句顺序.)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!