What is the storage
Posted on 2009-05-23 filed under: Development (RSS) 55 views
Will be used or very complicated job, with pre-written SQL statement and the name of a specified stored, then later asked to provide a database stored procedure well defined function of the same service, simply call the execute, can be auto-complete command.
Mentioned here, you may want someone ask: So is that a pile of stored procedure SQL statement only ah?
Microsoft the company so why should we add to this technology?
Then the stored procedure with SQL statement in general What is the difference between them?
The advantages of stored procedures:
1. Stored procedure only when the compiler to create the future, are each stored procedure do not need to recompile the general implementation of a SQL statement to compile every time, so the use of database stored procedures can improve speed of execution.
2. When the complexity of the database operations (such as multiple tables Update, Insert, Query, Delete), the complexity of the operation could be packaged together with stored procedures and database services provided by the use of combination treatment.
3. Stored procedure can be re-used, can reduce the workload of the database developers
4. Safe, this can be set only in a designated user only has the right to use the stored procedure
The type of stored procedure:
1. The system stored procedure: sp_ in the beginning of the system used for the set. To obtain information. Related to the management of the
Sp_help such as the object is to obtain the relevant information specified
2. XP_ extended stored procedure in the beginning, used to call operating system functions
exec master .. xp_cmdshell 'ping 10.8.16.1'
3. User-defined stored procedure, which is referred to our stored procedure
Common format
Create procedure procedue_name
[@ parameter data_type] [output]
[with] (recompile | encryption)
as
sql_statement
Explained:
output: that this parameter is returned
with (recompile | encryption)
recompile: that each time when the stored procedure recompilation time
encryption: the stored procedure created content will be encrypted
Such as:
Table book reads as follows
ID Title Price
001 C Language Introduction to $ 30
$ 002 PowerBuilder Development Report 52
Example 1: Query Table Book of the contents of stored procedures
create proc query_book
as
select * from book
go
exec query_book
Example 2: add a record to table book, and query this table the total amount of all books
Create proc insert_book
@ param1 char (10), @ param2 varchar (20), @ param3 money, @ param4 money output
encryption with encryption ---------
as
insert book (ID, Title, Price) Values (@ param1, @ param2, @ param3)
select @ param4 = sum (Price) from book
go
Implementation of examples:
declare @ total_price money
exec insert_book'003 ',' Delphi Control Development Guide ', $ 100, @ total_price
print 'The total amount of money' + convert (varchar, @ total_price)
go
Three kinds of stored procedure return value:
1. To Return return integer
2. To return the output format parameters
3.Recordset
Return value of the difference between:
output and the return can be used in the batch program to receive variables, and the recordset back to the implementation of the batch client
Example 3: There are two tables for the Product, Order, the table reads as follows:
Product
Product Name Product No.订数customers
001 pen 30
002 brush 50
003 pencil 100
Order
Customer name Product ID customer deposit
001 Nanshan District, $ 30
002 Luohu District, $ 50
003 Bao'an District, $ 4
Please realize the conditions in accordance with code to connect the two tables into a temporary table containing only numbers. Product Name. Customers were. Deposit. The total amount of
Total amount of money = deposit *订数, on a temporary table in stored procedure
Code is as follows:
Create proc temp_sale
as
select a. Product ID, a. Product name, b. Customer name, b. deposit customers, a. Customer订数* b. the total amount of customer deposits as
into # temptable from Product a inner join Order b on a. Product ID Product ID = b.
if @ @ error = 0
print 'Good'
else
& n bsp; print 'Fail'
go
Introduction stored procedure
1, first tell us what are stored procedures
Stored procedure using SQL Server are provided Tranact-SQL language preparation procedures. Tranact-SQL language is specifically provided for the SQL Server database application design language, it is the applications and SQL Server databases design of the main program interface. It is like the Oracle database system in the Pro-SQL and Informix database system in the able Informix-4GL languages. Such language to provide the following major functions, so that users can design procedures in line with demand quoted:
1), variables description
2), ANSI-compliant SQL command (such as Select, Update ....)
3), the general flow control commands (if ... else ..., while ....)
4), the internal function
Second, stored procedure writing grid
CREATE PROCEDURE [owner.] Stored procedure name [; procedure code]
[(Parameter # 1, ... parameter # 1024)]
[WITH
(RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION)
]
[FOR REPLICATION]
AS process line
Stored procedure name can not be one of more than 128 words. Each stored procedure set up to 1024 parameters
(SQL Server 7.0 or later), the use of parameters is as follows:
@ Parameters of data type [VARYING] [= default value] [OUTPUT]
Each parameter of the former have a "@" symbols, each of the parameters of a stored procedure the program is only for internal use, except the type of parameters IMAGE, other SQL Server data types support may be used.
[= Default value] equivalent to our database set up at the field to set a default value, this is the default setting for this parameter value. [OUTPUT] is used to specify the parameters are both input and output values, that is, call the stored procedure, if specified in the parameter values are the parameters we need to enter, but also required at the output results , then the need to OUTPUT, and if output parameter is only used to do, you can use CURSOR, to use the parameters at the same time, it is important to specify the two VARYING and OUTPUT statements.
Examples:
CREATE PROCEDURE order_tot_amt @ o_id int, @ p_tot int output AS
SELECT @ p_tot = sum (Unitprice * Quantity)
FROM orderdetails
WHERE ordered = @ o_id
Examples to illustrate:
The example is set up a simple stored procedure order_tot_amt, this stored procedure in accordance with orders entered by the user ID number (@ o_id), by order schedule (orderdetails) in order to calculate the total sales [Unit Price (Unitprice) * Quantity (Quantity)] , the amount of @ p_tot through the parameters of the output of this stored procedure to call the procedure
Three, in implementation of the SQL Server stored procedure
In the SQL Server's Query Analyzer, enter the following code:
declare @ tot_amt int
execute order_tot_amt 1, @ tot_amt output
select @ tot_amt
Order_tot_amt are the implementation of the code above the stored procedure to calculate the number of orders for sales orders 1, @ tot_amt for the definition of our output parameter, used to take us to the results of
when the sql statement before the implementation of the compiler, and then implementation. Stored procedure is to compile a good number of sql statements. When used can be used directly.
Stored procedure are controlled by the flow and the process of writing SQL statements, this process optimized by the compiler and stored in the database server can be used as long as the call. At ORACLE, the number of contact there is the process of composition can be combined package.
The use of stored procedure has the following advantages:
* Stored procedure's ability to greatly enhance the functionality of the SQL language and flexibility. Stored procedure can be used to prepare flow control statement, there is a strong flexibility to achieve complex and more complicated to determine the operator.
* To ensure data security and integrity.
# Through the stored procedure allows the user does not have permission to indirectly under the control of access to the database, thus ensuring the safety of data.
# Through the stored procedure so that relevant action can happen together, so that we can safeguard the integrity of the database.
* Re-run the stored procedure before the database has its grammar and syntax analysis, and give the implementation of the program is optimized. This process may well have been compiled greatly improve the performance of SQL statements. SQL statements as a result of the implementation of most of the work has been completed, so the stored procedure to speed the implementation.
* Can reduce the network traffic.
* So that business rules embodied Add database server computing process in order to:
# Centralized control.
# When an enterprise changes the rules on the server can change the stored procedure, no need to modify any application. The characteristics of business rules is to always change the rules if the corporate computing application procedures Add, when business rules change, they need to modify the application workload is enormous (Alteration, distribution and installation of applications) . If the rules of corporate computing Add storage process, when business rules change, as long as you can modify a stored procedure, the application without any changes.
Tags: