Netezza中的光标操作

Question

CREATE OR REPLACE PROCEDURE SP_NEW_PROCEDURE1( )
RETURNS REFTABLE(employees)
LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE

    l_conditions  varchar(1000);
    p_rec         RECORD;

BEGIN

    FOR P_REC IN  select empid, mgrid, empname, salary  from employees where mgrid = 7 
    LOOP


            l_conditions  :=  'insert into  '  || 
                              REFTABLENAME     || 
                              '  VALUES ('     || 
                              P_REC.EMPID      || 
                              ','              || 
                              P_REC.MGRID      || 
                              ','              || 
                              P_REC.EMPNAME    || 
                              ','              || 
                              P_REC.SALARY     || 
                              '  ) ; ' ;

     execute immediate l_conditions; 

      l_conditions  := ' ';

    END LOOP;      
    RETURN REFTABLE;
END;
END_PROC;

当我运行以下命令时：

select SP_NEW_PROCEDURE1()

我得到了错误：

ERROR [01000] NOTICE:  Error occurred while executing PL/pgSQL function SP_NEW_PROCEDURE1
ERROR [01000] NOTICE:  line 24 at execute statement
ERROR [42S22] ERROR:  Attribute 'DAN' not found

有人能帮我吗？...thanks怎么了？

Answer 1

这与游标本身无关，而与构建动态SQL字符串的方式有关。

在Netezza存储过程中构建动态SQL时，可以使用quote_ident和quote_literal助手函数让系统知道您是在向它传递文字，还是向它传递标识符。在online documentation here中有一个例子。本质上，他们所做的就是找出所需的转义引用符号。

由于您试图将存储在P_REC记录的列中的值放入insert语句的值部分，因此可以按如下方式使用quote_literal：

CREATE OR REPLACE PROCEDURE SP_NEW_PROCEDURE1( )
RETURNS REFTABLE(employees)
LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE

l_conditions  varchar(1000);
p_rec         RECORD;

BEGIN

FOR P_REC IN  select empid, mgrid, empname, salary  from employees where mgrid = 7 
LOOP


        l_conditions  :=  'insert into  '  || 
                          REFTABLENAME     || 
                          '  VALUES ('     || 
                          quote_literal(P_REC.EMPID)  || 
                          ','              || 
                          quote_literal(P_REC.MGRID)    || 
                          ','              || 
                          quote_literal(P_REC.EMPNAME)   || 
                          ','              || 
                          quote_literal(P_REC.SALARY )    || 
                          '  ) ; ' ;

 execute immediate l_conditions; 

  l_conditions  := ' ';

END LOOP;      
RETURN REFTABLE;
END;
END_PROC;

也就是说，在Netezza这样的MPP数据库中，使用游标循环记录以一次插入一行的效率非常低。假设这个问题是您提出的关于使用递归CTE来探索层次结构的替代方法的问题的后续问题，通常循环没有什么问题，但请尽量避免逐条记录地进行循环。这是一个利用系统的MPP特性的版本。根据记录，如果您要将结果集返回到REFTABLE，那么您唯一的选择就是动态SQL。

CREATE OR REPLACE PROCEDURE SP_NEW_PROCEDURE1( )
RETURNS REFTABLE(employees)
LANGUAGE NZPLSQL AS
BEGIN_PROC
  DECLARE

    l_conditions  varchar(1000);
    p_rec         RECORD;

BEGIN

--    FOR P_REC IN  select empid, mgrid, empname, salary  from employees where mgrid = 7 
--    LOOP


            l_conditions  :=  'insert into  '  || 
                              REFTABLENAME     || 
                              '  select empid, mgrid, empname, salary  from employees where mgrid = 7 ; ' ;

     execute immediate l_conditions; 

      l_conditions  := ' ';

--    END LOOP;      
    RETURN REFTABLE;
END;
END_PROC;

Answer 2

我怀疑您正在构建一个旨在插入文字' DAN‘的查询，但该查询不包括所需的引号，因此它引用了DAN-因此优化器正在尝试查找该名称的属性。

因此，修复方法是在构建SQL insert语句时包含引号，或者(最好)只使用静态SQL来插入值，而不是立即执行。

如果有疑问，请始终查看数据，因为如果您检查了l_conditions的值，这一点可能对您来说是显而易见的。