The SQL language provides two different ways to use it
interactive
Embedded
Why introduce embedded SQL
SQL language is a non procedural language
Transaction processing applications require high-level languages
There are differences in details between the two methods. In the programming environment, SQL statements need to be expanded
Process of embedded SQL
Main language
Embedded SQL is to embed SQL statements into programming language. The embedded programming languages, such as C, C + +, Java, are called host language and main language for short.
Processing process
Precompiled method
In order to distinguish SQL statements from main language statements, all SQL statements must be prefixed with EXEC SQL. When the main language is C, the statement format:
EXEC SQL <SQL sentence>;
Communication between embedded SQL statement and main language
Embedding SQL into high-level language for mixed programming, the program will contain statements of two different calculation models
SQL statement
Descriptive set oriented statements
Responsible for manipulating the database
High level language statement
Procedural record oriented statements
Responsible for control logic flow
How should they communicate?
Communication between database unit of work and source unit of work
(1) Transfer the execution status information of SQL statement to the main language, so that the main language can control the program flow accordingly, which is mainly realized by SQL communication area
(2) The main language provides parameters to SQL statements, which are mainly realized by main variables
(3) The results of SQL query database are processed by the main language, which is mainly realized by main variables and cursors
SQL communication area
SQLCA: SQL Communication Area
SQLCA is a data structure
Purpose of SQLCA
After the SQL statement is executed, the system feeds back the application information
Describe the current working state of the system
Describe the operating environment
This information will be sent to the SQL communication area
The application takes these status information from the SQL communication area and determines the next statement to be executed
SQLCA usage
Define SQLCA
Define with EXEC SQL INCLUDE SQLCA
Using SQLCA
In SQLCA, there is a variable SQLCODE that stores the return code after each SQL statement is executed
If SQLCODE is equal to the predefined constant SUCCESS, it indicates that the SQL statement is successful, otherwise it indicates an error
Every time an application executes an SQL statement, it should test the value of SQLCODE to understand the execution of the SQL statement and deal with it accordingly
Main variable
Main variable
Embedded SQL statements can use the program variables of the main language to input or output data
The main language program variables used in SQL statements are called host variables for short
Type of primary variable
Input primary variable
It is assigned by the application and referenced by the SQL statement
Output main variable
Assign value or set status information to it by SQL statement and return it to the application
Indicator variable
Is an integer variable used to "indicate" the value or condition of the indicated primary variable
A primary variable can be accompanied by an Indicator Variable
Indicates the purpose of the variable
Indicates whether the input primary variable is null
Check whether the output variable is null and whether the value is truncated
Methods of using primary variables and indicating variables in SQL statements
Describe the primary and indicator variables
BEGIN DECLARE SECTION ... ... (Description (primary and indicator variables) ... END DECLARE SECTION
Use primary variable
The main variable after the description can appear anywhere in the SQL statement where an expression can be used
In order to distinguish from the database object name (table name, view name, column name, etc.), the main variable name in the SQL statement should be marked with a colon (:)
Use indicator variable
The indicated variable must also be preceded by a colon
Must immediately follow the indicated primary variable
Use the main variable and the method of indicating the variable outside the SQL statement (in the main language statement)
It can be referenced directly without colon
cursor
Why use cursors
SQL language has different data processing methods from the main language
SQL language is set oriented. In principle, one SQL statement can produce or process multiple records
The main language is record oriented. A group of main variables can only store one record at a time
Using only primary variables does not fully meet the requirements of SQL statements to output data to applications
Embedded SQL introduces the concept of cursor to coordinate these two different processing methods
cursor
Cursor is a data buffer set up by the system for users to store the execution results of SQL statements
Each cursor area has a name
Users can use SQL statements to obtain records from cursors one by one, assign them to main variables, and submit them to the main language for further processing
Establishing and closing database connections
(1) Establish database connection
EXEC SQL CONNECT TO target[AS connection-name][USER user-name];
target is the database server to connect to
Common server identification strings, such as @:
SQL string constant containing server identity
DEFAULT
Connect name is an optional connection name. The connection name must be a valid identifier
When there is only one connection in the whole program, the connection name can not be specified
You can modify the current connection while the program is running
EXEC SQL SET CONNECTION connection-name |DEFAULT;
(2) Close database connection
EXEC SQL DISCONNECT [connection];
Program instance
[example 8.1] check the student records of a department in turn and update the age of some students interactively.
EXEC SQL BEGIN DECLARE SECTION; /*Main variable description start*/
char Deptname[20];
char Hsno[9];
char Hsname[20];
char Hssex[2];
int HSage;
int NEWAGE;
EXEC SQL END DECLARE SECTION; /*End of main variable description*/
long SQLCODE;
EXEC SQL INCLUDE SQLCA; /*Define SQL communication area*/
int main(void) /*C Language main program start*/
{
int count = 0;
char yn; /*The variable yn stands for yes or no*/
printf("Please choose the department name(CS/MA/IS): ");
scanf("%s",deptname); /*Assign a value to the main variable deptname*/
EXEC SQL CONNECT TO TEST@localhost:54321 USER
"SYSTEM"/"MANAGER"; /*Connect to database TEST*/
EXEC SQL DECLARE SX CURSOR FOR /*Define cursor SX*/
SELECT Sno,Sname,Ssex,Sage /*SX Corresponding statement*/
FROM Student
WHERE SDept = :deptname;
EXEC SQL OPEN SX; /*Open the cursor SX and point to the first row of the query result*/
for ( ; ; ) /*The records in the result set are processed one by one with a circular structure*/
{
EXEC SQL FETCH SX INTO :HSno,:Hsname,:HSsex,:HSage;
/*Push the cursor to put the current data into the main variable*/
if (SQLCA.SQLCODE!= 0) /*SQLCODE != 0，Indicates that the operation was unsuccessful*/
break; /*Use the status information in SQLCA to decide when to exit the cycle*/
if(count++ == 0) /*If it's the first line, type it first*/
printf("\n%-10s %-20s %-10s %-10s\n",
"Sno","Sname","Ssex", "Sage");
printf("%-10s %-20s %-10s %-10d\n",
HSno,Hsname,Hssex,HSage); /*Print query results*/
printf("UPDATE AGE(y/n)?"); /*Ask the user if they want to update the student's age*/
do{scanf("%c",&yn);}
while(yn != 'N' && yn != 'n' && yn != 'Y' && yn != 'y');
if (yn == 'y' || yn == 'Y') /*If you select an update operation*/
{
printf("INPUT NEW AGE:");
scanf("%d",&NEWAGE); /*The user enters the new age into the main variable*/
EXEC SQL UPDATE Student /*Embedded SQL UPDATE statement*/
SET Sage = :NEWAGE
WHERE CURRENT OF SX;
} /*Update the age of the student pointed to by the current cursor*/
}
EXEC SQL CLOSE SX; /*Close the cursor SX, which no longer corresponds to the query result*/
EXEC SQL COMMIT WORK; /*Submit update*/
EXEC SQL DISCONNECT TEST; /*Disconnect database*/
}
SQL statement without cursor
Types of SQL statements without cursors
Descriptive statement
Data definition statement
Data control statement
SELECT statement whose query result is a single record
Addition, deletion and modification statements in non CURRENT form
SELECT statement whose query result is a single record
Such statements do not need to use cursors, but only need to specify the main variable to store the query results in the INTO clause.
[example 8.2] query student information according to student number.
EXEC SQL SELECT Sno,Sname,Ssex,Sage,Sdept
INTO:Hsno,:Hname,:Hsex,:Hage,:Hdept
FROM Student
WHERE Sno=:givensno;
/*Assign the student number of the student to be queried to the main variable givensno*/
Primary variables can be used in conditional expressions of the INTO clause, WHERE clause, and HAVING phrase
In the records returned by the query, some columns may be NULL
If the query result is not a single record but multiple records, the program will make an error and the relational database management system will return an error message in SQLCA
[example 8.3] query the results of a student taking a course. Suppose that the student number of the student to be queried has been assigned to the main variable givensno, and the course number has been assigned to the main variable givencno.
EXEC SQL SELECT Sno,Cno,Grade
INTO :Hsno,:Hcno,:Hgrade:Gradeid
/*Indicates the variable Gradeid*/
FROM SC
WHERE Sno=:givensno AND Cno=:givencno；
If gradeid < 0, the student's score is considered null regardless of the value of Hgrade.
Addition, deletion and modification statements in non CURRENT form
The main variable can be used in the SET clause and WHERE clause of UPDATE, and the indicator variable can also be used in the SET clause
[example 8.4] modify the grade of a student's elective course No. 1.
EXEC SQL UPDATE SC
SET Grade=:newgrade
/*The modified grade has been assigned to the main variable: newgrade*/
WHERE Sno=:givensno;
/*Assign student ID to primary variable: givensno*/
[example 8.5] a student takes a new course and inserts the relevant records into the SC table. It is assumed that the inserted student number has been assigned to the primary variable
stdno，The course number has been assigned to the primary variable couno.
gradeid=-1； /*gradeid Is the indicator variable and is assigned a negative value*/
EXEC SQL INSERT
INTO SC(Sno,Cno,Grade)
VALUES(:stdno,:couno,:gr :gradeid)；
/*:stdno，:couno，:gr Primary variable*/
Since the student has just taken an elective course, the score should be empty, so the indicator variable should be given a negative value
SQL statement using cursor
SQL statements that must use cursors
The query result is a SELECT statement with multiple records
UPDATE statement in the form of CURRENT
DELETE statement in the form of CURRENT
The query result is a SELECT statement with multiple records
To work with cursors
(1) Description cursor
(2) Open cursor
(3) Advances the cursor pointer and fetches the current record
(4) Close cursor
Description cursor
Using the DECLARE statement
Statement format
EXEC SQL DECLARE <Tour label> CURSOR
FOR <SELECT sentence>;
function
Is an illustrative statement. At this time, the relational database management system does not execute the SELECT statement
Open cursor
Using the OPEN statement
Statement format
EXEC SQL OPEN <Tour label>;
function
Opening the cursor actually executes the corresponding SELECT statement to get the query results into the buffer
At this time, the cursor is active and the pointer points to the first record in the query result set
Advances the cursor pointer and fetches the current record
Using FETCH statements
Statement format
EXEC SQL FETCH <Tour label>
INTO <Main variable>[<Indicator variable>]
[,<Main variable>[<Indicator variable>]]...;
function
Push the cursor pointer in the specified direction, and take out the current record in the buffer and send it to the main variable for further processing by the main language
Close cursor
Use CLOSE statement
Statement format
EXEC SQL CLOSE <Tour label>;
function
Close the cursor and release the buffer and other resources occupied by the result set
explain
After the cursor is closed, it is no longer associated with the original query result set
The closed cursor can be opened again and associated with the new query result
UPDATE statement and DELETE statement in the form of CURRENT
Purpose of UPDATE statement and DELETE statement in the form of CURRENT
UPDATE statement and DELETE statement in non CURRENT form
Collection oriented operations
Modify or delete all records that meet the conditions at one time
If you only want to modify or delete one of the records
Use the SELECT statement with cursor to find out all records that meet the conditions
Further find the records to modify or delete
Use the UPDATE statement and DELETE statement in the form of CURRENT to modify or DELETE it
Clauses to be used in UPDATE and DELETE statements
Where current of >
Indicates that the last fetched record is modified or deleted, that is, the record pointed to by the cursor pointer
UPDATE and DELETE statements in the form of CURRENT cannot be used
When the SELECT statement in the cursor definition has a UNION or ORDER BY clause
The SELECT statement is equivalent to defining a non updatable view
Dynamic SQL
Static embedded SQL
Static embedded SQL statements can meet general requirements
The conditions for determining the SQL statement and query to be submitted cannot be met until execution
Dynamic embedded SQL
Allows temporary "assembly" of SQL statements while the program is running
Supports dynamic assembly of SQL statements and dynamic parameters
Use SQL statement master variable
SQL statement primary variable
The content of the program main variable is the content of the SQL statement, not the input or output variable that originally saved the data
The main variable of SQL statement can set different SQL statements during program execution, and then execute them immediately
[example 8.6] create the basic table TEST.
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt="CREATE TABLE test(a int);";
/*SQL Statement main variable, which is the SQL statement to create the table*/
EXEC SQL END DECLARE SECTION;
...
EXEC SQL EXECUTE IMMEDIATE :stmt;
/*Execute dynamic SQL statements*/
dynamic parameter
dynamic parameter
Variable elements in SQL statements
Use parameter symbol (?) The data indicating this position is set at run time
Difference between and main variable
The input of dynamic parameters is not a compile time binding
It is done by preparing the master variable with the PREPARE statement and executing the EXECUTE statement to bind the data or master variable
To use dynamic parameters
(1) Declare SQL statement primary variable
(2) PREPARE SQL statement (PREPARE)
EXEC SQL PREPARE <Statement name> FROM <SQL Statement main variable>;
EXECUTE prepared statement (EXECUTE)
EXEC SQL EXECUTE <Statement name>
[INTO <Main variable table>]
[USING <Primary variable or constant>];
[example 8.7] insert tuples into TEST.
EXEC SQL BEGIN DECLARE SECTION;
const char *stmt = "INSERT INTO test VALUES(?);";
/*Declare that the content of the SQL primary variable is an INSERT statement */
EXEC SQL END DECLARE SECTION;
...
EXEC SQL PREPARE mystmt FROM :stmt; /*Prepare statement*/
...
EXEC SQL EXECUTE mystmt USING 100;
/*Execute the statement and set the INSERT statement insertion value of 100 */
EXEC SQL EXECUTE mystmt USING 200;
/* Execute the statement and set the INSERT statement insertion value 200 */
Welcome to join me for wechat communication and discussion (Please add notes on csdn)
