Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

julen

Programmer
Mar 26, 2006
27
ES
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.
 
Here is an axample I pulled out of one of the IBM red books. I've never tried it however.
Code:
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;
 
An example of a stored procedure returning a resultset:

Code:
/* 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
 
another example:
Code:
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 ) ;
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top