|
Читайте также: |
LessonSummary
You should now be able to:
• Define internal tables
• Use basic ABAP statements with internal tables
• Analyze internal tables in debugging mode
UnitSummary
You should now be able to:
• Define elementary data objects (simple variables)
• Use basic ABAP statements with elementary data objects
• Execute and analyze programs in debugging mode
• Define structured data objects (structure variables)
• Use basic ABAP statements for structured data objects
• Analyze structured data objects in debugging mode
• Define internal tables
• Use basic ABAP statements with internal tables
• Analyze internal tables in debugging mode
Related Information
... Refer to the online documentation for the relevant ABAP statement.
Unit 5
133 DataRetrieval
See the introductory instructors’ notes in the lessons
Unit Overview
Refer to the individual lesson objectives for an overview of this unit.
UnitObjectives
After completing this unit, you will be able to:
• List different methods for searching relevant database tables
• Program read access to specific columns and rows within a particular database table
• List different methods for read accesses to several database tables
• Explain the SAP authorization concept
• Implement authorization checks
Unit Contents
Lesson: Reading Database Tables...........................................146
Exercise 8: Data Retrieval Using a SELECT Loop....................163
Exercise 9: Data Retrieval and Buffering in an Internal Table........169
Lesson: Authorization Check..................................................174
Exercise 10: Authorization Check........................................181
Lesson:
134
Reading Database Tables
Lesson Duration: 110 Minutes
Lesson Overview
In this lesson you will learn how to retrieve information on database tables and how to read data from them. An overview of techniques that allow you to access multiple database tables will also be covered.
This lesson concludes with a note about database table accesses that initiate changes.
LessonObjectives
After completing this lesson, you will be able to:
• List different methods for searching relevant database tables
• Program read access to specific columns and rows within a particular database table
• List different methods for read accesses to several database tables
|
Those participants that already have experience with platform-specific SQL statements (especially with porting problems) will be particularly enthused about ABAP Open SQL.
Business Example
You need to evaluate data from database tables.
Data Retrieval
|
Figure 86: Database Access (Architecture)
SQL is the abbreviation of Structured Query Language, a language that enables define, change, and read access to database tables.
Every relational database system has a native SQL, which is unfortunately database-specific. Hence, an ABAP program with native SQL statements cannot be used without restrictions in all SAP systems (due to the different database systems of different SAP systems).
In contrast Open SQL is an SAP-defined, database-independent SQL standard for the ABAP language. The Open SQL statements are dynamically converted to the corresponding native SQL statements of the currently used database system and are thus independent of the database. They allow the ABAP programmer uniform access to data, regardless of the database system installed.
|
The above graphic illustrates the options for searching for the required database tables.
Of course, you can also execute a free search via the Repository Information
System.
|
Figure 88: Database Read Access (Overview)
You use the Open SQL statement SELECT to program database read access. The
SELECT statement contains a series of clauses, each of which has a different task:
• Amongst other things, the SELECT clause describes which fields of the table are to be read.
• The FROM clause names the source (database table or view) from which the data is to be selected.
• The INTO clause determines the target variable into which the selected data is to be placed.
• The WHERE clause specifies the columns of the table that are to be selected.
For information about other clauses, please refer to the keyword documentation for the SELECT statement.
|
Figure 89: Reading Single Records
The SELECT SINGLE statement allows you to read a single record from the database table. To ensure a unique access, all key fields must be filled in the WHERE clause. The client field is an exception: If it is not specified the current client is assumed. (Please note that a client can only be specified in the SELECT select statement in combination with the CLIENT SPECIFIED addition. More details on this are provided in the course of this lesson.)
You use the asterisk * to specify that all fields of the table row to be selected are to be read. If you only want a specific selection of columns, you can list the required fields instead of the *. The next graphic illustrates this.
You use the INTO clause to specify the target variable to which the data record is to be copied. Left-justified, the target area must be structured like the table row or the specified required fields of the row.
If the system finds a suitable record, the return value SY-SUBRC equals 0.
|
Figure 90: Suitable Target Structure for the Field List
If you want only a certain selection of fields from the table row to be read, these can be specified as a field list within the SELECT statement (as described in the above graphic). In the INTO clause, you must then name a target structure variable that has the same structure as the field list (at least in the beginning), that is, it contains the fields of the field list in the same order. Only the corresponding field types have to match. The names of the target structure fields are not taken into account.
An alternative to specifying the target structure is to list the corresponding target fields in the INTO clause.
INTO (field_1,..., field_n)
|
If you want to use a structure variable for receiving the read record, which has fields with the same names as the fields in the target list but has a different structure (additional fields, different order of fields), it makes sense to use the CORRESPONDING FIELDS OF addition. This has the effect that only fields of the same name are filled in the target area. Please make sure that the corresponding field types are also the same, because otherwise (like in the MOVE statement), a (complicated) conversion happens and it is possible that incomplete data (caused by cut-offs) can be transported to the target fields.
The advantages of this variant are:
• The target structure does not have to be left-justified in the same way as the field list.
• This construction is easy to maintain, since extending the field list or target structure does not require other changes to be made to the program, as long as there is a field in the structure that has the same name (and if possible, the same type as well).
|
You can use the above-illustrated SELECT loop to read several rows of a database table into the program in succession.
The WHERE clause determines which lines are read into the target structure and processed using the statement block specified in the loop body. Multiple logical conditions within the WHERE clause can be logically connected using AND or OR.
The database delivers the data to the database interface of the application server in packages. The specified processing block then copies the records to the target area row-by-row for processing.
The loop is left automatically once all required rows have been read and evaluated. The return value should be queried after the SELECT loop (i.e., after the
ENDSELECTstatement). SY-SUBRC has the value 0 if the system read at least
one row. In this case, SY-DBCNT contains the number of records read.
|
You can use the INTO TABLE addition to copy the selected part of the database into an internal table directly instead of doing so row-by-row. This technique is called an array fetch. It is a high performance technology for filling an internal table with entries from a database table, as the data transport is realized in blocks and not in rows.
Because an array fetch is not a type of loop processing, no ENDSELECT
statement is required or allowed.
In the same way as the SELECT variants discussed before, the internal table that is specified as the target in the array fetch must be structured left-justified in the same way as the field list. If an internal table does not meet this prerequisite, then you have to use the INTO CORRESPONDING FIELDS OF TABLE addition instead of INTO TABLE. There, the database columns specified in the field list are copied to the columns of the internal table that have the same names. Here, you also have to make sure that the field types of the corresponding columns match in order to avoid complex conversions and possibly incomplete data in the target table.
With the array fetch, the content that might be in the internal table is overwritten. If you want to appends rows instead, you can use the APPENDING TABLE addition.
The value of SY-SUBRC is zero if at least one record was copied to the internal table. SY-DBCNT then contains the number of rows read.
|
A database table is called client-specific, if it has a client field (data type CLNT)
as the first key column and contains client-specific entries.
If you select data from client-specific tables without specifying the client, then only data records from the current client are read. The current client is transferred to the database system, where the data retrieval takes place.
If you want to read data from an explicitly-specified client, you have to specify this client in the WHERE clause. Note however, that this is only possible when you use the CLIENT SPECIFIED addition after the FROM clause.
As cross-client reading is rarely requested in practice and not relevant in the context of this course, the client field is usually omitted in the presentations.
|
If a database table is frequently accessed using a certain selection, you should define a secondary index for the fields contained in the selection in order to speed up corresponding accesses. This happens in the display mode of the relevant transparent table in the ABAP Dictionary using the Indexes pushbutton. When you activate the index, a secondary index is created in the database.
With selections from client-specific tables the client is always transmitted to the database (i.e., it is always part of the selection). Hence, it makes sense to include the client field when you define an index for such tables. At runtime, this can be used to restrict the database search to the relevant client block by means of the secondary index.
After an index has been created and activated, the Database Optimizer automatically uses this index when the program is executed if the index can contribute to speeding up the respective selection. You do not have to specify the index manually in the SELECT statement.
The primary index consists of all key fields of the database table and is created automatically. It speeds up accesses that have a selection across all key fields or at least a left justified part of the key fields.
Hint: Please note that unnecessary indexes tend to have a negative effect on your system's performance.
|
Show the high-performing alternative solution to the second exercise to illustrate the use of the array fetch in practice (program SAPBC400DDS_SELECT_ARRAY_FETCH).
Reading Multiple Database Tables, Accesses that
Initiate Changes (Outlook)
There is often a requirement to read data from different tables and display it. First of all, you should look for Reuse Components that take care of the read
process. The following graphic provides an overview of such read routines
supplied by SAP, which you can use in your program.
|
Figure 96: Reuse Components for Data Retrieval
There are four types of data selecting reuse components:
• Logical databases
(= data retrieval programs that read data from tables that belong together hierarchically)
• Function modules
(= subroutines stored in the function library of the SAP system with encapsulated functionality, such as reading from hierarchically related tables)
• BAPIs
(= Methods of Business Objects with read function, for example)
• Methods of global classes
For detailed information on searching for and using logical databases, please refer to the online documentation for “ABAP programming and runtime environment”, section on “ABAP database access” or course BC405.
Information about the other three techniques is available in the relevant units.
If there are no useable reuse components available for your data selection, you have to implement the read access yourself. In general, the technique with the best performance is the implementation using a table join.
Let's read and output SPFLI records as an example. However, for each record the long name of the respective airline, which is stored in SCARR, is to be output as well. The following graphic shows the logical creation of the corresponding table join, from which you can select all the required data using the SELECT statement.
|
|
Stress that it is possible to achieve the desired result using a loop on SPFLI with a SELECT SINGLE on SCARR for each loop pass but that the performance of this technique is very poor when there are large quantities of data.
|
You have to answer three questions when you define a table join:
• Join tables
Which database tables should be accessed?
• Join condition
What is the condition under which the corresponding records from the join tables are summarized to a join record?
• Join columns
Which columns from the join tables should be available in the table join?
|
There are two options for implementing a table join:
• In the ABAP Dictionary you create a database view that corresponds to a table join and select from it in your program.
For detailed information, please refer to the online documentation for the
ABAP Workbench, in the ABAP Dictionary section.
• In your program, you select from a join that is defined there (ABAP join).
At runtime, the system dynamically generates an appropriate database query in the database interface.
For more information, see the keyword documentation for the FROM clause of the SELECT statement.
Hint: A table join is a view of the relevant database tables and does not contain the corresponding data redundantly. With a selection from a table join the data is read from the corresponding database tables.
You can join more than two tables in a join.
|
the ABAP join is explained. If there is enough time, you can create a database view in the Dictionary with a view of SCARR and SPFLI. To do so, enter both tables and choose the Relationships function. Select the relationship between SCARR and SPFLI. The join condition is suggested automatically. Select some view fields under VIEW Fields. To do so, choose the “table fields” button. However, be sure to select the complete SPFLI key with the client field and the field SCARR-CARRNAME. To avoid warnings during activation, you should overwrite the default maintenance status with “restricted display/maintenance”. Activate the database view and write a small program with a SELECT on your view. Make it clear that the data is retrieved from the database tables SPFLI and SCARR according to the view definition and is not, for example, held redundantly in the view.
|
In addition to the SELECT statement, Open SQL also contains the UPDATE, INSERT, DELETE and MODIFY statements. However, the thus possible database change accesses should not be used without knowing the SAP transaction concept as you might cause data inconsistencies otherwise.
The SAP transaction concept is taught in course BC414.
|
|
Hint: The key points of the above slide are to point out that data inconsistencies can be caused when the above statements are used inappropriately and to mention course BC414 in which the SAP transaction concept is taught.
149 Exercise 8: Data Retrieval Using a SELECT Loop
Exercise Duration: 30 Minutes
Exercise Objectives
After completing this exercise, you will be able to:
• Program loop database accesses
• Limit the data selection according to the input parameters
Business Example
Enhance your ABAP program to issue flight schedules so that only the schedules of the airlines selected by the user are displayed.
The list should also include the occupancy, in percent, of each flight.
System Data
System: Will be assigned Client: Will be assigned User ID: Will be assigned Password: Will be assigned
Set up instructions: No special instructions when using a standard training
system
Task 1:
Read several rows using a loop according to the user selection
1. Copy your executable program ZBC400_##_GS_LIST (solution to exercise in lesson “Developing Programs and Organizing Developments”) or the template SAPBC400WBS_GS_LIST to the new name
ZBC400_##_SELECT_SFLIGHT.
2. Change the work area definition (name suggestion: wa_flight). Now use the global structure SBC400FOCC for typing. Find out information about the fields of this structure.
3. Define an input parameter for the airline abbreviation.
4. From database table SFLIGHT, select all flight schedules for the airline selected by the user. Restrict your selection to the table fields for which the work area contains a target field.
Continued on next page
5. What is the first key field in the transparent table SFLIGHT? Does your SELECT statement contain a WHERE clause? Does the database interface (still) use the primary index?
Task 2:
Calculate the percentage occupancy per scheduled flight
1. Within the loop statement, calculate the percentage occupancy for each flight. Assign the result to the PERCENTAGE field in your work area.
2. Extend your list output to include the percentage occupancy.
Solution 8: Data Retrieval Using a SELECT Loop
Task 1:
Read several rows using a loop according to the user selection
1. Copy your executable program ZBC400_##_GS_LIST (solution to exercise in lesson “Developing Programs and Organizing Developments”) or the template SAPBC400WBS_GS_LIST to the new name
ZBC400_##_SELECT_SFLIGHT. a) Carry out this step as usual.
2. Change the work area definition (name suggestion: wa_flight). Now use the global structure SBC400FOCC for typing. Find out information about the fields of this structure.
a) See source code excerpt in the model solution.
The global structure SBC400FOCC has the following components:
- CARRID with type S_CARR_ID,
- CONNID with type S_CONN_ID,
- FLDATE with type S_DATE,
- SEATSMAX with type S_SEATSMAX,
- SEATSOCC with type S_SEATSOCC and
- PERCENTAGE with type S_FLGHTOCC.
With the exception of the last component, all the others have the same type as the fields of the same name in the transparent table SFLIGHT. There is no corresponding field in SFLIGHT for the PERCENTAGE field, which is to receive the percentage occupancy of the current flight.
3. Define an input parameter for the airline abbreviation. a) See source code excerpt in the model solution.
4. From database table SFLIGHT, select all flight schedules for the airline selected by the user. Restrict your selection to the table fields for which the work area contains a target field.
a) See source code excerpt in the model solution.
Continued on next page
5. What is the first key field in the transparent table SFLIGHT? Does your SELECT statement contain a WHERE clause? Does the database interface (still) use the primary index?
a) The MANDT field is the first key field in the transparent table SFLIGHT. Even though you have not specified it in your WHERE clause, a value for this field (the current execution client) is transferred to the database due to the “automatic client”. That means, your selection is restricted by MANDT and not CARRID. Hence, the database interface can use the primary index for the selection of data records in this case (partial, but left justified specification of key fields in the selection restriction).
Hint: Note on the automatic client:
When the ABAP runtime system accesses client-specific tables for which no client is defined, it assumes that data should
only be taken into account if it belongs to the client under which the user is logged on. Hence, the database interface automatically supplements the WHERE clause with the mandt
= sy-mandt condition. (sy-mandt contains the current
execution client.)
Task 2:
Calculate the percentage occupancy per scheduled flight
1. Within the loop statement, calculate the percentage occupancy for each flight. Assign the result to the PERCENTAGE field in your work area.
a) See source code excerpt in the model solution.
Continued on next page
2. Extend your list output to include the percentage occupancy. a) See source code excerpt in the model solution.
Result
Source code excerpt: SAPBC400DDS_SELECT_SFLIGHT
REPORT sapbc400dds_select_sflight. DATA wa_flight TYPE sbc400focc.
Дата добавления: 2015-11-16; просмотров: 62 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| DATA result TYPE p DECIMALS 2. | | | Wa_flight-seatsocc / wa_flight-seatsmax. |
