SQL Server Database Programming

I. Definition and assignment of variables

DECLARE @ variable name type

SET @ variable name = value

eg

Check the car table with the name of the BMW two words
DECLARE @name varchar (20)
Set @name = ' BMW '
SELECT * from car where Name like '% ' [email protected]+ '% '

Check the average of all cars in the car table and output
DECLARE @price decimal (10,4)
Select @price = AVG (price) from Car
print ' average price for all cars: ' +cast (@price as varchar (20))

Second, the statement

1. Output statement print ' string '

2. Branch statements

A, if Else

If judging condition
Begin
The statement to execute
End
Else
Begin
The statement to execute
End

b, Case statement (equivalent to C # Swtich) (two usage)

1. Simple Case function

Case Sex

When ' 1 ' Then ' men '

When ' 2 ' then ' women '

ELSE

Other

END

2.Case search function

Case

When sex = ' 1 ' Then ' man '

When sex = ' 2 ' Then ' women '

ELSE

Other

END

EG1,

DECLARE @ccname varchar (20)
Set @ccname = ' BMW '
SELECT * from Car where Name is like
Case
When @ccname = ' bmw ' Then '% BMW '
When @ccname = ' Audi ' Then '% Audi '
Else '% '
End

1). The case search function is more powerful than the other.

2). The case function is similar to the If......else if statement, returning only the first qualifying value, followed by the part being ignored

Note: Do not write a semicolon or comma after the statement ends

Third, stored procedures

1. Stored procedure without parameters (same as view function)

eg

1 Create procedure -- procedure to create a stored procedure keyword, you can also abbreviate proc,mypro to a stored procedure name 2  as 3 Select *  from authors 4 -- Executing stored procedures 5 exec Mypro  --exec means execute stored procedure

2. Stored Procedures with parameters

Create procedureBuyfriut@Uid varchar( -),@Fcode varchar( -),@Sl int asbegin    Declare @kc int,@price float    Select @kc=Numbers,@price=Price fromFruitwhereIds=@Fcode    if @Sl>@kc    begin        Print 'Insufficient stock! '    End    Else    begin        Declare @ye float        Select @ye=Account fromLoginwhereUserName=@Uid        if @ye>= @price*@Sl        begin            UpdateFruitSetNumbers=Numbers-@Sl whereIds=@Fcode            UpdateLoginSetAccount=Account-@price*@Sl whereUserName=@Uid            Declare @sj int            Set @sj = cast(Rand()*10000     as int)                            Insert  intoOrdersValues(@sj,@Uid,GETDATE())--write the primary key table when adding content, and then write the Foreign key table            Insert  intoOrderDetailsValues(@sj,@Fcode,@Sl)                End        Else        begin            Print 'Insufficient balance! '                    End    End    EndGo--Using Stored ProceduresDeclare @s intexec  @s =Buyfriut'Wangwu','k001', -Print @s --To Delete a stored procedureDrop procBuyfriut

3. Trigger

A trigger is a special kind of stored procedure that can be used to enforce complex integrity constraints on a table and maintain data consistency. When the data protected by the trigger changes, the trigger is automatically activated and the related actions defined in the trigger are executed to ensure that the data is not integrity constrained or incorrectly modified.

Use of two tables inserted table and deleted table, temporary table, function is to restore data

eg

SELECT * FROM Loginone
SELECT * FROM Biandong
Go
--Create a trigger
Create Trigger Tr_loginone_delete
On Loginone
A, for delete trigger--delete Time execution (first Class)

eg

 asbeginDeclare @uid varchar( -),@name varchar( -)Select @uid=UserName,@name=Name fromdeletedInsert  intoBiandongValues(@uid,@name,'Delete')EndGo--The trigger executes when the table Loginnoe is deletedDelete  fromLoginonewhereUserName='AAA'Go

b, instead of DELETE trigger--Alternative execution (class III)

--Delete the data inside the student with two additional statements instead, delete the main table from the table firstCreate TriggerTr_student_delete onStudentinstead of Delete asbegin    Declare @sno varchar( -)    Select @sno=Sno fromdeletedDelete  fromScorewhereSno=@sno    Delete  fromStudentwhereSno=@sno    End--Trigger When delete is executedDelete  fromStudentwhereSno='101'

C, after delete--execution after deletion (type II)

4. Business

A, start transaction begin TRAN (transaction)
B. Submit a Commit
C, Roll back rollback

Select *  fromCarSelect *  fromBrandbegin Transaction --Start a transactionDelete  fromCarwhereCode='c005'Delete  fromBrandwhereBrand_Code='b002' if @ @ERROR=0--Judging if it's wrong, if you do it right @ @ERROR is 0begin    Commit --Commit a transactionEndElsebegin    rollback--rolling back a transactionEnd--Try Catchbegin Transaction --Start a transactionbeginTry--write code to execute in try    Delete  fromCarwhereCode='c005'    Delete  fromBrandwhereBrand_Code='b002'    CommitEndTrybeginCatchrollbackEndCatch

SQL Server Database Programming