One of the stored procedures-creating a simple Stored Procedure

One of the stored procedures-creating a simple Stored Procedure

I. Stored Procedure

Stored Procedures (Stored Procedure) are a set of SQL statements for specific functions in large database systems. They are compiled and Stored in the database.
You can run a stored procedure by specifying its name and giving a parameter (if the stored procedure has a parameter. While SQL statements are executed in Common Database operating languages
Compilation and execution are required first, so the execution efficiency is not as high as that of the stored procedure.

The advantages of stored procedures are as follows:

• Reuse. Stored procedures can be reused to reduce the workload of database developers.
• Improve performance. The stored procedure is compiled during creation and will not be re-translated for future use. A general SQL statement needs to be compiled every time it is executed, so the efficiency is improved by using the stored procedure.
• Reduce network traffic. The stored procedure is stored on the server. You only need to pass the name and parameters of the stored procedure when calling the procedure. This reduces the amount of data transmitted over the network.
• Security. Parameterized stored procedures can prevent SQL injection attacks and apply Grant, Deny, and Revoke permissions to stored procedures.

Simple syntax of the stored procedure:

Create procedure stored PROCEDURE name (
Input/output type variable name type,

Input/output type variable name Type

)
BEGIN
-- Declaration: add, delete, modify, and query operations to be completed by the statement...
END

Ii. Instances

The stored procedure in this example uses mysql as an example.
The table structure is as follows:

DROP TABLE IF EXISTS `person`;CREATE TABLE `person` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `username` varchar(255) DEFAULT NULL,  `age` int(11) DEFAULT NULL,  `password` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

1. Stored Procedures with only IN (input parameters)

Indicates that the value of this parameter must be specified when the stored procedure is called. Modifying the value of this parameter in the stored procedure cannot be returned, which is the default value.

Drop procedure if exists proc_person_findById; -- create procedure proc_person_findById (in n int) begin select * FROM person where id = n; END -- Define the variable SET @ n = 2; -- CALL the Stored Procedure CALL proc_person_findById (@ n );

The call result is as follows:

2. Stored Procedures with only OUT parameters

This value can be changed within the stored procedure and can be returned.

Drop procedure if exists proc_person_getCount -- create procedure proc_person_getCount (out n int (11) begin select count (*) INTO n FROM person; END -- CALL the Stored Procedure CALL proc_person_getCount (@ n); SELECT @ n as 'Total ';

The call result is as follows:

 

3. With IN (input parameter) and OUT (output parameter)

Can be changed and returned.

Drop procedure if exists proc_person_findInfoById; -- create procedure proc_person_findInfoById (IN n INT (11), OUT pusername VARCHAR (255), OUT page INT (11) begin select username, age INTO pusername, page FROM person WHERE id = n; END -- defines the variable SET @ id = 2; -- CALL the Stored Procedure CALL proc_person_findInfoById (@ id, @ username, @ age ); SELECT @ username as 'username', @ age ';

The call result is as follows:

　　

4. Stored Procedures with INOUT Parameters

-- Input and output drop procedure if exists proc_person_get_age; -- create procedure proc_person_get_age (INOUT n INT (11) begin select age into n from person WHERE id = n; ENDSET @ id = 1; CALL proc_person_get_age (@ id); SELECT @ id;

The call result is as follows:

 

5. Input and Output Parameters

• The IN parameter is input. If the IN parameter is not added, the input parameter is used by default.
• OUT is the output. When defining parameters, it must be added.
• INOUT is input and output and must be added. This parameter can be input or output after processing.

How to create a data insertion stored procedure in SQL

Create or replace PROCEDURE test_insert-definition name
(In_insert in number
In_insert2 in number,-define the input value
On_code out number)-define the output value
Is
Begin-implementation started
Insert into table_name (column name 1, column name 2, column name 3)
Values (in_insert, in_insert2) -- execute the insert table end statement
Test_insert;

In SQL Server, there is a stored procedure. Who can briefly introduce this stored procedure?

A stored procedure is one or more SQL commands stored as executable objects in the database.
Definition is always abstract. The stored procedure is actually a set of SQL statements that can complete certain operations, but these statements are stored in the database (Here we only talk about SQL Server ). If we create a stored procedure and call it in ASP, we can avoid mixing SQL statements with ASP code. There are at least three advantages:
First, greatly improve efficiency. Stored procedures are executed very quickly, and calling stored procedures can greatly reduce the number of interactions with the database.
Second, improve security. If SQL statements are mixed in ASP code, once the code is out of password, it also means that the database structure is out of password.
Third, it is conducive to the reuse of SQL statements.

In ASP, the stored procedure is generally called through the command object. Based on different situations, this article also introduces other calling methods. For convenience, the following simple classification is made based on the input and output of the stored procedure:
1. Only the stored procedure of a single record set is returned.
Suppose there are the following stored procedures (the purpose of this article is not to describe the T-SQL syntax, so the stored procedure only gives code, not to mention ):

/* SP1 */
Create procedure dbo. getUserList
As
Set nocount on
Begin
Select * from dbo. [userinfo]
End
Go

The above Stored Procedure Retrieves all records in the userinfo table and returns a record set. The ASP code for calling the stored procedure through the command object is as follows:

'** Call the stored procedure through the Command object **
DIM MyComm, MyRst
Set MyComm = Server. CreateObject ("ADODB. Command ")
MyComm. ActiveConnection = MyConStr 'myconstr is the database connection string
MyComm. CommandText = "getUserList" 'specifies the name of the stored procedure.
MyComm. CommandType = 4 'indicates that this is a stored procedure
MyComm. Prepared = true 'requires that the SQL command be compiled first
Set MyRst = MyComm. Execute
Set MyComm = Nothing

The set of records obtained by the stored procedure is assigned to MyRst. Next, you can operate on MyRst.
In the preceding code, the CommandType attribute indicates the request type. The values and descriptions are as follows:
-1 indicates that the CommandText parameter type cannot be determined

1 indicates that CommandText is a common command type
2. The CommandText parameter is an existing table name.
4. The CommandText parameter is the name of a stored procedure.

You can also call the stored procedure through the Connection object or Recordset object. The methods are as follows:

'** Call the stored procedure through the Connection object **
DIM MyConn, MyRst
Set MyConn = Server. CreateObject ("ADODB. Connection ")
... The remaining full text>