SPECIAL IMPLEMENTATION CONSIDERATIONS - Database Design and Development: An Essential Guide for IT Professionals - page 401

EXEC SQL BEGIN DECLARE SECTION ;
int depno ;
int empno ;
varchar depname (20) ;

/* department number */
/* employee number */
/* department name */

EXEC SQL END DECLARE ;
/* SQL communication area and declaration of exceptions */
EXEC SQL INCLUDE SQLCA ;
EXEC SQL WHENEVER NOT FOUND GOTO errA ;
EXEC SQL WHENEVER SQLERROR GOTO

errB ;

/* MAIN PROGRAM */
main ( )
{

empno = 5555 ;
strcpy (depname.arr, “FINANCE”) ;
dname.len = strlen (depname.arr) ;

EXEC SQL SELECT DeptNo INTO :depno
FROM DEPARTMENT
WHERE DeptName = :depname ;

EXEC SQL UPDATE EMPLOYEE
SET DeptNo = :depno
WHERE EmployeeNo = :empno ;

printf (“Transfer of employee %d to department %s complete. \n”,
empno, depname.arr) ;

EXEC SQL COMMIT WORK ;
exit (0) ;

errA:

printf (“Department named %s not found. \n, dname.arr) ;
EXEC SQL ROLLBACK WORK ;
exit (1)

errB:

printf (“SQL error - no updates to database made. \n”) ;
EXEC SQL ROLLBACK WORK ;
exit (1)
} /* -------- END -------- */
Figure 13-13 Sample program with embedded SQL.

What happens when you write and run an SQL query? How are the various SQL
statements executed? Are they verified by the DBMS before execution begins?
Are they executed in the order in which the statements are translated? If so, will
the natural sequence in the way a query is written be the optimal method for
execution?
In earlier database systems like the hierarchical or network database systems, the
application programmer writing a query in a procedural language was able to deter-
mine and direct the flow of the execution steps. A nonprocedural language such as
SQL does not consist of sequential execution steps; the programmer is not in control
of how a query must be executed. So it becomes the responsibility of the DBMS to
examine each query, analyze it, and come up with the best possible sequence and
plan for its execution. The optimal plan for the execution of a query must have