A more detailed definition and explanation of stored procedures __ Storage

Stored Procedures
The SQL statement is executed before it is compiled and then executed. A stored procedure is a compilation of some SQL statements. The application can be called directly when it needs to be used, so the efficiency is high.

Introduction to Stored Procedures

A stored procedure is a process that is written by flow control and SQL statements that are compiled and optimized and stored in the database server, as long as the application is invoked when it is used. In Oracle, several associated processes can be grouped together to form a package.

Using stored procedures has the following advantages:

* The ability of the stored procedure greatly enhances the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with strong flexibility to complete complex judgments and complex operations.

* Ensures data security and integrity.

# Through stored procedures, users without permission can access the database indirectly under control, thus ensuring the security of the data.

# Through stored procedures, the associated actions can be brought together to maintain the integrity of the database.

* The database has been syntactically and syntactically analyzed before running the stored procedure, and the optimal execution scheme has been given. This compiled process can greatly improve the performance of SQL statements. Since most of the work on executing the SQL statement has been completed, the stored procedure can be executed at a very fast rate.

* Can reduce the traffic of the network.

* To place an operator that embodies an enterprise rule into a database server so that:

# Centralized control.

# Change the stored procedure in the server when the enterprise rule changes, without modifying any applications. The characteristic of enterprise rule is to change frequently, if the operation that embodies enterprise rule is put into application, then when enterprise rule changes, need to modify the application workload very much (modify, publish and install application). If you put an operation that embodies an enterprise rule into a stored procedure, you can modify the stored procedure as long as the enterprise rules change, and the application needs no change.

The essence of a database stored procedure is a set of defined code and SQL deployed on the database side.

The SQL language allows you to write stored procedures for database access, with the following syntax:

CREATE proc[edure] procedure_name [; number]
[
{@parameter Data_type}] [Varying] [= default] [OUTPUT]
]
[,... N]
[With
{
RECOMPILE
| Encryption
| RECOMPILE, encryption
}
]
[For REPLICATION]
As
Sql_statement [... n]

[] The content within is optional, and () the content within is the required option,

Example: If a user wants to create a stored procedure that deletes records from table tmp Select_delete can be written as:

Create Proc Select_del as
Delete tmp

Example: A stored procedure that a user wants to query data for a year in the TMP table

create proc select_query @year int as
SELECT * FROM TMP where year= @year

Here @year is the parameter of the stored procedure

Example: The stored procedure is to start from a node n to find the highest level of the Father node, this often used process can be played by the stored procedures, in the Web page reuse to share.
Null: Indicates that the node is a top-level node.
Fjdid (parent node number)
Node n non-null: The Father node number representing the node
DWMC (unit name)

CREATE proc SEARCH_DWMC @dwidold int, @dwmcresult varchar (MB) output
As
DECLARE @stop int
DECLARE @result varchar (80)
DECLARE @dwmc varchar (80)
DECLARE @dwid int
SET NOCOUNT ON
Set @stop =1
Set @dwmc = ""
Select @dwmc =DWMC, @dwid =convert (int,fjdid) from JTDW where id= @dwidold
Set @result =rtrim (@dwmc)
If @dwid =0
Set @stop =0
while (@stop =1) and (@dwid <>0)
Begin
Set @dwidold = @dwid
Select @dwmc =DWMC, @dwid =convert (int,fjdid) from JTDW where id= @dwidold
If @ @rowcount =0
Set @dwmc = ""
Else
Set @result = @dwmc + @result
if (@dwid =0) or (@ @rowcount = 0)
Set @stop =0
Else
Continue
End
Set @dwmcresult =rtrim (@result)

Use exec pro-name [pram1 pram2 ...]