SQL Server stored procedures, triggers, cursors

Stored Procedures

1, stored procedures are prepared in advance, stored in the database of the program, these programs to complete the database of the specified operation.

2. System stored procedure: SQL Server itself provides a number of stored procedures for managing information about databases and users.

User stored procedures: Users can also write their own stored procedures and store them in the database for the client to call.

3, the main purpose of this arrangement is to give full play to the function of the database server, to minimize congestion on the network.

4. System stored Procedure

Concept:

It is designed to be able to easily query information from system tables, or to complete administrative tasks or other system administration tasks related to updating database tables.

System stored procedures can be executed in any database. is created and stored in the system database master, and the name begins with sp_ or xp_.

Some system stored procedures:

Sp_addtype: Used to define a user-defined data type.

sp_configure: Used to manage server configuration option settings.

xp_sendmail: Used to send e-mail or pager information.

Sp_stored_procedures: Used to return a list of stored procedures in the current database.

Sp_help: Used to display the parameter list and its data type.

Sp_helptext: The definition text used to display the stored procedure.

Sp_rename: Used to modify the name of the user object in the current database.

sp_who: Used to display the current user using the database

Sp_help: Used to display the parameter list and its data type.

Sp_depends: The object that is used to display the stored procedure, or the object to which the stored procedure is based

Sp_helptext: The definition text used to display the stored procedure.

5. User-defined stored procedures

Define the format:

CREATE proc[edure] procedure_name [; number]

[@parameter data_type [= Default][output], ... uc1]

As Sql_statement

Procedure_name: Give the name of the stored procedure;

Number: Specify an ordinal (with the same name) for a stored procedure of the same name;

@parameter: Give the name of the parameter;

Data_type: Indicates the data type of the parameter;

Output: Return value parameter

Default: Gives the defaults for the parameters;

Sql_statement: The SQL statement that the stored procedure executes, which can be a set of SQL statements that can contain process control statements, and so on.

Precautions:

Stored procedures are typically used to complete data query and processing operations, so you cannot use statements that create database objects in a stored procedure.

That is, the following statements generally cannot be included in a stored procedure:

Create TABLE; create VIEW; create DEFAULT;

Create RULE; create TRIGGER; Create PROCEDURE

The return value and status information for the stored procedure:

Whenever you execute a stored procedure, you always return a result code that indicates the execution state of the stored procedure.

If the stored procedure executes successfully, the result code returned is 0, and if the stored procedure fails, the result code returned is typically a negative number, which is related to the type of failure.

When we create a stored procedure, we can also define our own status codes and error messages.

To execute a stored procedure:

Example: Executing a stored procedure with parameters, querying students older than

Create proc show;3 (@pno char (6))

As

SELECT * from person where Pno = @pno

EXEC show;3 4

Example: CREATE Procedure sp_getstu;1

As

SELECT * FROM students

Example: A stored procedure with parameters that queries a student greater than the specified age

CREATE proc sp_getstu;2 (@sage int)

As

SELECT * from student where age > @sage

Example: A stored procedure with an output parameter that queries the age of a specified student

CREATE proc Sp_getstu;3 (@name char (), @age int output)

As

SELECT @age = age from student where name = @name

Declare @sage int

Exec sp_getstu;3 ' Zhang San ', @sage

Print @sage

Example: A stored procedure with parameters and a return status value.

CREATE PROCedure sp_getstu;3 (@sage int =null)

As

IF @sage is NULL

BEGIN

PRINT ' must provide a numeric value for the parameter! ‘

RETURN 13

END

IF not EXISTS (SELECT * from student WHERE sage > @sage)

BEGIN

PRINT ' does not meet the criteria of the record! ‘

RETURN-103

END

SELECT * FROM student WHERE sage > @sage

RETURN 0

DECLARE @status int

EXECUTE @status =sp_getstu;3 22

Print @status

Trigger

A: A trigger is a special stored procedure that cannot be called explicitly, but is automatically activated when a record is inserted into a table ﹑ a record is updated or a record is deleted. So the trigger can be used to implement complex completeness about the table to the ' bundle.

Two:sql server created two dedicated table:inserted tables and deleted tables for each trigger. These two tables.

A: A trigger is a special stored procedure that cannot be called explicitly, but is automatically activated when a record is inserted into a table ﹑ a record is updated or a record is deleted. So the trigger can be used to implement complex completeness about the table to the ' bundle.

Two:sql server created two dedicated table:inserted tables and deleted tables for each trigger. These two tables are maintained by the system, and they exist in memory rather than in the database. The structure of the two tables is always the same as the structure of the table that the trigger acts on. The two tables associated with the trigger are also deleted after the trigger execution is complete.
The deleted table holds all rows that are to be removed from the table because of execution of the DELETE or UPDATE statement.
The inserted table holds all rows to be inserted into the table because of an INSERT or UPDATE statement execution.
Three:instead of and after triggers
SQL Server2000 provides two trigger:instead of and after triggers. The difference between the two triggers is that they are activated in the same way:

