数据仓库服务 GAUSSDB(DWS)-Procedure:表达式

时间：2024-10-26 11:41:18

表达式

将SELECT结果赋值为变量。

Netezza语法

迁移后语法

       
            CREATE OR REPLACE PROCEDURE sp_sel_res_to_var 
   ( NATIONAL CHARACTER VARYING(10) ) 
     RETURNS CHARACTER VARYING(ANY) 
     LANGUAGE NZPLSQL 
 AS BEGIN_PROC 
 DECLARE 
      counts INTEGER := 0 ; 
      I_LOAD_DT ALIAS FOR $1 ; 
 BEGIN 
       
         COUNTS := SELECT COUNT( * ) 
                     FROM tb_sel_res_to_var 
                    WHERE ETLDATE = I_LOAD_DT; 
                           
   EXECUTE IMMEDIATE 'insert into TABLES_COUNTS values( ''tb_sel_res_to_var'', ''' || I_LOAD_DT || ''', ' || COUNTS || ')' ; 
       
      RETURN '0' ; 
 END; 
 END_PROC;

       
            CREATE OR REPLACE FUNCTION sp_sel_res_to_var 
   ( NATIONAL CHARACTER VARYING(10) ) 
     RETURN CHARACTER VARYING 
 AS  
      counts INTEGER := 0 ; 
      I_LOAD_DT ALIAS FOR $1 ; 
 BEGIN 
       
         SELECT COUNT(*) 
     INTO COUNTS 
           FROM tb_sel_res_to_var 
          WHERE ETLDATE = I_LOAD_DT; 
                           
   EXECUTE IMMEDIATE 'insert into TABLES_COUNTS values( ''tb_sel_res_to_var'', ''' || I_LOAD_DT || ''', ' || COUNTS || ')' ; 
       
      RETURN '0' ; 
 END; 
 /