ABAP语法讲解二（s e l e c t语句）

SELECT

Basic form

SELECT select clause [INTO clause] FROM from clause [WHERE cond1] [GROUP BY fields1] [HAVING cond2] [ORDER BY fields2].

In an ABAP Objects context, a more severe syntax check is performed that in other ABAP areas. See Short Forms Not Allowed and * Work Areas Not Allowed. Effect

Reads a selection and/or a summary of data from one or more database tables and/or views (see relational database). SELECT is an OPEN SQL statement.

Each SELECT statement consists of a series of clauses, each with a differen task: The SELECT clause select clause describes

? ? ?

Whether the result of the selection should be a single record or a table, Which columns should be contained in the result, Whether identical lines may occur in the result.

The INTO clause INTO clause determines the target area into which the selected data is read. If the target area is an internal table, the INTO clause specifies:

? ? ?

Whether you want to overwrite the contents of the internal table or Append the results to the internal table, and

Whether you want to place the data in the internal table in a single step, or in a series of packages.

The INTO clause can also occur after the FROM clause. You may omit it if

? ?

The SELECT clause contains a \

The FROM clause does not contain a JOIN, and

? You have declared a table work area dbtab in your program using TABLES.

The data, if it exists in the database, is then made available using the table work area dbtab. The statement is then processed further like the SELECT * INTO dbtab FROM dbtab statement, which has the same effect.

If the result of the selection is a table, the data is normally read line by line (for further information, see INTO clause) in a processing loop, which is introduced with SELECT and concludes with ENDSELECT. The loop is processed once for each line that is read. If you want the result of the selection to be a single record, there is no concluding ENDSELECT statement. The FROM clause FROM clause specifies the source of the data (database tables or views), from which you want to select the data. It also specifies the

? ? ?

Client handling,

Behavior for buffered tables, and

The maximum number of lines that you want to read.

The WHERE clause cond1 specifies the conditions that the result of the selection must satisfy. By default, only data from the current client is selected (without you having to specify the client field specifically in the WHERE clause). If you want to select data from several clients, you must use the ... CLIENT SPECIFIED addition in the FROM clause.

The GROUP BY clause fields1 combines groups of lines into single lines of the result table. A group is a set of records with the same value of each database field listed in the GROUP BY clause.

The HAVING clause cond2 specifies conditions for the combined lines of the result table. The ORDER BY clause fields2 specifies how the records in the result table should be arranged.

The system field SY-DBCNT contains the number of lines read so far ecah time the SELECT statement is executed. After ENDSELECT, SY-DBCNT contains the total number of records read.

The return code is set as follows: SY-SUBRC = 0:

The result table contains at least one record.

SY-SUBRC = 4:

The result table is empty.

SY-SUBRC = 8:

Applies only to SELECT SINGLE FOR UPDATE: You did not specify all of the primary key fields in the WHERE condition. The result table is empty.

Note

The SELECT COUNT( * ) FROM ... statement returns a result table containing a single line with the result 0 if there are no records in the database table that meet the selection criteria. In an exception to the above rule, SY-SUBRC is set to 4 in this case, and SY-DBCNT to zero. Example

Displaying the passenger list for Lufthansa flight 0400 on 2/28/1995:

DATA: WA_SBOOK TYPE SBOOK.

SELECT * FROM SBOOK INTO WA_SBOOK WHERE

CARRID = 'LH ' AND CONNID = '0400' AND FLDATE = '19950228' ORDER BY PRIMARY KEY.

WRITE: / WA_SBOOK-BOOKID, WA_SBOOK-CUSTOMID, WA_SBOOK-CUSTTYPE, WA_SBOOK-SMOKER, WA_SBOOK-LUGGWEIGHT, WA_SBOOK-WUNIT, WA_SBOOK-INVOICE. ENDSELECT. Note Performance:

Storing database tables in a local buffer (see SAP buffering) can lead to considerable time savings in a client/server environment, since the access time across the network is considerably higher than that required to access a locally-buffered table. Notes

1. A SELECT statement on a table for which SAP buffering has been declared in the

ABAP Dictionary usually reads data from the SAP buffer without accessing the database. This does not apply when you use: - SELECT SINGLE FOR UPDATE or

- SELECT DISTINCT in the SELECT clause, - BYPASSING BUFFER in the FROM clause, - ORDER BY f1 ... fn in the ORDER BY clause, - Aggregate functions in the SELECT clause,

