Summary

Some advanced SQL concepts are discussed this hour. Although this hour does not go into a lot of detail, it does provide you with a basic understanding of how you can apply the basic concepts that you have learned up to this point. You start with cursors, which are used to pass a data set selected by a query into a location in memory. After a cursor is declared in a program, it must first be opened for accessibility. Then the contents of the cursor are fetched into a variable, at which time the data can be used for program processing. The result set for the cursor is contained in memory until the cursor is closed and the memory deallocated.

Stored procedures and triggers are covered next. Stored procedures are basically SQL statements that are stored together in the database. These statements, along with other implementation-specific commands, are compiled in the database and are ready to be executed by a database user at any given time. A trigger is also a type of stored procedure—one that allows actions to be automatically performed based on other actions that occur within the database. Stored procedures typically provide better performance benefits than individual SQL statements.

Dynamic SQL, using SQL to generate other SQL statements, and the differences between direct SQL and embedded SQL were the last subjects discussed. Dynamic SQL is SQL code dynamically created during runtime by a user, unlike static SQL. Using SQL code to generate other SQL statements is a great time-saver. It is a way of automating the creation of numerous, tedious SQL statements using features available with your implementation, such as concatenation and the selection of literal values. Finally, the main difference between direct SQL and embedded SQL is that the user issues direct SQL statements from some terminal, whereas embedded SQL is actually embedded within a host program to help process data.

The concepts of some of the advanced topics discussed during this hour are used to illustrate the application of SQL in an enterprise, covered in Hour 23, "Extending SQL to the Enterprise, the Internet, and the Intranet."