Let you know software development in advance (33): data manipulation language (DML)

Part 1 Database SQL language

Data manipulation language (DML)

 

Data Manipulation Language,DMLIncluding insert, delete, and update statements for adding, deleting, and modifying data.

The following table tb_employeeinfo is used as an example:

Create table tb_employeeinfo

(

Employeeno varchar (20) not null, -- employee ID

Employeename varchar (20) not null, -- employee name

Employeeage int null -- employee age

);

In actual software development projects, the rules for using DML are as follows:

1. The insert statement must list field names.

To save trouble, many developers can directly list the table names when inserting data into all fields in a table, as shown below:

Counterexample:

Insert into tb_employeeinfo vaues ('2013', 'Jim ', 30)

In this way, if the structure of the tb_employeeinfo table is modified, data cannot be written to the previous SQL statement. The correct method is to list the field names no matter how many fields in the table are inserted. As follows:

Example:

Insert into tb_employeeinfo (employeeno, employeename, employeeage) vaues ('20140901', 'jim', 30)

Similarly, do not use the select * from statement. The field names must be listed, even if the values of all fields are returned.

 

2. The delete and update statements must contain the where condition.

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

Example:

Delete from tb_employeeinfo where employeeage = 30

Update tb_employeeinfo set employeeage = 32 where 1 = 1

 

3. Check the execution results of DML statements in SQL statement blocks or stored procedures.

This is a database error/Exception Handling Mechanism. You need to check whether the SQL statement runs successfully or fails to determine subsequent operations based on the execution results.

(1) SQL Server/Sybase Database Processing

In the SQLServer/Sybase database, you can check the system variable "@ error" and process it according to the results, if the transaction has already started (the database modification is performed in transaction units. A transaction is a sequence of operations. These operations are either performed in full or not, and they are an inseparable unit of work.) rollback is required.

Example:

Begin tran --Start transaction

Insert ......

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 part, it immediately terminates the current SQL statement block and performs automatic rollback. If you need to handle the exception as needed, use the Exception Handling Section. Otherwise, use the default Oracle behavior to abort the current SQL block and perform automatic rollback.

Example 1:

Begin

Insert...

Update...

Insert...

Exception

When others then

Begin

...... -- Handle errors

Rollback;

End;

End;

 

 

Example 2:

Begin

Insert...

Update...

Insert... -- No Exception Handling

End;

 

 

In addition, to use transactions in SQL statement blocks and stored procedures, you must ensure that the start and end of the transaction match, that is, you must ensure that "begin tran" corresponds to the correct "commit" or "rollback" in any branch or exception of the SQL statement block.

 

In actual software projects, data manipulation language (DML) is widely used. correct use of this language is a basic requirement for a software developer.

 

 

(My microblogging: http://weibo.com/zhouzxi? Topnav = 1 & wvr = 5, No.: 245924426, welcome !)