DATABASE PROGRAMMING:
To interact with the SAP Database from ABAP applications, we use two types of
SQL (Standard query language) statements.
1. Open SQL
2. Native SQL
Native SQL: Every database vendor provides their own set of SQL
statements used for interacting with their own databases. Native SQL
statements are enclosed between ‘EXEC SQL’ and ‘ENDEXEC’.
Oracle Corporation Oracle database Native SQL statements (specific
to ORACLE).
Microsoft SQL Server Native SQL statements (specific to SQL Server)
Open SQL: is not specific to any database. i.e. they can be used to interact
with any of the databases. These open SQL statements are internally
converted to native SQL by a tool called as database interface.
Performance wise Native sql is faster than open sql.
But it is recommended to use Open sql only because in future if the
customer changes the database also, we need not modify the objects.
Procedure for interacting with databases from ABAP Programs:
1. Analyze the db table and the required fields (field name, data types,
sequence) which needs to be retrieved
2. Declare the corresponding declarations (structure / internal table/ individual
fields)
structure -> for holding single record
[Link] -> for holding multiple records
fields --> for holding individual fields
3. Retrieve the data from the database tables and store the same in the required
target variables
4. Process the target variables (structure / internal table / fields) accordingly
Select single: is an open sql statement used for retrieving single record from the
database table. Whenever we use select single, it is recommended to use where
clause in the select query comparing with primary key field to ensure appropriate
record is retrieved (if available).
Select..endSelect: is a open SQL statement used for retrieving multiple records
from the database. It is a looping statement, where in, for each select statement
execution, it retrieves single record, and the no. of times the select query
execution depends on the no. of records satisfying the condition. If there are
more select queries, it increases network traffic and thereby decreases
performance. Because of this reason, it is recommended to minimize the usage of
select..endselect and instead use ‘select into table’ statement and retrieve the
required no. of records using one select statement execution and store the
retrieved data into an internal table.
Inserting Single Record into Database table
Insert / modify <dbtable> from <work area>.
In the above syntax, Insert always inserts new record, if the record already exists
(based on primary key field value), it ignores the record. In case of Modify, It will
either insert the new record or updates the record if already exists.
Inserting Multiple Records into Database table
Insert <dbtable> from table <internal table> [accepting duplicate keys].
In the above syntax, Insert always inserts new records, if any of the record already
exists, it leads to run time error, to avoid this runtime error, we can use the
addition ‘ignoring duplicate keys’.
Modify <dbtable> from table <internal table>.
In the above syntax, Modify will insert new records otherwise updates the
existing records.
CURSORS:
A cursor is a data structure which can be used for holding multiple records.
Cursors in OPEN SQL:
1. Declaring the cursor
Syntax:
Data <cursor name> type CURSOR.
2. Open the cursor
Syntax:
Open cursor <cursor name> for <select query>.
Note: Only select statements can be associated with the cursors. Whenever
a cursor is opened, the select query associated with the cursor will be
executed and the result of the select query is stored in the cursor which is
called as activeset.
3. Read the cursor data
Syntax:
Fetch next cursor <cursor name> into <target fields>.
Note: Fetch statement reads content of the current cursor line into target
fields. If the fetch is successful, sy-subrc is set to 0 otherwise 4.
4. Close the cursor
Syntax:
Close cursor <cursor name>.
Cursors in NATIVE SQL:
1. Open the cursor
Syntax:
EXEC SQL.
Open <cursor name> for <select query>.
ENDEXEC.
2. Read the cursor data
Syntax:
EXEC SQL.
Fetch next <cursor name> into <target fields>.
ENDEXEC.
3. Close the cursor
Syntax:
EXEC SQL.
Close <cursor name>.
ENDEXEC.