CONNECT TO TESTER@

drop procedure my.count_rows@

create procedure my.count_rows
(
  in  sel_stmt varchar( 1024 )
, in  tmptbl varchar( 16 )
, out rcnt int
)
dynamic result sets  1 
begin
  declare stmt varchar( 1024 );
  declare c1 cursor with return for s1;
  
  set stmt = 
    'declare global temporary table '||tmptbl
  ||' as ('||sel_stmt||') definition only with replace not logged';
  execute immediate stmt;
  set stmt = 
    'select * from final table(insert into '||tmptbl||' '||sel_stmt||') t';
  prepare s1 from stmt;
  open c1;
  GET DIAGNOSTICS rcnt = ROW_COUNT;
end@

DROP TABLE MY.TORT@

CREATE TABLE MY.TORT
(
  ID         INT NOT NULL PRIMARY KEY,
  NAME    VARCHAR( 200 ) NOT NULL
)@

INSERT INTO MY.TORT VALUES(  1 , 'First row' )@
INSERT INTO MY.TORT VALUES(  2 , 'Second row' )@
INSERT INTO MY.TORT VALUES(  3 , 'Third row' )@
INSERT INTO MY.TORT VALUES(  4 , 'Fourth row' )@

DROP TABLESPACE torts1@

COMMIT@

CREATE USER TEMPORARY TABLESPACE torts1 MANAGED BY SYSTEM USING ('torts')@

CALL MY.COUNT_ROWS('SELECT * FROM MY.TORT','session.TMPT',?)@

ROLLBACK@

CONNECT RESET@

  Значение выходных параметров
  --------------------------
  Имя параметра   : RCNT
  Значение парам. :  4 


  Набор результатов  1 
  --------------

  ID          NAME                                                                                                                                                                                                    
             1  First row                                                                                                                                                                                               
             2  Second row                                                                                                                                                                                              
             3  Third row                                                                                                                                                                                               
             4  Fourth row                                                                                                                                                                                              

   4  записей выбрано.

  Статус возврата =  0