Instead of triggers are used to replace T-SQL statements that cause trigger execution. In addition to tables,instead of triggers can also be used for views that extend the update operations that the view can support.

After triggers are executed after a insert,update or deleted statement, and actions such as constraint checking occur before the after trigger is activated. After triggers can only be used for tables.

Each modification action (insert,update and delete) of a table or view can have a instead of trigger, and each modification action of a table can have more than one after trigger.
Four: The execution of the trigger process
If a insert﹑update or DELETE statement violates the constraint, then the after trigger does not execute because the check for the constraint occurs before the after trigger is agitated. So after triggers cannot go beyond constraints.

The Instead of trigger can be executed in place of the action that fired it. It is executed when the inserted table and the deleted table have just been created and no other operations have occurred. Because the instead OF trigger executes before the constraint, it can perform some preprocessing on the constraint.

1. Define the format

CREATE TRIGGER trigger_name

On table

for {INSERT | UPDATE | DELETE}

As

[IF UPDATE (column) [{and | OR} UPDATE (column) ...]

Sql_statement

2. Inserting views and deleting views

Two views that are automatically derived for the trigger to run:

inserted--to store new records just inserted

deleted--storing old records that have just been deleted

3. Trigger classification

Insert class trigger ... insert delete class trigger ... delete update class trigger ... update

4. Trigger Time

after| The for trigger is after the SQL Server server receives a request to execute the SQL statement.

Create a temporary inserted and deleted tables, and then actually change the data before activating the trigger.

Instead of triggers when a SQL Server server receives a request to execute a SQL statement, a temporary inserted table and a deleted table are created.

The instead of trigger is then triggered, as to whether the SQL statement is inserting data, updating data, or deleting data.

The power of execution is delegated to the instead of the trigger, which is done after the operation.

Example: Create a simple trigger.

CREATE TRIGGER Test_trigger

On student for INSERT

As PRINT ' inserts a tuple '

Example: Define a trigger so that when a student record is deleted, all of the student's selection records are deleted at the same time

CREATE TRIGGER Del_trigger

On STUDENT for DELETE

As

DELETE from SC

WHERE SNO = (SELECT SNO from deleted)

Example: The insert operation for a student table defines a trigger that, when the record is inserted, checks whether the appropriate student age satisfies the condition and displays an error message if it does not exist.

Create Trigger Stu_tri

On student instead of insert

As

If (select age from inserted) >40

print ' cannot insert student records older than age '

Else INSERT INTO student select * from inserted

Cursor

1. Data operations that require cursors

When multiple tuples are included in the result of a SELECT statement, cursors can be used to access the tuples individually

Activity set: The collection of tuples returned by the SELECT statement

Current line: The row currently being processed in the active set. The cursor is a pointer to the current row.

2. Cursor classification

Scroll cursor: The position of the cursor can be moved back and forth, and any tuple can be taken in the active set.

Non-scrolling cursors: Only a tuple can be removed sequentially in the active set.

Update cursor: The database locks the current row that the cursor points to, and when the program reads the next line of data, the bank data is unlocked and the next row of data is locked.

3. Define and use a cursor statement

DECLARE:

DECLARE cursor name [scroll] cursor FOR SELECT statement [for UPDATE [of list name]]

Define a cursor so that it corresponds to a SELECT statement

For UPDATE option, which indicates that the cursor can be used to modify and delete the current row

Open

Opens a cursor, executes the query corresponding to the cursor, and the result set is the active set of the cursor

Open cursor Name

Fetch

Move the cursor to a specific row in the active set and drop the row data into the appropriate variable

Fetch [Next | prior | first | last | current | relative n | absolute m] CURSOR name into [variable table]

Close

Closes the cursor, releasing the active set and the resources it occupies. When you need to use the cursor again, execute the Open statement

Close cursor Name

Deallocate

Delete the cursor and no longer execute the open statement on the cursor

DEALLOCATE cursor Name

@ @FETCH_STATUS

Returns the state of the last cursor executed by the FETCH statement.

0 FETCH statement Succeeded

-1 FETCH statement failed

-2 rows that were fetched do not exist

Example: Inquiry E-Commerce Department student Information, gender for female output for female, otherwise output for male?

Declare c1 cursor FOR select Sno,sname,ssex from student where sdept= ' EC '

Declare @sno char (TEN), @sname char (Ten), @ssex char (2)

Open C1

Fetch C1 into @sno, @sname, @ssex

While @ @fetch_status ==0

Begin

If @ssex = ' female '

Begin set @ssex = ' Female ' end

Else

Begin set @ssex = ' Male ' end

Select @sno, @sname, @ssex

Fetch C1 into @sno, @sname, @ssex

End

SQL Server stored procedures, triggers, cursors