Storage process:

A stored procedure is a declarative SQL statement stored in a database directory. A stored procedure is an important object in a database. The user executes it by specifying the name of the stored procedure and giving it parameters.

Advantages of stored procedures:

① Enhanced the function and flexibility of SQL statements;

② There is no need to repeatedly establish a series of processing steps to ensure the integrity of the data;

(3) Reduce the network traffic, the client calls the stored procedure only need to pass the stored procedure name and related parameters, compared with the transmission of SQL sentences natural data volume is much less;

(4) enhanced the security of the use, through the stored procedure can make users without permission under control of the indirect access to the database, so as to ensure the security of the data;

⑤ Can realize centralized control, when the rule changes, only need to modify the stored procedure;

Disadvantages of stored procedures:

① Debugging is not very convenient;

② You may not have the right to create a stored procedure;

③ Recompile problems;

(4) portability;

Classification of stored procedures:

1) System stored procedures: start with SP_ to carry out various Settings of the system. Obtain information. Related management work.

2) Local stored procedure: A user-created stored procedure is a stored procedure created by a user to complete a specific function. In fact, a stored procedure generally refers to a local stored procedure.

3) Temporary stored procedure

① A local temporary stored procedure with a hash (#) as the first character of its name becomes a local temporary stored procedure in the TEMPDB database and can be executed only by the user who created it;

② When a global temporary stored procedure starts with two hash numbers (##), the stored procedure becomes a global temporary stored procedure stored in the TEMPDB database. Once created, the global temporary stored procedure can be executed by any user connected to the server without specific permissions.

4) Remote Stored Procedures: In SQL Server2005, Remote Stored Procedures are Stored on a Remote server. Usually, distributed queries and EXECUTE commands can be used to EXECUTE a Remote Stored procedure.

5) Extended Stored Procedures: Extended Stored Procedures are Stored Procedures that can be written by users using external programming languages, and their names usually start with XP_

Parameters for creating a stored procedure:

Procedure_name: The name of the stored procedure, preceded by # for a local temporary stored procedure and ## for a global temporary stored procedure.

Number: is an optional integer used to group procedures with the same name so that procedures in the same group can be removed together with a DROP PROCEDURE statement.

Parameter: parameter of a stored procedure. There can be one or more. The user must supply the value of each declared parameter when executing the procedure (unless a default value for that parameter is defined).

Data_type: indicates the data type of the parameter. All data types, including text, ntext, and image, can be used as parameters for stored procedures.

VARYING: Specifies the result set (dynamically constructed by stored procedures and whose content can change) that is supported as an output parameter. This applies only to cursor parameters.

Default: indicates the default value of the parameter. If a default value is defined, you do not need to specify a value for this parameter to execute the procedure. The default value must be constant or NULL.

OUTPUT: indicates that the parameter is a return parameter.

RECOMPILE: Indicates that SQL Server does not cache plans for this process, which will be recompiled at run time.

ENCRYPTION: indicates SQL Server ENCRYPTION.

FOR REPLICATION: Specifies that stored procedures created FOR REPLICATION cannot be executed at the subscriber.

AS: Specifies the operation 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.

Calling a stored procedure:

1) Create a stored procedure

Create PROCEDURE Name of the stored PROCEDURE (parameter list) BEGIN PROCEDURE body end;Copy the code

Ex. :

Create a stored procedure:

create procedure porcedureName () 
begin 
 select name from user; 
end;  
Copy the code

2) Call the stored procedure:

Call Stored procedure nameCopy the code

Ex. :

call porcedureName(); 
Copy the code

3) Delete stored procedures:

DROP PROCEDURE The name of the stored PROCEDURECopy the code

Trigger:

Triggers are special event-driven stored procedures defined by users on relational tables. A trigger is a piece of code that is automatically executed when an event is triggered.

Trigger action:

(1) Enforce restriction can implement more complex constraints than CHECK statement.

(2) Auditing changes allows the detection of operations in a database and therefore does not permit unauthorized specified updates and changes in the database.

(3) The Cascaded operation can detect the operations in the database and automatically cascade the contents of the entire database. For example, if a trigger on one table contains a data operation (such as delete, update, insert) on another table that causes the trigger on that table to fire.

In response to database updates, a trigger can call one or more Stored procedure invocation, or even operate outside of the DBMS itself through invocation of an external procedure.

Limitations of triggers:

1) The triggering program cannot CALL the stored program that returns data to the client, nor can it use a dynamic SQL statement that uses a CALL statement. However, it is possible to allow the stored program to return data to the triggering program with arguments, that is, stored procedures or functions that return data to the trigger with arguments of type OUT or INOUT. But you cannot call a procedure that returns data directly;

2) Statements that explicitly or implicitly start or end transactions can no longer be used in triggers;

Create trigger:

(1) Create a trigger with only one statement

The syntax is as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_stmt
Copy the code

Trigger_name: user-defined trigger name;

Trigger_time: Identifies the trigger event, which can be specified before (executed before the time) or after (executed after the event);

Trigger_event: identifies trigger events, including INSERT, UPDATE, and DELETE.

Table_name: the table on which the trigger is built;

Trigger_stmt: Trigger execution statement.

(2) Create a trigger with multiple execution statements

The syntax is as follows:

CREATE TRIGGER trigger_NAME trigger_time TRIGger_event ON table_name FOR EACH ROW BEGIN statement ENDCopy the code

When a trigger has at least one statement, multiple statements must be wrapped with BEGIN and END, indicating the beginning and END of the entire code block, respectively.

View triggers:

show triggers;
Copy the code

Delete trigger:

DROP TRIGGER [schema_name] trigger_name;Copy the code

Schema_name: indicates the database name. This parameter is optional. If omitted, the trigger is deleted from the current database.

Thank you for reading, if you want to get more basic knowledge of Java, you can poke me to exchange learning together!