Interactive SQL (Transact SQL, TSQL)
It is the query language of SQL Server. Provide the following command:
· Create and database objects.
· Access and modify data.
· Data aggregation (AGGREGATION, also known as aggregation).
· Implement safety measures.
Database object
Database objects are physical objects in the database. These objects have unique names and save data and data relationship information. SQL Server defines the following objects:
1, table (Table)
Table is two-dimensional arrays used to store data, which have rows and columns. Columns are also known as the table properties or fields, each column in the table holds a unique name, each column contains the specific data type, which is defined by the data type in the column.
2, view (view)
The view is a virtual table, which only contains a part of the table. Different from the table, the data saved in the view is not a physical storage data, which is a base table that is derived by the derived table. The definition of the view is stored in the database.
3, constraint conditions (construint)
Constraint conditions define the integrity and effectiveness of the data. The constraints are set up in columns. In other words, if the end condition is defined on a column, each value inserted into this column needs to be checked by constraints. Constraint conditions are preferred for data integrity and effectiveness on triggers and rules. SQL Server Query Optimizer uses constraints to produce low-cost query plan. The constraints have the following types:
· NOT NULL This constraint condition requires that there must be no NULL value in the column.
· Check is specified for the column to specify the collection of values, check the constraints. Any data outside the column is invalid data. Valid values are called columns.
• The primary key master keyword is a column or column combination, which is used to uniquely identify a row.
· Foreign Key is used to define the father and child relationship between two tables. If a keyword is part of a list of primary keywords, it is also the primary keyword of another table, called it as a foreign keyword. Foreign keywords are used to define the reference integrity of the data.
• Unique unique constraints refers to no two rows have the same Non-Null value in columns. Uniqueness is guaranteed by the primary keyword, but they don't allow NULL values, and each table can only have a keyword.
4, default (default)
The default value is the value defined by the column, and if the value is not provided when inserted a row, this column uses the default value, the default value can be one of the following:
· Constant
· Mathematical expression
· Internal function (Built-in function)
5, rules (Rule)
Rules implement and CHECK constraints the same function. However, the rules and constraints are different: the rules exist as separate objects, which can be used in multiple tables, while constraints are stored as part of the table. However, the rule is provided as a backward compatible feature, it is recommended to use the user to use constraints.
6, trigger and stored procedure
The trigger and stored procedure are a TSQL command set that is stored as an object in the database.
Object naming agreement
SQL Server uses three-part name name identifier:
The first two parts can be omitted, and the system has a default value. The default value of the database name is the current database, the default value of the owner name is the database owner (DBO).
type of data
Any object containing data has a data type and it is associated. The data type is an attribute that specifies what data can be included.
SQL Server Data Type
Data types say that the same Bit 1 bit, the value is 0 or 1 int inteer 4 bytes, the value is -2 ^ 31 ~ 2 ^ 31-1 Smallint 2 bytes, the value is -2 ^ 15 ~ 2 ^ 15-1 Tinyint 1 byte, the value is 0 ~ 255 decimal (p, s) digital data, fixed accuracy is p, width is S Numeric Money 8 byte, stored currency type, value is -2 ^ 63 ~ 2 ^ 63-1 Small Money 4 bytes, stored currency type, value of -214748.3648 ~ 214748.3647 Approximate numerical data type float (n) n between 1 ~ 24, 4 bytes, 7-bit accuracy n = 1 ~ 7 is REAL N at 25 ~ 53, 8 bytes, 15-bit precision = 8 ~ 15 is Float DateTime 8 bytes, describe the date and time of a day, the value of the value is 1/300 seconds SmallDateTime 4 bytes, describing the date of a day and At the moment, the accuracy is a reference TimeStamp 8 byte of the cursor, and the unique data uniqueIdentifier 16 bytes in the database is stored, and the unique identifier (GUID) CHAR (N) is a fixed length of the Unicode string, n = 1 ~ 8000 Character (N) VARCHAR (N) variable length, non-Unicode string n = 1 ~ 8000 char varying (n) Text server code page variable length non-Unicode data. The maximum length is 231-1 characters nchar fixed length Unicode string n = 1 ~ 4000 national character (n), national char (n) nvarchar fixed length Unicode string n = 1 ~ 4000 national character varying (n) nText variable The length Unicode data, the maximum length of 230-1 characters national text binary (n) fixed length binary data, N is between 1 to 8000, the storage space is N 4 byte varbinary (n) variable length binary data, N = 1 ~ 8000 binary varying (n) TMAGE variable length binary data, size is 0 ~ 231-1
note:
1) For numerical data types, the width (scale) refers to the number of digits stored after the decimal point, and the precision refers to all numerical digits that can be stored.
2) The storage width of Money and Small Money is 4.
3) Timestamp column value When updating each line, the system is automatically updated, and the timestamp column cannot be part of the keyword or keyword.
4) Unique identification data types cannot use arithmetic operators (such as , -,,, etc.), which can only use equal comparison operations.
5) Unicode is a standard for unanimous storage data of all character sets. It uses two times that of non-Unicode data stored in storage.
Access data
SELECT statement
1. Select all columns in the table
"*" Has special significance to the SELECT statement. It specifies all columns in the table without listed the column name. The order of the column is the same as the order in the table.
2. Select different values
If the column in the selected list has a repetition value, the "DistINCT" keyword can be used to ignore the repetition value. Note: If more than one column in the Select List, the Distinct keyword is generally valid. If a column has a repetition value, the value of other columns is unique, and a column of the repeated value is included in the result.
3. Named the column again in the result data collection
The default names in the results are the column names in the source database, and users can replace the default column name with the column names you specify.
Select AuthorfirstName = au_fname from authors
- Change the column name by "au_fname" to "Authorfirstname"
4. Select the calculated value
The calculated value or constant can be included in the SELECT LIST. The calculated value is calculated based on the arithmetic expression, which can be included in one or more columns in the table.
SELECT TOTALE = price * YTD_SALES from Titles
Conditional selection (WHERE clause)
Use a comparison or logical operation to specify a filter condition in the WHERE clause to generate the rows you want to get in the table.
1. Compare operation
Compare operations can compare values, characters, and date data, return TRUE or FALSE.
Compare operator
<(Less than)> (greater than) = (equal to) <> (not equal)> = (greater than or equal) <= (less than or equal)! = (Not equal)! <(Not less than)!> (Not greater than)
2. Logical operation
Logical Operation Tests whether certain conditions are correct and returns true or false based on test results.
· Like If the operation is the same as the specified string, returns TRUE, and the specified string can also contain wildcards.
LIKE operation with wildcard is more useful.
· "%" Specifies that all strings can replace the characters "%". Any specified string before or after "%" is considered constant. Such as: "new%" means all strings starting with "new", "% new" indicates a string ending with "New".
· "_" Specifies that any single character can be entered in "_". This is very useful in situations where only one character is similar.
· "[]" Specifies that the characters defined in square brackets instead of a character. In square brackets, you may be separate characters (such as [AHG]), but it is a range of characters (such as [C-I]).
· "[^]" Specifies the characters after "^" in square brackets, can be independent characters (such as [AHG]), but also characters range (such as [C-I]).
Note: These wildcards can be used in one expression.
• Between If the operand is within the range provided, it returns True.
BetWeen can be used with the NOT operator. If the data is outside the specified range, Returns True.
Note: The Betten operator is a specified range. For example, BetWeen A and B represents all values between A and B, including A and B.
· Ins If the operands are matched, the IN operator returns TRUE. The specified value can be a list of constant values or can also be generated by additional queries (called subqueries).
The in operator can also be used in combination with the NOT operator. If the data is not in the specified value, it returns true.
· And it combines two expressions, if both expressions are true, then it returns true, otherwise returns false. · OR It combines two expressions, if there is one of the two expressions, it returns true if two are false, then return false.
· NOT It is reversed to logical operations, which can be used in combination with most logical operations, reversible to the value returned.
· Some | Any These two operations have the same format. They compare the operands and the collection of specified values. If the number of operands returns True with the comparison of any specified value, it returns True. The comparison operation can be a comparison operation defined by any SQL Server. The specified value list can be generated by another query.
· All all ALL operations and Some and ANY have similar formats. Different, if all operands and the comparison of the specified value return True, it returns true.
Data sort
The Order By clause is sorted in the specified order. It requires a list of column names or non-negative individual lists to specify the location of the column. The ASC represented by ASC, DESC represents desperation, and defaults to ASC.
Limit the number of returns
The number of rows in the restriction result is not used without using the WHERE clause. "TOP" clause can limit the number of lines as specified or percentage.
Data packet and calculation aggregate functions
The aggregate function calculation tables in the sum of data in the table. SQL Server provides the following aggregate functions:
· AVG This function calculates the average. The syntax is as follows:
AVG ([All | Distinct] Expression)
Keyword DistINCT is only used to calculate the average of different values. If there are many repetition values, these values are only calculated once, and the default is ALL.
The Expression can be an arithmetic expression involving a column or multiple columns.
· MIN this function lookup the minimum in the expression. The syntax is as follows:
Min (Expression)
· The function of this function is to find the maximum value in the provided expression. The syntax is as follows:
Max (Expression)
Note: If the string type uses Min and Max, the output is dependent on the order defined for SQL Server. MIN and MAX cannot be used in the position.
• Sum SUM calculates the sum of all data values. The syntax is as follows:
Sum ([All | Distinct] Expression)
Note: SUM and AVG can only be used for numerical data types.
· Count calculates the number of expression values. The syntax is as follows:
Count ([all | distinct] Expression)
COUNT has another usage that can return the number of rows selected.
Such as: select nuMrows = count (*) from Titles
The aggregate function ignores all null values, except for count (*). Although all aggregate functions are calculated based on airless value, count (*) calculates all rows (including a row with null values).
1. Group By clause
The Group By clause establishes a relatively small group on the basis of the defined data and calculates each group. In other words, it produces overall information for each group. Group By can be more than one column as a grouping column. It summarizes information about non-repetition values in the combination column.
You can only include the following items in the selection list of Group By clauses:
· Constant value.
· Combined column.
· Expression. Each expression returns a value (such as a polymerization function) for each group. If a column is in addition to in the combined column, in the selection list, it has multiple values to the combined columns, which is not allowed.
2. Group By and HavingHaving clauses are used to add data filtering guidelines to the query using the Group By clause. Having's usage is the same as the WHERE clause in SELECT. It is possible to use the WHERE clause in a query that contains the Group By clause. Having and where have the same syntax. There are different from having and where it is:
• In the WHERE clause, before the packet, the row that does not satisfy the condition is removed, in the Having clause, the condition is applied after the packet.
· Having can include the polymerization function in the condition, but where where WHER cannot.
Note: Group By and Having clauses cannot use text or image data types.
3. Compute By clause
The Compute By clause can get detailed or total records. It divides the data into a smaller group and then builds a detailed record result dataset (like Select) for each set, which can also generate a total record for each group (like group BY).
In Comput By, define the BY clause is not necessary. If the BY clause is not defined, it is considered that the entire table is a group, and only two result datasets are generated, one has all a detailed record, and the other is only one line, it has a total record.
Note: When using BY in Compute, you are required to include ORDER BY in all combined columns.
Cube and Rollup operation
Cube and Rollup operations can produce more aggregates than simple group BY. These operations are useful when the Cross Tab Reports is generated. If the query uses n combined columns, 2N calculates a combination of aggregates.
Access data from multiple tables
We have discussed how to access data in a single table. It is also possible to access data from multiple tables. Accessing data from multiple tables is a connection table (Joining a Table).
1, Cross Join (Descartes)
Cross Join is simply, combining tables without any constraints. The number of rows after the result of Cross Join is the product of the first two table rows. If two tables with thousands of rows are connected, the result is unimaginable.
2, Inner Join
Inner Join is the most commonly used method for combining two tables. Inner Join is based on a discriminant, this discrimination is called a connection condition. Connection conditions and WHERE clauses are defined together. The connection condition consists of columns from two tables and compares the values of columns using a comparison condition. By comparing the value included in the result data set, the following is the syntax of Inner Join:
Syntax 1: (ANSI 92)
Select
From
ON
Syntax 2:
Select
From
In the FROM clause, you can define an alias for the table and can be used in any place to replace the real name.
Note: If there is a null value as a column of the connection condition, the null value cannot be matched with any value, so there is no row with a null value in the results.
3, Left Outer Join
In Inner Join, only rows that match in both tables can be concentrated in the result data. However, in Left Oter Join, all rows in the left table appear in the result data set, if a row in the left table does not match the row in the right table, the value in the right table is replaced with the value in the right table with it. The syntax is as follows: (ANSI 92)
Select
From
ON
4, Right Outer Join
Right Out Join and Left Outer Joins are similar, and different is to use the table on the right as an external table (all the rows in all tables are included in the result data set).
The syntax is as follows:
Select
From
ON
5, Full Outer Join
In Full Outer Join, the rows in all two tables are included in the result data set.
The syntax is as follows:
Select
From
ON
CASE statement
When different results values are generated for different conditions, the CASE statement can be used.
The CASE statement calculates all defined conditions and returns the result as true.
The syntax is as follows:
Case [
When
[ELSE
End
Input_expression is any effective SQL Server expression or Boolean expression.
When_expression is any effective SQL Server expression or Boolean expression. This expression and input_expression are compared, if INPUT_EXPRESSION is not defined, when the gen_expression should be a Boolean expression.
Result_expression is any valid SQL Server expression. If the comparison of WHEN_EXPRESSION and INPUT_EXPRESSION Returns true (if INPUT_EXPRESSION) or WHEN_EXPRESSION value is true, the expression is calculated and the result is returned. Otherwise calculate the expression in the ELSE_EXPRESSION and return its result.
E.g:
SELECT AU_FNAME, AU_LNAME,
State = Case State
When 'Ca' Then 'California'
When 'Ks' Ten 'Kansas'
End
From authors
UNION UNION statement combines the results of two or more queries into a result set.
The syntax is as follows:
Select
From
Where
Union [all] select
From
Where
All keyword specifies that the duplicate data will also be included in the final result data set. There are many UNITION statements in a query if needed. All SELECT_LIST should have the same number of columns and are the same or compatible data types.
Go command
The go command is used to mark an end of Query Batch. The query batch is a collection of TSQL statements, which are executed together. Go with OSQL or SQL Server Query Analyzer.
Manage text and image data
Text and Image Data Type Columns can be included in the SELECT_LIST of the SELECT statement like other columns. The maximum text size retrieved by the SELECT statement is controlled by the TextSize setting. The default textsize value is 4096 bytes, and you can reset it using the Set TextSize statement. The current TEXTSIZE setting is stored in the global variable @@ textsize.
Another way to retrieve text and image data is through the TextPtr and ReadText functions. The TextPtr function returns the column name as an input parameter and returns a text pointer in a binary format.
This pointer and the read start pointer called the offset and the number of bytes to be read together are passed to the ReadText function to retrieve text and image data.
change the data
TSQL also provides commands from database insertion, modify, and delete.
insert
The INSERT command is used to insert data in the table, the syntax is as follows:
INSERT [INTO]
Value_list is a corresponding value in column in column_list. These values can be constants, TSQL variables, or internal functions of the SQL Server. The order of the value should correspond to the order in column_list. If you do not define a column_list, the order of the value should correspond to the order in the list.
INSERT's VALUES_LIST can also be provided through a SELECT statement or a stored procedure. The syntax is as follows:
INSERT [INTO]
SELECT
INSERT [INTO]
Execute
The SELECT statement here can contain multiple connections. If the stored procedure is used, the stored procedure should return the result that can be used as a column_list.
The timestamp (TimeStamp, also known as the time standard) value is not available. At the same time, if the column Identity property is defined, the value of this column cannot be provided. These values are generated by the system. If the default value is column, their value is not provided, and the default value is used.
modify
The UPDATE statement is used to modify the line, the syntax is as follows:
Update
Where
SERACH_CONDITION is the condition to be met to modify. The UPDATE statement without WHERE clause will modify all rows in the table. Connections can also be used in the UPDATE statement.
delete
The DELETE statement is used to remove rows from the table. The syntax is as follows:
Delete [from]
[Where
SERACH_CONDITION is the condition that deletes a row to meet. An unconditional delete statement will delete all rows in the table. Connections can also be used in the Delete statement. Develop applications using SQL Server
Write a stored procedure
The stored procedures and triggers are created by the user, a set of Transact SQL query statements that reside in the server. The trigger is performed under specific conditions. The stored procedure can improve the performance of the application. When the client needs to access the data, it is usually five steps to access the data:
1) The query statement is sent to the server.
2) Server compile SQL code.
3) Optimize the production plan of the inquiry.
4) Data Engine execution query.
5) The result is sent back to the client.
The stored procedure is compiled when creating, when the stored procedure is executed, SQL Server generates a query execution plan and stores it in order to use it in future use. When a request is issued by a stored procedure, the above-described second and third steps are not, which greatly improves the performance of the system. Even performance can be improved even in step 1. Because the statement sent to the server is only a stored proTe statement, not a huge, complex query. This feature can reduce the traffic of the network.
In addition to performance improvements, the stored procedure provides convenience to centrally maintaining the functions of the application. If you embed the query into your application. It is also necessary to change the query, the application needs to recompile and re-released all clients. In the stored procedure, modification is transparent to the user, it only needs to recompile the stored procedure on the server.
The stored procedure can also provide a security mechanism, although the user may not have commands in the stored procedure, but it may have the right to perform the stored procedure itself. Sometimes, the system administrator will not give the user to the power of UPDATE, INSERT and DELETE. The created stored procedure can do this. Of course, the user needs to have the power to perform the stored procedure.
Create a stored procedure
The stored procedure can reach the following:
· Belt parameters.
• Return the status value.
· Call other stored procedures.
• Perform it on the remote server.
The stored procedure has a entry in the "SysObjects" system table, which is "P". The text of the stored procedure is stored in the "SysComments" system table. Creating a stored procedure You need to use the Transact SQL command CREATE Procedure.
E.g:
USE PUBS
Go
Create Procedure AP_GetAuthorsforpublisher
AS
SELECT A.AU_LNAME, A.AU_FNAME
From Authors A, Titleauthor TA, Titles T, Publishers P
WHERE A.AU_ID = TA.AU_ID
And ta.title_id = t.title_id
And T.PUB_ID = P.PUB_ID
And puB_name = 'new moon books'
Go
The syntax of the CREATE Procedure statement is as follows:
Create Proc [Dure] Procedure_name [; Number]
[@Parameter_name [output] [, _n]]
[With {recompile | Encryption}]
[For replication]
AS
Number is an integer used to group the process of the same name. The group is to combine all the processes into one group through the Drop Procedure statement.
@Parameter_name Specifies the name of the parameter.
Recompile indicates compilation of each execution process.
Encryption The text of the process is encrypted in the "SysComments" table.
The for Replication indicates that the process cannot be executed on the submission server.
Pass the parameters to the stored procedure
The stored procedure can accept parameters.
Note: The parameters of the process can also be a user-defined data type. Give parameters a default value
The user can also define the default value for the parameters during the stored procedure. When the required parameters are not provided, the system uses the default value as the parameters. If there is no default value, it is not provided when it is executed, and SQL Server will return an error. Define the default value during the stored procedure and use some logical detection whether the parameters are specified to take the corresponding action, which is a good habit.
E.g:
USE PUBS
Go
Create Procedure AP_GetAuthorsforpublisher
@Publishername varchar (40) = 'New Moon Books'
AS
SELECT A.AU_LNAME, A.AU_FNAME
From Authors A, Titleauthor TA, Titles T, Publishers P
WHERE A.AU_ID = TA.AU_ID
And ta.title_id = t.title_id
And T.PUB_ID = P.PUB_ID
And puB_name = @publishername
Go
Establish storage procedure
The stored procedure logic resides in the stored procedure body. An arbitrary TRANSACT SQL statement can be included in a storage procedure body. The following Transact SQL statement cannot appear in any storage procedure body:
· Create Default
· Create Trigger
CREATE Procedure
CREATE VIEW
· CREATE RULE
1, local variable
The local variable remains the intermediate value of the stored procedure. When a value requires multiple times during the stored procedure, or the result of a query needs to use local variables when used in subsequent queries. In these cases, the value is stored in a local variable and can be used in future use. The name of the local variable begins with the "@" symbol. The name of the variable can contain characters and values. The local variable needs to be declared before use. Assign a partial variable to use the SELECT statement. SELECT can retrieve values from a table and assign it to a variable or assign a constant value to the variable. A simple SELECT statement can assign a plurality of local variables.
E.g:
Declare @ var1 integer, @ var2 varchar (20)
SELECT @ var1 = 32,
@ var2 = 'myage'
If any data is not returned from the SELECT query, the Select also assigns local variables to the value of the data, the value of the local variable will not change.
2, conditional words
The conditional statements provided during the stored procedure include:
· If ... ELSE statement.
· While statement.
1) IF ... ELSE statement. In this statement, three parts: Boolean operations, IF state block, and ELSE block. The syntax is as follows:
IF (Boolen_EXPR)
{Statements}
Else
{Statements}
There are multiple statements in the IF or ELSE statement block. In this case, statements Begin and End are required to mark the symbol.
2) While statement. The While statement is used to process the statement that repeatedly executed before TRUE. The syntax is as follows:
While (Boolen_EXPR)
Begin
Statement (s)
Break
Statement (s)
Continue
End
Begin and end statement logo cyclic body. The Break statement ends the execution of the loop (ie after walking to the END statement). The Continue statement returns the control process back to the beginning of the loop (ie the right side of the Begin statement).
Note: If there are two or more While cycles being nest, the internal BREAK exits the sub-outer loop. All statements after the end of the internal cycle will continue to be executed after the internal loop is executed. 3, goto statement
In the execution of the stored procedure, the statement is in order. The goto statement is used to break the order in which this statement is executed, and it jumps to a statement, and this statement often does not follow the previous statement. The GOTO statement is used with a flag (Label), which is used to identify a statement.
E.g:
USE PUBS
Go
Declare @num int
SELECT
IF @num = 0
Goto ERR
Else
Begin
Print 'Authors Found'
Select * from authors
Goto Last
End
Err: Print 'No Authors Found'
Last: Print 'Finish Execution'
Go
4, returnite
Return statements are used for unconditional exit stored procedures. Any statement after return is no longer executed. The RETURN statement can return a value to the calling statement, but cannot return NULL values. SQL Server often returns a status value for the stored procedure. If it is successfully performed, one 0 is returned, and if an error occurs, it returns an error code for negative numbers.
The error code returned by the stored procedure
Value Ming Value The 0 Process Performs Success-8 A Non-Derminal Problem-1 Object-9 Did the System Limit-2 A Data Type Error-10 A fatal internal inconsistent error -3 Processing Selected Femrill -11, a deadlock, a fatal internal inconsistency error - 4 A permissions error -12 table or index is destroyed -5 A syntax error -13 database is destroyed - 6 a mixed user Error -14 Hardware error -7 resource errors, if space is not enough, etc.
5, use the cursor (CURSOR)
The cursor is very useful when you need a row. The cursor can open a result collection (line according to the specified standard selection) and provide a function of one line in the result set. Based on the type of cursor, it can be rolled or advanced. 5 steps when using cursors:
1) Declaration of the cursor
In this step, you need to specify the properties of the cursor and the result set generated according to the requirements. There are two ways to specify a cursor.
Form 1 (ANSI 92)
Declare cursor_name [INSENSITIVE] [Scroll] Cursor
For select_statement
[For {read only | update [of coluMn_list]}]
Form 2
Declare Cursor_name Cursor
[Local | Global]
[Forward_only | Scroll]
[Static | keyset | Dynamic]
[Read_only | scroll_locks | Optimistic]
For select_statement
[For {read only | update [of coluMn_list]}]
InSensitive Keywords indicate a temporary copy to establish a temporary copy to retrieve the result set, which is obtained from this temporary copy. If the data in the original base table changes in the original base table, they are invisible for the cursor. This insensitive cursor does not allow data changes.
The scroll keyword indicates that the target can be scrolled in any direction. All fetch options (first, last, next, relative, absolute) can be used in the cursor. If this option is ignored, the cursor can only scroll forward (Next). SELECT_STATEMENT indicates the result set of SQL statements. Transact SQL Statements Compute, Compute By, for Browse and INTO are not allowed in the selection statement of the cursor statement.
Read Only indicates that data modifications are not allowed in the cursor result set.
Update keyword indicates that the result set of the target can be modified.
Of column_list indicates a column that can be modified in the result set. By default (using the Update key), all columns can be modified.
The Local keyword indicates that the target is part, it can only be used in the process it declares.
The Global keyword makes the cursor for the entire connection global visible. The global cursor is available at any time of connection activation. The cursor is no longer available when the connection ends.
Forward_only indicates that the cursor can only scroll forward.
Static's cursor is the same as the Insensitive cursor.
KeySet indicates the order of the selected rows. SQL Server will create a temporary key set from the result set. If you modify the non-key column of the database, they are visible to the cursor. Because it is a fixed keyword collection, modifying or new insert columns is invisible for keyword columns.
Dynamic indicates that the cursor will reflect all modifications to the result set.
Scroll_lock is to ensure the success of the cursor operation, and lock the modified or delete.
Optimistic indicates which changes or deletions that pass cursors will not succeed.
note:
• If the Distinct, Union, Group By statement is used in the Select statement, and the sputum is automatically cursor for the Insensitive expression in the Select statement.
· If the base table does not have a unique index, the cursor creates a cursor for INSENSITIVE.
• If the SELECT statement contains ORDER BY, the Dynamic cursor will be converted into a KeySet cursor by the column of Order By. If the KeySet cursor cannot be opened, it will be converted to an INSENSITIVE cursor. The same is true for the cursor defined using the SQL ANSI-92 syntax, just there is no INSENSITIVE keyword.
2) Open the cursor
Opening the cursor is to create a result set. The cursor is defined by the DECLARE statement, but its actual execution is through the Open statement. The syntax is as follows:
Open {{[global] cursor_name} | CURSOR_VARIABLE_NAME}
Global specifies a global cursor.
Cursor_name is the name of the open cursor.
Cursor_variable_name is the variable name of the referenced cursor. This variable should be a cursor type.
After the cursor is opened, the system variable @@ cursor_rows can be used to detect the number of rows of the result set. When @@ cursor_row is a negative number, it means that the cursor is migrating asynchronous, its absolute value (if @@ Cursor_ROWS is -5, the absolute value is 5) is the number of rows of the current result set. Asynchronous cursors are still able to access the results of the cursor when the cursor is fully migrated.
3) Take the value from the cursor
In the process of values from the cursor, move and process can be moved and processed on each line of the result set. If the cursor is defined as scrolling (using the scroll key when declaring), you can take any of the results in the result set. For non-rolling cursors, only the next line of the current line can be taken. The result set can be taken in a local variable. The syntax of the fetch command is as follows:
Fetch [Next | PRIOR | First | Last | Absolute {n | @nvar} | relative {n | @nvar}] from [global] cursor_name} | CURSOR_VARIABLE_NAME}
[INTO @variable_name "[, ..... n]]]
Next specifies the value from the next line of the current row.
PRIOR indicates the value from the current line of the current line.
First is the first line of the result set.
Last is the last line of the result set.
Absolute N represents the Nth line in the result set, which can also be propagated by a local variable. The line number starts from 0, so N is 0 when it cannot be obtained.
Relative N indicates the line to be taken on the position of the previous N row or the rear N row of the current line. If the value is positive, the row to be taken is in the position of the N r line before the current row, and if the value is negative, return the rear N row of the current row.
INTO @cursor_variable_name indicates a list of variables in the place where the cursor is stored. The number of variables in this list should be the same as the number of variables used in the DECLARE statement. The data type of the variable should also be the same as the data type selected by the selected column. The value in the variable will remain until the next time use the Fetch statement.
Each time FETCH is executed in system variable @@ fetch_status. If FETCH is successful, @@ fetch_status is set to 0. @@ fetch_status is -1 indicates a part of the result set (for example, after the cursor is opened, the row in the base table is deleted). @@ fetch_status can be used to construct a loop of the cursor processing.
E.g:
Declare @iname char (20), @fname char (20)
Open author_cur
Fetch first from author_cur @iname, @fname
While @@ fetch_status = 0
Begin
IF @fname = 'albert'
Print "Found Albert Ringer"
Else
Print "Other Ringer"
Fetch next from author_cur @iname, @fname
End
4) Close the cursor
The Close statement is used to close the cursor and release the result set. After the cursor is closed, FETCH operation cannot be performed again. If you need to use the FETCH statement, you will re-open the cursor. The syntax is as follows:
Close [Global] CURSOR_NAME | CURSOR_VARIABLE_NAME
5) Release the cursor
After the cursor uses no longer needs, it will release the cursor. DEAALLOCATE statement releases the lock of the data structure and the cursor. The syntax is as follows:
Deallocate [global] cursor_name | CURSOR_VARIABLE_NAME
A complete example of the cursor is given below:
Use master
Go
Create Procedure SP_BUILDINDEXES
AS
Declare @tablename sysname, @msg varchar (100), @cmd varchar (100)
DECLARE TABLE_CUR CURSOR for
Select Name from sysobjects where type = 'u'
Open Table_Cur
Fetch next from table_cur @tablename
While @@ fetch_status = 0
Begin
IF @@ fetch_status = -2
Continue
SELECT @msg = "building indexes for table" @ Tablename "..."
Print @MSG
SELECT @cmd = "DBCC DBREINDEX ('" @ TableName ")"
EXEC (@cmd)
PRINT ""
Fetch next from table_cur @tablename
End
Deallocate Table_Cur
Go
The following script will perform sp_buildindexes for the PUBS database
USE PUBS
Go
Exec AP_BUILDINDEXES
Note: The above is also an example of creating a user-defined system stored procedure.
Use temporary table
The temporary table is a table created in the TEMPDB. The name of the temporary table is starting with "#". The scope of the temporary table is to create a temporary table. Because the temporary table cannot be shared between two connections, once the connection is closed, the temporary table will be discarded. If the temporary table is created in the stored procedure, the range of the temporary table is in the stored procedure, or in any stored procedure called by the stored procedure. If you need to share a temporary table between connections, you need to use a global temporary table. The global temporary table begins with the "##" symbol, which will always exist in the database until the SQL Server restarts. Once this type of temporary table is created, all users can access. Permissions cannot be clearly indicated in the temporary table.
The temporary table provides the ability to store intermediate results. Sometimes, the temporary table can also achieve performance improvement by breaking a complex query into two queries. This can be achieved by first existing the result of the first query, and then uses a temporary table in the second query. A temporary table is recommended when a subset in a large table is used in a seat process. In this case, the subset of data is maintained in the temporary table to use in subsequent connections, which can greatly improve performance. You can also create an index in a temporary table.
Return the result during storage
There are three ways to return from the stored procedure:
1, return result set
This is the most versatile method for the returning result of the client application. The result set is generated by using the SELECT statement. Result Sets can be generated from permanent tables, temporary tables, or local variables. Returning the result to another stored procedure is not an effective way. The stored procedure cannot access the result set of another stored procedure.
For example, return the result set from the permanent table:
USE PUBS
Go
Create Procedure AP_CREATERESULTFROMPERMTABLE
AS
Select au_iname from authors
Go
For example, create result sets from local variables:
USE PUBS
Go
Create Procedure AP_CREATERESULTFROMVARIABLE
AS
Declare @au_iname char (20)
SELECT @AU_INAME = au_iname from authors
WHERE AU_ID = '172-32-1176'
SELECT @AU_ID
Go
2, set the value of the Output parameter
Output parameters are often used to retrieve results from stored procedures. If a parameter is defined as OUTPUT when transferring to the stored procedure, any modification of the parameter is still valid after exiting the storage.
E.g:
USE PUBS
Go
Create Procedure AP_SETOTOUTPUTVAR @count inTeger Output
AS
SELECT @count = count (*) from authors
Go
Retrieve the value from the output parameters:
USE PUBS
Go
Create Procedure AP_GetOutputVaras
Declare @num integer
Execute ap_setoutputvar @num output
Print "The Count Is" Convert (Char, @ Num)
Go
· Use the cursor into an OUTPUT parameter. Cursors can use Output parameters, but cannot be used as input parameters. That is, the cursor can be returned as a result, but it cannot be transferred to the process. When the cursor is used as a parameter, it needs to be limited to OUTPUT and VARYING. Varying Keywords indicate that the result set is to support the output parameters. This provides the ability to return the result set to the calling process.
E.g:
USE PUBS
Go
Create Procedure Gettitlecount @count_cursor Cursor Varying Output
AS
Set @count_cursor = cursor
For
SELECT AU_ID, COUNT (*)
From titleauthood
GROUP BY AU_ID
Open @count_cursor
Go
3, return status by return parameter
This is a method of returning an error code from the stored procedure. The stored procedure always returns a status value, and the user can also use the RETURN statement to return its own state.
E.g:
USE PUBS
Go
Create Procedure AP_SetReturNStatus
AS
Declare @count integer
SELECT @count = count (*) from authors
IF @count = 0
Return (1)
Else
Return (0)
Go
For example, retrieve the return status:
USE PUBS
Go
Create Procedure AP_GETRETURNSTATUS
AS
Declare @status integer
Execute @status = AP_SETRETURNSTATUS
IF @status = 1
Print "No Rows Found"
Else
Print "Successful"
Go
Error processing during storage
As with other programs, it is very important to perform error processing during storage. System Change @@ error will get a value after performing each Transact SQL statement. For successful execution, the value of @@ Error is 0, and if an error occurs, the @@ Error will contain an error message. The @@ ERROR system variable is very important to the error handling of the stored procedure.
Note: In order to prevent errors, @@ error can set it in the "Error" of the SysMessages table.
There are two types of errors during storage procedures:
1. Database related errors
These errors are caused by inconsistencies of the database, and the system uses non-0 @@ ERROR values to represent specific database issues. After Transact SQL is executed, you can get the error that appears by @@ Error. If @@ error is not 0, the necessary actions must be taken, in most cases, storage will no longer continue processing. The following example shows a typical way to get database errors. This process places the error code into the output variable so that the call program can be accessed.
USE PUBS
Go
Create Procedure AP_TRAPDATABASEERROR @return_code integer Outputas
Update authors set au_iname = "jackson"
WHERE AU_INAME = "Smith"
IF @@ Error <> 0
Begin
SELECT @Return_code = @@ Error
Return
End
Else
@Return_code = 0
Go
2, business logic errors
These errors are caused by violating business rules. To get these errors, first need to define business rules, based on these rules, need to increase the necessary error detection code during the stored procedure. People often use the raiserror statement to notify these errors. RaisError provides the ability to return user-defined errors and set the @@ ERROR variable to a user-defined error number. The error message can be dynamically established, or the error number is retrieved from the "SysMessages" table. Once an error occurs, the error returns to the client in a server error message. Below is the syntax of the raiserror command:
Raiserror (msg_id | msg_str, severity, state
[, argument [, ... n]])
[With options]
MSG_ID indicates the ID of the user-defined message, which is stored in the "SysMessages" system table.
MSG_STR is used to dynamically create a message string. This is very similar to "Printf" in the C language.
Severity defines the severity of the error message of the user assignment.
State is any integer value from 1 to 127, which represents the error called status information. The number of STATE values will be default.
Options specifies the wrong custom options. The valid values of Options are as follows:
1) log.
Record the error to the server error log and the NT event log. This option requires a message with severity from 19 to 25. And only system administrators can issue this message.
NOWAIT.
Immediately send the message to the client server.
3) SETERROR.
Regardless of its severity level, set the value of @@ Error to MSG_ID or 5000.
Remote process call
SQL Server provides the ability to call the stored procedures residing on different servers. Calling such a stored procedure called a remote stored procedure call. To make the call to transfer from one SQL Server to another, two servers should define a valid remote server to each other.
Set the configuration of the remote server:
· Extend a group of a server.
· Right-click on the server and click "Properties".
· Setting option "Allow Other SQL Servers to Connect Remotely to this SQL Server Via RPC".
• Set the value of the "Query Time Out" option, which specifies that the number of seconds that can be returned from one query process. The default is 0, indicating that an unlimited wait time is allowed.
· After setting the configuration option, click "OK".
· After restarting the server, the modification will take effect.
• Repeat the same step on another remote server.
Calling the remote stored procedure The name of the server needs to indicate the name of the server, the name of the database and the name of the owner. Here is an example of calling a stored procedure on a different server (Server2).
EXEC Server2.pubs.dbo.myproc
Laterness:
Here is just a shallow introduction of SQL Server commonly used knowledge, and objects are also programmers based on SQL Server databases, rather than database managers. But for the application programmer, it is also very useful to understand the management of the database. It is recommended to understand the database management in the future time, which is also quite useful for optimization procedures. -------- full text ---------
