Teaching Ideas SQL entry exercise student achievement 7. Basic Stored Procedure knowledge

If we have learned ADO. Net or actually used an application that interacts with the databaseProgramIt is very easy to understand the knowledge point we teach today-stored procedures in SQL. stored procedures are the most common way to process database data during actual project development, this is the most basic database operation and must be mastered. Since it is so important, we will start to learn about the stored procedure and its benefits and essence, after learning the syntax and examples, let's make a discovery summary, so that we can better understand the advantages and convenience of the stored procedure.

First, let's learnDefinitionFor the simplest Stored Procedure syntax, the statements in "[]" can be omitted:

Create proc or procedure < Stored Procedure name >
[Parameter name, parameter type,…] -- Define parameters
As
< SQL-code > -- The SQL statements stored in this stored procedure and the SQL commands to be completed

From the definition of the stored procedure syntax, we can find that the syntax "create database object type object name" is also used here. The stored procedure is represented by the proc or procedure keyword in the database object type, the as keyword is the SQL statement to be executed in the actual stored procedure. Let's look at a specific simple example, the database used in this lecture is still the "Score Management 0902" database used in the first six lectures:

1 Use Score Management 0902
2 Go
3 Create proc proc_selstudent
4 As
5 Select * From student

Let's analyze the above stored procedure. In the first row, use Score Management 0902 specifies to create a stored procedure in "Score Management 0902" of the database; the Go keyword is added because most non-Table create statements cannot match any otherCodeShared batch processing, and the stored procedure should be the first execution statement of the batch processing; the third row create proc proc_selstudent means to create a stored procedure named "proc_selstudent, from the naming method of the stored procedure, we can clearly see that the prefix proc indicates the stored procedure. selstudent indicates that the Stored Procedure stores the statement for querying the result set of the student table, the SQL statement is written after the as statement to query all the records in the student table. For now, there is only one query statement in this stored procedure, in fact, a stored procedure can contain multiple t_ SQL statements to complete a business logic you want to complete.

After writing this stored procedure, we select the statement and press F5 to compile the stored procedure, prompting "the command has been completed successfully ." Your stored procedure will be stored in the "Score Management 0902" database-programmable-stored procedure. If we want to query all student information, in addition to executing the "select * from student" statement, you can also use the "proc_selstudent" stored procedure we just defined to achieve this effect,Execute the Stored ProcedureThe method isExec or execute Proc_selstudentThe results are consistent with those of the traditional select * from student. From this point, we can preliminarily summarize the definition of the stored procedure.

Stored procedures are a set of reusable T-SQL statement modules to complete specific business functions, which are compiled and stored in the database. Next, let's take a look at what can be reused. We believe that if I want to complete a business, it should be composed of multiple SQL statements, at the same time, it is possible to add parameters, throttling, or return values to these statements to complete the results. If these complex statements are used each time, we need to write them again in the traditional method, if we encapsulate these complex statements in a stored procedure, each time we want to execute this effect, we only need to use exec and the name of the stored procedure to execute the stored procedure, it is convenient for us to call, and after a stored procedure is compiled, we do not need to compile it again when we need to execute it again next time, you can find an execution plan developed by SQL server2005 when the stored procedure is compiled for the first time in the cache of the memory process. This solution reflects which index the user wants to use to return the fastest data, it accelerates the performance and speed of statement execution. Now, students may think that if I modify the content of this stored procedure, this stored execution plan may affect our desired results. In this case, we only need to rewrite and compile the stored procedure. Here, we have learned how to optimize the stored procedure, next section We will learn in the future.

UseDrop proc proc_selstudent DeleteStored Procedure, useAlter proc keywordModifyThe following example shows how to modify a stored procedure to query student information older than 0.

1 Alter proc DBO. proc_selstudent
2 As
3 Select * From student Where Age > 20

DBO is the name of the framework to which a stored procedure belongs. When writing a stored procedure, it is best to explicitly specify its framework name and knowledge about the framework, I will explain it later in the tutorial. Now we will understand it as a namespace similar to C. Remember that the modified stored procedure will completely replace the original stored procedure, but it retains any permissions and dependencies of the stored procedure. The permission mentioned here refers to the unique security mechanism advantage of stored procedures for operating database objects. SQL Server allows a user to restrict certain operations on tables or views, for example, we can disable user WF from restricting the Select Operation on the student table, but allow user WF to use the stored procedure to perform conditional access to the table, in this way, we can use stored procedures to encapsulate and protect the tables or views referenced by them, so that users can only get authorized operations. The following example limits the user's WF permission to query the student table, but provides him with the permission to use the stored procedure to operate the table student.

1 Deny select on DBO. Student to WF;
2 Grant exec on DBO. pro_selstudetn to WF;

Dependency refers to the fact that a stored procedure can contain (CALL) other stored procedures. In this case, you will think about what can be included and cannot be included in the stored procedure? First, the stored procedure can contain complex SQL statements. The reason is that it can reference multiple database objects, such as multiple tables, views, and stored procedures, if there are interpretation conditions between these referenced objects, you can also use flow control, loop structure, error capture, functions, parameters, return values, and other Members. Statements that cannot be included in the create proc definition are displayed in a list:

1. Create default -- create a default Column

Create default address
As 'address unknown'
Sp_bindefault 'address', 'student. address' -- bind the address field value in the student table to 'address unknown'

