- A stored procedure is a precompiled batch 2 of SQL statement(s) that is stored on the database server. - The fact that the stored procedure is pre- compiled will save you time as well when executed. - A stored procedure can contain any SQL statement that your database server can understand. - Another task you can use a stored procedure for is database maintenance.. - Why Use a Stored Procedure?. - You should use a stored procedure in the following cases (please note that other cases do apply, depending on your circumstances):. - You can simply change the stored procedure and not the application itself, meaning you don’t have to recompile a business service or even your client application, depending on how you have designed your application. - SET ANSI_DEFAULTS: This statement sets the ANSI defaults on for the duration of the query session, trigger, or stored procedure.. - Creating and Running a Stored Procedure. - Among other things, it’s much easier to run and test a stored procedure directly from the text editor. - Anyway, here’s how you would create a stored procedure for the example UserMan database:. - Right-click the Stored Procedures node and select New Stored Procedure.. - This brings up the Stored Procedure text editor, which incidentally looks a lot like your C# code editor. - Creating a Simple Stored Procedure. - As you can see from your stored procedure editor, the template automatically names it StoredProcedure1. - If you’re wondering about the dbo prefix, it simply means that the stored procedure is created for the dbo user. - In SQL Server terms, dbo stands for database owner, and it indicates who owns the database object, which is a stored procedure in this case. - An ownership chain is the dependency of a stored procedure upon tables, views, or other stored procedures.. - Stored procedure editor with SQL Server default template. - Generally, the objects that a view or stored procedure depend on are also owned by the owner of the view or stored procedure. - To save the stored procedure before continuing, press Ctrl+S. - The editor will make sure that the stored procedure is saved on the database server with the name you’ve entered, which in this case is. - Although you can see your stored procedure in the Stored Procedure folder of the SQL Server Enterprise Manager and the Stored Procedure node in the Server Explorer, there isn’t actually an area in your database designated for just stored procedures. - The stored procedure is saved to the system tables as are most other objects in SQL Server.. - As soon as you have saved it, the very first line of the stored procedure changes. - In case you’re wondering what happens when you change the name of your stored procedure and the SQL statement still reads ALTER PROCEDURE. - However, this can be a dangerous practice, if you inadvertently change the name of your stored procedure to the name of an already existing one.. - In Figure 6-1, you can see two parts of the stored procedure: The first part is the header and then there is the actual stored proce- dure itself. - The second part of the stored procedure is the part that starts with the AS clause on Line 8. - The AS clause indicates that the text that follows is the body of the stored procedure, the instructions on what to do when the stored procedure is called and executed.. - Now the stored procedure should look like the example in Figure 6-2. - The stored procedure will return the number of rows in the tblUser table. - Running a Simple Stored Procedure from the IDE. - If you do this with the stored procedure you created in the exercise in the previous section, the Output window, located just below the editor window, should display the output from the stored procedure as shown in Figure 6-3.. - Stored procedure that returns the number of rows in the tblUser table. - If you have closed down the stored procedure editor window, you can run the stored procedure from the Server Explorer. - Expand the database node, right-click the Stored Procedures node, and select Run Stored Procedure from the pop-up menu. - This will execute the stored procedure the exact same way as if you were running it from the editor window.. - Running a Simple Stored Procedure from Code. - Listing 6-1 shows you some very simple code that will run the stored procedure. - Running a Simple Stored Procedure 1 public void ExecuteSimpleSP(). - The code in Listing 6-1 retrieves the return value from the stored procedure.. - The stored procedure itself could just as well have had a DELETE FROM tblUser WHERE LastName=’Johnson’. - If you want to execute this from code, you need to know if the stored procedure returns a value or not. - 1) Create a new stored procedure and save it with the name uspGetUsers.. - Now the stored procedure should look like the one in Figure 6-4. - This stored procedure will return all rows in the tblUser table.. - The uspGetUsers stored procedure. - Retrieving Rows from a Stored Procedure 1 public void ExecuteSimpleRowReturningSP. - Creating a Stored Procedure with Arguments. - Sometimes it’s a good idea to create a stored procedure with arguments 3 instead of having more stored procedures essentially doing the same. - Another reason for using arguments with stored procedures is to make the stored procedure behave differently, depending on the input from the argu- ments. - One argument might hold the name of a table, view, or another stored procedure to extract data from.. - 1) Create a new stored procedure and save it with the name uspGetUsersByLastName.. - The stored procedure should look like the one in Figure 6-5. - TIP In SQL Server you can use the EXECUTE sp_executesql statement and System Stored Procedure with arguments of type ntext, nchar, or nvar- char to execute parameterized queries. - The uspGetUsersByLastName stored procedure. - NOTE I only cover the absolute basics of how to create a stored procedure in this chapter. - Running a Stored Procedure with Arguments from the IDE Try and run the stored procedure you created in the last exercise and see how the argument affects how it’s run. - You can try running the stored procedure from either the editor window or the Server Explorer window. - Now all users with the last name of Doe are returned as the result of the stored procedure.. - Using a Stored Procedure with Arguments. - The uspGetUsersByLastName stored procedure seems to work, so try and run it from code. - Retrieving Rows from a Stored Procedure with an Input Argument 1 public void GetUsersByLastName(). - In Listing 6-3, a SqlParameter object specifies the input parameter of the stored procedure. - Create a new stored procedure and save it with the name. - This stored procedure should return the value 55 for the OUTPUT argument lngNumRows, and then all rows in the tblUser table and all rows in the tblUserRights table.. - The stored procedure should look like the one in Figure 6-6.. - The uspGetUsersAndRights stored procedure. - Running a Stored Procedure with Arguments and Return Values from the IDE. - If you’ve created and saved the stored procedure in the previous exercise, test it by running it. - NOTE Syntax testing of your stored procedure is done when you save it, and I have a feeling you have already encountered this. - Retrieving Rows and Output Values from a Stored Procedure 1 public void GetUsersAndRights(). - The Output window with output from the uspGetUsersAndRights stored procedure. - This stored procedure should return the value 55 as the RETURN_VALUE. - The stored procedure should look like the one in Figure 6-8.. - Retrieving RETURN_VALUE from a Stored Procedure 1 public void GetRETURN_VALUE(). - The uspGetRETURN_VALUE stored procedure. - In Listing 6-5, the ExecuteScalar method gets the RETURN_VALUE from a stored procedure. - Changing the Name of a Stored Procedure. - If you change the name of your stored procedure in the editor window, the stored procedure is saved with the new name when you save (Ctrl+S). - However, if you’re not using this method to copy an existing stored procedure, you should be aware that the old stored procedure still exists. - Viewing Stored Procedure Dependencies. - In SQL Server Enterprise Manager, you can see what tables and other objects your stored procedure uses or is dependent on. - This brings up the Dependencies dialog box, where you can see what database objects your stored procedure depends on and vice versa. - If you want to use an Oracle stored procedure instead of a stored function, like the one shown in Listing 6-8, to retrieve one or more simple data types using output parameters, you can use the example code shown in Listing 6-9.. - A Simple Oracle Stored Procedure. - The Oracle stored procedure in Listing 6-8 accepts one output parameter (lngNumRows) and sets this parameter to the number of rows in the tblUser table when executed. - You can see how you can call this stored procedure from code, in Listing 6-9.. - Running a Simple Oracle Stored Procedure 1 public void ExecuteSimpleOracleSP(). - In Listing 6-9, I again use the ExecuteScalar method of the DataReader class on Line 24 for retrieving a value from a stored procedure. - 4 Listing 6-10 shows a stored procedure that returns a result set using cursors.. - Oracle Stored Procedure Returning Result Set 1 CREATE OR REPLACE PACKAGE PKGTBLUSER. - The package definition only holds the type declaration, which is used in the stored procedure. - Listing 6-11 shows you how to retrieve the result set from the stored procedure.. - Retrieving Result Set from Oracle Stored Procedure 1 public void OracleGetUsersByLastName(). - If you compare the stored procedure in Listing 6-10 and the example code in Listing 6-11, you’ll see. - The stored procedure has two parameters, the last name input parameter and the result set output parame- ter. - Now the stored procedure should look like the one in Figure 6-13.. - I demonstrated stored procedures, views, and triggers, and showed you how to create, run, and execute a stored procedure from code
Xem thử không khả dụng, vui lòng xem tại trang nguồn hoặc xem
Tóm tắt