Database principles and Applications-sql data manipulation language (manipulation Language) and embedded sql& stored procedures

2018-02-19 18:03:54

Data manipulation language (manipulation Language)

Data manipulation language refers to inserting, deleting, and updating languages.

Second, Views (view)

Database three-level mode, two-level mapping, the use of views can be implemented outside the mode, you can achieve the logical independence of data.

Views can be divided into two types:

1. General View: The view generated using the CREATE view is computed from the base table and is also known as a virtual table, and its definition is persisted and its contents are calculated temporarily.

The view can guarantee the security of the data to a certain extent, because the data that is presented to the user can be selected. Also, in the early days, the data in the view is not allowed to be modified because the view is a virtual table and a table that does not actually exist. However, if you can guarantee that the data is unique to the base table, you can modify it.

2. Temporary views (temporary view and recursive query): temporary tables defined with the WITH clause are called temporary views, note that their definitions are not saved, and recursive queries can be implemented.

Third, Embedded SQL

How do I use SQL in a programming language?

The main problem is to solve the following problems:

1. How to make the programming language accept SQL statements;
2. How to get programs and databases to exchange messages and data;
3. The query result is a collection, how to pass it to the variables in the programming language;
4. Data type Matching issues

Main solutions:

1. Embedded SQL;
2. Programming API (ODBC,JDBC, etc.);
3. Encapsulation class Library;

In the case of Embedded SQL in C, there are some basic concepts:

1. Start with EXEC SQL and end with a semicolon;
2. Interaction of data through host variables (host variables);
3. The use of host variables in SQL needs to be added: to differentiate;
4. There is a special host variable SQLCA (SQL communication area) that is defined using the EXEC SQL INCLUDE SQLCA;
5. SQLCA. SQLCODE to judge the execution state of the statement;
6. Indicator (short int) to characterize null;

You can first define some host variables and then interact with those host variables. However, one of the problems here is how to interact with data in the SQL language that returns the result as a collection. The solution is a very common cursor, cursors mechanism. With the cursor mechanism, the returned collection can be read as a file.

Iv. Dynamic SQL

SQL statements executed by the program before it is run are not deterministic, so dynamic SQL is required. The SELECT statement in the previous cursor was written dead, but not in the actual application, often requiring runtime determination.

There are three main types of dynamic sql:

1. Dynamic SQL executed directly: mainly refers to non-query interpolation and deletion statements;
2. Dynamic SQL with Dynamics parameters: with dynamical parameters;
3. Dynamic SQL for query: with query statements;

1)Dynamic SQL executed directly

2)dynamic SQL with dynamic parameters

Use placeholders for dynamic SQL with dynamic parameters.

3)Dynamic SQL for query

V. Stored Procedures

The regular use of a set of SQL statements can be stored as a stored procedure, the system can be optimized to store, the next time the user can be very convenient to make calls, do not have to write repeatedly. The essence is the encapsulation of a SQL statement.

Database principles and Applications-sql data manipulation language (manipulation Language) and embedded sql& stored procedures