Stored ProceduresStored Procedures (Stored Procedure) are in a large database system, a set of SQL statements to complete a specific function, stored in the database, after the first compilation after the call does not need to compile again, The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures.
Advantages① re-use. Stored procedures can be reused to reduce the workload of database developers. ② improve performance. The stored procedure is compiled at the time of creation and will not be re-translated when it is used in the future. A typical SQL statement needs to be compiled once per execution, so using stored procedures improves efficiency. ③ 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. ④ security. Parameterized stored procedures can prevent SQL injection attacks, and grant, deny, and revoke permissions can be applied to stored procedures.
Disadvantages1: Debugging trouble, but with PL/SQL Developer debugging is very convenient! compensate for this shortcoming. 2: Porting issues, database-side code is of course related to the database. However, if you are doing engineering projects, there is basically no transplant problem. 3: Recompile the problem because the backend code is compiled before it is run, and if an object with a reference relationship changes, the affected stored procedures, packages will need to be recompiled (but can also be set to run for automatic compilation). 4: If in a program system to use a large number of stored procedures, to the time of application delivery with the increase in user demand will lead to changes in data structure, then the system related problems, and finally if users want to maintain the system can be said to be difficult, and the cost is unprecedented, maintenance more troublesome.
user-defined functions:
Microsoft SQL Server 2000 allows you to create user-defined functions. As with any function, a user-defined function is a routine that can return a value. Depending on the type of value returned, each user-defined function can be divided into the following three categories:
1. Returns a function that updates the data table: if the user-defined function contains a single SELECT statement and the statement can be updated, the table formatting results returned by the function can also be updated.
2. Returns a function that does not update the data table: If a user-defined function contains more than one SELECT statement, or contains a SELECT statement that is not updatable, the result of the table format returned by the function is not updatable.
3. A function that returns a scalar value: A user-defined function can return a scalar value.
Advantages and disadvantages of custom functions
1. Many limitations, many statements can not be used, a lot of functions can not be implemented
2. Return values can be referenced directly
3. Returning a recordset with a table variable
The difference between a stored procedure and a custom function in SQL
