I'm converting Oracle plsql function into DB2 plsql function.Below I mentioned the structure of Oracle and DB2 query.

CREATE FUNCTION FUNCTION_NAME (IN PARAMETER1 DATATYPE)
                    Return varchar(4000) IS
                    N_COLUMN1 varchar(1);
                    N_COLUMN2 INT;
                    N_COLUMN3 VARCHAR(2000);
                    BEGIN 
                    SELECT A.COLUMN1,A.COLUMN2 INTO N_COLUMN1,N_COLUMN2 FROM TABLE A WHERE A.COLUMN1=PARAMETER1;
                    IF N_COLUMN1 = 'A' then  N_COLUMN3:= 'NEW_A';
                    ELSEIF N_COLUMN1 = 'B' then  N_COLUMN3:= 'NEW_B';
                    END IF;
                    exception when others then N_COLUMN3:= 'OTHERS'
                    RETURN N_COLUMN3;
                    END;
/

DB2 query:

CREATE FUNCTION FUNCTION_NAME (IN PARAMETER1 DATATYPE)
                    Returns varchar(4000)
                    BEGIN 
                    declare N_COLUMN1 varchar(1);
                    declare N_COLUMN2 INT;
                    declare N_COLUMN3 VARCHAR(2000);
                    SELECT A.COLUMN1,A.COLUMN2 INTO N_COLUMN1,N_COLUMN2 FROM TABLE A WHERE A.COLUMN1=PARAMETER1;
                    IF N_COLUMN1 = 'A' then set N_COLUMN3= 'NEW_A';
                    ELSEIF N_COLUMN1 = 'B' then  set N_COLUMN3= 'NEW_B';
                    END IF;
                    exception when others then set N_COLUMN3= 'OTHERS'
                    RETURN N_COLUMN3;
                END;

I'm getting error on Exception part.I searched over internet and i'm getting DECLARE type HANDLER FOR SQLEXCEPTION they provide this syntax and mentioned before execution part need to declare this one.i'm not sure what word i need to use in place of type.can anyone help me what is the equaivalent DB2 syntax for exception when others

CodePudding user response：

This depends on where you want to pass the control in your code after the handler invocation.
Look at the handler-declaration description.
Below is a possible example of the function code.

CREATE FUNCTION FUNCTION_NAME 
(
  IN PARAMETER1 VARCHAR (1)
)
Returns varchar(4000)
BEGIN
  declare N_COLUMN1 varchar(1);
  declare N_COLUMN2 INT;
  declare N_COLUMN3 VARCHAR(2000);
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      -- The last statement executed in the function 
      -- when the handler fires.
      -- The control is passed out of the function body
      -- since the handler is declared 
      -- in the main BEGIN END function block.
      RETURN 'OTHERS';
    END;
  
  SELECT A.COLUMN1,A.COLUMN2 
  INTO N_COLUMN1,N_COLUMN2 
  FROM 
  --TABLE A 
  (
    VALUES
      ('A', 0)
    , ('A', 0)
    , ('B', 0)
  ) A (COLUMN1, COLUMN2)
  WHERE A.COLUMN1=PARAMETER1;
  IF N_COLUMN1 = 'A' then 
    set N_COLUMN3 = 'NEW_A';
  ELSEIF N_COLUMN1 = 'B' then
    set N_COLUMN3 = 'NEW_B';
  END IF;
  --exception when others then set N_COLUMN3= 'OTHERS'
  RETURN N_COLUMN3;
END

SELECT P, FUNCTION_NAME (P) AS F
FROM (VALUES 'A', 'B') T (P)

fiddle