- When you use IS [NOT] NULL in the WHERE condition,

or when the table has generic buffering and the appropriate section of the key is not specified in the WHERE condition.

2. The SELECT statement does not perform its own authorization checks. You should

write your own at program level.

3. Proper synchronization of simultaneous access by several users to the same set of

data cannot be assured by the database lock mechanism. In many cases, you will need to use the SAP locking mechanism.

4. Changes to data in the database are not made permanent until a database commit

(see LUW) occurs. Up to this point, you can undo any changes using a databse rollback (see Programming Transactions). At the lowest isolation level (see lock mechanism ), the \selected by a SELECT statement was never written to the database. While a program is selecting data, a second program could be adding data to, changing data in, or deleting data from the database at the same time. If the second program then executes a rollback, the first program has selected a set of data that may only represent a temporary state from the database. If this kind of %unacceptable in the context of your application, you must either use the SAP locking mechanism or change the isolation level of the database system to at least \locking mechanism).

5. In a SELECT - ENDSELECT loop, the CONTINUE statement terminates the current

loop pass and starts the next.

6. If a SELECT - ENDSELECT loop contains a statement that triggers a database

commit, the cursor belonging to the loop is lost and a program termination and runtime error occur. Remote Function Calls and changes of screen always lead to a database commit. The following statements are consequently not allowed wihtin a SELECT-ENDSELECT loop: CALL FUNCTION ... STARTING NEW TASK , CALL FUNCTION ... DESTINATION , CALL FUNCTION ... IN BACKGROUND TASK , CALL SCREEN, CALL DIALOG, CALL TRANSACTION, and MESSAGE.

7. On some database systems (for example DB2/390)

locking conflicts can be caused even by read access. You can prevent this problem from occurring using regular database commits.

SELECT Clause

Variants:

1. SELECT [SINGLE [FOR UPDATE] | DISTINCT] *

2. SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn 3. SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab) Effect

The result of a SELECT statement is itself a table. The SELECT clause, along with the database tables and/or viewsin the FROM clause, specifies the sequence, name, database type, and length of the columns of the result table.

You can also use the optional additions SINGLE or DISTINCT to indicate that only certain lines in the result set should be visible to the program: SINGLE

The result of the selection should be a single entry. If it is not possible to identify a unique entry, the system uses the first line of the selection. If you use the FOR UPDATE addition, the selected entry is protected against parallel updates from other transactions until the next database commit (see LUW and database lock mechanism). If the database system identifies a deadlock, a runtime error occurs.

DISTINCT

Duplicate entries in the result set are automatically deleted.

Note

To ensure that an entry can be uniquely identified, you can specify all of the fields in the primary key using AND expressions in the WHERE condition. Note Performance:

1. The additions SINGLE FOR UPDATE and DISTINCT bypass the SAP buffering.

2. The addition DISTINCT forces a sort on the database server. You should therefore

only use it if you are really expecting duplicates in the result set.

Variant 1

SELECT [SINGLE [FOR UPDATE] | DISTINCT] * Effect

The columns of the result set will have exactly the same sequence, names, database type, and length as the fields of the database table or view specified in the FROM clause. Examples

Example to display all flights from Frankfurt to New York:

DATA WA_SPFLI TYPE SPFLI.

SELECT * FROM SPFLI INTO WA_SPFLI WHERE

CITYFROM = 'FRANKFURT' AND CITYTO = 'NEW YORK'.

WRITE: / WA_SPFLI-CARRID, WA_SPFLI-CONNID. ENDSELECT.

Example to display the free seats on Lufthansa flight 0400 on 02.28.1995:

DATA WA_SFLIGHT TYPE SFLIGHT. DATA SEATSFREE TYPE I.

SELECT SINGLE *

FROM SFLIGHT INTO WA_SFLIGHT WHERE

CARRID = 'LH ' AND CONNID = '0400' AND FLDATE = '19950228'.

SEATSFREE = WA_SFLIGHT-SEATSMAX - WA_SFLIGHT-SEATSOCC.

WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID, WA_SFLIGHT-FLDATE, SEATSFREE. Note

If you specify more than one table in the FROM clause and the INTO clause contains an internal table or work area instead of a field list, the fields are placed into the target area from left to right in the order in which they occur in the tables in the FROM clause. Gaps may occur between the table work areas for the sake of alignment. For this reason, you should define the target work area by referring to the types of database tables instead of simply listing the fields. For an example, refer to the documentation of the FROM clause.

