Stored Procedure Learning notes (SQL database

First, Introduction to Stored Procedures

SQL Server's stored procedure is a named collection of transacation-sql statements stored on the server, a way to encapsulate repetitive work that supports user-declared variables, conditional executions, and other powerful programming features.

Stored procedures have the following advantages over other database access methods:

(1) Re-use. Stored procedures can be reused to reduce the workload of database developers.

(2) Improve performance. The stored procedure is compiled when it is created, and is not recompiled when it is used in the future. A typical SQL statement needs to be compiled once per execution, so using stored procedures improves efficiency.

(3) Reduce network traffic. The stored procedure is located on the server, which only needs to pass the name and parameters of the stored procedure, thus reducing the amount of data transmitted over the network.

(4) security. Parameterized stored procedures can prevent SQL injection attacks, and grant, deny, and revoke permissions can be applied to stored procedures.

Stored procedures are divided into three categories: User-defined stored procedures, extended stored procedures, and system stored procedures.

Among them, user-defined stored procedures are divided into Transaction-sql and CLR two types.

A transaction-sql stored procedure is a collection of saved Transaction-sql statements that can accept and return user-supplied parameters.

A CLR stored procedure refers to a reference to a. Net Framework common Language Runtime (CLR) method that can accept and return user-supplied parameters. They are implemented as public static methods of the class in the. Net Framework assembly. (This article will not be introduced.)

Second, creation of stored procedures

For example:

--delete the stored procedure if it exists

IF object_id (' proc_getwellproduct ') is not NULL

DROP PROCEDURE proc_getwellproduct

GO

--Create a stored procedure

CREATE PROCEDURE proc_getwellproduct

As

......

Third, Notes

1 、--single-line comment, from this to the end of the bank as a comment, similar to c++,c# in//

2,/* ... */Multiline comment, similar to/* in c++,c# ... */

Four, variables

Variable type:

(int, smallint, tinyint, decimal,float,real, Money, smallmoney, text, image, char, varchar ...) ）

2. Syntax:

DECLARE + "variable name" + "type"

For example:

DECLARE @ID INT--Declares a variable named @ID, type int

Five, assigning values to variables

For example:

--Remove the ID of the first row of data from the data table, assign to the variable @id, and print it out

Declare @ID int

Set @ID = (select Top (1) CategoryID from categories)

Print @ID

NOTE: If an SQL query statement is assigned, the entire query statement is enclosed in parentheses.

Six, Print

Print the value of a variable in the SQL Server window

Grammar:

PRINT ' any ASCII text ' | @local_variable | @ @FUNCTION | string_expr

Seven, comparison Operators

? > (greater than).

? < (less than).

? = (equals).

? <= (less than or equal to).

? >= (greater than or equal to).

? = (Not equal to).

? <> (not equal to).

?!< (not less than).

?!> (not greater than).

Eight, statement block

Form: Begin ... end

Add multiple statements as a block, similar to the {} in c++,c#

For example:

IF (...)

Begin

......

End

Nine, while (@ @fetch_status = 0) Loop

DECLARE @strLoginID VARCHAR (16)

BEGIN

Declare DB Cursor for

SELECT LoginID from Dbo.s_users WHERE len (unitcoding) in (9,12)

END

Open db

FETCH NEXT from DB into @strLoginID

While @ @fetch_status = 0

BEGIN

INSERT INTO S_p_user

Select @strLoginID, levelid from s_p_user where LoginID = ' AA '

FETCH NEXT from DB into @strLoginID

END

Close db

DEALLOCATE db

@ @fetch_status = 0? How to understand? This is the information I found from the online help.

Return value Description

0:fetch statement succeeded.

The -1:fetch statement failed or the row is not in the result set.

-2: The fetched row does not exist.

10. execute other stored procedure EXEC

EXEC stored Procedure name parameter 1, parameter 2 ...

For example

EXEC proc_getwellproduct 1, ' 2011-07-01 '

11. Cursors

1 , Introduction to Cursors:

A cursor is a way of working with data, and in order to view or manipulate the data in the result set, the cursor provides the ability to move forward or backward through the data in the result set one time, either in rows or in multiple rows. We can use a cursor as a pointer, which can specify any location in the result, and then allow the user to manipulate the data at the specified location.

2 , the composition of the cursor:

1), the cursor contains two parts: one is the cursor result set, and one is the cursor position.

