Help Center/ GaussDB(DWS)/ Tool Guide/ DSC: SQL Syntax Migration Tool/ Netezza Syntax Migration/ PROCEDURE with RETURNS
Updated on 2023-04-03 GMT+08:00
View PDF

PROCEDURE with RETURNS

PROCEDURE with RETURNS will be modified to FUNCTION with RETURN.

Netezza Syntax

Syntax After Migration

 
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;

  --Writes logs and starts the recording process. 
  CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'The process running ',' ');

  V_STEP_INFO := '1.Initialization';
  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;

  /* Writes logs and starts the recording process. */
  SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'The process running',' ');

  V_STEP_INFO := '1.Initialization';
  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;
/

Qualifying Language

Migrate the nzplSQL language to the plpgSQL language or delete the language.

Netezza Syntax

Syntax After Migration

 
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;

  --Writes logs and starts the recording process. 
  CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'The process running ',' ');

  V_STEP_INFO := '1.Initialization';
  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;

  /* Writes logs and starts the recording process. */
  SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'The process running',' ');

  V_STEP_INFO := '1.Initialization';
  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;
/

Process Compilation Specification

The process which is started with Begin_PROC and ended with END_PROC should be removed.

Netezza Syntax

Syntax After Migration

 
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;

  --Writes logs and starts the recording process. 
  CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'The process running ',' ');

  V_STEP_INFO := '1.Initialization';
  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;

  /* Writes logs and starts the recording process. */
  SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'The process running',' ');

  V_STEP_INFO := '1.Initialization';
  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 Keyword to Declare the Local Variables

DECLARE should be modified to AS.

Netezza Syntax

Syntax After Migration

 
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;

  --Writes logs and starts the recording process. 
  CALL SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'The process running ',' ');

  V_STEP_INFO := '1.Initialization';
  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;

  /* Writes logs and starts the recording process. */
  SP_LOG_EXEC(V_PRCNAME,V_PAR_DAY,D_TIME_START,0,0,'The process running',' ');

  V_STEP_INFO := '1.Initialization';
  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;
/
Parent topic: Netezza Syntax Migration