Variant 2

SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn Effect

The columns of the result table will have the same format as the column references s1 ... sn.

If si stands for a field f, MAX( f ), MIN( f ), or SUM( f ), the corresponding column in the result set will have the same ABAP Dictionary format as f. For COUNT( f ) or COUNT( * ) the column has the type INT4. For AVG( f ) it has the type FLTP. If you use aggregate functions with one or more database fields in the SELECT clause, you must include all of the database fields that are not used in the aggregate function in the GROUP BY clause. The result of the selection in this case is a table.

If the SELECT clause only contains aggregate functions, the result of the selection will be a single entry. In this case, SELECT does not have a corresponding ENDSELECT statement. Notes

1. You can only use this variant for pool and cluster tables if the SELECT clause does

not contain any aggregate functions.

2. As a rule, aggregate functions used together with the FOR ALL ENTRIES addition do

not return the desired values. The result is only correct if the fields in the WHERE condition that are linked with AND and tested for equality with the aggregated fields can identify the table line uniquely.

3. If you use a database field with type LCHAR or LRAW in the SELECT clause, you must

specify the corresponding length field immediately before it in the SELECT clause.

Notes Performance:

1. When you use aggregate functions, the system bypasses the SAP buffer.

2. Since not all database systems can store the number of lines in a table in their

catalog, and therefore retrieving them is time-consuming, the COUNT( * ) function is not suitable for testing whether a table contains any entries at all. Instead, you should use SELECT SINGLE f ... for any table field f.

3. If you only want to select certain columns of a database table, use a field list in the

SELECT clause or a view.

Examples

Example to display all destinations served by Lufthansa from Frankfurt:

TABLES SPFLI.

DATA TARGET LIKE SPFLI-CITYTO.

SELECT DISTINCT CITYTO

INTO TARGET FROM SPFLI WHERE

CARRID = 'LH ' AND CITYFROM = 'FRANKFURT'. WRITE: / TARGET. ENDSELECT.

Example to display the number of airlines that fly to New York:

TABLES SPFLI.

DATA COUNT TYPE I.

SELECT COUNT( DISTINCT CARRID ) INTO COUNT FROM SPFLI WHERE

CITYTO = 'NEW YORK'. WRITE: / COUNT.

Example to find the number of passengers, the total luggage weight, and the average weight of the luggage for all Lufthansa flights on 02.28.1995:

TABLES SBOOK.

DATA: COUNT TYPE I, SUM TYPE P DECIMALS 2, AVG TYPE F. DATA: CONNID LIKE SBOOK-CONNID.

SELECT CONNID COUNT( * ) SUM( LUGGWEIGHT ) AVG( LUGGWEIGHT ) INTO (CONNID, COUNT, SUM, AVG) FROM SBOOK WHERE

CARRID = 'LH ' AND FLDATE = '19950228' GROUP BY CONNID.

WRITE: / CONNID, COUNT, SUM, AVG. ENDSELECT.

Variant 3

SELECT [SINGLE [FOR UPDATE] | DISTINCT] (itab) Effect

Works like SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn, if the internal table itab contains the list s1 ... sn as ABAP source code, and works like SELECT [SINGLE [FOR UPDATE] | DISTINCT] *, if itab is empty. The internal table itab may only contain one field, which must have type C and not be longer than 72 characters. You must specify itab in parentheses. Do not include spaces between the parentheses and the table name. Note

The same restrictions apply to this variant as to SELECT [SINGLE [FOR UPDATE] | DISTINCT] s1 ... sn. Example

Example to display all Lufthansa routes: DATA CLEAR FTAB FTAB SELECT WRITE: ENDSELECT.

/

CARRID

WA_SPFLI-CITYFROM,

=

INTO = =

'CITYFROM'. 'CITYTO'.

APPEND APPEND DISTINCT

CORRESPONDING

FROM

FIELDS

OF

WA_FTAB WA_FTAB

TO TO

FTAB. FTAB. FTAB. (FTAB) WA_SPFLI

SPFLI WHERE 'LH'.

WA_SPFLI-CITYTO.

WA_SPFLI TYPE

WA_FTAB(72)

FTAB LIKE

TABLE

TYPE

OF

SPFLI,

C,

WA_FTAB.

本文来源：https://www.bwwdw.com/article/3kf7.html