Concept advantages and syntax of SQL Stored Procedure _ MySQL-mysql tutorial

Concepts, advantages, and syntax of SQL Stored procedures before learning program procedures, what is a stored procedure first? What advantages does stored procedure have?

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.

Reference content is as follows:
Exec master .. xp_mongoshell 'ping 10.8.16.1'

3. user-defined stored procedures, which we refer to as stored procedures

Common formats

Reference content is as follows:

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.