T-SQL Learning Essay 2

A. Stored Procedures

1. Create a stored procedure (PROCEDURE)/* My understanding stored procedure is a function, the stored procedure creates the equivalent input parameter that must be declared at the beginning of the first line of the query */

CREATE PROCEDURE t_procedure (@SnoVARCHAR)  as BEGIN    SELECT *      from Student     WHERE @Sno = Sno END;

2. Stored Procedure execution

EXECUTE ' 1307020 '; /* Execution */

3. Stored Procedure Delete

DROP PROCEDURE T_procedure;

B

T-SQL error handling mechanism

BEGIN TRANSACTIONBEGINTRYINSERT  intoStudentVALUES('13070201','Xiaohuang'); INSERT  intoStudentVALUES('13070202','Xiaolv'); COMMIT;ENDTRYBEGINCATCHPRINTError_number ()+Error_severity ()+Error_state ()+Error_procedure ()+Error_line ()+Error_message ();/*Print error message*/    ROLLBACK;ENDCatch

C cursor/**/

1. Declares that cursors can traverse all column names

Declare @colName varchar( +)Set @colName = "'Select @colName = @colName +column_name+ ' ' frominformation_schema. COLUMNSwheretable_name= 'Student'Print subString(@colName,1,Len(@colName)-1);

2. Output the contents of the table

DECLARE Custcursor CURSOR/*Creating Cursors*/For SELECT sno,snamefrom student;declare @SNO VARCHAR (Ten), @SNAME VARCHAR (Ten) OPEN Custcursor/*Open Cursor*/FETCH NEXT from Custcursor/*per line traversal*/Into @SNO, @SNAMEWHILE @ @FETCH_STATUS=0BeginPrint @SNO+' '+@SNAME; FETCH NEXT from Custcursor to @SNO, @SNAMEENDCLOSE custcursor; /*Close Cursors*/

3. Deleting cursors

Deallocate custcursor;

The D Trigger/* Trigger is a special stored procedure, but it is a spontaneous execution */

Triggers are divided into after, and instead OF triggers

After triggers are triggered after an event has occurred

INSTEAD of triggers is a substitute for what you want to do, so you don't have to do it, you need to handle it if you want to continue.

Data Update (update), Data insertion (insert) produces a inserted table

Data deletion (delete) produces a deleted table that can go inside to find information

But that is a table, simply write deleted. (column name) cannot specify an object that requires cursors access information

/*Create a trigger that inserts information to automatically add Xiao to a name*/CREATE TRIGGERT_trigger onStudentafterINSERT asDECLARE @SNO VARCHAR(Ten)SELECT @SNO =Sno fromINSERTEDUPDATEStudentSETSname= 'Xiao' +SnameWHERE @SNO =Student.sno;/*-This is the wrong version of Create TRIGGER T_ttriggeron studentafter insertasupdate studentset Sname = ' Xiao ' + snamewhere INSERTED. Sno = student.sno;--inserted is a table and cannot bind multiple objects*/

CREATE TRIGGERTt_trigger/*Modify the information, if the modified student name is not the beginning of Xiao, it is not allowed to update*/ onStudentinstead of UPDATE asDECLARE @SNO VARCHAR(Ten),        @SNAME VARCHAR(Ten)SELECT @SNO =Sno,@SNAME =Sname fromINSERTEDIF(@sNAME  not  like 'xiao%')    PRINT 'name does not conform to specification';ELSE    UPDATEStudent/*because instead of is replaced so the corresponding manual update*/    SETSname= @SNAME    WHERESno= @SNO;

D User

1. Create a user

CREATE  with = ' 123456 ' /* Create a login account */ CREATE USER STU; /* Create user */ /* login account is you can log on to the SQL management and use the appropriate user needs and users need the appropriate permissions to perform the appropriate action */

2. Giving permission

GRANT SELECT  on Student  to STU; /* give the user access to the query Stu */

3. Delete permissions

/* to remove Permissions */ REVOKE SELECT  on Student  from STU;

4. Roles

A role is a collection of permissions

Role creation

CREATE ROLE STU1;

Roles can be granted after a role is created

You can then give the role's permissions to the user, reclaim the permissions of the role, and the user's permissions are recovered accordingly.

It's better to manage.

/* Supplemental Query foreign key */ SELECT NAME  from JOIN  on  = O.object_idWHERE=object_id('  Student');

Finally, that's it ...

A shallow layer of learning ...

T-SQL Learning Essay 2