让LOCATE返回变量sqldb2

下面是一个示例,在您的环境中比较其输出:

--#SET TERMINATOR @
set serveroutput on@
update command options using s on@

CREATE or replace PROCEDURE locateTEST(IN QUERYSTRING VARCHAR(128))
 LANGUAGE SQL
 specific locatetest
 BEGIN
     DECLARE switch integer default 0;
     set switch = LOCATE('select', querystring,1);
     IF switch <> 0 then
       call dbms_output.put_line('Switch: '||varchar(switch));
    ELSE
       call dbms_output.put_line('Switch: 0');
    END IF;
END@

call locatetest('    insert into fred(mycol) values(1)')@
call locatetest('    select count(*) from testtag')@

如果从Db2命令行执行(例如,在已存在数据库连接的db2cmd.exe窗口的窗口上执行),则上面给出了以下输出:

set serveroutput on
DB20000I  The SET SERVEROUTPUT command completed successfully.

update command options using s on
DB20000I  The UPDATE COMMAND OPTIONS command completed successfully.

CREATE or replace PROCEDURE locateTEST(IN QUERYSTRING VARCHAR(128))
 LANGUAGE SQL
 specific locatetest
 BEGIN
     DECLARE switch integer default 0;
     set switch = LOCATE('select', querystring,1);
     IF switch <> 0 then
       call dbms_output.put_line('Switch: '||varchar(switch));
    ELSE
       call dbms_output.put_line('Switch: 0');
    END IF;
END
DB20000I  The SQL command completed successfully.

call locatetest('    insert into fred(mycol) values(1)')

  Return Status = 0

Switch: 0

call locatetest('    select count(*) from testtag')

  Return Status = 0

Switch: 5