1. Purpose and requirements
- Learn how to write database stored procedures.
- Master the method of establishing database triggers, observe the functions of triggers and setting trigger conditions and other related operations through experiments.
- Complete the case for the teacher's class (optional)
2. Experiment preparation
- Understand the T-SQL syntax for writing stored procedures and calls;
- understand what triggers do;
- Learn the T-SQL syntax for writing triggers.
3. Experimental content
(1) Stored procedure
1. Reserve knowledge
1.1 Create a stored procedure
If there is output, it is an output parameter, and if there is no output, it is an input parameter.
create procedure | proc stored procedure name @parameter name 1 {parameter data type}[=Defaults] [output], @parameter name 2 {parameter data type}[=Defaults] [output], .... as SQL_statements
1.2 Execute the stored procedure
exec stored procedure name parameter value 1 [output],parameter value 2 [output]...
1.3 Modify the stored procedure
alter procedure stored procedure name parameter name {@parameter data type}[=Defaults] [output], parameter name {@parameter data type}[=Defaults] [output], .... as SQL_statements
1.4 Delete the stored procedure
drop procedure stored procedure name
1.5 Modify the stored procedure name
[exec] sp_rename 'original stored procedure name','new stored procedure name'
1.6 Others
- begin and end
- Declare variables: declare @variable name data type (int,varchar,datetime,char)
- The custom variable name should not be the same as the field name
📝 Example demo
-- Create stored procedure create procedure cal @a int, @b int, @sub int output, @sum int output as set @sub = @a - @b set @sum = @a + @b go -- call stored procedure declare @sub int declare @num int exec cal 5,4,@sub output,@num output select @sub,@sum
2. Create a stored procedure instance
Create a stored procedure getPractice in the studentdb database, and query the specified department (name) (as the input parameter of the stored procedure) for all students participating in the "practice" course study number, name, course number and course name, if the department does not exist, returns a prompt message.
create procedure getPractice -- Designated Faculty(name)(as an input parameter to a stored procedure) @name varchar(24) --mysql Medium means 24 means 24 characters sqlserver neither utf-8 as --1.Inquire about the students of the specified department --> D_ID --2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok --3.""Practice" course --> first look s_c_info Student-Course Correspondence --4.If the department does not exist, a prompt message is returned. if (select count(*) from D_Info where D_Name=@name) <> 0 --Indicate that there are departments begin select st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name from st_info,s_c_info,C_Info where left(st_info.St_ID,2) = (select D_ID from D_Info where D_Name=@name) and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student and s_c_info.c_no=C_Info.C_No and C_Info.C_Type='practice' end else print('Faculty does not exist') go
3. Stored procedure processing
1️⃣ Execute the stored procedure getPractice respectively to query the student numbers, names, course numbers and course names of all students participating in the "Practice" course among the students of "School of Law" and "School of Materials Science and Engineering".
exec getPractice 'law school'
exec getPractice 'material science and Engineering School'
2️⃣ Use the system stored procedure sp_rename to rename getPractice to getPctStu
[exec] sp_rename 'getPractice','getPctStu'
3️⃣ Modify the stored procedure getPctStu to return the number of students participating in the practical course in the specified department, and use this stored procedure to verify the execution result with "law school" as the input parameter
alter procedure getPctStu -- Designated Faculty(name)(as an input parameter to a stored procedure) @name varchar(24) -- mysql 24 means 24 characters sqlserver neither utf-8 as -- 1.Inquire about the students of the specified department --> D_ID -- 2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok -- 3.""Practice" course --> first look s_c_info Student-Course Correspondence -- 4.If the department does not exist, a prompt message is returned. if (select count(*) from D_Info where D_Name=@name) <> 0 -- Indicate that there are departments begin select count(*) -- Number of query records(The same student number does not matter, it counts as multiple entries) from st_info,s_c_info,C_Info where left(st_info.St_ID,2) = (select D_ID from D_Info where D_Name=@name) and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student and s_c_info.c_no=C_Info.C_No and C_Info.C_Type='practice' end else print('Faculty does not exist') go
Validation: calling a stored procedure
exec getPctStu 'law school' exec getPctStu 'law school'
4️⃣ Modify the stored procedure getPctStu to return the number of students participating in the practical course in the specified department.
alter procedure getPctStu -- Designated Faculty(name)(as an input parameter to a stored procedure) @name varchar(24) -- mysql Medium means 24 means 24 characters sqlserver neither utf-8 as -- 1.Inquire about the students of the specified department --> D_ID -- 2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok -- 3.""Practice" course --> first look s_c_info Student-Course Correspondence -- 4.If the department does not exist, a prompt message is returned. if (select count(*) from D_Info where D_Name=@name) <> 0 -- Indicate that there are departments begin -- distinct st_info.St_ID --> Filter out the same student number and merge them into one record select count(distinct st_info.St_ID) from st_info,s_c_info,C_Info where left(st_info.St_ID,2) = (select D_ID from D_Info where D_Name=@name) and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student and s_c_info.c_no=C_Info.C_No and C_Info.C_Type='practice' end else print('Faculty does not exist') go
Validation: Invoking a stored procedure call
exec getPctStu 'law school' exec getPctStu 'law school'
⚠️Note: "Number of people" and "Number of people" are different. For a student, if he has participated in multiple practical courses, "Number of people" refers to the number of courses he has participated in, while "Number of people" is still 1.
(2) Trigger
1️⃣ Create a trigger with audit function in the studentdb database: the trigger name is tr_sc, and the function requirements: audit the update and insert operations on the score field in the s_c_info table, and record these operations in the sc_log table, which contains The following fields: operation type type, student number st_id, course number c_no, old score oldscore, new score newscore, operator uname, operation time udate, where the default value of operator setting is user, and the default value of operation time is system time.
Create the sc_log table:
create table sc_log( type char(6), st_id char(10), c_no char(10), oldscore int, newscore int, uname varchar(20) default 'user', udate datetime default getdate() )
Create a stored procedure:
create trigger tr_sc on s_c_info for update,insert as if (select count(*) from deleted)<>0 -- Description is an update operation insert into sc_log(type,st_id,c_no,oldscore,newscore) select 'update',s_c_info.st_id,s_c_info.c_no,deleted.score,inserted.score from s_c_info,deleted,inserted where s_c_info.st_id = deleted.st_id and s_c_info.c_no = deleted.c_no else insert into sc_log(type,st_id,c_no,newscore) select 'insert',inserted.st_id,inserted.c_no,inserted.score from inserted
Test: Calling a Stored Procedure
insert into s_c_info(st_id,c_no,score) values('0603060108','9720013',88) -- View Results select * from sc_log
update s_c_info set score = 99 where st_id = '0603060108' and c_no = '9720013' -- View Results select * from sc_log
2️⃣ Create a trigger tr_updasc on the s_c_info table to monitor the update of grades. It is required that the updated grades cannot be lower than the ones before the update. If the new grades are lower, the operation will be cancelled and a prompt will be given, otherwise the update is allowed.
create trigger tr_updasc on s_c_info for update as if update(score) begin declare @oldscore int declare @newscore int set @oldscore = (select score from deleted) set @newscore = (select score from inserted) if @oldscore > @newscore begin print('The new grade is not allowed to be lower than the old grade, and the update fails') rollback transaction end end
Test: Calling a Stored Procedure
update s_c_info set score = 98 where st_id = '0603060108' and c_no = '9720013'
(3) View storage
Use sp_helptext to view the code of stored procedures and triggers
sp_helptext 'stored procedure name'
🚩 Supplement: The demo SQL code I wrote when I was explaining at the Tencent conference
-- create procedure | proc stored procedure name -- @parameter name 1 {parameter data type}[=Defaults] [output], -- @parameter name 2 {parameter data type}[=Defaults] [output], -- .... --as -- SQL_statements -- Calculate the sum of two numbers create procedure cal @a int, -- input @b int, -- input @sum int output -- ginseng as set @sum = @a + @b go -- define variable declare @sum1 int -- call stored procedure execute: implement -- Execute the stored procedure syntax: exec stored procedure name parameter 1, parameter 2 [output] exec cal 5,4,@sum1 output -- If it is a parameter, you must write output select @sum1 go -- delete stored procedure -- drop table Table Name drop procedure cal go alter procedure cal @a int, -- input @b int -- input as select @a+@b go -- Change the stored procedure name -- grammar:[exec] sp_rename 'old stored procedure name','new stored procedure name' sp_rename 'cal','newCal' -- there will be warnings exec sp_rename 'newCal','cal' -- there will be warnings -- begin end alter procedure cal @a int, -- input @b int -- input as begin select @a+@b end -- exist`studentdb database`create stored procedure`getPractice`,Inquire about the designated department(name)(as an input parameter to a stored procedure)middle -- The student number, name, course number and course name of all students participating in the "practice" course. If the department does not exist, a prompt message will be returned. drop procedure getPractice -- varchar String type create procedure getPractice -- Designated Faculty(name)(as an input parameter to a stored procedure) @name varchar(24) -- mysql Medium means 24 means 24 characters sqlserver neither utf-8 as -- 1.Inquire about the students of the specified department --> D_ID -- 2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok -- 3.""Practice" course --> first look s_c_info Student-Course Correspondence -- 4.If the department does not exist, a prompt message is returned. if (select count(*) from D_Info where D_Name=@name) <> 0 -- Indicate that there are departments begin select st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name from st_info,s_c_info,C_Info where left(st_info.St_ID,2) = (select D_ID from D_Info where D_Name=@name) and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student and s_c_info.c_no=C_Info.C_No and C_Info.C_Type='practice' end else print('Faculty does not exist') go -- call stored procedure exec getPractice 'law school' exec getPractice 'material science and Engineering School' select count(*) from D_Info where D_Name='material science and Engineering School' select left('wkx cool',5) -- Rename: remember to write single quotes sp_rename 'getPractice','getPctStu', -- Stored procedure name: getPctStu -- Modify the stored procedure getPctStu,Returns the number of students participating in practical courses in a given department go alter procedure getPctStu -- Designated Faculty(name)(as an input parameter to a stored procedure) @name varchar(24) -- mysql 24 means 24 characters sqlserver neither utf-8 as -- 1.Inquire about the students of the specified department --> D_ID -- 2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok -- 3.""Practice" course --> first look s_c_info Student-Course Correspondence -- 4.If the department does not exist, a prompt message is returned. if (select count(*) from D_Info where D_Name=@name) <> 0 -- Indicate that there are departments begin select count(*) -- Number of query records(The same student number does not matter, it counts as multiple entries) from st_info,s_c_info,C_Info where left(st_info.St_ID,2) = (select D_ID from D_Info where D_Name=@name) and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student and s_c_info.c_no=C_Info.C_No and C_Info.C_Type='practice' end else print('Faculty does not exist') go -- And use the stored procedure to verify the result of the execution with "law school" as the input parameter go exec getPctStu 'law school' go -- Modify the stored procedure again getPctStu,Returns the number of students participating in practical courses in the specified department.--> A student can only appear once -- Original Title: Modifying Stored Procedures getPctStu,Returns the number of students participating in practical courses in a given department --> There can be multiple occurrences in the query table alter procedure getPctStu -- Designated Faculty(name)(as an input parameter to a stored procedure) @name varchar(24) -- mysql Medium means 24 means 24 characters sqlserver neither utf-8 as -- 1.Inquire about the students of the specified department --> D_ID -- 2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok -- 3.""Practice" course --> first look s_c_info Student-Course Correspondence -- 4.If the department does not exist, a prompt message is returned. if (select count(*) from D_Info where D_Name=@name) <> 0 -- Indicate that there are departments begin -- distinct st_info.St_ID --> Filter out the same student number and merge them into one record select count(distinct st_info.St_ID) from st_info,s_c_info,C_Info where left(st_info.St_ID,2) = (select D_ID from D_Info where D_Name=@name) and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student and s_c_info.c_no=C_Info.C_No and C_Info.C_Type='practice' end else print('Faculty does not exist') go -- sc_log In the table, sc_log The table has the following fields: -- Operation type type,student ID st_id,Course number c_no,old grades oldscore,new grade newscore,operator uname,Operation time udate, -- where the operator sets the default value as user,The operation time defaults to the system time. create table sc_log( type char(10), st_id char(10), c_no char(10), oldscore int, newscore int, -- uname varchar(24) default user, --Incorrect --> System keywords uname varchar(24) default 'user', udate datetime default getdate() -- in difference sql huge difference in function names ) -- exist studentdb Create a trigger with audit function in the database: -- trigger named tr_sc, -- Feature Request: Audit in s_c_info in the table score field update and insert operations, logging these operations to sc_log table -- create trigger trigger name -- on Table Name -- works on that table -- for [update,delete,insert] -- as -- SQL_statement -- When the trigger takes effect, there will be two temporary tables deleted inserted -- When deleting or updating operations: deleted For deletes, old data For updates, old data --> only one record -- When performing an insert or update operation: inserted new record -- only one record go create trigger tr_sc on s_c_info for update,insert as -- audit in s_c_info in the table score field update and insert operations, logging these operations to sc_log table --> log operations -- If it is an insert operation --> deleted table is empty -- If it is an update operation --> delete The table has an old piece of data if (select count(*) from deleted) != 0 -- update operation begin if update(score) -- if modified score field insert into sc_log(type,st_id,c_no,oldscore,newscore) -- There are default values, we don't need to write -- deleted.score correspond oldscore -- inserted score correspond newscore select 'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score from deleted,inserted end else -- Indicates that this is an insert operation begin -- No oldscore ,Then ignore it and do not write, the default value in the generated record is null insert into sc_log(type,st_id,c_no,newscore) -- type is the key of the database, so it is blue select 'insert',inserted.st_id,inserted.c_no,inserted.score from inserted end -- test:insert operation insert into s_c_info(st_id,c_no,score) values('0603060108','9720013',88) select * from sc_log; -- update record update s_c_info set score = 99 where st_id='0603060108' and c_no='9720013' select * from sc_log; -- exist s_c_info Create a trigger on the table tr_updasc,Used to monitor updates to grades(update) go -- end of previous statement create trigger tr_updasc on s_c_info for update as -- It is required that the score after the update cannot be lower than the score before the update. If the new score is lower, the operation will be cancelled and a prompt message will be given, otherwise the update is allowed. if update(score) -- It will only take effect if the grades are updated begin -- write separately declare @oldscore int declare @newscore int -- actual variable set @oldscore = (select score from deleted) set @newscore = (select score from inserted) if @newscore < @oldscore begin print('bad grades') rollback transaction -- Rollback, all operations will not take effect end end go -- End this trigger execution -- update record --sc_log update s_c_info set score = 100 where st_id='0603060108' and c_no='9720013' select * from s_c_info where st_id='0603060108' and c_no='9720013' -- Update record does not take effect --sc_log update s_c_info set score = 98 where st_id='0603060108' and c_no='9720013' select * from s_c_info where st_id='0603060108' and c_no='9720013' go sp_helptext tr_updasc -- show defined SQL statement go