|
|
Posted on 3/15/2019 10:45:21 PM
|
|
|
|
preface
Last time, I was going to organize all the basic content of SQL, but I found that the clock was turning before I knew it. This article continues below.
Text
First, let's create the tables we need
Let's execute a SQL statement first
This code subtracts the balance of 0001 by 1000 and the balance of 0002 by 1000. But we added constraints when we built the table, and the balance must be greater than or equal to 10.
The results are as follows:
So this code execution error: "The UPDATE statement conflicts with the CHECK constraint "CH_balance". The conflict occurs in the database "DemoDb" with the table "dbo. Bank", column 'balance'。” 。 If one statement is error, then whether the other statement has been executed successfully.
The query results are as follows:
We will see that another SQL statement has no errors, but still does not commit successfully. Here is what we will reveal for you.
#transactions
What is Transaction?
Strictly speaking, if an operation satisfies atomicity, persistence, isolation, and consistency, then the operation is called a transaction.
Automatically submit transactions: in SQL Server. By default, every SQL statement is a transaction.
Show Transactions: Each transaction starts explicitly with a BEGIN TRANSACTION statement and ends explicitly with a COMMIT or ROLLBACK statement.
The demo we just mentioned above ended with Rollback, so SQL execution does not take effect. The data was not successfully modified.
In actual development and application, we usually set up a layer of transactions outside the non-query statement to ensure the integrity and consistency of the data according to actual needs. Either do it all or don't do it all.
There are two system variables that log the SQL that is faulty. - @@rowcount returns the number of affected lines, @@error returns the wrong encoding
Let's use these two system variables to determine whether there is an error in SQL and execute the above two SQL statements.
#Stored procedures
Meaning: Encapsulate a process that is performed (which can be complex) into a name, and then use that name to perform the process.
> Parameterless stored procedures
>Stored procedures with parameters
>Stored procedure with return value
try catch in the database
There are also stored procedures with default parameters, which were mentioned in the previous article about pagination.
Epilogue
Stored procedures are used more in our actual projects, many ancient systems, business logic is written in stored procedures, and the people behind maintain it, and they simply want to hammer the dog's head in front of them in minutes. Generally speaking, business logic is written in the code, but the number of times the data is read from the database, and the business logic is written in the storage process. Last year, a very complex string of business logic in the company's project was moved to the storage process, which increased the speed by dozens of times. Of course, special circumstances are treated specially. The specific application depends on the actual scenario.
(Note: The above content is the study notes of the year, if there is anything inappropriate, please correct it!) ）
|
Previous:I feel about the customer service system of large companiesNext:Video of Andale HCNP and HCIE
|
