New Document
Computer Science
Computer Catlog
Oracle Catlog

History of Oracle
Introduction to Terms
Oracle Configurations
Roles of Database Administrator
Oracle Architecture
A Brief History of SQL
Dr. Codd's 12 Rules
An Overview of SQL
The SELECT Statement
Expressions, Conditions, and Operators
Clauses in SQL
Joining Tables
Sub Query
Manipulating Data
Building a Database
Views and Indexes
Controlling Transactions
Database Security
Advanced SQL Topics
Stored Procedures
Embedded SQL
SQL Tuning
Using Views in Data Dictionary
Using SQL to Generate SQL Statements

Embedded SQL

    This book uses the term embedded SQL to refer to the larger topic of writing actual program code using SQL--that is, writing stored procedures embedded in the database that can be called by an application program to perform some task. Some database systems come with complete tool kits that enable you to build simple screens and menu objects using a combination of a proprietary programming language and SQL. The SQL code is embedded within this code.

On the other hand, embedded SQL commonly refers to what is technically known as Static SQL.

Static and Dynamic SQL

    Static SQL means embedding SQL statements directly within programming code. This code cannot be modified at runtime. In fact, most implementations of Static SQL require the use of a precompiled that fixes your SQL statement at runtime. Both Oracle and Informix have developed Static SQL packages for their database systems. These products contain precompilers for use with several languages, including the following:






Advantages of Static SQL

Improved runtime speed

Compile-time error checking

Disadvantages of Static SQL

It is inflexible.

It requires more code (because queries cannot be formulated at runtime).

Static SQL code is not portable to other database systems.

    If you print out a copy of this code, the SQL statements appear next to the C language code (or whatever language you are using). Program variables are bound to database fields using a precompiler command

    Dynamic SQL, on the other hand, enables the programmer to build an SQL statement at runtime and pass this statement off to the database engine. The engine then returns data into program variables, which are also bound at runtime.

Programming with SQL

    The second is the capability to embed SQL statements within third- or fourth-generation language code. Obviously, the first use for SQL is essential if you want to understand the language and database programming in general.


    The popularity of programming environments such as Visual Basic, Delphi, and PowerBuilder gives database programmers many tools that are great for executing queries and updating data with a database. However, as you become increasingly involved with databases, you will discover the advantages of using the tools and topics discussed today. Unfortunately, concepts such as cursors, triggers, and stored procedures are recent database innovations and have a low degree of standardization across products. However, the basic theory of usage behind all these features is the same in all database management systems.

    Temporary tables are tables that exist during a user's session. These tables typically exist in a special database (named tempdb under SQL Server) and are often identified with a unique date-time stamp as well as a name. Temporary tables can store a result set from a query for later usage by other queries. Performance can erode, however, if many users are creating and using temporary tables all at once, owing to the large amount of activity occurring in the tempdb database.

    Cursors can store a result set in order to scroll through this result set one record at a time (or several records at a time if desired). The FETCH statement is used with a cursor to retrieve an individual record's data and also to scroll the cursor to the next record. Various system variables can be monitored to determine whether the end of the records has been reached.

    Stored procedures are database objects that can combine multiple SQL statements into one function. Stored procedures can accept and return parameter values as well as call other stored procedures. These procedures are executed on the database server and are stored in compiled form in the database. Using stored procedures, rather than executing standalone queries, improves performance.

    Triggers are special stored procedures that are executed when a table undergoes an INSERT, a DELETE, or an UPDATE operation. Triggers often enforce referential integrity and can also call other stored procedures.

    Embedded SQL is the use of SQL in the code of an actual program. Embedded SQL consists of both Static and Dynamic SQL statements. Static SQL statements cannot be modified at runtime; Dynamic SQL statements are subject to change.