PROCEDURE（使用RETURNS）

使用RETURNS的PROCEDURE迁移为使用RETURNS的FUNCTION。

Netezza语法	迁移后语法
CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8)) RETURNS INTEGER LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE V_PAR_DAY ALIAS for $1; V_PRCNAME NVARCHAR(50):= 'SP_O_HXYW_LNSACCTINFO_H'; V_CNT INTEGER; V_STEP_INFO NVARCHAR(500); D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP; O_RETURN INTEGER; BEGIN O_RETURN := 0; --写日志，记录过程开始运行 CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' '); V_STEP_INFO := '1.初始化'; BEGIN --1.1 SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1'; if V_CNT>0 then EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1'; end if; END; RETURN O_RETURN; END; END_PROC;	CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8)) RETURN INTEGER AS V_PAR_DAY ALIAS for $1; V_PRCNAME NCHAR VARYING(50):= 'SP_O_HXYW_LNSACCTINFO_H'; V_CNT INTEGER; V_STEP_INFO NCHAR VARYING(500); D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP; O_RETURN INTEGER; BEGIN O_RETURN := 0; /* 写日志，记录过程开始运行 / SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' '); V_STEP_INFO := '1.初始化'; BEGIN / 1.1 / SELECT COUNT() INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1'); if V_CNT>0 then EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1'; end if; END; RETURN O_RETURN; END; /

Netezza语法

迁移后语法

CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8)) 
RETURNS INTEGER 
LANGUAGE NZPLSQL AS 
BEGIN_PROC 
DECLARE
    V_PAR_DAY ALIAS for $1;
    V_PRCNAME    NVARCHAR(50):= 'SP_O_HXYW_LNSACCTINFO_H';
    V_CNT        INTEGER;
    V_STEP_INFO   NVARCHAR(500);
    D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP;
    O_RETURN INTEGER;
BEGIN
  O_RETURN := 0;

  --写日志，记录过程开始运行
  CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' ');

  V_STEP_INFO := '1.初始化';
  BEGIN
    --1.1
    SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1';
    if V_CNT>0 then
       EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
    end if;
  END;

 RETURN O_RETURN;

END;
END_PROC;

CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8))
RETURN INTEGER
AS
    V_PAR_DAY ALIAS for $1;
    V_PRCNAME    NCHAR VARYING(50):= 'SP_O_HXYW_LNSACCTINFO_H';
    V_CNT        INTEGER;
    V_STEP_INFO   NCHAR VARYING(500);
    D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP;
    O_RETURN INTEGER;
BEGIN
  O_RETURN := 0;

  /* 写日志，记录过程开始运行 */
  SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' ');

  V_STEP_INFO := '1.初始化';
  BEGIN
    /* 1.1 */
    SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1');
    if V_CNT>0 then
       EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
    end if;
  END;

 RETURN O_RETURN;

END;
/

修饰语言

nzplSQL语言迁移为plpgSQL语言，或者直接删除。

