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

Question

基于 how to return a dynamic result set in Oracle function

我正在尝试创建一个函数，该函数将从多个表中返回具有多个计数的一行。这是我到目前为止所拥有的:

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

帮助？乙:

Answer 1

您非常接近，但是您没有足够忠实地遵循链接的问题。 not enough values的原因消息是您的 INTO 中只有一个值子句，但 Oracle 期望您有八个，因为这是您的 SELECT * 中(有效地)的表达式数量。 .您需要将这八个表达式转换为一个值。为此，您需要更改您的 WITH ... SELECT * INTO out_rec ...进入 SELECT CAST(MULTISET(WITH ... SELECT * ...) AS result_table) INTO out_rec FROM dual或 WITH ... SELECT CAST(MULTISET(SELECT * ...) AS result_table) INTO out_rec FROM dual ， 根据你喜欢的。

另外，您需要输入您的 FROM条款以正确的顺序，用 LOC前 ONE通过 EIGHT ;转换是基于表达式的相对位置执行的，而不是基于字段别名。 (或者，您可以更改 SELECT * 以按正确的顺序明确标识字段，而不是依赖于 FROM 子句顺序。)