OPEN SQL

    

OPENSQL 語句包含有: SELECT、INSERT、UPDATE、MODIFY、DELETE、OPEN CURSOR、FETCH、CLOSE CURSOR、COMMIT WORK、ROLLBACK WORK等.

 

1. SELECT語句

語法格式:

SELECT <result> [INTO <target>] [FROM <source>] [WHERE <condition>]

               [GROUP BY <fields>] [ORDER BY <sort order>]

其中: <result>指定要抓取的欄位

      <target>將讀取的記錄存放在work area中

      <source>指定從那個TABLE中讀取資料

      <condition>抓取資料的條件

      <fields>指定按那些欄位分組

       <sort order>排序的欄位及方式

相關的系統變量:

     SY-SUBRC = 0 表示讀取數据成功

               <> 0 表示未找到符合條件的記錄

     SY-DBLNT: 被處理過的記錄的筆數.

相關的命令:

     EXIT. 退出循環.

     CHECK <logistic statement>.如果邏輯表達式成立,則繼續執行,否則,開

                            始下一次循環.

◆利用循環方式讀取所有記錄

SELECT ….ENDSELECT.是循環方式讀取記錄的.

     例如:

TABLES MARD.

SELECT [DISTINCT] * FROM MARD WHERE MATNR = ‘3520421700’.

    <Statements>.

ENDSELECT.

(從MARD中抓取所有料號=3520421700的資料)

◆讀取一筆資料

TABLES MARD.

SELECT SINGLE * FROM MARD WHERE MATNR = ‘3520421700’.

(從MARA中抓取一筆料號=3520421700的資料)

◆將讀取的記錄放在work area中,并且加入Internal table 中.

格式有:

   ... INTO <work area>

   ... INTO CORRESPONDING FIELDS OF <work area>

   ... INTO (f1, ..., fn) 變量組.

   ... INTO TABLE <internal table>

   ... INTO CORRESPONDING FIELDS OF TABLE <internal table>

  ... APPENDING TABLE <internal table>

   ... APPENDING CORRESPONDING FIELDS OF TABLE <internal table>

舉例一:

TABLES MARD.

DATA: BEGIN OF ITAB OCCURS 10,

              MATNR LIKE MARD-MATNR,

              WERKS LIKE MARD-WERKS,

              LGORT LIKE MARD-LGORT,

              LABST LIKE MARD-LABST,

        END OF ITAB.

SELECT MATNR WERKS LGORT LABST 

              INTO CORRESPONDING FIELDS OF ITAB

              FROM MARD

              WHERE MATNR = ‘3520421700’.

       APPEND ITAB.

       CLEAR ITAB.

ENDSELECT.

(將讀取的結果放在Internal table ITAB中)

舉例二.

TABLES MARD.

SELECT MATNR MTART MAKTX INTO (t_matnr, t_mtart, maktx)

               FROM MARD

               WHERE MATNR = ‘3520421700’.

       <Statements>.

ENDSELECT.

(從MARD中抓取料號=3520421700的料號、類型和描述,放在變量t_matnr, t_mtart, maktx中)。

◆按指定的欄位排序

TABLES SBOOK.

SELECT * FROM SBOOK    WHERE CARRID = ‘LH’ AND

                                 CONNID = ‘0400’ AND

                                 FLDATE = ‘19950228’

            ORDER BY BOOKID [ASCENDING/DESCENDING].

   WRITE: / SBOOK-BOOKID,   SBOOK-CUSTOMID,

           SBOOK-CUSTTYPE, SBOOK-SMOKER,

           SBOOK-LUGGWEIGHT, SBOOK-WUNIT,

           SBOOK-INVOICE.

ENDSELECT.

(利用參數ORDER BY所指定的欄位排序)

◆     抓取數据的條件敘述

(1)    BETWEEN <g1> AND <g2>

例如: WHERE YEAR BETWEEN 1995 AND 2000.

(2)    LIKE <g>

例如: WHERE NAME LIKE ‘MIKE%’.

(‘%’是通配符號)

(3)    IN (<g1>…<gn>)

是<g1>…<gn>里面的任意一個值即可.

例如: WHERE PLANT IN (‘CHUNGLI’, ‘TAOYUAN’,’LIUTU’).

(表示PLANT 只要是’CHUNGLI’或’TAOYUAN’或’LIUTU’都可以).

2. INSERT 語句

◆從work area 加入到Internal Table中

格式: INSERT INTO <database> VALUES <work area>

例如:

DATA: BEGIN OF WA,

              CODE(6) TYPE C,

              NAME(30) TYPE C,

        END OF WA.

DATA: VEN LIKE WA OCCURS 10.

…

WA-CODE = ‘530120’.

WA-NAME = ‘XINGDA ELECTRONICS CO.,LTD’.

INSERT INTO VEN VALUES WA .

如果work area的名稱就是internal table的名稱,可以直接寫成:

      INSERT <internal table>

例如:

DATA: BEGIN OF WA OCCURS 10,

              CODE(6) TYPE C,

              NAME(30) TYPE C,

        END OF WA.

…

WA-CODE = ‘530120’.

WA-NAME = ‘XINGDA ELECTRONICS CO., LTD’.

INSERT WA.

◆從另外一個Internal table中INSERT 資料

格式:

INSERT <itab1> FROM TABLE <itab2> [ACCEPTING DUPLICATE KEY]

將<itab2>中非NULL的資料加入<itab1>中,加上[ACCEPTING DUPLICATE KEY]能限制相同PRIMARY KEY不重复加入.

3. MODIFY 語法

MODIFY <internal table> [FROM <work area>].

4. DELETE 語法

     DELETE <internal table> [FROM <work area>].

    或: DELETE <internal table> [WHERE <conditions>]

5. DATABASE CURSOR

   Database Cursor是一個資料庫暫存區, 將經SELECT指令讀取的記錄存放至此暫存區, 再由此暫存區放至Work Area中, 可減少資料庫讀取的次數.

1.開啟 Database Cursor

 語法:

        OPEN CURSOR <c> FOR SELECT … WHERE <condition>

        Example:

                TABLES SPFLI.

                DATA: WA LIKE SPFLI,

                        C1 TYPE CURSOR.

                OPEN CURSOR C1 FOR SELECT * FROM SPFLI

                       WHERE AREA =’TAIWAN’.

 2.讀取 Database Cursor的資料存入 Work Area

語法:

      FETCH NEXT CURSOR <c> INTO <wa>

          Example:

                   FETCH NEXT CURSOR C1 INTO WA.

 讀取下一筆Cursor位置的資料存入WA, 如果已無資料可讀, SY-SUBRC <>0.

關閉 Database Cursor

語法:

      CLOSE CURSOR <c>

       Example:

               CLOSE CURSOR C1.

6. COMMIT WORK & ROLLBACK WORK

要確定資料成功寫入資料庫,可使用COMMIT WORK指令,如:

   COMMIT WORK.

相反的, 如果反悔要復原, 可使用 ROLLBACK WORK, 可復原在上個COMMIT WORK指令之後的資料, 如:

   ROLLBACK WORK.