Intro to ABAP - Chapter 05

•
The Open SQL SELECT statement automatically places records from the database into the work area. Since the work area can only hold one record at a time, the SELECT statement automatically creates a looping structure. One record is transferred from the database to the work area with each loop pass. The SELECT loop is delimited by the ENDSELECT statement. All statements inside the loop are executed once for each record retrieved.
•
– •
This course covers Data Manipulation Language only. –
2.05.3
Version 2.0
ASCENT - Strategic Initiative (SAP Development ABAP) SAP Release 4.6b
Introduction to ABAP Retrieving Data with the SELECT Statement
2.05.1
Version 2.0
ASCENT - Strategic Initiative (SAP Development ABAP) SAP Release 4.6b
Introduction to ABAP Retrieving Data with the SELECT Statement
•
•
If you wanted to select every single field from KNA1, you could have used the SELECT * syntax. This is not efficient coding practice for selecting just a few fields so you should always specify only the fields that you will be using. As an alternative to SELECT * it is possible to select only those columns required by the program. Note: This form has the advantage of transferring significantly less data from the database server to the The SELECT statement is used in all versions of SQL. However, the specific syntax and application server. functionality discussed here is specific to Open SQL. Except where otherwise noted, – all references in this course to SQL are to Open SQL. table an INTO clause is required. Data is not When selecting individual columns from a database automatically placed into the corresponding fields of the table work area. – The INTO clause must contain the same number of columns as the SELECT statement. The INTO clause may refer to individual fields from the table work area or to any other variable in your Version 2.0 2.05.6 program. –
Objectives
2.05.2
Version 2.0
ASCENT - Strategic Initiative (SAP Development ABAP) SAP Release 4.6b
Introduction to ABAP Retrieving Data with the SELECT Statement
In all versions of SQL the SELECT statement is used to read records from a database table. The most basic form of the SELECT statement is: SELECT <fields> FROM <table name>
2.05.4
Version 2.0
ASCENT - Strategic Initiative (SAP Development ABAP) SAP Release 4.6b
Introduction to ABAP Retrieving Data with the SELECT Statement
•
•
To see a list of the fields that are available in a table: – – – Double-click on the name of the table in your program code in the ABAP Editor. You will be taken to the dictionary definition of that table. Choose table structure in the ABAP Help function. Use the ABAP Repository Information System.
•Biblioteka Baidu
As noted on the previous page, SQL is a standard language used across many different systems. The majority of the functionality provided by SQL remains the same across all systems, but some database manufacturers have added their own proprietary extensions to SQL. Additionally, the syntax of SQL has minor variations across systems. SAP had to ensure that SQL statements in ABAP programs would be compatible with each of the underlying database systems supported by SAP. This was accomplished through the development of Open SQL (also called SAP SQL). Open SQL is a subset of standard SQL with a specific syntax recognised by ABAP. The run-time environment of the R/3 System automatically translates Open SQL statements to the appropriate syntax for the underlying database system. Therefore, code written in Open SQL will run on any database system supported by SAP.
•
Information is retrieved from the database for use in our programs via a special language known as Structured Query Language (SQL). SQL is a language specifically designed for use with relational database systems. However, ABAP has certain SQL commands built-in, so the connection between ABAP and SQL is fairly seamless. SQL has three main components, which are used for different purposes: – – Data Manipulation Language (DML): For retrieving and updating information in the database. Data Definition Language (DDL): For defining database objects and specifying the structure of the data in the database. In SAP these functions are handled primarily by the ABAP Dictionary and the Database Utility. Data Control Language (DCL): For controlling access to DML and DDL functions. The DML provides the four basic data operations: • • • • Select (retrieve) Insert Update Delete
•
2.05.5
Version 2.0
ASCENT - Strategic Initiative (SAP Development ABAP) SAP Release 4.6b
Introduction to ABAP Retrieving Data with the SELECT Statement
•
•
Note: Sometimes, for technical reasons, it is advantageous to know which database system an application is intended for. However, if Open SQL is used, it is not necessary to know this. SAP itself was written using Open SQL because it was intended to run on multiple systems.
•
To write the ID and name of all customers in the system we could write the code that appears on the above slide. Note that individual fields within the work area are referenced by using the work area name, followed by a hyphen, followed by the individual field name.
ASCENT - Strategic Initiative (SAP Development ABAP) SAP Release 4.6b
Introduction to ABAP Retrieving Data with the SELECT Statement
•
Retrieving Data with SELECT Statements
• •
This statement retrieves all specified fields (columns) and all rows from the specified table. In ABAP, the data is placed into a work area created by the TABLES statement. The TABLES statement serves two purposes: – – It declares to the program which ABAP Dictionary tables will be accessed. It creates a work area where individual records from the table can be stored. The work area is an area in memory, just like a program variable, and it is sized so that it can hold exactly one record from the specified table.