Syntax Element           In Ada
prefix                   EXEC SQL
terminator               ;
target                   label_name

Variable Definition Syntax

Ada variable definition =
    Ada variable name.,.. : Ada type spec
    [ = character representation...]

Ada type spec is given below.
The = character representation... sequence
  is an optional initial value.

The Ada variable definition is specified within the scope
of Ada WITH and USE clauses that specify the following:

with SQL_STANDARD;
use SQL_STANDARD;
use SQL_STANDARD.CHARACTER_SET;

This will import the following built-in SQL type definitions:

SQL_STANDARD.BIT (1..length)
SQL_STANDARD.CHAR [CHARACTER SET [IS]
  character set name] (1..length)
SQL_STANDARD.DOUBLE_PRECISION
SQL_STANDARD.INT
SQL_STANDARD.REAL
SQL_STANDARD.SMALLINT
SQL_STANDARD.SQLCODE_TYPE
SQL_STANDARD.SQLSTATE_TYPE

SQL Type                Ada Type
BIT(length)             BIT (1..length)
CHAR(length)            CHAR (1..length)
DOUBLE PRECISION        DOUBLE_PRECISION
INT                     INT
REAL                    REAL
SMALLINT                SMALLINT
SQLCODE                 SQLCODE_TYPE or INTEGER
SQLSTATE                SQLSTATE_TYPE or CHAR(5)
indicator               INDICATOR_TYPE

An SQL query is a non-procedural process. This means that one query may result in many thousands of rows being processed to give you just a few rows in the results table. With such vast amounts of data being processed, anything that can be done to make the process faster is called performance tuning.SQL tuning is the process of arranging the SQL statement for the most efficient access of the database. This includes such things as the order in the FROM clause, WHERE clause, use of an INDEX, etc.

Database tuning is the process of configuring the database for the most efficient access. This includes database design, layout of tables, creation or dropping of indexes, views, disk allocation, memory usage, etc.

To do SQL tuning, start with proper SQL formatting of the statement. See SQL Formatting under SQL Basics. While this will not improve performance, it is necessary for easy human comprehension of the statement.
> Many RDBMS systems have tools for evaluating an SQL statement.

> DB2 and Oracle have a tool called EXPLAIN PLAN.

Concurrency is when two users try to update the same data at nearly the same time.

 

RDBMS systems use locking to prevent users from reading data that is being changed by other users. This is why a SELECT statement may need the option FOR UPDATE. The locking ensures transaction integrity and database consistency. Concurrency transparency means that data locking makes each transaction appear as though it is the only transaction. Sometimes each user is trying to access data locked by the other user causing a deadlock. Most RDBMS systems unlock one user, process that user, and then unlock the other user.

A transaction needs to have a starting and ending point. The starting point can be implied, for example, by starting a host program, or it can be explicit with a START TRANSACTION or a BEGIN TRANSACTION. The ending point is explicit with either a COMMIT or ROLLBACK command.

SQL Server starts with BEGIN TRANSACTION.

     BEGIN TRANSACTION;
       INSERT INTO MANAGER_TBL
         ('STOR_NO', 'MGR_NO', 'MGR_NAME')
           SELECT STOR_ID, EMPL_ID, EMPL_NAME
             FROM EMPLOYEE_TBL
            WHERE TITLE = 'STORE MANAGER';
     COMMIT;

SQL:1999, MySQL, PostgreSQL, and SQL Server switched to the new SQL standard START TRANSACTION:

     START TRANSACTION;
       INSERT INTO MANAGER_TBL
         ('STOR_NO', 'MGR_NO', 'MGR_NAME')
           SELECT STOR_ID, EMPL_ID, EMPL_NAME
             FROM EMPLOYEE_TBL
            WHERE TITLE = 'STORE MANAGER';
     COMMIT;

> Access transaction control starts with BeginTrans and ends with CommitTrans or RollbackTrans.

> DB2 and ORACLE always have an implied starting point, so they do not need an explicit starting statement.

A transaction is a sequence of one or more SQL statements that are to be executed together as a group. If any part of the transaction’s statements fails, then all actions taken by the statements within the transaction must be reversed. It is an all-or-nothing requirement.If all statements in a single transaction are successful, then all actions taken are made permanent with a COMMIT command. On the other hand, if any of the statements within a transaction fail, then all actions must be reversed with a ROLLBACK command.

An example of a transaction in accounting would be a set of debit and credit entries where the amount of the debit entry exactly matches the amount of the credit entry.

It is your responsability to start and end a transaction at a point that creates a logical transaction. It should be as small as possible while maintaining a logical unit.

See the following EXAMPLEs.

If you wish to UPDATE or DELETE a row in a host program, the DECLARE CURSOR must include FOR UPDATE. If columns are not stated, all columns can be updated. Syntax is:

     EXEC SQL
       DECLARE cursor_name [CURSOR FOR]
        SELECT column1, column2 ...
          FROM table_name ...
         WHERE ...
          [FOR UPDATE [OF column1, column2 ...]]
     END-EXEC

This SELECT FOR UPDATE can select only one table or view. DISTINCT, ORDER BY, and UNION can not be used. The DECLARE CURSOR FOR UPDATE would look like this:

     EXEC SQL
       DECLARE VENDOR_CSR
        SELECT VENDOR_NAME, STATUS
          FROM VENDOR_TBL
         WHERE COUNTRY = :SEARCH-COUNTRY
           FOR UPDATE OF STATUS
     END-EXEC