2. Create or alter function -- create or modify a User-Defined Function

3. Create or alter proc -- create or modify a stored procedure

4. Set parseonly {on | off} -- when set parseonly is on, SQL Server only analyzes statements. When set parseonly is off, SQL Server compiles and runs the statement.

5. Set showplan_text -- when set showplan_text is on, SQL Server Returns the execution information of each Transact-SQL statement, but does not execute the statement. After this option is set to on, information about the execution plan of all subsequent SQL Server statements is returned until this option is set to off.

6. Set showplan_all -- set showplan_all is set during execution or runtime, rather than during analysis.

7. Set showplan_xml -- if set showplan_xml is on, SQL Server Returns the execution plan information for each statement without executing the statement.

8. Use keywords

9. Create Schema-create a schema in the current database.

10. Create or alter trigger -- create or modify a trigger

11. Create or alter View -- create or modify a view

After reading so much, it is not allowed. For Beginners, we only need to know that the stored procedure should reference the defined objects. Below we will add the parameters to the stored procedure.ParametersThere are a lot of SQL statements. Like variables in a program, a result can be stored and used by users. At the same time, parameters can be divided into two types: input and output.

For example, "output the corresponding student name based on the student ID entered by the user", there are two uncertain factors that affect the query results, one is the student ID entered by the user, and the other is the student name of the output user. Therefore, we should define the student ID for the input parameter, and the query result should be defined as the output parameter, the code for modifying the above stored procedure to the above effect is:

1 Alter proc [DBO]. [proc_selstudent]
2 @ Stunumber Int , -- Define the input parameter stunumber
3 @ Stuname nchar ( 10 ) = Null Output -- Define the output parameter stuname
4 As
5 Select @ stuname = [Name] from student Where Number = @ Stunumber
6

In the above example, we will summarize the following information about the parameters defined in the stored procedure:

1. Parameter Name: Start with @ to define the parameter name
2. Data Type of a parameter: the data type can be either built-in or user-defined by SQL Server. The data type of a parameter must be consistent with that of a field.
3. Default Value of a parameter: You can use "=" to assign the default value after the parameter type.
4. Parameter direction: For an output parameter, output must be abbreviated as out. (For a cursor parameter, varying and output must be used)
5. Separate parameters with commas (,). You can also use commas () to enclose all parameters.
   When executing this stored procedure, we need to provide the parameter value. The output parameter needs to define a variable to receive the returned value. Next we will query the students whose student number is 3, then find out his name.

1 Declare @ returnname nchar ( 10 ) -- When executing a stored procedure, define a variable to receive the query return value (different names can be used)
2 Exec proc_selstudent ' 3 ' , @ Returnname output -- And specify the output keyword.
3 Select @ returnname -- Query the value of a variable to obtain the stored procedure result.
4

The first line uses declare to define a variable, and the second line executes the stored procedure. Note the Parameter order during execution (if the @ parameter_name = value statement is explicitly used to provide the parameter value, the parameter sequence can be ignored), ",", and output keywords are separated. After the second row is executed, the queried values are stored in the Defined variables, then we query the value of this variable and get the result.

Let's change the stored procedure:

Code
1 Alter proc [DBO]. [proc_selstudent]
2 @ Stuname nchar ( 10 ) = Null Output,
3 @ Stunumber Int = 3
4 As
5 Select @ stuname = [Name] from student Where Number = @ Stunumber
6 -- Execute the Stored Procedure
7 Declare @ returnname nchar ( 10 )
8 Exec proc_selstudent @ returnname output
9 Select @ returnname

Row 8th directly queries the Student name numbered 3 because row 3rd adds the default value of = 3 to the parameter. When row 8th is changed to Exec proc_selstudent @ returnname output, 5. The name of the student numbered 5 should be found. If the input parameter has a default value, when the input parameter value is omitted during the stored procedure, the default value is returned. Note that if you want to omit the value of the input parameter, the defined position of the input parameter should be at the end.

Now let's summarize the considerations for executing a parameter stored procedure. When a stored procedure is called, we must specify the input value for the input parameter that does not provide the default value in the definition, if we use the direct write Value Method to specify the parameter value, it must be input in the order of parameter Declaration; if the parameter name = value is displayed, the order problem can be ignored; if we want to use the default value for one of multiple parameters when executing the stored procedure, we can use the default keyword to solve this problem, such as "Exec proc proctest 1, default, 2 "; when the last input parameter has a default value, we just want to use the default value when calling the parameter, so we can omit the value assigned to the input parameter. The output parameter must specify the output keyword, otherwise, SQL server will not produce runtime errors, but may encounter the most terrible situation-the term "unexpected results ".

Next, let's ask the students to define a stored procedure: Create a stored procedure that modifies the student's age based on the student's name, and then display the student's information.

Job
1 Create proc updatestudent
2 @ Name nchar ( 10 ),
3 @ Age Int
4 As  
5 Update student Set Age = @ Age Where Ltrim (name) = @ Name
6 Select name As Student name, student age = Age from student
7   Where Name = @ Name
8 Go
9 -- Execute the following command to change the student's age to 44.
10 Exec updatestudent ' Dumb ' , 44

The students who passed this course have been taught about the basic operations of stored procedures. In the next section, we will learn about the difficulties in stored procedures, this includes storage encryption, error information processing, optimization of stored procedures, and storage process classification. Finally, we summarize the advantages of stored procedures.