在Embedded Sqlrpgle中如何查询姓名以Y开头的记录并显示？

Hey there! Let's tackle your two questions about Embedded SQL in RPGLE clearly and practically.

Absolutely! Embedded SQL in RPGLE fully supports standard SQL syntax, so you can absolutely query records where a name starts with "Y". The key here is using the LIKE operator with the wildcard %—% matches any sequence of zero or more characters, so 'Y%' will target any name that begins with "Y".

Let's break this down into actionable steps with code examples:

Step 1: Define RPGLE variables to hold the name #

First, you need to declare a variable that matches the data type and length of the name field in your table. For example, if your name field is a 30-character CHAR field named LAST_NAME:

DCL-S lastName CHAR(30); // Match the field's type/length from your table

Step 2: Modify the SQL statement to filter and fetch into your variable #

How you structure this depends on whether you expect one matching record or multiple:

Scenario 1: Fetch a single matching record

If you only need one record (e.g., the first match), use INTO to directly populate your variable:

EXEC SQL
  SELECT LAST_NAME INTO :lastName
  FROM filename
  WHERE LAST_NAME LIKE 'Y%';

After running this, check SQLCOD (SQL code) to confirm if a record was found—SQLCOD = 0 means success.

Scenario 2: Fetch all matching records (multiple names)

If there are multiple records with names starting with "Y", use a cursor to loop through them:

// Declare a cursor for the filtered query
EXEC SQL DECLARE nameCursor CURSOR FOR
  SELECT LAST_NAME
  FROM filename
  WHERE LAST_NAME LIKE 'Y%';

// Open the cursor to start retrieving records
EXEC SQL OPEN nameCursor;

// Loop through each record until no more are found
DOW SQLCOD = 0;
  // Fetch the next name into your variable
  EXEC SQL FETCH nameCursor INTO :lastName;
  
  // If a record was fetched, display it
  IF SQLCOD = 0;
    DSPLY ('Found name: ' + lastName); // DSPLY outputs to the screen
  ENDIF;
ENDDO;

// Close the cursor when done
EXEC SQL CLOSE nameCursor;

Step 3: Display the name #

In the examples above, we use the DSPLY command to show the name directly on the screen. If you're working with a display file (for a more polished screen), you'd populate the screen's name field instead and use WRITE or EXFMT to show it.

A quick note: The colon : before lastName is crucial—it tells the SQL preprocessor that this is an RPGLE variable, not a SQL column name.

内容的提问来源于stack exchange，提问作者Nicolas