DataBase stored procedures, functions, triggers, transactions, cursors

(1) Stored procedures

[1] Creating a stored procedure (no parameters)

CREATE PROCEDURE Protesta
As
SELECT * FROM TestA

--Modify the stored procedure (with parameters as output)
Alter PROCEDURE Protesta
@LastName varchar = NULL output
As
If @LastName is null
Begin
print ' null '
print ' 123 '
End
Else
Begin
Print @LastName
print ' 456 '
End
EXEC Protesta
exec protesta ' Olive '

(2) Custom functions (key points: functions and stored procedures differ primarily in that functions must declare return value types)

[1] User-defined function (return time)

Create function Dayonly (@datedatetime)returnsvarchar(a)as  begin    returnconvert(varchar),@date ,101)End
SELECT dbo. Dayonly (GETDATE ())

[2] User-defined function (return table)

Drop function testatable

Create function testatable (@Name nvarchar (10))

Returns table

As

Return select * from TestA where [email protected]

SELECT * FROM TestA

SELECT * FROM Testatable (' 12 ')

--user-defined function input parameters, return table

Create function resulttable (@Name nvarchar (10))

Returns @Result table (Rid varchar), rpwd varchar (10))

As

Begin

INSERT INTO @Result select * from TestA where [email protected]

Return

End

SELECT * FROM Resulttable (' 12 ')

(3) Trigger

[1] Creating a trigger

Create Triggerupdatestudent onStudent for Insert asbegin  UpdateStudentSetSname='Zhang San' whereSid=(SelectInserted.sid fromInserted)EndInsert  intoStudentSelect 'MM','1988-11-11','male';Select *  fromStudentOrder  bySidDesc;

[2] Disabling triggers

ALTER TABLE Sales.SalesOrderDetail

Disabletrigger All – All here can be a specific trigger name

[3] Delete trigger

Drop Trigger orderdetailnotdiscontinued

[4] Modifying triggers

Alter Trigger Tr_title

On MyTable

For Insert,update

As

If update (title)

Begin

Print (' Title1 ')

End

[5] Correlation function of trigger update ()

Create Trigger Updatetest

On Student

For Insert,update

As

If update (SName)----If the field in Update () is updated then true otherwise false

Select Inserted.sname from Inserted

Insert INTO Student select ' Oliveveve ', ' 1989-12-22 ', ' Women '

[6] Trigger (insert new data and update the name of the input you just inserted after inserting)

Create Trigger Inserttwo

On Student

After insert

As

Update Student set sname= ' oooo ' where sid= (select inserted. SID from Inserted)

Insert INTO Student select ' Moyao ', ' 1989-11-15 ', ' Male '

SELECT * FROM Student

Where the inserted table is a temporary table

Stores the information that will be inserted

The purpose of this trigger is to check if the information to be inserted conforms to the specified

(There are no special records in the product table)

This is an example of a check constraint that cannot be resolved.

Use AdventureWorks

Go

Create Trigger orderdetailnotdiscontinued

On Sales.SalesOrderDetail

After Insert,update

As

if exists

(

Select ' True ' from Inserted I

Join Production.Product P

On i.productid= P.productid

where P.discontinueddateis NOT NULL

)

Begin

Print (' Error appear. ')

Rollback Tran

End

(4) Business

SQL transactions are transactions that use the SQL Server itself: Use the Begin Tran,rollback Tran,commit Tran to implement transactions directly in a stored procedure:

Advantages: Best Execution efficiency

Restriction: Transaction contexts are only called in the database, and complex business logic is difficult to implement.

Begin try

BEGIN Tran

Insert into Student (SID) VALUES (-1)

Commit Tran

print ' commited '

End Try

Begin Catch

Rollback

print ' Rolled back '

End Catch

TransactionScope transactions

The TransactionScope transaction class, which enables code blocks to become transactional code. and automatically promoted to distributed transactions

Benefits: Simple to implement and automatically promoted to distributed transactions

So how to solve the database concurrency problem?

The solution to this problem is to lock the database to prevent multiple components from reading the data, by locking in the data used by the transaction, can guarantee the unlock, only this business can access the database. This avoids the problem of cross-access

(5) Cursors

--cursor: A pointer that moves up and down the data set

Drop cursor Studentcursor

--Declaring variables

Declare @SID int

Declare @Sname nvarchar (30)

Declare @Sage nvarchar (30)

Declare @Ssex nvarchar (5)

--Declaring cursors

Declare studentcursor Cursor FOR

SELECT * FROM Student

--Open cursor

Open Studentcursor

--Move the cursor down one line and put the fetched data in the previously declared variable

FETCH NEXT from Studentcursor to @SID, @Sname, @Sage, @Ssex

--Determine if the data is successful, @ @fetch_status is the system global variable, 0-Indicates success, 1-Indicates no data, 2-exceeds the last piece of data

While @ @fetch_status =0

Begin

Print (Cast (@SID as varchar) + ': ' [email protected]+ ': ' [email protected]

--Extract the next record

FETCH NEXT from Studentcursor to @SID, @Sname, @Sage, @Ssex

End

Close Studentcursor

Deallocate studentcursor

--Using cursors in stored procedures (scope of cursors)

CREATE PROCEDURE Teststudentcursor

As

--Declare variables to populate the data retrieved by the cursor

DECLARE @SID int

declare @Sname nvarchar (30)

declare @Sage nvarchar (30)

declare @Ssex nvarchar (5)

--Declares a global cursor inside a stored procedure

Declare studentcursor cursor Global for

SELECT * FROM Student

--Open cursor

Open Studentcursor

--Reads the next piece of data and populates the data into the previously declared variables

FETCH NEXT from Studentcursor to @SID, @Sname, @Sage, @Ssex

--Determine if the data is being taken and whether the data is being taken to meet the requirements

while (@SID <5) and (@ @fetch_status =0)

Begin

--Print data

Print (CAST (@SID as varchar) + ':: ' [email protected]+ ':: ' [email protected])

--Read the next piece of data

FETCH NEXT from Studentcursor to @SID, @Sname, @Sage, @Ssex

End

--Close cursor

Close Studentcursor

--Releasing cursors

Deallocate studentcursor

--Execute Stored procedure

EXEC teststudentcursor

DataBase stored procedures, functions, triggers, transactions, cursors