How to create anonymous block in DB2 like as PL/SQL

Question

Oracle PL/SQL Code:

declare
  N integer not null := 0;
  null_variable exception;
begin
  select col1 into N from T;
  if N is null then
    raise null_variable;
  end if;

exception
  when null_variable then
    dbms_output.put_line('Null variable detected');
end;

For DB2 unable to create.

Answer 1

Place the following to a file q1.sql:

--#SET TERMINATOR @

set serveroutput on@

BEGIN
    DECLARE N INT DEFAULT 0;
    select col1 into N from T;
    IF N IS NULL THEN 
        CALL dbms_output.put_line ('Null variable detected');
    END IF;
END@

set serveroutput off@

Run it:

db2 -f q1.sql

If you like to use an exception handler, then:

BEGIN
    DECLARE N INT DEFAULT 0;
    DECLARE null_variable CONDITION FOR '75001';
    DECLARE EXIT HANDLER FOR null_variable
    BEGIN
        --DECLARE L_TEXT VARCHAR (32672) DEFAULT '*';
        --GET DIAGNOSTICS EXCEPTION 1 L_TEXT = MESSAGE_TEXT;
        --CALL dbms_output.put_line ('MSG: ' || L_TEXT);
        CALL dbms_output.put_line ('Null variable detected');
    END;
    select col1 into N from T;
    IF N IS NULL THEN 
        SIGNAL null_variable 
        SET MESSAGE_TEXT = 'Some message';
    END IF;
END@

Refer the Compound SQL (compiled) statement topic for more details.