SQL advanced application

SQL advanced application
1. Use of Embedded SQL
SQL is a non-procedural, set-oriented data manipulation language. Most of its statements are independent of context conditions. In transaction processing, process control is often required, that is, the program needs to execute different tasks according to different conditions. If you only use the SQL language, it is difficult to implement such applications. On the other hand, advanced languages cannot efficiently access data when it involves database operations. Therefore, the use of Embedded SQL combines the process of advanced languages and the data manipulation capability of SQL languages to improve the efficiency of database applications.
DBMS can process embedded SQL languages in two ways: Pre-compilation and extended compilation procedure.
Pre-compilation is performed by the DBMS pre-compiler to scan the source program, identify the SQL statements in the program, convert them into the calling statements of the host language, and enable the host language compiler to recognize them, finally, the compiler compiles the entire source program as the target code.
The extended compilation procedure is to modify and expand the Compilation Program of the host language so that it can directly process SQL statements.
Currently, many pre-compilation methods are used. The key step in the embedded SQL statement processing process is to use the source code of the host language embedded with SQL through the pre-compiler (precomplier) becomes the source code of the pure host language. In addition to the SQL language interface, RDBMS generally provides a batch of SQL functions written in the host language, and provides various functions for the program to call the DBMS. Such as establishing a connection and connection environment with DBMS, transmitting SQL statements, executing SQL statements, returning execution results and statuses. These functions form the SQL function library. The pre-compiler compiles SQL statements into the calls of the host language to SQL functions, so as to convert the source code of the host language embedded with SQL into the source code of the pure host language and execute it after compiling the connection.
1.1 General Form of Embedded SQL
The syntax structure of the SQL language is basically the same in interactive and embedded usage.
When implementing embedded SQL statements, each DBMS uses the same method for different host languages.
The pre-compiler cannot check the syntax validity of the host language. What it can do is to find the signal indicating "embedded SQL starts here" and "embedded SQL ends here. Therefore, embedded SQL generally has a prefix and an Terminator.
The following describes how to embed SQL into the C language.
The SQL statement embedded in C Language starts with exec SQL and ends with a semicolon:
Exec SQL <SQL statement>;
Exec SQL is case-insensitive. Only valid SQL statements can be included between exec SQL and semicolons, and no host language statements can be provided.
Embedded SQL statements can be divided into executable statements and descriptive statements based on different functions. In a host language program, descriptive SQL statements can appear wherever descriptive programming statements are allowed. executable SQL statements can appear wherever executable programming statements are available.
1.2 information transmission between embedded SQL and host languages
Data transmission between DBMS and host language programs is achieved through host language program variables, referred to as main variables.
(1) primary variables
When an SQL statement references a primary variable, a colon ":" must be added before the variable to distinguish it from the Database Object Name (such as the column name, table name, and view name ), therefore, the primary variable can have the same name as the database variable. When the main variable is referenced in the host language, a colon is not required.
Through primary variables, the host language can provide parameters to SQL statements, such as specifying the data to be inserted (or modified) into the database. On the other hand, the SQL statement should be able to assign values to the main variable or set the status information, and return it to the application so that the application can get the results and status of the SQL statement.
In embedded programs, all the main variables, except those defined by the system, must be described in advance. The description is placed between two embedded SQL statements:
Exec sqlbegin declare section;
......
Exec sqlend declare section;
The content in the middle is called the description section. The format of the variables in the description section must comply with the requirements of the host language, and the Data Type of the variables should be the types that can be processed by both the host language and SQL.
(2) SQL Communication zone
In the main variable, there is a system-defined main variable called sqlca. sqlca is a global variable and is used for communication between programs and DBMS. Sqlca variables do not need to be described. You only need to add the include statement before the embedded executable SQL statement. The format is:
Exec sqlinclude sqlca;
Sqlca. sqlcode is a component of sqlca and belongs to the integer type. It allows DBMS to Report SQL statement execution to the application. Each time an SQL statement is executed, a sqlcode is returned. Therefore, in an application, the sqlcode value should be tested after each SQL statement is executed to understand the execution of the SQL statement and perform corresponding operations.
Different systems may have different definitions of sqlcode values. General Conventions:
Sqlcode = 0, indicating that the statement is successfully executed without exception;
Sqlcode is a negative integer, indicating that the SQL statement execution failed. A negative value indicates the type of the error;
Sqlcode is a positive integer, indicating that the SQL statement has been executed, but exceptions occur. For example, sqlcode = 100 indicates that the statement has been executed, but no data is desirable (for example, no data that meets the conditions in the DB ).
The Select syntax with the into clause is unique to embedded SQL.
Exec SQL select count (*) into: emp_num
From emplolee where Eno =: Eno;
1.3. cursor
Different Data Processing Methods of the SQL language and the host language are coordinated through cursor. A cursor is a data buffer provided by the system for users to store the execution results of SQL statements. Each cursor has a name. You can use SQL statements to obtain records from the cursor one by one and assign them to the primary variables for further processing by the host language.
The cursor operation involves four steps:
(1) indicates the cursor.
Use the declare statement to define a cursor for a select statement in the following format:
Exec SQL declare cursor name cursor for <SELECT statement>;
The SELECT statement can be a simple query or a join query or nested query. Its result set is a new relationship. When the cursor moves forward (FETCH), it can point to each record of the new link in sequence.
The cursor is only a descriptive statement. In this case, the DBMS does not perform the query operation specified by select.
(2) Open the cursor
Use the open statement to open the specified cursor. The general format is:
Exec SQL open <cursor Name>;
Open the cursor and execute the corresponding SELECT statement to fetch all records that meet the query conditions from the table to the buffer zone. The cursor is activated and points to the first record in the result set.
(3) Push the cursor pointer and retrieve the current record
Use the fetch statement to extract the current record in the buffer to the primary variable for further processing by the host language. At the same time, the cursor pointer is pushed forward to a record. The statement format is as follows:
Exec SQL fetch <cursor Name> into <list of primary variable names>. The list of primary variable names is separated by commas (,) and must correspond to the target column expressions in the SELECT statement. The forward cursor is used to retrieve the next record in the buffer zone. Therefore, the fetch statement is usually used in a loop structure statement to retrieve all records in the result set one by one for processing. If the record has been obtained, sqlca. sqlcode returns 100.
(4) Close the cursor
Close the cursor with the close statement to release the buffer and other resources occupied by the result set. However, the closed cursor can be reinitialized with an open statement, which is associated with the new query result. The general format of closing a cursor is:
Exec sqlclose <cursor Name>;
1.4 update and delete a cursor
Use the update or delete statement to modify or delete the records pointed to by the cursor.
Where current of <cursor Name>
When defining a cursor, if the update statement of the cursor is used to describe the cursor, use
For update of <column Name>
Indicates that the retrieved data can be modified in the specified column. However, you do not need to use this clause when defining a cursor for the delete statement that uses the cursor.
2. Transactions
Transactions are a special means provided by RDBMS. Through this method, the application combines a series of database operations and performs them as a whole to ensure that the database is in a consistent (correct) state.
2.1. Concepts of transactions
Transactions are user-defined database operation sequences and are an inseparable unit of work. Whether or not there is a fault, the database system must ensure that the transaction is correctly executed-or that the entire transaction or the operation that belongs to the transaction is not executed. A transaction can be an SQL statement, a group of SQL statements, or the entire program. Generally, a program contains multiple transactions.
The start and end of a transaction can be explicitly controlled by the user. If no explicit transaction is defined, the specific DBMS automatically divides the transaction according to the default rule.
In SQL, there are three statements used to process transactions:
Begin transaction
Commit
Rollback
2.2. Features of transactions
To ensure data integrity, the database system is required to maintain the following transaction properties:
1) The automicity transaction is the most basic unit of work in the database logically. The operations contained in a transaction must either be fully executed and normally ended, or do nothing, it seems that this transaction has never happened.
2) the result of a consistency transaction must be that the database changes from one consistent state to another. If the transaction execution is interrupted due to hardware or software faults during the transaction execution process, Database Inconsistency will occur. Therefore, the atomicity of transactions is the guarantee of consistency.
3) isolation (isolation) multiple transactions in the database system can be executed simultaneously (concurrently), but the execution of one transaction must not be disturbed by other transactions. That is, the operations in a transaction and the data they use are isolated from other transactions. In this way, every transaction does not feel that other transactions are being executed concurrently in the system.
4) durability: Once a transaction is committed (that is, it is completed normally), its changes to the data in the database are persistent, even if the database is damaged due to a fault, DBMS should be able to recover it correctly. The preceding four features of transactions are also called acid features. Ensuring ACID properties of transactions is one of the important tasks of the database system.
3. SQL stored procedures
Currently, most RDBMS provides stored procedures and triggers to solve some complicated business rules in applications.
In C/S and B/S architecture software development, SQL is the main programming interface between foreground applications and background database servers. SQL programs can be stored and executed in two ways. First, you can store the program in the foreground and create an application that sends commands to the background server and processes the returned results. Second, you can store the program in the database as a stored procedure, create an application on the front-end that executes the stored procedure and processes the result.
Stored Procedures in databases are similar to those in other programming languages. stored procedures can be:
· Accept input parameters and return multiple values to the call process or batch process in the form of output parameters;
· Programming statements that execute database operations (including calling other processes;
· Return Status values to the call process or batch processing to indicate success or failure (and cause of failure ).
A stored procedure is composed of a set of SQL statements, which are pre-compiled and stored in the database for multiple calls by foreground applications. Using Stored procedures not only facilitates software development, but also reduces the time required for parsing and query optimization when SQL statements are interpreted and executed, improving efficiency. In the C/S structure, the application (client) only needs to send a request to the server to call the stored procedure, and a batch of SQL commands can be executed on the server without returning the intermediate results to the client, this greatly reduces network traffic and server overhead.
3.1 Definition of Stored Procedures
The definition of a stored procedure consists of two parts: process name and parameter description; and process body description. The statement format for creating a stored procedure is as follows:
Create procedure <Stored Procedure Name>
[<Parameter list>]
As
<Process body>;
The <parameter list> consists of one or more parameter descriptions. Each parameter description includes the parameter name and Data Type of the parameter. Of course, the stored procedure can have no parameters; <process body> is a set of SQL statements that implement the stored procedure function.
3.2. Execution of Stored Procedures
Once a stored procedure is created, it is stored in the database as an object of the database. Users can execute in RDBMS, but more often they are called in front-end applications.
4. SQL triggers
Triggers not only implement integrity rules, but also ensure the implementation of some complicated business rules. A trigger is a special process driven by database operation events (insert, delete, and modify). Once defined by a user, when any user adds, deletes, or modifies the data specified by the trigger, the system automatically activates the trigger action to perform centralized Integrity Control on the database server.
4.1. Trigger composition and type
The trigger definition includes two aspects:
· Specify trigger events;
· Define the action performed by the trigger.
The trigger event includes inserting, deleting, and modifying rows in the table, that is, executing the insert, delete, and update statements. When defining a trigger, you must specify one or more trigger conditions. In the up-date operation, you can also specify that the modification of a specific attribute or attribute group is a trigger condition.
There are two related times for event triggering: after and before. The After trigger is triggered after an event, and the before trigger is triggered before the event occurs.
The trigger action is actually a series of SQL statements, also known as the trigger body. There are two ways:
(1) Each row affected by the event (for each row)-The trigger process is performed for each group of one RMB, which is called a row-Level Trigger;
(2) Only one trigger process (for each statement) for the entire event is called a statement-Level Trigger. This is the default trigger method.
4.2 create a trigger
(1) The statement used to create a trigger is generally in the format
Create trigger <trigger Name> [{before after}]
{[Delete! Insert! Update of [column name list]}
On Table Name
[Referencing <temporary view Name>]
[When <trigger condition>]
<Trigger action>
[For each {row statement}]
(2) Description
Before: instructs the DBMS to trigger the trigger before executing the trigger statement.
After: instructs the DBMS to trigger the Trigger After executing the trigger statement.
Delete: indicates the delete trigger. The trigger is triggered when a delete statement deletes a row from the table.
Insert: indicates the insert trigger. The trigger is triggered when an insert statement inserts a row into the table.
Update: indicates the update trigger. The trigger is triggered whenever the update statement modifies the column value specified by the of clause. If the of clause is ignored, the DBMS will trigger every time the update statement modifies any column value of the table.
Referencing <temporary view Name>: Specifies the alias of the temporary view. When a trigger is running, the system generates two temporary views (or virtual tables) to store the updated values (old values) and updated values (new values ). For row-level triggers, the default temporary view names are old and new. For statement-level triggers, the default temporary view names are old-table and new-table. The temporary view does not exist once the trigger ends.
When <trigger condition>: Specify the trigger condition. When the trigger condition is met, DBMS triggers the trigger. The trigger condition must contain the temporary view name, not the query