SQL stored procedure definition and stored procedure benefits-PHP开发-php.cn

Reuse. Stored procedures can be reused, reducing the workload of database developers.
(2) Improve performance. Stored procedures are compiled when they are created and do not need to be recompiled when used in the future. General SQL statements need to be compiled every time they are executed, so using stored procedures improves efficiency.
(3) Reduce network traffic. The stored procedure is located on the server. When calling, you only need to pass the name and parameters of the stored procedure, thus reducing the amount of data transmitted over the network.
(4) Safety. Parameterized stored procedures can prevent SQL injection attacks, and Grant, Deny, and Revoke permissions can be applied to stored procedures.
Stored procedures are divided into three categories: user-defined stored procedures, extended stored procedures and system stored procedures.
Among them, user-defined stored procedures are divided into two types: Transaction-SQL and CLR.
Transaction-SQL stored procedure refers to a saved collection of Transaction-SQL statements that can accept and return parameters provided by the user.
CLR stored procedures refer to references to .Net Framework common language runtime (CLR) methods that can accept and return user-provided parameters. They are implemented as public static methods of classes in .Net Framework assemblies.
The statement to create a stored procedure is as follows:
The following is the quoted content:
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,n ]
[ WITH [ ,n ]
[ FOR REPLICATION ]
AS { [;][ n ] | }
[; ]
_specifier> ::=
External name assembly_name.class_name.method_name

[schema_name]: The name of the architecture of the storage procedure

For example:

Create SCHEMA YANGYANG88848

Create PROC Angyang8848.allgoods
as select * from master_goods
Go
execution: An error occurred in Exec AllGoods.
Execution: Exec yangyang8848.AllGoods is executed correctly.

[Number]: Optional integer used to group procedures with the same name. Use a DROP PROCEDURE statement to drop these grouped procedures together. Re For example: at Create Proc S1; 1

AS
Select * from Master_goods
Go
Create PROC S1; 2
AS
SELECT * from Master_Location
Go
completes two storage procedures. They are in the same group S1. If Exec S1 is executed, the stored procedure will execute Exec S1 by default;1. If we want to get all the base information, we need to execute Exec S1;2. When we want to delete a stored procedure, we can only execute Drop Exec S1 and all stored procedures in the group will be deleted.
Unless there is a default value when the parameter is defined or the parameter is set equal to another parameter, the user must assign a value to the parameter when calling the stored procedure.

Stored procedures can have up to 2100 parameters.

For example:

          Create Proc yangyang8848.OneGoods
        @GoodsCode varchar(10)
                                                                                                                                                                                                      Declare @Code varchar(10)
Set @Code = '0004'
Exec yangyang8848.OneGoods @Code
Adding Output after the parameter indicates that the parameter is an output parameter. O Create PROC Yangyang8848.Onegoods
@GOODSCODE2 VARCHAR (10) Output, @GOODSCODE VARCHAR (10) = '0011'
AS
From Master_GOODS WHERE GOODSCODE = @GOODSCODE
SET @GOODSCODE2 = '0005'
Go
call method ：
using using using using            ‐ ‐ off‐out out‐ ~~
               Attentive friends may see some differences in the above statements. For example, the stored procedure uses output, while the calling statement uses out. I'm here to tell you, both are the same.

[RECOMPILE]: Instructs the database engine not to cache the plan for this procedure, which is compiled at runtime. This option cannot be used if FOR REPLICATION is specified. For CLR stored procedures, RECOMPILE cannot be specified.
This is a very useful function OBJECT_ID: returns the database object identification number of the object within the scope of the schema.
For example: when we create a stored procedure, we can write the code as follows

If Object_ID('yangyang8848.OneGoods') Is Not Null
Drop Proc yangyang8848.OneGoods Go

Create Proc yangyang8848.OneGood s

@GoodsCode2 varchar(10) out, @GoodsCode varchar(10) = '0011'
We call the following SQL query
Select definition From sys.sql_modules

Where object_id = Object_ID('yangyang8848.OneGoods');

Where object_id = Object_ID('yangyang8848.OneGoods');
But if we add [ENCRYPTION] to the stored procedure, you will not be able to see any results

If Object_ID('yangyang8848.OneGoods') Is Not Null
gyang8848.OneGoods
@GOODSCODE2 VARCHAR (10) OUT, @GOODSCODE VARCHAR (10) = '0011'

With Encryption
s
Select * from Master_goods where goodScode = @GOODSCODE
SET OODSCODE2 = '0005'

then we check sys .sql_modules catalog view, will return you Null.

Then we execute the following SQL: Exec sp_helptext 'yangyang8848.OneGoods'

You will get the following results: The text for object 'yangyang8848.OneGoods' is encrypted.

You should understand at this point that parameter [ENCRYPTION]: is an encryption function that converts the original text of the CREATE PROCEDURE statement into fuzzy format. The output of the obfuscated code cannot be displayed directly in any catalog view in SQL Server 2005. Users without access to system tables or database files cannot retrieve obfuscated text. However, this text can be used by privileged users with access to system tables through the DAC port or direct access to database files. Additionally, users with the ability to attach a debugger to a server process can retrieve the decrypted process from memory at runtime.
I wrote an introductory article about cursors two days ago. Here is an example of using a cursor with a stored procedure:

If Object_ID('dbo.GetMasterGoods') Is Not Null
Drop Proc dbo.GetMasterGoods
Go O Create Proc GetmasterGoods

@Mycursor Cursor Varying Output

With Encryption
as
SET @Mycursor = Cursor
Select GoodsCode, goodsname From_go ODS
Open @MyCursor
Go

-Another storage procedure below Traverse the cursor output results

Create Proc GetAllGoodsIDAndName

Declare @GoodsCode varchar(18)
Declare @GoodsName nvarchar(20)

Ex Cursor ec GetMasterGoods @MasterGoodsCursor out

        Fetch Next From @MasterGoodsCursor
         InTo @GoodsCode, @GoodsName
                                                                                             Fetch Next From @MasterGoodsCursor
InTo @GoodsCode,@GoodsName
End
Close @MasterGoodsCursor
Deallocate @ MasterGoodsCursor
Go

The final result of executing Exec GetAllGoodsIDAndName is the following

0003: Product 0003
0004: Product 0004
0005:123123
0006: Product 0006
0007: Product 0007
0008: Product 0008

0009: Product 0009

0010 :Item 0010

0011:Item 0011

0012:Item 0012
0013:Item 0013
0014:Item 0014