Use stored procedures and stored procedures

Use stored procedures and stored procedures
What is a stored procedure is simply a set of one or more MySQL statements for future use. It can be considered as a batch file, although their role is not limited to batch processing. Like defining a function, you can pass in outgoing parameters, and the function performs some calculations internally. (This article is for MySQL learning and testing)
Why are stored procedures used? 1. simplify complicated operations by encapsulating processing in easy-to-use units. 2. Data integrity is guaranteed because a series of processing steps are not required to be established repeatedly. 3. simplify the management of changes.
Create a stored procedure create a very simple stored procedure and return the average product price stored procedure:
Create procedure productpricing () BEGINSELECT Avg (prod_price) AS priceaverage FROM products; END //

Calling the stored procedure is relatively simple:

Delete stored PROCEDURE: drop procedure productpricing;



Use parameter Example 1 and below are the modified versions of productpricing: create procedure productpricing (OUT pl DECIMAL (), OUT ph DECIMAL (), OUT pa DECIMAL () BEGINSELECT Min (prod_price) INTO plFROM products; SELECT Max (prod_price) INTO phFROM products; SELECT Avg (prod_price) INTO paFROM products; END //
CALL the Stored Procedure CALL productpricing (@ pricelow, @ pricehigh, @ priceaverage )//

Display call results:


In Example 2, the following example shows how to create a stored procedure, input the order number, and return the total amount of the order:
Create procedure ordertotal (IN onumber INT, OUT ototal DECIMAL (8, 2) BEGINSELECT Sum (item_price * quantity) FROM orderitemsWHERE order_num = onumberINTO ototal; END //
Call the stored procedure:
CALL ordertotal (20005, @ total )//
Display results;



To create a smart storage process, consider setting up the following storage process to calculate the total number of orders. However, this order requires a value-added tax, but not all customers need a value-added tax. Therefore, we have the following work: 1. Obtain the total value. 2. Add the value-added tax to the total value. 3. Return the total value.
The complete work of the stored procedure is as follows:

When calling this stored procedure, enter the order number, whether to collect the tax, and the final result is:


You can use show procedure status to view the Stored procedure status:




How to use stored procedures in SQL?

SQL stored procedures and Applications

I. Introduction:

Stored Procedure is a set of SQL statements for specific functions.
Stored in a database, you can specify the name of a stored procedure and provide parameters. If the stored procedure has parameters

It,
In SQL Server editions, stored procedures are classified into two types: stored procedures provided by the system and custom stored procedures.

.
System SP, which is mainly used to store the master database and prefixed with sp _. The system stored procedure is mainly obtained from the system table.
To manage SQL Server for the system administrator. The user-defined stored procedure is created by the user and can be completed
A specific function, such as the Stored Procedure for querying user data information.

Stored Procedures have the following advantages:
1. stored procedures allow standard component programming (modular design)
After a stored procedure is created, it can be called multiple times in the program without having to re-compile the SQL statement of the stored procedure.

And
Database professionals can modify stored procedures at any time, but it has no impact on application source code. Because the application source code

Code only contains
Saves the Call Statement of the process, which greatly improves the portability of the program.

2. Fast execution of Stored Procedures
If an operation contains a large number of Transaction-SQL code, or is executed multiple times, the stored procedure is compared with the batch process.

Of
The execution speed is much faster, because the stored procedure is pre-compiled. When a stored procedure is run for the first time, the query optimizer

Row analysis optimization
And give the execution plan that is finally stored in the system table.

All are required
Compilation and optimization, so the speed is relatively slow.

3. stored procedures can reduce network traffic
For the same operation on data database objects, such as query modification, if the Transaction-SQL
The statement is organized into a stored procedure. When the stored procedure is called on the client's computer, only

Statement used, no
Multiple SQL statements greatly increase network traffic and reduce network load.

4. stored procedures can be fully utilized as a security mechanism
The system administrator restricts the permission to execute a stored procedure, so that the corresponding data access permissions can be achieved.

Limited
.

Ii. Variables

@ I

Iii. Process Control Statement (if else | select case | while)
Select... CASE instance
DECLARE @ iRet INT, @ PKDisp VARCHAR (20)
SET @ iRet = '1'
Select @ iRet =
CASE
WHEN @ PKDisp = '1' THEN 1
WHEN @ PKDisp = '2' THEN 2
WHEN @ PKDisp = '3' THEN 3
WHEN @ PKDisp = '4' THEN 4
WHEN @ PKDisp = '5' THEN 5
The ELSE 100
END

Iv. Stored Procedure format

Create a stored procedure
Create Proc dbo. Name of the stored procedure
Stored Procedure Parameters
AS
Execution statement
RETURN
Execute the Stored Procedure
GO
**************************************** *****************/

-- Variable declaration. The @ symbol must be added before the variable when the SQL statement declares the variable.
DECLARE @ I INT

-- Assign a value to a variable. set must be added before the variable is assigned a value.
SET @ I = 30

-- Declare multiple variables
DECLARE @ s varchar (10), @ a INT

-- If statement in SQL
IF condition BEGIN
Execution statement
END
ELSE BEGIN
... The remaining full text>

Why use stored procedures?

When I came back to the IBM interview, I complained: I went to several different IBM Project Teams. Almost every interviewer asked me about databases and asked me if I had never used the stored procedure. Is it annoying? The programmer is not the author's opinion. To put it bluntly, the stored procedure is a combination of a bunch of SQL statements. Point logic control is added in the middle. However, stored procedures are more practical when processing complex businesses. For example, a complex data operation. If you are at the front-end. It may involve multiple database connections. But if you use stored procedures. Only once. It has advantages in response time. That is to say, the stored procedure can bring us the benefits of improved operational efficiency. In addition, projects that are prone to bugs with a small amount of data, or projects that are irrelevant to money, can operate normally without the need for stored procedures. The mysql stored procedure remains to be tested. For a formal project, we recommend that you use SQL Server or oracle stored procedures. When dealing with data, the process will be much faster than the program. The interviewer asked if storage was used. In fact, he wanted to know whether the programmers who came to the interview had done large data projects. If it is trained or developed by small projects or small companies, there will certainly be little access to storage. Therefore, it is impossible to enter a large company without rich storage process experience. Error. Stored procedures are not only suitable for large projects, but also for small and medium-sized projects. Its power and advantages are mainly reflected in: 1. the stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution. 2. when performing complex operations on the database (for example, performing Update, Insert, Query, and Delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database. If these operations are completed by a program, they become SQL statements that may need to be connected to the database multiple times. Instead of storage, you only need to connect to the database once. 3. stored procedures can be reused to reduce the workload of database developers. 4. High security. You can set that only one user has the right to use the specified stored procedure. Disadvantages of stored procedures 1: debugging is troublesome, but it is very convenient to debug with PL/SQL Developer! Make up for this shortcoming. 2: The database code is of course related to the database. However, for engineering projects, there is basically no porting problem. 3: Re-compilation problem, because the back-end code is compiled before running. If the object with a reference relationship changes, the affected stored procedures and packages need to be re-compiled (but you can also set it to automatically compile at runtime ). 4: if a large number of stored procedures are used in a program system, changes in the data structure will occur as the user needs increase, followed by system problems, finally, if you want to maintain the system, it is very difficult and costly. It is more difficult to maintain!