如何在DB2中像PL/SQL一样创建匿名块

2exbekwf  于 2022-11-07  发布在  DB2
关注(0)|答案(1)|浏览(288)

Oracle PL/SQL代码:

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;

对于DB2无法创建。

dddzy1tm

dddzy1tm1#

将以下内容放入文件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@

运行它:

db2 -f q1.sql

如果要使用异常处理程序,则:

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@

如需详细信息,请参阅复合SQL(已编译)陈述式主题。

相关问题