This helps you quickly understand the definition and advantages of stored procedures.

Definition and advantages of "stored procedure"

Stored Procedure

Compile the SQL statement before executing it. Stored procedures are compiled SQL statements. It can be called directly when the application needs to be used, so the efficiency will be high.

Stored Procedure

A stored procedure is a process written by flow control and SQL statements. The procedure is compiled and optimized and stored on the database server. You only need to call it when using the application. In Oracle, several associated processes can be combined to form a package.

Using Stored Procedures has the following advantages:

* The Stored Procedure capability greatly enhances the functionality and flexibility of the SQL language. Stored procedures can be written using flow control statements. With great flexibility, they can complete complicated judgment and computation.

* Ensures data security and integrity.

# Using Stored Procedures, users without permissions can indirectly access the database under control to ensure data security.

# Through the stored procedure, relevant actions can be taken together to maintain the integrity of the database.

* Before the stored procedure is run, the database has analyzed its syntax and provided an optimization execution plan. This compiled process can greatly improve the performance of SQL statements. Since most of the SQL statement execution has been completed, the stored procedure can be executed very quickly.

* Reduces network traffic.

* Put the computing program that embodies the enterprise rules into the database server:

# Centralized control.

# When enterprise rules change, you can change the stored procedure on the server without modifying any applications. Enterprise rules are characterized by frequent changes. If the computing program that reflects the enterprise rules is put into the application, when the enterprise rules change, it takes a lot of effort to modify the application (modifying, releasing, and installing the application ). If you put the operations that reflect the enterprise rules into the stored procedure, when the enterprise rules change, you only need to modify the stored procedure, and the application does not need to change.

The essence of the database stored procedure is to deploy a set of definition codes and SQL statements on the Database End.

You can use the SQL language to write stored procedures for database access. The syntax is as follows:

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 in [] is optional, and the content in () is required,

For example, if you want to create a stored procedure for deleting records in Table TMP, select_delete can be written as follows:

Create Proc select_del As　
Delete tmp　

 

For example, you want to query the data stored in the TMP table for a certain year.

create proc select_query @year int as 
select * from tmp where year=@year

Here @ year is the parameter of the stored procedure

For example, the stored procedure starts from a node N and finds the parent node at the top. This frequently used process can be undertaken by the stored procedure and shared by repeated use on the webpage.

Null: indicates that the node is a top-level node.

Fjdid (parent node number)

Node N is not empty. It indicates the parent node number of the node.

Dwmc (unit name)

Create proc search_dwmc @ dwikidold int, @ dwmcresult varchar (100) 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....]

From: http://tech.ccidnet.com/zt/guocheng/