MySQL stored procedures

What is a stored procedure:

A stored procedure can be said to be a recordset, which is a block of code consisting of some T-SQL statements that implement functions like a method (to and from a single table or multiple tables), and then give the code block a name, which is called when the function is used.

Advantages of stored procedures:

1. Because the database executes the action, it is compiled and executed first. However, a stored procedure is a compiled block of code, so execution is more efficient than T-SQL statements.

2. A stored procedure can replace a large number of T-SQL statements when the program interacts with the network, so it can reduce the traffic of the network and improve the communication rate.

3. The stored procedures enable users who do not have permissions to access the database indirectly under control, thereby ensuring the security of the data.

Basic syntax for stored procedures:

--------------Create a stored procedure-----------------

CREATE PROC [edure] procedure_name [; number]
[{@parameter data_type}
[VARYING] [= default] [OUTPUT]
] [,... N]

[With
{RECOMPILE | Encryption | RECOMPILE, encryption}]

[For REPLICATION]

As sql_statement [... n]

--------------call a stored procedure-----------------

EXECUTE procedure_name ' --stored procedure if there are parameters, the following parameter format is: @ parameter name =value, can also be directly parameter value

--------------Delete a stored procedure-----------------

drop procedure procedure_name --can call another stored procedure in the stored procedure, but cannot delete another stored procedure

to create parameters for a stored procedure:
1.procedure_name: The name of the stored procedure, preceded by # for the local temporary stored procedure, plus # #为全局临时存储过程.

2.; Number: is an optional integer that is used to group procedures of the same name so that the same set of procedures can be removed with a DROP PROCEDURE statement. For example, an application named orders uses a procedure that can be named Orderproc;1, Orderproc;2, and so on. The drop PROCEDURE orderproc statement will drop the entire group. If the name contains a bounding identifier, the number should not be included in the identifier, only the appropriate delimiter should be used before and after procedure_name.

3.@parameter: The parameters of the stored procedure. can have one or more. The user must provide the value of each declared parameter when the procedure is executed (unless the default value for that parameter is defined). A stored procedure can have a maximum of 2.1 parameters.
Use the @ symbol as the first character to specify a parameter name. Parameter names must conform to the rules for identifiers. The parameters for each procedure are used only for the procedure itself, and the same parameter names can be used in other procedures. By default, a parameter can be used instead of a constant instead of the name of a table name, column name, or other database object. For more information, see EXECUTE.

4.data_type: The data type of the parameter. All data types, including text, ntext, and image, can be used as parameters for stored procedures. However, the cursor data type can only be used for OUTPUT parameters. If the specified data type is cursor, you must also specify both the VARYING and the OUTPUT keywords. For more information about the data types provided by SQL Server and their syntax, see Data types.
Description There is no maximum number of limits for output parameters that can be the cursor data type.

5.VARYING: Specifies a result set that is supported as an output parameter (dynamically constructed by the stored procedure and content can vary). Only the cursor parameter is applicable.

6.Default: defaults for parameters. If you define a default value, you do not have to specify the value of the parameter to perform the procedure. The default value must be constant or NULL. If the procedure uses the LIKE keyword for the parameter, the default value can include wildcards (%, _, [], and [^]).

7.OUTPUT : Indicates that the parameter is a return parameter. The value of this option can be returned to Exec[ute]. Use the OUTPUT parameter to return information to the calling procedure. The Text, ntext, and image parameters can be used as OUTPUT parameters. The output parameter that is used with the Export keyword can be a cursor placeholder.

8.RECOMPILE: Indicates that SQL Server does not cache the schedule for this procedure, which is recompiled at run time. Use the RECOMPILE option when you are using atypical or temporary values and you do not want to overwrite the execution plan that is cached in memory.

9.Encryption: Represents an entry in SQL Server encrypted syscomments table that contains the text of the CREATE PROCEDURE statement. Use encryption to prevent the process from being published as part of SQL Server replication. Description during the upgrade process, SQL Server re-creates the encryption process by leveraging the cryptographic annotations stored in syscomments.

ForREPLICATION : Specifies that stored procedures created for replication cannot be performed at the Subscriber. The stored procedure that is created with the For REPLICATION option can be used as a stored procedure filter and can only be performed during the replication process. This option cannot be used with the WITH RECOMPILE option.

One. as: Specifies the action to be performed by the procedure.

sql_statement : Any number and type of Transact-SQL statements to include in the procedure. But there are some limitations.

Instance:

-------------Create a stored procedure named Getuseraccount----------------Create ProcedureGetuseraccount@UserName nchar( -),@UserID intOutput asif(@UserName>5)Select @UserID=COUNT(*) fromUserAccountwhereUserid> -ElseSet @UserID= +Select *  fromUserAccountreturn @ @rowcountGo-------------Execute the above stored procedure----------------execGetUserAccountRe2'7',NULL

Returns the result set of the code that executes the select * from UserAccount, while @userid is coout (*) that is =1,retun value=9

MySQL stored procedures