1. 编写DB2存储过程

    我们首先来看DB2的存储过程。存储过程一般被用来返回数据库中表的数据或者对表的数据进行更改。在这里我们使用存储过程从表中获得结果集。

    首先我们需要建立DB2环境：

    1) 把远程DB2服务器编制到本地。在这里我们远程的DB2主机地址是zxl01.cn.ibm.com，端口是50001

    清单1
    db2 CATALOG TCPIP NODE node01 REMOTE zxl01.cn.ibm.com SERVER 50001

    2) 在本地建立远程DB2的别名。在这里我们使用testDB作为别名。

    清单2
    db2 CATALOG DB testDB AT NODE node01

     3) 创建customer数据表

    清单3

    DROP TABLE DSW.CUSTOMER
    @
    CREATE TABLE DSW.CUSTOMER
    (
    CUST_NUMVARCHAR(10) NOT NULL,
    CUST_NAMEVARCHAR(35) NOT NULL,
    ADDRESSVARCHAR(35) NOT NULL,
    CITYVARCHAR(35) NOT NULL,
    COUNTRYVARCHAR(3)NOT NULL,
    ADD_DATETIMESTAMPNOT NULL
    )
    @
    ALTER TABLE DSW.CUSTOMER
    ADD CONSTRAINT CUSTOMER_PK
    PRIMARY KEY (CUST_NUM)
    @

    4) 创建存储过程 

   清单4
    DROP PROCEDURE DSW.S_CUSTOMER
    @
    CREATE PROCEDURE DSW.S_CUSTOMER (
    OUT poStatusINTEGER,
    INpiCust_numVARCHAR(10)
    )
    LANGUAGE SQL
    SPECIFIC DSW.S_CUSTOMER
    RESULT SETS 2

    P1: BEGIN NOT ATOMIC
    -------------------------------------------------------------
    -- CONDITION declaration
    -------------------------------------------------------------
    DECLARE sqlResetCONDITION for sqlstate '80100';
    -- Generic Variables
    DECLARE SQLCODEINTEGERDEFAULT 0;
    DECLARE SQLSTATECHAR(5)DEFAULT '00000';
    -- Generic handler variables
    DECLARE hSqlcodeINTEGERDEFAULT 0;
    DECLARE hSqlstateCHAR(5)DEFAULT '00000';
    DECLARE v_Cnt INT;
    -- Cursor for returning RS to the client
    DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR (
    SELECT CUST_NUM, CUST_NAME FROM DSW.CUSTOMER
    );
    DECLARE cursor2 CURSOR WITH RETURN TO CLIENT FOR (
    SELECT * FROM DSW.CUSTOMER
    WHERE CUST_NUM = piCust_num    );

    OPEN cursor1;
    OPEN cursor2;
    SET poStatus = 2;
    RETURN poStatus;
    END P1
    @

    5) 插入测试数据

    清单5
    INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
    VALUES('1','Steven','shang di','BeiJing','CHN')
    @
    INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
    VALUES('2','David','shang di','BeiJing','CHN')
    @
    INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
    VALUES('3','Tony','shang di','BeiJing','CHN')
    @
    INSERT INTO DSW.CUSTOMER (CUST_NUM,CUST_NAME,ADDRESS,CITY,COUNTRY)
    VALUES('4','Maggie','shang di','BeiJing','CHN')
    @