Netezza语法	迁移后语法
CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8)) RETURNS INTEGER LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE V_PAR_DAY ALIAS for $1; V_PRCNAME NVARCHAR(50):= 'SP_O_HXYW_LNSACCTINFO_H'; V_CNT INTEGER; V_STEP_INFO NVARCHAR(500); D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP; O_RETURN INTEGER; BEGIN O_RETURN := 0; --写日志，记录过程开始运行 CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' '); V_STEP_INFO := '1.初始化'; BEGIN --1.1 SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1'; if V_CNT>0 then EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1'; end if; END; RETURN O_RETURN; END; END_PROC;	CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8)) RETURN INTEGER AS V_PAR_DAY ALIAS for $1; V_PRCNAME NCHAR VARYING(50):= 'SP_O_HXYW_LNSACCTINFO_H'; V_CNT INTEGER; V_STEP_INFO NCHAR VARYING(500); D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP; O_RETURN INTEGER; BEGIN O_RETURN := 0; /* 写日志，记录过程开始运行 / SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' '); V_STEP_INFO := '1.初始化'; BEGIN / 1.1 / SELECT COUNT() INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1'); if V_CNT>0 then EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1'; end if; END; RETURN O_RETURN; END; /

Netezza语法

迁移后语法

CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8)) 
RETURNS INTEGER 
LANGUAGE NZPLSQL AS
BEGIN_PROC 
DECLARE
    V_PAR_DAY ALIAS for $1;
    V_PRCNAME    NVARCHAR(50):= 'SP_O_HXYW_LNSACCTINFO_H';
    V_CNT        INTEGER;
    V_STEP_INFO   NVARCHAR(500);
    D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP;
    O_RETURN INTEGER;
BEGIN
  O_RETURN := 0;

  --写日志，记录过程开始运行
  CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' ');

  V_STEP_INFO := '1.初始化';
  BEGIN
    --1.1
    SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1';
    if V_CNT>0 then
       EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
    end if;
  END;

 RETURN O_RETURN;

END;
END_PROC;

CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8))
RETURN INTEGER
AS
    V_PAR_DAY ALIAS for $1;
    V_PRCNAME    NCHAR VARYING(50):= 'SP_O_HXYW_LNSACCTINFO_H';
    V_CNT        INTEGER;
    V_STEP_INFO   NCHAR VARYING(500);
    D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP;
    O_RETURN INTEGER;
BEGIN
  O_RETURN := 0;

  /* 写日志，记录过程开始运行 */
  SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' ');

  V_STEP_INFO := '1.初始化';
  BEGIN
    /* 1.1 */
    SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1');
    if V_CNT>0 then
       EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
    end if;
  END;

 RETURN O_RETURN;

END;
/

进程编译规范

如果进程以Begin_PROC开始以END_PROC结束，则直接删除。

Netezza语法	迁移后语法
CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8)) RETURNS INTEGER LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE V_PAR_DAY ALIAS for $1; V_PRCNAME NVARCHAR(50):= 'SP_O_HXYW_LNSACCTINFO_H'; V_CNT INTEGER; V_STEP_INFO NVARCHAR(500); D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP; O_RETURN INTEGER; BEGIN O_RETURN := 0; --写日志，记录过程开始运行 CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' '); V_STEP_INFO := '1.初始化'; BEGIN --1.1 SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1'; if V_CNT>0 then EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1'; end if; END; RETURN O_RETURN; END; END_PROC;	CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8)) RETURN INTEGER AS V_PAR_DAY ALIAS for $1; V_PRCNAME NCHAR VARYING(50):= 'SP_O_HXYW_LNSACCTINFO_H'; V_CNT INTEGER; V_STEP_INFO NCHAR VARYING(500); D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP; O_RETURN INTEGER; BEGIN O_RETURN := 0; /* 写日志，记录过程开始运行 / SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' '); V_STEP_INFO := '1.初始化'; BEGIN / 1.1 / SELECT COUNT() INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1'); if V_CNT>0 then EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1'; end if; END; RETURN O_RETURN; END; /

Netezza语法

迁移后语法

CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8))
RETURNS INTEGER
LANGUAGE NZPLSQL AS
BEGIN_PROC 
DECLARE
    V_PAR_DAY ALIAS for $1;
    V_PRCNAME    NVARCHAR(50):= 'SP_O_HXYW_LNSACCTINFO_H';
    V_CNT        INTEGER;
    V_STEP_INFO   NVARCHAR(500);
    D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP;
    O_RETURN INTEGER;
BEGIN
  O_RETURN := 0;

  --写日志，记录过程开始运行
  CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' ');

  V_STEP_INFO := '1.初始化';
  BEGIN
    --1.1
    SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1';
    if V_CNT>0 then
       EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
    end if;
  END;

 RETURN O_RETURN;

END;
END_PROC;

CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8))
RETURN INTEGER
AS
    V_PAR_DAY ALIAS for $1;
    V_PRCNAME    NCHAR VARYING(50):= 'SP_O_HXYW_LNSACCTINFO_H';
    V_CNT        INTEGER;
    V_STEP_INFO  NCHAR VARYING(500);
    D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP;
    O_RETURN INTEGER;
BEGIN
  O_RETURN := 0;

  /* 写日志，记录过程开始运行 */
 SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' ');

  V_STEP_INFO := '1.初始化';
  BEGIN
    /* 1.1 */
    SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1');
    if V_CNT>0 then
       EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
    end if;
  END;

 RETURN O_RETURN;