A column that is to be updated does not have to appear in the SELECT statement. The OPEN, FETCH, and CLOSE statements used for UPDATE or DELETE are identical to those used for retrieval. However, UPDATE or DELETE must have the phrase WHERE CURRENT OF. If WHERE CURRENT OF is not stated, the entire table will be updated or deleted.

     EXEC SQL
       UPDATE table_name
          SET column1 = value1,
              column2 = value2 ...
        WHERE CURRENT OF cursor_name
     END-EXEC

     EXEC SQL
       DELETE table_name
        WHERE CURRENT OF cursor_name
     END-EXEC

     EXEC SQL
       UPDATE VENDOR_TBL
          SET STATUS = :WS-STATUS
        WHERE CURRENT OF VENDOR_CSR
     END-EXEC

A host program INSERT does not require cursor control and functions the same as a stand-alone SQL INSERT. Here is an example of a host program CREATE TABLE and INSERT examples:

     EXEC SQL
       CREATE TABLE PILOT_TBL
        (PILOT_ID CHAR(5) PRIMARY KEY,
         FIRST_NAME CHAR(20) NOT NULL,
         LAST_NAME CHAR(20) NOT NULL,
         LICENSE_NO CHAR(10),
         PHONE_NO INTEGER NOT NULL DEFAULT)
     END-EXEC

     EXEC SQL
       INSERT INTO PILOT_TBL
       VALUES (:WS-PILOT, :WS-FIRST, :WS-LAST, :WS-LICENSE, WS-PHONE)
     END-EXEC

     EXEC SQL
       INSERT INTO PILOT_TBL
              (PILOT_ID, FIRST_NAME, LAST_NAME, LICENSE_NO, PHONE_NO)
       VALUES (:WS-PILOT, :WS-FIRST, :WS-LAST, :WS-LICENSE, WS-PHONE)
     END-EXEC

Here is an example of the processing, written in COBOL (which is very similar to English in context):

 WORKING-STORAGE SECTION.
     EXEC SQL
          DECLARE VENDOR_CSR CURSOR FOR
           SELECT VENDOR_NAME, STATUS
             FROM VENDOR_TBL
            WHERE COUNTRY = 'USA'
     END-EXEC

 PROCEDURE DIVISION.
     PERFORM 0000-HOUSEKEEPING.
     PERFORM 1000-PROCESS.
     PERFORM 9000-TERMINATE.

 0000-HOUSEKEEPING.
     EXEC SQL
          OPEN VENDOR_CSR
     END-EXEC.

 1000-PROCESS.
     PERFORM 1100-GET-DATA
       UNTIL SQLCODE = +100.

 1100-GET-DATA.
     EXEC SQL
          FETCH VENDOR_CSR
           INTO :VEN-NAME,
                :VEN-STATUS:VEN-STATUS-IND
     END-EXEC.
     IF SQLCODE NOT = +100
        PERFORM 1110-PROCESS-DATA.

 1110-PROCESS-DATA.
********************************
*    PROCESS THE DATA HERE     *
********************************

 9000-TERMINATE.
     EXEC SQL
          CLOSE VENDOR_CSR
     END-EXEC.
     STOP RUN.

If the cursor is defined as follows:

          EXEC SQL
              DECLARE VENDOR_CSR CURSOR FOR
                  SELECT VENDOR_NAME, STATUS
                    FROM VENDOR_TBL
                   WHERE COUNTRY = 'USA'
          END-EXEC

To bring a row of the result table into your host program for processing, you must use a FETCH statement. The syntax is:

          EXEC SQL
              FETCH cursor_name
               INTO :host_variable1[:host_ind1],
                    :host_variable2[:host_ind2]...
          END-EXEC

And the statement is:

          EXEC SQL
              FETCH VENDOR_CSR
               INTO :VEN-NAME,
                    :VEN-STATUS:VEN-STATUS-IND
          END-EXEC

The INTO host variable name can be one name to hold all the columns retrieved (like a record) or it can be many names, one for each column retrieved. Null indicators should be used if columns can be null (such as STATUS).

Each time the FETCH statement is executed, it is like retrieving a single record from a sequential file. You can not go backwards to a previously retrieved row without closing and reopening the cursor. You also can not skip rows. If the FETCH statement retrieves a row, SQLCODE will be set to +0. If the FETCH statement fails to retrieve a row because there are no more rows to retrieve, SQLCODE will be set to +100. See SQLCODE and SQLSTATE.

A cursor does not point to any rows until it is opened. When you execute the OPEN statement, the underlying SELECT creates the result table to which the cursor points.
The syntax of OPEN cursor is:

          EXEC SQL
              OPEN cursor_name
          END-EXEC

So, let us open the VENDOR_CSR:

          EXEC SQL
              OPEN VENDOR_CSR
          END-EXEC

Now we can process the rows selected by the cursor.
When we are finished with the results table, we should CLOSE the cursor with the following syntax:

          EXEC SQL
              CLOSE cursor_name
          END-EXEC

So, let us close the VENDOR_CSR:

          EXEC SQL
              CLOSE VENDOR_CSR
          END-EXEC

If you are only going to use a cursor once, the end of the program execution will close the cursor. But it is good programming to CLOSE the cursor. The following COMMIT statement (which makes all changes permanent) also closes the cursors:

          EXEC SQL
              COMMIT
          END-EXEC