2), cursor result set: Defines the collection of rows returned by the SELECT statement for the cursor.

Cursor Position: The current pointer to a row of this result set.

3 , the classification of cursors:

There are 3 types of cursors: API server cursors, TRANSACTION-SQL cursors, and API client cursors. The first two cursors are run on the server, so they are also called server cursors.

API server Cursors

API server cursors are primarily applied to the service, and the server processes the API functions when the client's application invokes the API cursor function. The following functions can be implemented using API functions and methods:

(1) Open a connection.

(2) Set the attributes or attributes that define the cursor feature, and the API automatically alludes the cursor to each result set.

(3) executes one or more transaction-sql statements.

(4) Use an API function or method to extract rows from the result set.

API server cursors contain the following four types: static, dynamic, forward-only, keyset-driven (Primary key)

The complete result set of a static cursor is stored in the staging table when the cursor is opened, (static cursors are always read-only). Static cursors have the following characteristics: Always display the result set as it is when the cursor is opened, do not reflect any modifications made in the database, and do not reflect changes made to the column values of the result set rows; the rows that are newly inserted in the database after the cursor is opened; the rows that make up the result set are updated by other users But a static cursor displays the rows that were deleted from the database after the cursor was opened.

Dynamic cursors In contrast to static cursors, dynamic cursors reflect all changes in the result set when the cursor is scrolled. The row data values, order, and members of the result set will change each time the member is fetched.

A forward-only cursor does not support scrolling, it only supports fetching rows of data sequentially from beginning to end. Note: A forward-only cursor also reflects all changes made to the result set.

Keyset-driven cursors feature both static and dynamic cursors. When a cursor is opened, the members of the cursor and the order of the rows are fixed, and the keyset is stored on the temporary worksheet when the cursor is opened, and changes to the data values of the non-keyset columns are visible when the user cursor scrolls, and the rows inserted in the database after the cursor is opened are not visible unless the reopen cursor is closed.

Transaction-sql Cursors

the cursor is based on the DECLARE CURSOR syntax and is used primarily in transaction-sql scripts, stored procedures, and triggers. The Transaction-sql cursor processes the TRANSACTION-SQL statement sent by the client to the server on the server.

The process of using a TRANSACTION-SQL cursor in a stored procedure or trigger is:

(1) declares that the Transaction-sql variable contains the data returned by the cursor. Declare a variable for each result set column. Declare a variable that is large enough to hold the value returned by the column and declare the type of the variable to be the data type that can be implicitly converted from the data type.

(2) Use the DECLARE CURSOR statement to associate the TRANSACTION-SQL cursor with the SELECT statement. You can also use the DECLARE cursor to define features such as read-only, forward-only, etc. of the cursor.

(3) Use the Open statement to execute the SELECT statement to populate the cursor.

(4) Use the FETCH into statement to extract a single row and move the data in each column to the specified variable. Note: Other Transaction-sql statements can refer to those variables to access the extracted data values. Transaction-sql cursors do not support extracting row blocks.

(5) Use the Close statement to end the use of the cursor. Note: After you close the cursor, the cursor is still present and can be opened using the Open command, and only the call to the DEALLOCATE statement will be released completely.

Client Cursors

The cursor caches the entire result set on the client using the default result set, and all cursor operations are performed in the client's cache. Note: client-side cursors only support forward-only and static cursors. No other cursors are supported.

4 , the life cycle of the cursor

The life cycle of a cursor consists of five stages: declaring a cursor, opening a cursor, reading cursor data, closing a cursor, releasing a cursor.

[1] To declare a cursor:

is to specify the SELECT statement that is used when fetching data for the cursor, declaring that the cursor does not retrieve any data, it simply indicates the corresponding SELECT statement for the cursor.

Declare cursor name cursors parameter

