In ABAP/4 programming language, there are two types of SQL being used.
- NATIVE SQL
- OPEN SQL.
Native SQL allows you to use database-specific SQL statements in an ABAP/4 program. This means that you can use database tables that are not administered by ABAP dictionary, and therefore integrate data that is not part of the R/3 system.
Open SQL consists of a set of ABAP statements that perform operations on the central database in the R/3 system. The results of the operations and any error messages are independent of the database system in use. Open SQL thus provides a uniform syntax and semantics for all of the database systems supported by SAP. ABAP programs that only use Open SQL statements will work in any R/3 system, regardless of the database system in use. Open SQL statements can only work with database tables that have been been created in the ABAP dictionary.
Basic Open SQL Commands
- OPEN CURSOR,FETCH, CLOSE CURSOR
TABLES SBOOK. DATA C TYPE CURSOR, WA LIKE SBOOK. OPEN CURSOR C FOR SELECT * FROM SBOOK WHERE CARRID = 'LH ' AND CONNID = '0400' AND FLDATE = '19950228' ORDER BY PRIMARY KEY. DO. FETCH NEXT CURSOR C INTO WA. IF SY-SUBRC <> 0. CLOSE CURSOR C. EXIT. ENDIF. WRITE: / WA-BOOKID, WA-CUSTOMID, WA-CUSTTYPE, WA-SMOKER, WA-LUGGWEIGHT, WA-WUNIT, WA-INVOICE. ENDDO.
Output the passenger list for the Lufthansa flight 0400 on 28-02.1995:
Open SQL Return CodesAll Open SQL statements fill the following two system fields with return codes.
After an Open SQL statement, the system field SY-DBCNT contains the number of database lines processed.
Native SQLAs already mentioned, Native SQL allows you to use database-specific SQL statements in an ABAP program.
To use Native SQL statement, you must precede it with the EXEC SQL statement, and follow it with the ENDEXEC statement.
EXEC SQL [PERFORMING <form>]. <Native SQL statement> ENDEXEC.There is no period after Native SQL statements. Furthermore, using inverted commas (") or an asterisk (*) at the beginning of a line in a native SQL statement does not introduce a comment as it would in normal ABAP syntax. You need to know whether table and field names are case-sensitive in your chosen database.
As in Open SQL, after the ENDEXEC statement, SY-DBCNT contains the number of lines processed. In nearly all cases, SY-SUBRC contains the value 0 after the ENDEXEC statement.
Open SQL - Performance RulesTo improve the performance of the SQL and in turn of the ABAP program, one should take care of the following rules-
Keep the Result Set Small
- Using the where clause
- If only one record is required from the database, use SELECT SINGLE whenever possible .
- Restrict the number of lines
- If only certain fields are required from a table, use the SELECT <field1> <field2> INTO ... statement
- Restrict no of columns
- Use aggregate functions
- Avoid nested select loops
- An alternative option is to use the SELECT .. FOR ALL ENTRIES statement. This statement can often be a lot more efficient than performing a large number of SELECT or SELECT SINGLE statements during a LOOP of an internal table.
- Use dictionary views
- Use Joins in the FROM clause
- Use subqueries in the where clause
- Use index fields in the where clause
- When accessing databases, always ensure that the correct index is being used .
- Logical databases
- Avoid repeated database access
- To avoid executing the same SELECT multiple times (and therefore have duplicate selects), an internal table of type HASHED can be used to improve performance.