sql - 在 CTE 中使用用户定义的函数

Question

我有一个简单的函数，用于计算 Oracle 数据库中数据框中的行数(回收本教程中的代码 here)。

CREATE OR REPLACE FUNCTION TABCOUNT (tbl_name IN VARCHAR2)
   RETURN PLS_INTEGER
IS
   retval   PLS_INTEGER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tbl_name INTO retval;

   RETURN retval;
END;

如果我想将此函数应用于存储表，它执行得很好:

CREATE TABLE EXAMPLE_TABLE ("ROW_ID" INT);
INSERT INTO EXAMPLE_TABLE VALUES (1);
INSERT INTO EXAMPLE_TABLE VALUES (2);

SELECT TABCOUNT('EXAMPLE_TABLE') AS "N_ROW" FROM DUAL;

但是，我想在 CTE 中使用这种类型的函数，如下所示。

WITH TABLE_1 AS (SELECT * FROM EXAMPLE_TABLE)
SELECT 'T1' AS "TABLE_NAME", TABCOUNT (TABLE_1) AS "N_ROW"
  FROM DUAL;

出于某种原因，我无法让它运行。谁能告诉我该怎么做？如果这是一个基本问题，我深表歉意，因为我对 PL/SQL 函数还很陌生。

我想在 CTE 中执行此操作的简单原因是，使用上面的示例，我想将它应用于 CTE 中的各种不同表并合并结果，即:

编辑:在下面的示例中，“EXAMPLE_TABLE”和“EXAMPLE_TABLE_2”是占位符。假设 TABLE_1 和 TABLE_2 是中间表，它们是进一步处理查询的结果。包含“EXAMPLE_TABLE”和“EXAMPLE_TABLE_2”只是为了使示例中的 CTE 运行。

CREATE TABLE EXAMPLE_TABLE_2 AS SELECT * FROM EXAMPLE_TABLE;

WITH TABLE_1 AS (SELECT * FROM EXAMPLE_TABLE),
     TABLE_2 AS (SELECT * FROM EXAMPLE_TABLE_2)
SELECT 'T1' AS "TABLE_NAME", TABCOUNT (TABLE_1) AS "N_ROW" FROM DUAL
UNION ALL
SELECT 'T2' AS "TABLE_NAME", TABCOUNT (TABLE_2) AS "N_ROW" FROM DUAL;

Answer 1

如果我理解你是对的，那么你需要像下面这样的东西，

with table_list 
as 
( select 'EXAMPLE_TABLE' table_name from dual union all
  select 'EXAMPLE_TABLE_2' from dual
)
select t.table_name,tabcount(table_name)  no_of_rows
  from table_list t

但是，如果您使用的是 12c 或更高版本，您也可以在 WITH 子句中使用 PL/SQL 声明部分，

with 
function tabcount (tbl_name in varchar2)
return pls_integer 
is
  retval pls_integer;
begin
   execute immediate 'select count(*) from ' || tbl_name into retval;
   return retval;
end;
table_list 
as 
( select 'EXAMPLE_TABLE' table_name from dual union all
  select 'EXAMPLE_TABLE_2' from dual
)
select t.table_name,tabcount(table_name)  no_of_rows
  from table_list t

有关 WITH 子句的更多详细信息 here