To declare the parameters of a cursor:

(1) Local and global:local indicate that the cursor is scoped to the stored procedure, trigger, and batch in which it resides, and the cursor is automatically released after the execution is completed. Global indicates that the cursor scope is the entire session layer. Any stored procedure, batch, and so on that is executed by the connection can reference the cursor name and is implicitly deallocated only when the connection is disconnected.

(2) Forward_only and scroll: The former is indicated as a forward-only cursor, the latter is expressed as can be arbitrarily positioned. The default is the former.

(3) Static, keyset, and dynamic: The first expression defines a cursor whose data is stored in a temporary table, and all requests to the cursor are answered from the temporary table, so that the data returned when the cursor is fetched does not reflect the modifications made to the base table, and the cursor does not allow modification. Keyset indicates that when a cursor is opened, the identity and order of the keyset-driven row are fixed and placed in a temporary table. Dynamic cursors represent changes to all the data in the result set when a scroll cursor is represented by the.

(4) Read_Only, Scroll_locks and optimistic: The first one represents a read-only cursor, the second represents the placement of locks on the data used for the cursor result set, and when rows are read into the cursor and then modified, the database locks the rows to ensure data consistency. The implication of optimistic is that after the cursor has read the data, if the data is updated, the update and delete operations through cursor positioning will not succeed.

Standard cursors:

Declare MyCursor Cursor
For Select * from Master_goods

Read-only Cursors

Declare Mycusror Cursor

For Select * from Master_goods

For Read only

Updatable cursors

Declare Mycusror Cursor

For Select * from Master_goods

For UpDate

[2] To open a cursor:

Using the Open statement for opening a TRANSACTION-SQL server cursor, the process of executing the Open statement is to populate the data with the SELECT statement, and the cursor position on the first row after the cursor is opened.

To open a cursor:

Global cursor: OPEN global mycursor Local cursor: Open mycursor

[3] To read cursor data:

After the cursor is opened, a specific row is retrieved from the TRANSACTION-SQL server cursor using a FETCH statement. With a fetch operation, you can move the cursor to the next record and assign the data for each column returned by the cursor to the declared local variable, respectively.

Fetch [Next | Prior | First | Last | Absolute N | Relative N] from MyCursor

Into @GoodsID, @GoodsName

which

Next indicates that the next row of the current row in the result set is returned, and if the first read returns the first row. The default read option is next

Prior indicates that the previous row of the current row in the result set is returned, if the first read has no rows returned, and the cursor is placed before the first row.

First means that you return the top row in the result set and use it as the current row.

Last indicates that the final row in the result set is returned and is used as the current row.

Absolute N if n is a positive number, the nth row starting from the cursor header is returned, and the return row becomes the new current row. If n is negative, the nth row starting at the end of the cursor is returned, and the new current row is returned, and if n is 0, the current row is returned.

Relative N if n is a positive number, returns the nth row starting at the current line, if n is negative, returns the nth row from the current row, or 0, returns the current row.

[4] To close a cursor:

Call the close statement in the following manner: Close Global MyCursor Close MyCursor

[5] To release a cursor:

The DEALLOCATE statement is called with the following method: Deallocate glboal mycursor deallocate mycursor

12. Experience Summary

1, the stored procedure does not define the array. If it is SQL Server, then you can use table variables, cursors to implement your function.

2, the top function if there are parameters, the parameters should be enclosed in parentheses, otherwise it will error.

For example:

Select Top (@i) wellid from T_well where PlatformID [e-mail protected] ORDER by wellid ASC

3, determine whether a query value is empty (there is a record, but the value is "NULL")

For example:

--Declare a variable

Declare @WellProduct int

--Assigning a value to a variable (@WellID to an assigned parameter)

Set @WellProduct = (select Wellproduct from T_wellrecord where [email protected])

--Judgment

IF (@WellProduct is not NULL)

......

4. Determine if the record of the query is empty (this record does not exist)

For example:

--Judgment (@WellID as an assigned parameter)

If exists (select Wellid from T_well where [email protected])

Stored Procedure Learning notes (SQL database