dbms和sql_DBMS中的嵌入式SQL（静态SQL）

dbms和sql

The structured query language provides us 2 features:

• It allows us to write queries.

• It allows us to use it in programming languages so that

  database can be accessed through application programs also.

Due to this duality SQL is sometimes called dual mode
language.

Actually all the queries can’t be expressed in SQL alone.
There are many queries that are expressed in programming languages like C, C++,
Java but can’t be expressed in SQL. For writing such queries we need to embed
the SQL in general purpose programming languages. The mixture of SQL and
general purpose programming languages is called embedded SQL.

There are some special embedded SQL statements which are
used to retrieve the data into the program. There is a special SQL precompiler
that accepts the combined source code with other programming tools and converts
them into an executable program.

Concepts for Embedding the SQL Statements

We can mix the SQL statements directly into general purpose
programming language like C, Java or Pascal. There are some techniques to embed
SQL statements in the programming languages.

1. The programming language in which the SQL statements are

   embedded is called the host language. The SQL statements and host language

   statements make the source program which is fed to a SQL precompiler for

   processing the SQL statements.

2. The host programming languages variables can be referenced

   in the embedded SQL statements, which allows values calculated by the programs

   to be used by SQL statements.

3. There are some special program variables which are used to

   assign null values to database columns. These program variables support the

   retrieval of null values from the database.

Embedded SQL Program Development

Since the embedded SQL is a mixture of SQL and programming
language, so it cannot be fed directly to a general purpose programming
language compiler. Actually the program execution is a multi-step which is as
follows.

1. First, the embedded SQL source code is fed to the SQL

   precompiler. The precompiler scans the program and processes the embedded SQL

   statements present in the code. There can be different precompilers for

   different type of programming languages.

2. After processing the source code, the precompiler produces

   2 files as its output. The first file contains the source program without

   embedded SQL statements and the second file contains all the embedded SQL

   statements used in the program.

3. The first file prodiced by precompiler (that contains the

   source program) is fed to the

   compiler for the host programming language (like

   C compiler). The compiler processes the source code and produces object code as

   its output.

4. Now the linker takes the object modules produced by the

   compiler and link them with various library routines and produces an executable

   program.

5. The database request modules, produced by the precompiler

   (in steps) are submitted to a special BIND program. The BIND program examines

   the SQL statements, parse them, validates them, optimizes them and finally

   produces an application plan for each statement. The result is a combined

   application plan for the entire program, that represents a DBMS-executable

   version of its embedded SQL statements. The BIND program stores the plan in the

   database, usually assigning it the name of the application program that has

   created it.

An Embedded SQL Example in C

Although the SQL statements can be embedded in any general purpose
programming language, still we just take an example in C language so that a
clear picture can be drawn. We just take an interactive SQL statement and see
how it can be embedded in C language.

Increase the salary of teacher by 10% who are B.Tech

update teacher set salary=1.1*salary where qualification=’B.Tech’;

The embedded SQL program for above written SQL statement
will be:

main()
{
                exec sql include sqlca;
                exec sql declare table teacher (tid char(6) not null,tname char(20),sex char(1),age number(3),qualification char(7),salary number(7),city varchar(15));
 
                //Display a message to user
                printf("updating teacher salary who are B.Techn");
 
                //this code executes the SQL statement
                exec sql update teacher set salary=1.1*salary where qualification='B.Tech';
                printf(update done");
               
                exit();
}

Explanation

Although the above shown program is very easy to understand,
still we would like to discuss some very basic features of embedded SQL.

1. The embedded SQL statement can be written in any case (lower

   or upper). Although we should follow the convention of that programming

   language in which we are embedding the SQL statements. For e.g., COBOL and

   FORTRAN are written in upper case so, the SQL statements are also written in

   upper case, while in C, the SQL statements are written in lower case as shown

   in above program.

2. Each embedded SQL statement begins with an introducer which

   indicates that it is a SQL statement. For most of the programming language

   EXEC
   SQL is used as an introducer.

3. Each embedded SQL statement ends with a terminator. There

   can be different terminators for different programming languages. For example,

   there is

   END EXEC for COBOL and a semicolon (;) for C.

4. The DECLARE TABLE statement is used to declare a table. With

   the use of

   DECLARE TABLE statement our program specifies the column and data

   type explicitly.

5. When we type SQL statement, we may make an error. This error

   is displayed by the interactive SQL program and we are prompted to type a new statement.

   There can be two

   types of errors: compile time and runtime.

Error Handling with SQL Code

In this scheme the DBMS communicates the status to the
embedded SQL program through an area of program storage called the SQL
communication are or SQLCA. The SQLCA is a data structure that contains the
error variables and the status indicators. By examining the SQLCA, the
application program can determine the success or failure of its embedded SQL statements
and can take actions accordingly.

Image Credit

翻译自: https://www.thecrazyprogrammer.com/2014/11/embedded-sql-static-sql-in-dbms.html

dbms和sql