Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2011
    Posts
    18

    Smile Unanswered: Stored procedure in db2

    why to use with return for in cursor
    while using stored procedure..
    thanx.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    When you are returning multiple rows to the calling program, anything other than individual values of output variables.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2011
    Posts
    18

    stored procedure

    hi..
    thanx for ur reply...
    i m new to stored procedure in db2..
    so do help me..

    i wrote 1 procedure.. let me knw. wht all changes shld be there..

    create or replace procedure sp_displayEMP(in deptid integer,out name varchar(200))
    language sql
    begin

    declare deptno integer;
    declare ename varchar(200);
    declare at_end integer default 0;
    declare not_found CONDITION FOR SQLSTATE '02000';

    declare c1 cursor for
    select name from employee where dept_id = deptid;

    declare c2 cursor with return for
    select emp_id,emp_name,emp_sal from employee
    where dept_id = deptid
    order by emp_id;

    declare CONTINUE HANDLER FOR not_found SET at_end=1;

    open c1;

    ins_loop : loop
    fetch c1 into ename;
    IF at_end=1 THEN
    LEAVE ins_loop;
    ELSE
    set name = ename;
    iterate ins_loop;
    end if;
    end loop;
    close c1;

    open c2;
    return;
    close c2;
    end


    -- lets say 1 dept contains multiple employees..
    is this right?
    or i want to do any modifications????

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't understand why you have the output variable name and why you have cursor c1 (but I am not 100% sure what you are trying to do since you didn't explain it). Also, can't you test it yourself?:

    This SP uses SAMPLE database:

    Code:
    create or replace procedure sp_displayEMP(in deptid char(3))
    language sql
    begin
    
    declare c2 cursor with return for
    select empno, lastname, firstnme, salary from employee
    where workdept = deptid
    order by empno;
    
    open c2;
    end@
    
    
    $ db2 "call sp_displayEMP('D21')"
    
    
      Result set 1
      --------------
    
      EMPNO  LASTNAME        FIRSTNME     SALARY
      ------ --------------- ------------ -----------
      000070 PULASKI         EVA             96170.00
      000230 JEFFERSON       JAMES           42180.00
      000240 MARINO          SALVATORE       48760.00
      000250 SMITH           DANIEL          49180.00
      000260 JOHNSON         SYBIL           47250.00
      000270 PEREZ           MARIA           37380.00
      200240 MONTEVERDE      ROBERT          37760.00
    
      7 record(s) selected.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Dec 2011
    Posts
    18

    stored procedure

    hi....
    thanx a lot for ur help..... it works.........

    result set 1 i hav not mentiond into SP


    now 1 more question...


    can u explain me abt this..

    SET param = 'IMPORT FROM NUL OF DEL REPLACE INTO '||full_name;
    SET stmt = 'CALL SYSPROC.ADMIN_CMD (?)';

    i knw its for truncate.. bt not clear..

  6. #6
    Join Date
    Dec 2011
    Posts
    18

    multiple results of db2 in java

    nw i have the list of multiple records
    hw can i display on console using java (jdbc)

    Connection con = null;
    CallableStatement cs = null;

    cs = con.prepareCall("{CALL iris.sp_displayEMP(?)}");
    cs.setInt(1, 1);
    cs.execute();

    hw to retrieve resultset in java.

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •