SQL Storage Management

Transaction management:

A single unit of work is called a transaction, and we combine several SQL commands to form a stored procedure, a trigger, and so on, using stored procedures and triggers to manage transactions.

?

The basics of programming are: functions, programming statements, and so on.

Programming Statements:

1. Begin ... end inserts several SQL statements in the middle, which we treat as a unit.

2. Jump Statement (goto statement):

The GOTO statement allows the program to jump to the specified location with an identifier to continue execution.

3. Conditional Branch Statement (if---else):

?

--using conditional branching statements and jump statements to find the sum of 1 to 5 and output

declare @sum int, @count int

Select @sum =0, @count =1

Label_1:

If @count <=5

Begin

Select @sum = @sum + @count

???? Select @count = @count + 1

???? Goto Label_1

End

Else

Select @count, @sum

4. Loop statement (while .... Continue ... break)

--Find the sum of 1 to 5 and output

declare @sum int, @count int

Select @sum =0, @count =1

while (@count <=5)

Begin

Select @sum = @sum + @count

???? Select @count + = 1

End

Select @count, @sum

5. PrintOut statement (print statement)

Print string |@ local variable |@@ 全局 variable

declare @sum int, @count int

Select @sum = 0, @count = 1

while (@count <= 5)

Begin

???? Select @sum + = @count

???? Select @count + = 1

???? End

???? The sum of print ' 1 to 5 is: '

???? Print @sum

6. Termination statement (return statement)

return unconditional termination

7. Comment Statement

--Comments for a single statement

/* */Comments for multiple statements

?

?

A transaction (transation) is a unit of work. Transactions can bind logically related operations together to preserve the integrity of the data. Furthermore, these logic-related operations are die relationships and must be executed together.

If you use several SQL commands as a transaction, you can define them by using the following statement.

Begin (Transation|tran)

SQL statement Group

Commit (Transation|tran)

One is the beginning of a transaction, and the other is the end of the transaction.

Rollback (Transation|tran) [Hold point name] uses a rollback mechanism before commit Tran to cancel a transaction and undo any changes made to the data.

Save Tran[sation] SavePoint name

?

???? Begin Transation

???? INSERT INTO department values (2, ' Sales Department ')

???? Save Tran A

???? INSERT INTO department values (3, ' marketing Department ')

???? Rollback Tran A

???? INSERT INTO department values (5, ' Human Resources ')

???? Commit

The save point is defined above with Save Tran, rollback TRAN A action revokes the second insert from the database

?

Stored procedures:

It refers to the process of centralizing some fixed operations and having a SQL Server to accomplish a task, which is a stored procedure. Stored Procedures

is a compiled SQL statement that allows the user to declare variables, output parameters, return single or multiple result sets, and return values. Stored procedures exist in the database and can be executed by application calls.

Some system stored procedures: such as sp_helptext sp_rename. There are, of course, some user-defined stored procedures.

Create Proc[edure] Procedure name [@ parameter name parameter type [= Default value][output] ...] as SQL statement Group

syntax for executing stored procedures:

