Database Programming in ABAP
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:
This 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 database 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
int.table -> 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:
This 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:
This 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.
Note:- If anybody want some example of these all topic then kindly send/comment your mail id, i will send the all examples through mail in word format.
**Kindly like and share these all topic with your friends**
0 Comments