how can I return a Table in a function or procedure 1

[julen]

Hi friends,

I have a question. Can I return a table in a db2 function or procedure?

Please, can you write a small example?

Thanks.

 

[ddiamond]

Here is an axample I pulled out of one of the IBM red books. I've never tried it however.

CREATE FUNCTION RANK(N INTEGER)
RETURNS TABLE(
  POSITION INTEGER,
  EMPNO CHAR(6),
  FIRSTNME CHAR(20),
  LASTNAME CHAR(20),
  SALARY DECIMAL(13,2)
)
  LANGUAGE SQL
  DISALLOW PARALLEL
  MODIFIES SQL DATA
  NOT FENCED
BEGIN
  DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
  DECLARE I INTEGER DEFAULT 1;
  DECLARE STMT VARCHAR(255);
  DECLARE TABLE_ALREADY_EXISTS CONDITION FOR '42710'; 
  DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_EXISTS 
    DELETE FROM SESSION.RETURN_TBL;

  DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_TBL ( 
    POSITION INTEGER NOT NULL,
    EMPNO CHAR(6) NOT NULL,
    FIRSTNME CHAR(20) NOT NULL,
    LASTNAME CHAR(20) NOT NULL,
    SALARY DECIMAL(13,2) NOT NULL);

  FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
    SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
    FROM SAMPLEDB01.EMPLOYEE ORDER BY SALARY DESC DO

    IF (I > N) AND (EACH_ROW.SALARY < LAST_SALARY) THEN 
      LEAVE FOR_LOOP;
    ELSE
      SET LAST_SALARY = EACH_ROW.SALARY;
    END IF;
  
    INSERT INTO SESSION.RETURN_TBL
    VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTNME,
             EACH_ROW.LASTNAME, EACH_ROW.SALARY);
    SET I = I + 1;
  END FOR;
  RETURN SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
  FROM SESSION.RETURN_TBL;
END;

 

[ddiamond]

An example of a stored procedure returning a resultset:

/* Enter one or more SQL statements separated by semicolons */
CREATE PROCEDURE GetCusName()
  RESULT SETS 1
  LANGUAGE SQL
BEGIN
  DECLARE c1 CURSOR WITH RETURN FOR 
  SELECT cusnam FROM customer ORDER BY cusnam;
  OPEN c1; 
END

 

[ddiamond]

another example:

CREATE FUNCTION SAMPLEDB01.EMPBYPRJ ( 
PRJNBR VARCHAR(6) ) 
RETURNS TABLE (
  EMPNO CHAR(6) ,
  FIRSTNME CHAR(20) , 
  LASTNAME CHAR(20) ,
  BIRTHDATE DATE )
LANGUAGE SQL 
SPECIFIC SAMPLEDB01.EMPBYPRJ 
  RETURN
    SELECT EMPNO , FIRSTNME , LASTNAME , BIRTHDATE
    FROM SAMPLEDB01 . EMPLOYEE WHERE EMPNO IN ( 
    SELECT EMPNO FROM SAMPLEDB01 . EMPPROJACT
    WHERE PROJNO = PRJNBR ) ;

 

[julen]

Thanks