EXEC (UTE) procedure name [[@ parameter name =][parameter] [default value][output] ...

EXEC who has edit permissions

?

--A stored procedure with parameters that can be assigned in two ways when we execute it

--where + appears as a string connector.

CREATE proc Permissions Query

(@permit varchar (20))

As

SELECT *

???? From Mag_dept

???? Where permitstr like '% ' + @permit + '% '

???? ?

???? EXEC permissions Query ' nuclear draft '

???? EXEC permissions query ' Finalize '

Example: Create a stored procedure called a person's position, query the job status of an employee, and return the results of the query as output parameters.

The input parameter of the stored procedure is the employee name, expressed as @emp_name. The declaration output parameter @role holds the job information, and the parameter type must be the same as the type of the output parameter when the stored procedure is defined.

???? CREATE PROC Personnel positions

???? (@emp_name varchar (+) = ' ', @role varchar output)

???? As

???? Select @role =emprole

???? From Mag_dept

???????? where [email protected]_name

???? Return

?????

????

???? DECLARE @emprole varchar (20)

???? exec position @emp_name = ' Zhao Hua '

???? @role = @emprole Output

???? Select @emprole as Position

?

To modify a stored procedure:

Changing the Create to alter is the process of modifying the stored procedure.

Example: Modifying a Stored Procedure permission query to increase the number of employees who meet the criteria

ALTER PROC permission Query

(@permit varchar (20))

As

SELECT *

???? From Mag_dept

???? Where permitstr like '% ' + @permit + '% '

????

???? Select COUNT (*) as number of people

???? From Mag_emp

???? Where permitstr like '% ' + @permit + '% '

EXEC permission query ' issue '

?

To delete a stored procedure

drop proc Permission Query

?

Stored Procedures and transaction management

To avoid transactional fragmentation, we typically write transactions into stored procedures, where the server catches error messages, which helps to manage the nesting of transactions in the stored procedure.

???? -Edit the stored procedure journal editor to ensure that a new record is added to the Mag_info table in the database of the periodical collection system, the completion date is before the publication date.

???? -Where begin-end equals {} curly braces.

???? create proc Journal editor

???? (@magid char (9), @magname varchar, @pubdate datetime, @depid int,

???? @designername varchar (in), @finishdate datetime)

???? As

???? BEGIN Tran

???? INSERT INTO mag_info values

???? (@magid, @magname, @pubdate, @depid, @designername, @finishdate)

???? If @pubdate > @finishdate

???? Begin

???????? Commit Tran

???????????? return 0

???????? End

???????? Else

???????? Begin

???????? Rollback Tran

???? return 9999

End

--Execution????????

DECLARE @status int

EXEC @status = ' Journal editor ' ' Baxddd ', ' Beijing Information Weekly ', ' 2007-4-2 ', ' 2 ', ' Zhang Bin ', ' 2004-3-30 15:00 '

Select @status

?

Trigger

A trigger is an object that is defined on a table and is a special kind of stored procedure. A trigger does not require a special statement to be invoked, it is executed primarily by triggering an event, that is, when an insert delete update is executed, the statement is automatically triggered, and the stored procedure can be

Stored procedure name and is called directly. It can be used for constraints, default values, and integrity checks for rules.

To create and execute a trigger:

Create TRIGGER Trigger Name

On table name

For{[detete],[insert],[update]}

As SQL statement Group

NOTE: Triggers can reference objects other than the current database, but can only create triggers in the current database. In other words, a trigger can reference a temporary table object.

--Establishing constraints

--Create Update department trigger to ensure the referential integrity of department heads and departmental information in the Mag_dept table in the database of the periodical collection system in the Mag_emp table

--Statement flow:

--Get Department number and owner name from inserted table, save in variable @depid and @manager respectively.

--Check out the depid of the person in charge in the Mag_emp, and determine if it is consistent with the variable @depid, if not, the person responsible @manager in Mag_emp

--The Depid in is modified to @depid.

CREATE TRIGGER Update Department

On mag_dept for update

As

DECLARE @depid int

???? DECLARE @manager varchar (50)

???? Select @depid =depid, @manager = Depmanager

???? From inserted

???? if (@depid <> (select Depid from mag_emp where [email protected])

???? Update mag_dept Set depid = @depid

???? where EmpName = @manager

????

???? --Using

???? Update mag_dept

???? Set Depmanager = ' Wang Lingling ', deptel= ' 1111111 '

???? where depid = 3

????

???? --View

???? Select EmpName, Depid

???? From Mag_emp

???? Where Empname= ' Wang Lingling '

modifying triggers

???? ALTER TRIGGER UPDATE Department

On mag_dept for update

As

DECLARE @depid int

???? DECLARE @manager varchar (50)

???? Select @depid =depid, @manager = Depmanager

???? From inserted

???? if (@depid <> (select Depid from mag_emp where [email protected])

???? Update mag_dept Set depid = @depid

???? where EmpName = @manager

???? print ' Update succeeded '

Delete Trigger

Drop Trigger Update Department

?

Triggers and transaction management

The most common application of triggers is to perform complex row validation to ensure the integrity of the data. If the trigger is able to determine that the command that fired the trigger is invalid, it can roll back the transaction.

???? Create TRIGGER Add Journal

???? On Mag_info for insert

???? As

???? DECLARE @magid char (9)

???? DECLARE @num tinyint

???? Select @magid =magid from inserted

???? Select @num =count (*) from Mag_info

???? where Mag[email protected]

???????? If @num >0

???????? Begin--The SQL statement has multiple lines, so use the BEGIN END statement.

???????? Rollback Tran

???????? print ' Journal number is not unique '

???????? End

?

Organize the code as follows:

SQL Storage Management