DB2

DB2

1.创建Table

     CREATE TABLE #CORELIB#.XXHSPHT(

          BQSCID_L FOR BQSCID CHAR(30) NOT NULLDEFAULT ' ',

          BQMPDT_L FOR BQMPDT DECIMAL(8,0) NOTNULL DEFAULT 0,

          XXHSPHT_TCREATE_ROWTS FOR COLUMN BQTMZ1TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

          XXHSPHT_ROW_CHANGE_TS FOR COLUMN BQTMZ2TIMESTAMP GENERATED ALWAYS FOR EACH ROW NO UPDATE AS ROW CHANGE TIMESTAMP NOTNULL,

          CONSTRAINT #CORELIB#.XXHSPHT-t_PK_CONSTRAINTPRIMARY KEY(BQSCID,BQMPDT));

  Table描述

     LABEL ON TABLE #CORELIB#.XXHSPHT IS 'XXX history file');

  列名缩写

     LABEL ON COLUMN #CORELIB#.XXHSPHT(

          BQSCID IS 'SEC       ID                ',

          BQMPDT IS 'MARKET          PRICE          DATE');

  字段描述

     LABEL ON COLUMN #CORELIB#.XXHSPHT(

          BASCID TEXT IS 'Security ID      ',

          BQMPDT TEXT IS 'Market price date  ',

          BQTMZ1 TEXT IS 'Create time   ',

          BQTMZ2 TEXT IS 'Chang time   ');

2.创建View

     CREATE VIEW #CORELIB#.XXHSPHV1 AS SELECT BQSCID,BQMPDTFROM XXHSPHT;

  View描述

     LABEL ON TABLE #CORELIB#.XXHSPHV1IS 'XXX history view');

3.游标

     定义游标：

          EXCE SQL

               DECLARECS_XXHSPHV1 CURSOR FOR

               SELECTBQSCID,BQMPDT FROM XXHSPHV1

               FOR READ ONLY

               OPTIMIZE FOR1000ROW

               WITH NC;

     打开游标：

          ECCE SQL

               OPEN CS_XXHSPHV1;

          ERROR 处理（Return ErrorID:SQSTT）

     Get数据：

          EXCE SQL

               FETCH NEXT FROM CS_XXHSPHV1 FOR 1000ROWS INTO :peData(结构体);

               peFetchCount =SQLERRD(3);获取游标内总条数

          ERROR 处理（Return Error ID:SQSTT）                

     关闭游标：

          ECCE SQL

               CLOSE CS_XXHSPHV1;

          ERROR 处理（Return ErrorID:SQSTT）