Understanding software development in advance (33) Data Manipulation language (DML)

Data manipulation language (manipulation Language,dml) includes insert, delete, and update statements for adding, deleting, and modifying data.

This article uses the following table Tb_employeeinfo as an example to illustrate:

CREATE TABLE Tb_employeeinfo
    
(
    
    employeeno         varchar) not       null       --employee work number
    
    EmployeeName    varchar not      null       --employee name
    
    employeeage       int                            null        --employee age
    
);

In a real software development project, the rules for using DML are as follows:

1. Insert statement must list field names

For the sake of convenience, many developers simply list the table names when they need to insert data into all the fields in a table, as follows:

Counter Example:

INSERT INTO Tb_employeeinfo vaues (' 10000 ', ' Jim ', 30)

This way, if the TB_EMPLOYEEINFO table structure changes, the previous SQL statement cannot write data. The right thing to do is to list the field names regardless of how many fields are inserted into the table. As shown below:

Positive example:

Insert into Tb_employeeinfo (Employeeno, EmployeeName, Employeeage) vaues (' 10000 ', ' Jim ', 30)

Also, do not use the "SELECT * from" statement, you must list the field names, even if you return the values of all the fields.

2. The Delete,update statement must take a where condition

This is done to prevent the entire table from being modified and emptied, resulting in system paralysis. For full table processing, use "where 1=1".

Positive example:

Delete from Tb_employeeinfo where employeeage=30

Update Tb_employeeinfo set employeeage=32 where 1=1

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/Programming/project/

3. The execution result of a DML statement needs to be checked in an SQL statement block or stored procedure

This is a database error/exception handling mechanism that requires checking the execution of SQL statements for success or failure to determine subsequent operations based on the results of the execution.

(1) Processing of sqlserver/sybase database

In the Sqlserver/sybase database, the system variable "@ @error" can be checked and processed according to the result, if the transaction has already begun (the database modification is in the transaction unit). A transaction is a sequence of operations, either wholly or wholly, which is an indivisible unit of work, and needs to be rolled back.

Positive example:

BEGIN Tran-                   transaction begins
    
    insert INTO ...
    
    If @ @error <> 0
    
    begin
    
        Rollback TRAN         --transaction rollback return
    
        end
    
     
    
    update ...
    
    If @ @error <> 0
    
    begin
    
       Rollback TRAN-        -transaction rollback return
    
       end
    
   Commit Tran--                 Transaction Commit

(2) Oracle database Processing

In Oracle, errors are handled through the exception mechanism. If there is no exception handling section, it immediately aborts the current block of SQL statements and automatically makes a fallback. If you need to deal with the situation, you should use the Exception handling section, or use the Oracle default behavior to abort the current SQL block and automatically rewind.

Positive Example 1:

Begin
    
    INSERT INTO ...
    
    Update ...
    
    Insert INTO ...
    
    Exception when
    
        others then
    
        begin
    
           ...            --Error handling
    
           rollback;
    
       End;
    
End

Positive Example 2:

Begin
    
    INSERT INTO ...
    
    Update ...
    
    Insert INTO ...        --no exception handling part end
    
;

In addition, transactions are used in SQL statement blocks and stored procedures, which must be guaranteed to match the start and end of a transaction, that is, it must be guaranteed that "BEGIN TRAN" has the correct "commit" or "rollback" corresponding to any branch and exception of the block of SQL statements.

In real software projects, data manipulation language (DML) is used very extensively, and using the language correctly is the basic requirement for a software developer.