This is the 8th day of my participation in Gwen Challenge

A, variables,

Variables are classified into global variables and local variables.

p.s.

  1. Global variables cannot be user-defined, assigned or declared explicitly, and their names begin with @@.

  2. Local variables are user-defined and need to be declared with DECLARE. Local variables, like their names, are valid only within the batch statement or procedure in which they are declared.

2. Stored procedures

Benefits of using stored procedures:

  1. High operating efficiency
  2. Reduced traffic between client and server
  3. Facilitate the implementation of enterprise rules

Statement format:

CREATE PROCEDURE Name of the stored PROCEDURE [; Version number] [{@ Parameter data type} [VARYING] [= Default value] [OUTPUT],... [WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}] [FOR the REPLICATION] AS SQL statementCopy the code

Example 1: Create a simple stored procedure to view all the records in the student table

ALTER PROCEDURE EXP1
AS 
	SELECT * FROM s
Copy the code

Example 2: Call the stored procedure nested to find the information of the student with the highest total score:

CREATE PROCEDURE EXP6 @sno1 char(10) OUTPUT
AS 
	SELECT TOP 1 @sno1 = sc.sno
	FROM sc
	GROUP BY sc.sno
	ORDER BY SUM(sc.grade)	DESC
GO
CREATE PROCEDURE EXP7
AS
	DECLARE @sno2 char(10)
	EXECUTE EXP6 @sno2 OUTPUT
	SELECT *
	FROM s
	WHERE sno = @sno2;
GO

Copy the code

3. Trigger

Function of flip-flop

  • To strengthen the constraints
  • Track changes
  • Cascade operation
  • Calls to stored procedures

Example 3: Use the INSTEAD OF trigger to implement cascading deletion, i.e. if a student’s data is deleted from the S table, the student’s grade information should be deleted from the SC table at the same time.

CREATE TRIGGER tr1_S
ON s
INSTEAD OF DELETE
AS 
BEGIN TRANSACTION
	DELETE FROM sc
	WHERE sno IN (SELECT sno FROM DELETED)
	DELETE FROM s
	WHERE sno IN (SELECT sno FROM DELETED)
COMMIT TRANSACTION;
Copy the code

Running results:

CREATE TRIGGER tr1_S
ON s
INSTEAD OF DELETE
AS 
BEGIN TRANSACTION
	DELETE FROM sc
	WHERE sno IN (SELECT sno FROM DELETED)
	DELETE FROM s
	WHERE sno IN (SELECT sno FROM DELETED)
COMMIT TRANSACTION;
> Affected rows: 0> time:0.017s
Copy the code