CREATE GLOBAL TEMPORARY TABLE "DBA"."PROC_PARAM_VALUE" (
  "PROC_NAME" long varchar NOT NULL DEFAULT '',
  "PARAM_NAME" long varchar NOT NULL DEFAULT '',
  "PARAM_VALUE" long varchar NOT NULL DEFAULT '',
  "PARAM_TYPE" integer NOT NULL DEFAULT  0 ,
  CONSTRAINT "PROC_PARAM_VALUE_PK" PRIMARY KEY ( "PROC_NAME", "PARAM_NAME" )
) NOT TRANSACTIONAL;

CREATE PROCEDURE "DBA"."PROC_PARAM_VALUE_SET"
(
  in @PROC_NAME long varchar DEFAULT '',
  in @PARAM_NAME long varchar DEFAULT '',
  in @PARAM_VALUE long varchar DEFAULT '',
  in @PARAM_TYPE integer DEFAULT  10 
)
RESULT(MSG long varchar)
BEGIN
/*
description
*/
  -- comment
  declare @msg long varchar;
  declare @param_exists integer;
  declare @string_delimiter char;
  -- comment
  set @msg = '';
  set @param_exists =  0 ;
  set @string_delimiter = ',';
  -- comment
  set @PROC_NAME = ISNULL(@PROC_NAME, '');
  set @PARAM_NAME = ISNULL(@PARAM_NAME, '');
  set @PARAM_VALUE = ISNULL(@PARAM_VALUE, '');
  set @PARAM_TYPE = ISNULL(@PARAM_TYPE,  10 );
  if (@msg = '') and (@PROC_NAME = '') then
    set @msg = STRING('Procedure name is empty.');
  end if;
  -- comment
  if (@msg = '') and (@PROC_NAME = '') then
    set @msg = STRING('Parameter name is empty.');
  end if;
  -- comment
  if (@msg = '') then
    -- comment
    if exists(select  1  from DBA.PROC_PARAM_VALUE where PROC_NAME = @PROC_NAME and PARAM_NAME = @PARAM_NAME) then
      set @param_exists =  1 ;
    end if;
    -- comment
    case @PARAM_TYPE
      -- string
      when  10  then
        -- check @PARAM_VALUE

       -- strings
      when  11  then
        -- check @PARAM_VALUE
        if (@param_exists =  1 ) then
          select STRING(PARAM_VALUE, @string_delimiter, @PARAM_VALUE) into @PARAM_VALUE from DBA.PROC_PARAM_VALUE where PROC_NAME = @PROC_NAME and PARAM_NAME = @PARAM_NAME;
          set @PARAM_VALUE = ISNULL(@PARAM_VALUE, '');
        end if;

      -- integer
      when  20  then
        -- check @PARAM_VALUE

      -- decimal
      when  30  then
        -- check @PARAM_VALUE
      else
        set @msg = STRING('Invalid parameter type.');
    end case;
  end if;
  -- comment
  if (@msg = '') then
    if (@param_exists =  0 ) then
      insert into DBA.PROC_PARAM_VALUE ("PROC_NAME", "PARAM_NAME", "PARAM_VALUE", "PARAM_TYPE") values(@PROC_NAME, @PARAM_NAME, @PARAM_VALUE, @PARAM_TYPE);
    else
      update DBA.PROC_PARAM_VALUE set PARAM_VALUE = @PARAM_VALUE where PROC_NAME = @PROC_NAME and PARAM_NAME = @PARAM_NAME;
    end if;
  end if;
  -- comment
  select @msg as MSG;
END;

CREATE PROCEDURE "DBA"."PROC_PARAM_VALUE_DEL"
(
  in @PROC_NAME long varchar DEFAULT '',
  in @PARAM_NAME long varchar DEFAULT ''
)
BEGIN
  set @PROC_NAME = ISNULL(@PROC_NAME, '');
  set @PARAM_NAME = ISNULL(@PARAM_NAME, '');
  if (@PROC_NAME <> '') then
    if (@PARAM_NAME <> '') then
      delete from DBA.PROC_PARAM_VALUE where PROC_NAME = @PROC_NAME and PARAM_NAME = @PARAM_NAME;
    else
      delete from DBA.PROC_PARAM_VALUE where PROC_NAME = @PROC_NAME;
    end if;
  else
    delete from DBA.PROC_PARAM_VALUE;
  end if;
END;

CREATE PROCEDURE "DBA"."PROC_PARAM"
(
)
BEGIN
  --
  declare @string_value long varchar;
  declare @strings_value long varchar;
  declare @integer_value integer;
  declare @decimal_value decimal( 127 ,  67 );
  --
  select PARAM_VALUE into @string_value from DBA.PROC_PARAM_VALUE where PROC_NAME = 'PROC_PARAM' and PARAM_NAME = 'STRING';
  select PARAM_VALUE into @strings_value from DBA.PROC_PARAM_VALUE where PROC_NAME = 'PROC_PARAM' and PARAM_NAME = 'STRINGS';
  select PARAM_VALUE into @integer_value from DBA.PROC_PARAM_VALUE where PROC_NAME = 'PROC_PARAM' and PARAM_NAME = 'INTEGER';
  select PARAM_VALUE into @decimal_value from DBA.PROC_PARAM_VALUE where PROC_NAME = 'PROC_PARAM' and PARAM_NAME = 'DECIMAL';
  --
  set @string_value = ISNULL(@string_value, '');
  set @strings_value = ISNULL(@strings_value, '');
  set @integer_value = ISNULL(@integer_value,  0 );
  set @decimal_value = ISNULL(@decimal_value,  0 . 0 );
  --
  select
    @string_value as STRING_VALUE,
    @strings_value as STRINGS_VALUE,
    @integer_value as INTEGER_VALUE,
    @decimal_value as DECIMAL_VALUE;
END;

BEGIN
COMMIT;
  --
  call PROC_PARAM_VALUE_DEL();
  --
  call PROC_PARAM_VALUE_SET('PROC_PARAM', 'STRING', 'DEF');
  call PROC_PARAM_VALUE_SET('PROC_PARAM', 'STRINGS', 'A',  11 );
  call PROC_PARAM_VALUE_SET('PROC_PARAM', 'STRINGS', 'AB',  11 );
  call PROC_PARAM_VALUE_SET('PROC_PARAM', 'STRINGS', 'ABC',  11 );
  call PROC_PARAM_VALUE_SET('PROC_PARAM', 'INTEGER', '12345',  20 );
  call PROC_PARAM_VALUE_SET('PROC_PARAM', 'DECIMAL', '12345.54321',  30 );
  call PROC_PARAM_VALUE_SET('PROC_PARAM', 'STRING', 'ABC');
  --
  call PROC_PARAM();
  --
COMMIT;
END