Open SQL are statements that make up a subset of Standard
SQL which is fully integrated in ABAP. Open SQL consists of Data Manipulation
Language (DML) which is a part of Standard SQL.
One of the ways to access the database from a program is
Open SQL. These Open SQL statements are nothing but a subset of Standard SQL,
which is fully integrated in ABAP. Irrespective of which database system the
R/3 installation is using, they allow you to access data. When I said that, Open
SQL consists of the Data Manipulation Language (DML). I meant that it allows
you to read (i.e. to SELECT) and change (i.e. to INSERT, UPDATE, DELETE) data.
Moreover, Open SQL also goes beyond Standard SQL. This is to
provide statements that can simplify or speed up database access in conjunction
with other ABAP constructions. Apart from that, it also gives you the freedom
to buffer certain tables on the application server, thereby enabling you to
save excessive database access. In this case, the database interface is
responsible for comparing the buffer with the database. As far as buffer
storage is concerned, they may be stored in two parts: the working memory of
the current work process, and the shared memory for all work processes on an
application server. The data in the various buffers is synchronized at set
intervals by buffer management where an R/3 system is distributed across more
than one application server. It should be noted that data in the buffer is not
always up to date when you are buffering the database. That is why you should
only use the buffer for data which does not change often. You can specify
whether a table can be buffered in its definition in the ABAP Dictionary.
Open SQL consists of a set of ABAP statements. These
statements perform operations on the central database in the R/3 system. The
results of the operations and any error messages which come out of it are
independent of the current database system. Thus, uniform syntax and semantics
for all of the database systems supported by SAP is provided by Open SQL.
Regardless of the current database system, the ABAP programs, which use Open
SQL statements only, will work in any R/3 system. Moreover, Open SQL statements
work only with database tables that have been created in the ABAP Dictionary.
You have the freedom to combine columns belonging to
different database tables to a database view (or view for short) in the ABAP
Dictionary. Views are also handled in exactly the same way as database tables
in Open SQL statements.
Some Open SQL keywords are as follows:
SELECT - It reads data from database tables.
INSERT - It adds rows to database tables.
UPDATE - It changes the contents of rows of database tables.
MODIFY - It inserts rows into database tables or changes the
content of existing rows.
DELETE - It deletes rows from database tables.
OPEN CURSOR, FETCH, CLOSE CURSOR - It reads rows of database
tables using the cursor.
The following two system fields are filled with return codes
by all Open SQL statements:
SY-SUBRC: The system field SY -SUBRC contains the value 0
after every Open SQL statement if the operation was successful. When a value is
other than 0, then it is unsuccessful.
SY-DBCNT: The system field SY-DBCNT contains the number of
database lines processed after an open SQL statement.