END;
/

声明局部变量的关键字DECLARE

DECLARE应该修改为AS。

Netezza语法	迁移后语法
CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8)) RETURNS INTEGER LANGUAGE NZPLSQL AS BEGIN_PROC DECLARE V_PAR_DAY ALIAS for $1; V_PRCNAME NVARCHAR(50):= 'SP_O_HXYW_LNSACCTINFO_H'; V_CNT INTEGER; V_STEP_INFO NVARCHAR(500); D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP; O_RETURN INTEGER; BEGIN O_RETURN := 0; --写日志，记录过程开始运行 CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' '); V_STEP_INFO := '1.初始化'; BEGIN --1.1 SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1'; if V_CNT>0 then EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1'; end if; END; RETURN O_RETURN; END; END_PROC;	CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8)) RETURN INTEGER AS V_PAR_DAY ALIAS for $1; V_PRCNAME NCHAR VARYING(50):= 'SP_O_HXYW_LNSACCTINFO_H'; V_CNT INTEGER; V_STEP_INFO NCHAR VARYING(500); D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP; O_RETURN INTEGER; BEGIN O_RETURN := 0; /* 写日志，记录过程开始运行 / SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' '); V_STEP_INFO := '1.初始化'; BEGIN / 1.1 / SELECT COUNT() INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1'); if V_CNT>0 then EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1'; end if; END; RETURN O_RETURN; END; /

Netezza语法

迁移后语法

CREATE OR REPLACE PROCEDURE "DWDB"."EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8))
RETURNS INTEGER
LANGUAGE NZPLSQL AS
BEGIN_PROC 
DECLARE
    V_PAR_DAY ALIAS for $1;
    V_PRCNAME    NVARCHAR(50):= 'SP_O_HXYW_LNSACCTINFO_H';
    V_CNT        INTEGER;
    V_STEP_INFO   NVARCHAR(500);
    D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP;
    O_RETURN INTEGER;
BEGIN
  O_RETURN := 0;

  --写日志，记录过程开始运行
  CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' ');

  V_STEP_INFO := '1.初始化';
  BEGIN
    --1.1
    SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME='TMPO_HXYW_LNSACCTINFO_H1';
    if V_CNT>0 then
       EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
    end if;
  END;

 RETURN O_RETURN;

END;
END_PROC;

CREATE OR REPLACE FUNCTION "EDW"."SP_O_HXYW_LNSACCTINFO_H"(CHARACTER VARYING(8))
RETURN INTEGER
AS
    V_PAR_DAY ALIAS for $1;
    V_PRCNAME    NCHAR VARYING(50):= 'SP_O_HXYW_LNSACCTINFO_H';
    V_CNT        INTEGER;
    V_STEP_INFO  NCHAR VARYING(500);
    D_TIME_START TIMESTAMP:= CURRENT_TIMESTAMP;
    O_RETURN INTEGER;
BEGIN
  O_RETURN := 0;

  /* 写日志，记录过程开始运行 */
 SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'过程开始运行！',' ');

  V_STEP_INFO := '1.初始化';
  BEGIN
    /* 1.1 */
    SELECT COUNT(*) INTO V_CNT FROM information_schema.columns WHERE TABLE_NAME=lower('TMPO_HXYW_LNSACCTINFO_H1');
    if V_CNT>0 then
       EXECUTE IMMEDIATE 'DROP TABLE TMPO_HXYW_LNSACCTINFO_H1';
    end if;
  END;

 RETURN O_RETURN;

END;
/

父主题： Netezza语法迁移

上一篇：表（Netezza）

下一篇：Procedure

意见反馈

文档内容是否对您有帮助？

有帮助没帮助

提供反馈

提交成功！非常感谢您的反馈，我们会继续努力做到更好！

系统繁忙，请稍后重试

在使用文档中是否遇到以下问题

内容与产品页面不一致

内容不易理解

缺失示例代码

步骤不可操作

搜不到想要的内容

缺少最佳实践

意见反馈（选填）

0/500

请至少选择一项反馈信息并填写问题反馈

字符长度不能超过500

直接提交取消