SQL statements with ? and stored procedures

1.The stored procedure compiler checks the syntax of the stored procedure in advance to avoid the problem of abnormal runtime caused by the incorrect syntax of SQL statements. 2.

The database server only compiles the stored procedure when it is saved, and it does not need to be recompiled every time the stored procedure is executed in the future, and the general SQL statement needs to be recompiled by the database engine every time it is executed, so the use of stored procedures can improve the efficiency of database execution (supplement: this is very useful, especially when you feel that your program is a little slow related to the database, you can use this point to help your program run faster)3It is possible to check whether the running result is correct directly when defining the stored procedure, and the visual design interface improves efficiency.

4.Avoided the problem of query strings containing special characters such as single quotes5The same SQL statement may be used in multiple places in a project, and the use of stored procedures makes it easy to reuse6

Modification is flexible and convenient, when the completed function needs to be modified, only the defined stored procedure needs to be modified, and each reference does not need to be modified individually.

A stored procedure is written in the database, it is precompiled, and its arguments must be prefixed with @. Whereas? It is a parameter placeholder, which can be used in SQL statements in the program, not in stored procedures, but arguments with @ can be used in both SQL statements and database stored procedures.

The problem is not clearly described.

A stored procedure is a set of SQL statements in a large database system that is stored in a database and is permanently valid after a single compilation, and the user executes it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters). A stored procedure is an important object in a database.

The essence of a database stored procedure is a set of definitions and SQL that are deployed on the database side. Commonly used or very complex work is written in SQL statements in advance and stored with a specified name, so that when the database is called to provide the same function as the defined stored procedures, the execute command can be automatically completed by calling execute.

Each parameter name should be preceded by an "@" symbol, and the parameters of each stored procedure are only used internally by the program, and the types of parameters can be used by other data types supported by SQL Server except image.

Functions have return values. The stored procedure does not have a return value.

Upstairs, it's not right, oracle's stored procedures have return values.

Stored procedures are stored in a database and are permanently valid after a single compilation, and are invoked by the user to perform the function.

A stored procedure is a set of SQL statements that provide a specific function stored in the database for a large return database system, and it is permanently valid after compilation. The user can execute it by specifying the name of the stored procedure and giving parameters (if the stored procedure has parameters). The deposit-and-answer process is an important object in the database.

Languages such as stored procedures provide the following functions that allow users to design programs that meet the needs of the application: variable description; ANSI (National Organization for Standardization) compliant SQL commands (e.g. SELECT, UPDATE, etc.); General Process Control Commands (if...)else…、while….intrinsic functions.

There is no difference in essence. It's just that the function is like a restriction that only one variable can be returned.

Whereas, stored procedures can return more than one. Functions can be embedded in SQL and can be called in select, while stored procedures cannot. The essence of execution is the same.

There are many function restrictions, such as not being able to use temporary tables, only table variables, and some functions are not available, etc., while stored procedures have relatively few restrictions.

1.Generally speaking, the functions implemented by stored procedures are a little more complex, while the functions implemented by functions are more targeted.

2.For stored procedures, parameters can be returned, while functions can only return values or table objects.

3.Stored procedures are generally executed as a separate part (exec execution), while functions can be called as part of a query statement (select call), and since a function can return a table object, it can be placed after the from keyword in the query statement.

4.When stored procedures and functions are executed, SQL Manager will go to the Procedure Cache to retrieve the corresponding query statement, and if there is no corresponding query statement in the Procedure Cache, SQL Manager will compile the stored procedures and functions.

The execution plan in the Procedure Cache is saved, and the execution plan in the Procedure Cache is executed after compilation, and then SQL Server will consider whether to save the plan in the cache according to the actual situation of each execution plan, and one of the criteria for judging is this execution the frequency at which the plan may be used; The second is the cost of generating this plan, which is the time it takes to compile. The plan stored in the cache does not need to be compiled the next time it is executed.

MySQL Tutorial 4 Mysql8 Operators, Functions, Stored Procedures and New Data Types The Difference Between Stored Procedures and Custom Functions Learn apeland.