调用失败的函数：ORA-00904：“TEMP_DATA”。“P_LNAME”：无效标识符

Question

下面的函数编译得很好。但是当以匿名的PL/SQL代码调用函数时，调用失败。 任何人都可以请建议为什么？我试图插入未匹配（新）数据并更新匹配（现有）数据。

Function Fn_Insert_Data_Using_Merge (p_Id in Number, 
            p_fname in varchar2, 
            p_mname in varchar2, 
            p_lname in varchar2, 
            p_birth_date in date) Return Boolean 
Is 
Begin 
    Dbms_Output.put_line ('Inside the function Fn_Insert_Data_Using_Merge ...'); 
    Merge Into test_employee te 
    using (select distinct p_Id, p_fname, p_mname, p_lname, p_birth_date 
      from test_employee) temp_data 
    on (te.first_name = temp_data.p_fname and 
     te.middle_name = temp_data.p_mname and 
     te.last_name = temp_data.p_lname) 
    when matched then 
     update 
     set first_name = p_fname, 
      middle_name = p_mname, 
      last_name = p_lname, 
      run_status = 'Updated' 
    when not matched then 
     insert (id, first_name, middle_name, last_name, birth_date, run_status) 
     values 
      (p_id, p_fname, p_mname, p_lname, p_birth_date, 'Inserted'); 

    Dbms_Output.put_line ('Returning successfully from the function Fn_Insert_Data_Using_Merge ...'); 
    Return True; 
Exception 
    When Dup_Val_On_Index Then 
    Dbms_Output.put_line ('The name already exists...Cannot insert again ..'); 
    Return False; 
    When Others Then 
    Dbms_Output.put_line ('Facing some critical error : ' || SQLERRM); 
    Return False; 
End Fn_Insert_Data_Using_Merge; 

Answer 1

该错误试图告诉您TEMP_DATA摘录中没有列名为P_LNAME。名为'p_lname'的函数有一个参数，但是在提取中，您没有分配列别名，因此没有命名字段。另外（尽管不是错误的原因），从可能有多行的表中执行DISTINCT是将函数参数获取到USING子句中的临时表中的一种较慢的方式;你最好使用SELECT ... FROM DUAL。尝试用以下方法替换您的MERGE声明并让我们知道它是如何发生的：

Merge Into test_employee te 
using (select p_Id as p_Id, 
       p_fname as p_fname, 
       p_mname as p_mname, 
       p_lname as p_lname, 
       p_birth_date as p_birth_date 
     from dual) temp_data 
on (te.first_name = temp_data.p_fname and 
    te.middle_name = temp_data.p_mname and 
    te.last_name = temp_data.p_lname) 
when matched then 
    update 
    set first_name = temp_data.p_fname, 
     middle_name = temp_data.p_mname, 
     last_name = temp_data.p_lname, 
     run_status = 'Updated' 
when not matched then 
    insert (id,    first_name,    middle_name, 
      last_name,   birth_date,    run_status) 
    values 
     (temp_data.p_id, temp_data.p_fname,  temp_data.p_mname, 
      temp_data.p_lname, temp_data.p_birth_date, 'Inserted'); 

分享和享受。