Introduction to open SQL

Open SQL statements include select, insert, update, modify, delete, open cursor, fetch, close cursor, commit work, and rollback work.

1. SELECT statement
Syntax format:
Select <result> [into <target>] [from <source>] [where <condition>]
[Group by <fields>] [order by <sort order>]
Where: <result> specifies the field to be crawled
<Target> store the read records in the work area.
<Source> specify the table from which the data is read.
<Condition> conditions for capturing data
<Fields> specify the fields to be grouped.
<Sort order> sorting fields and Methods
Related System variables:
SY-SUBRC = 0 indicates Data Reading successful
<> 0 indicates that no matching record is found.
SY-DBLNT: number of records processed.
Related commands:
Exit. Exit the loop.
Check <logistic Statement>. If the logical expression is true, continue. Otherwise
The next cycle.
◆. Read all records cyclically
Select .... Endselect. Records are read cyclically.
For example:
Tables Mard.
Select [distinct] * From Mard where matnr = '000000 '.
<Statements>.
Endselect.
(Capture all the materials with the item No. = 3520421700 from MARD)
◆ Read a piece of data
Tables Mard.
Select single * From Mard where matnr = '2013 '.
(Capture a piece of information with the item number = 3520421700 from Mara)
◆ Place the read records in the work area and add them to the internal table.
Formats:
... Into <work area>
... Into corresponding fields of <work area>
... Into (F1,..., FN) variable group.
... Into Table <internal table>
... Into corresponding fields of table <internal table>
... Appending table <internal table>
... Appending corresponding fields of table <internal table>
Example 1:
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 = '000000 '.
Append itab.
Clear itab.
Endselect.
(Put the read result in internal table itab)
Example 2.
Tables Mard.
Select matnr mtart maktx into (t_matnr, t_mtart, maktx)
From Mard
Where matnr = '000000 '.
<Statements>.
Endselect.
(Capture the number, type, and description of the number = 3520421700 from MARD, and place it in the variable t_matnr, t_mtart, and maktx ).
◆ Sort by specified fields
Tables sbook.
Select * From sbook where carrid = 'lh 'and
Connid = '000000' and
Fldate = '000000'
Order by bookid [Ascending/descending].
Write:/SBOOK-BOOKID, SBOOK-CUSTOMID,
SBOOK-CUSTTYPE, SBOOK-SMOKER,
SBOOK-LUGGWEIGHT, SBOOK-WUNIT,
SBOOK-INVOICE.
Endselect.
(Sort by the field specified by order)
◆ Description of data capturing Conditions
(1) between <G1> and <G2>
For example, where year between 1995 and 2000.
(2) Like <G>
Example: Where name like 'Mike % '.
('%' Is the wildcard number)
(3) In (<G1>... <GN>)
Yes <G1>... <GN> any value in it is enough.
For example, where plant in ('chungi', 'taoyua', 'liutu ').
(It indicates that plant can be 'chungi', 'taoyuand', or 'liutu ).

2. Insert statement
◆ Add from work area to internal table
Format: insert into <database> values <work area>
For example:
Data: Begin of WA,
Code (6) type C,
Name (30) type C,
End of WA.
Data: ven like Wa occurs 10.
...
WA-CODE = '000000 '.
WA-NAME = 'xingda Electronics Co., Ltd '.
Insert into ven values wa.
If the work area name is the name of the internal table, you can directly write it:
Insert <internal table>
For example:
Data: Begin of WA occurs 10,
Code (6) type C,
Name (30) type C,
End of WA.
...
WA-CODE = '000000 '.
WA-NAME = 'xingda Electronics Co., Ltd '.
Insert wa.
◆ Insert data from another internal table
Format:
Insert <itab1> from Table <itab2> [accepting duplicate key]
Add <itab2> non-null data to <itab1>, and add [accepting duplicate key] to restrict duplicate values of the same primary key.

3. Modify syntax
Modify <internal table> [from <work area>].

4. Delete syntax
Delete <internal table> [from <work area>].
Or: delete <internal table> [where <conditions>]

5. Database cursor
Database cursor is a temporary database storage zone. It stores records read by the SELECT command in this temporary storage zone and then stores the records in the work area to reduce the number of database reads.
1. enable database cursor
Syntax:
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. Read database cursor data and save it to work area
Syntax:
Fetch next cursor <C> into <wa>
Example:
Fetch next cursor C1 into WA.
Read the data at the next cursor location into WA, if there is no data readable, SY-SUBRC <> 0.
Disable database cursor
Syntax:
Close cursor <C>
Example:
Close cursor C1.

6. Commit Work & rollback work
To confirm that the data is successfully written to the database, use the commit work command, for example:
Commit work.
On the contrary, you can use rollback work to restore data after the previous commit work command, for example:
Rollback work.

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/shin_zhong/archive/2009/09/18/4568358.aspx