Getting Started and Improving Stored Procedures_PHP Tutorial

Getting started and improving stored procedures
What is a stored procedure?
Definition:
Pre-write commonly used or very complex work with SQL statements and store them with a specified name. Then in the future, the database will be called to provide the same service as the defined stored procedure. , just call execute to automatically complete the command.
At this point, someone may ask: So the stored procedure is just a bunch of SQL statements?
Why does Microsoft want to add this technology?
So what is the difference between stored procedures and ordinary SQL statements?
Advantages of stored procedures:
1. Stored procedures are only compiled when they are created. , there is no need to recompile every time the stored procedure is executed in the future, and generally SQL statements are compiled once every time they are executed, so using stored procedures can improve the execution speed of the database.
2. When performing complex operations on the database (such as Update, Insert, Query, Delete on multiple tables), this complex operation can be encapsulated in a stored procedure and used in conjunction with the transaction processing provided by the database.
3. Stored procedures can be reused, which can reduce the workload of database developers
4. High security, you can set that only certain users have the right to use specified stored procedures
Stored procedures Category:
1. System stored procedures: starting with sp_, used to set up the system, obtain information, and related management work,
For example, sp_help is to obtain relevant 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..
Common format
Create procedure proceedue_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 that this stored procedure will be recompiled every time it is executed
encryption: The content of the created stored procedure will be encrypted
For example:
The content of table book is as follows
Numbered book title price
001 Introduction to C language $30
002 PowerBuilder report development $52
Example 1: Stored procedure for querying the contents of table Book
create proc query_book
as
select * from book
go
exec query_book

http://www.bkjia.com/PHPjc/631040.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631040.htmlTechArticleIntroduction and improvement of stored procedures What are stored procedures? Definition: Write commonly used or very complex tasks in advance with SQL statements and store them with a specified name. Then they will be called...