Oracle创建PL/SQL函数报编译终止、PLS-00201标识符未声明问题求助

问题根因 #

你遇到的两个报错是连锁关系：

• 错误2是直接原因：DIV_DUR_PRICE_TABLE是你定义的返回值集合类型，当前Oracle会话的schema下没有创建这个类型，或者你没有访问该类型的权限
• 错误1是衍生结果：因为依赖的类型不存在，PL/SQL编译器直接终止了整个编译单元的分析

修复步骤 #

步骤1：创建缺失的自定义集合类型 #

你需要先定义DIV_DUR_PRICE_TABLE对应的对象类型和嵌套表类型，结构要和你返回的查询列完全匹配，示例代码如下（可根据你实际的字段类型调整）：

-- 先定义行对象类型，字段名、类型要和GTT_DIV_DUR_PRICE_TABLE的字段完全对齐
CREATE OR REPLACE TYPE DIV_DUR_PRICE_ROW AS OBJECT (
    mydate DATE,
    column1 NUMBER(8,2),
    column2 NUMBER(8,2),
    Final_value NUMBER(8,2)
);
/
-- 再定义嵌套表集合类型，即函数返回的类型
CREATE OR REPLACE TYPE DIV_DUR_PRICE_TABLE AS TABLE OF DIV_DUR_PRICE_ROW;
/

步骤2：修正原函数中的语法错误 #

你原代码里还有几处语法问题，不修正也会编译失败：

• BEGIN; 多了分号，应该为BEGIN
• Insert Into语句末尾缺少分号、多了多余的括号
• 循环里的update tab set 没有定义tab别名，应改为实际表名GTT_DIV_DUR_PRICE_TABLE
• 最后MULTISET里的排序字段P_DATE和实际表字段mydate不匹配，需要统一

修正后的完整函数代码如下：

CREATE OR REPLACE FUNCTION DIV_DAR(FID IN VARCHAR, DATE1 IN DATE, DATE2 IN DATE)   
 RETURN DIV_DUR_PRICE_TABLE PARALLEL_ENABLE AS  
 PRAGMA AUTONOMOUS_TRANSACTION;  
    CNT NUMBER;  
    V_RET DIV_DUR_PRICE_TABLE;  
BEGIN -- 去掉多余分号
EXECUTE IMMEDIATE 'DELETE from GTT_DIV_DUR_PRICE_TABLE';  

Insert Into GTT_DIV_DUR_PRICE_TABLE 
select mydate, column1, column2, 0 as Final_value from tablename; -- 去掉多余括号、补分号

-- 内部块逻辑
declare
  v_num integer := 1;
  v_column1 number(8,2);
  v_column2 number(8,2);
  v_Final_value number(8,2);
begin
  for rec in (select * from GTT_DIV_DUR_PRICE_TABLE order by mydate)
  loop
  if(v_num = 1) then
   -- 修正表名、补全列来源
   update GTT_DIV_DUR_PRICE_TABLE set Final_value = rec.column1 where mydate = rec.mydate;     
  else
     if(rec.column2 is not null) then
      update GTT_DIV_DUR_PRICE_TABLE set Final_value =
       v_Final_value * (v_column1/rec.column1) +
       rec.column2 * (v_column1/v_Final_value) where mydate = rec.mydate;
   else
      update GTT_DIV_DUR_PRICE_TABLE set Final_value =
      v_Final_value * (rec.column1 / v_column1) where mydate = rec.mydate;
   end if;
end if;

v_num:= v_num +1;
v_column1 := rec.column1;
v_column2 := rec.column2;

select final_value into v_Final_value from GTT_DIV_DUR_PRICE_TABLE 
where mydate = rec.mydate;
 
end loop;
end;

SELECT 
            CAST(
                MULTISET(
                            -- 修正排序字段为mydate
                            SELECT * FROM GTT_DIV_DUR_PRICE_TABLE order by mydate desc
                         )AS DIV_DUR_PRICE_TABLE
                ) INTO V_RET FROM DUAL;

COMMIT;      
RETURN V_RET;
END DIV_DAR;
/

内容的提问来源于stack exchange，提问作者Amit