Introduction and improvement of stored procedure

Introduction and improvement of stored procedure
What is a stored procedure?
Definition:
Store common or complex tasks in advance with SQL statements and a specified name, to enable the database to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command.
At this point, someone may ask: is the stored procedure just a bunch of SQL statements?
Why does Microsoft add this technology?
What is the difference between a stored procedure and a general SQL statement?
Advantages of stored procedures:
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.
3. The stored procedure 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.
Types of stored procedures:
1. System stored procedure: starts with sp _. It is used to set the system, obtain information, and manage the system,
For example, sp_help is used to obtain information about the specified object.
2. The extended stored procedure starts with XP _ and is used to call the functions provided by the operating system.
Exec master .. xp_mongoshell 'ping 10.8.16.1'
3. User-defined stored procedures, which we refer to as stored procedures
Common formats
Create procedure procedue_name
[@ Parameter data_type] [output]
[With] {recompile | encryption}
As
SQL _statement
Explanation:
Output: indicates that this parameter can be returned.
With {recompile | encryption}
Recompile: indicates re-compiling every time this stored procedure is executed.
Encryption: the content of the created stored procedure is encrypted.
For example:
The table book content is as follows:
Title price
001 C language entry $30
002 PowerBuilder report development $52
Example 1: query the stored procedure of the table Book content
Create proc query_book
As
Select * from book
Go
Exec query_book