Database experiment series 3 stored procedure and trigger experiment (stored procedure and trigger)

Time:2021-2-7

Experiment 9: stored procedure experiment

Write and debug 8 stored procedures, master the use of stored procedures. You can also use the assignment in the figure below.

SQL statement code

--1. Example 1
Use student homework management database;
Select * from student table;
Select * from curriculum;
Select * from students' homework list;
--First check whether there is a student with the name of student_ The stored procedure of course, if any, can be deleted
if exists (select name from  sysobjects where  name='student_course' and type='P')
drop procedure stuent_course;
--Create stored procedure
create procedure student_course
as
	Student number, name, course name, Assignment 1 score
	From student schedule, course schedule, student assignment list
	Student number = student work list. Student number and course schedule. Course number = student work list. Course number and name ='zhang Yan '

--Executing stored procedures
execute student_course;

--Stored procedure with input parameters
create procedure  student_course1
@Studentname varchar(10)
as
	Student number, name, course name, Assignment 1 score
	From student schedule, course schedule, student assignment list
	Student number = student work list. Student number and course schedule. Course number = student work list. Course number and name [email protected];

--Call student_ course1
execute student_ Course 1 'Yu Lan';

--Create a stored procedure to insert records into the student table
create procedure student_insert
@Student number int, @ name nvarchar (10), @ gender nvarchar (2), @ professional class nvarchar (10), @ date of birth nvarchar (20), @ contact number nvarchar (20)
as
	Insert into student table
	Values (@ student number, @ name, @ gender, @ professional class, @ date of birth, @ contact number);

--Execute student_ insert
execute student_ Insert '007', 'a gang', 'male', 'e-06', '2000-1-1', '13333333333';


--Create stored procedure,若没有给出学生姓名,则返回所有学生情况
create procedure student_course2
@StudentName nvarchar(20)=null
as
	if @StudentName is null
	begin 
		Student number, name, course name, Assignment 1 score
		From student schedule, course schedule, student assignment list
		Student number = student work list. Student number and curriculum. Curriculum number = student work list. Curriculum number
	end
	else
	begin
		Student number, name, course name, Assignment 1 score
		From student schedule, course schedule, student assignment list
		Student number = student work list. Student number and course schedule. Course number = student work list. Course number and name [email protected];
	end

execute student_ Course 2 'Zhang Zhiguo';
execute student_ Course2; -- use default parameter values


--Using output parameters
CREATE procedure student_count
@CourseName varchar(20),
@StudentSum int output
as
	select @StudentSum=COUNT(*)
	Student work list
	Course number = student's work list, course number and course name [email protected];


--For stored procedures with output parameters, corresponding variables need to be defined to receive parameter values returned from stored procedures
declare @StudentSum1 int;
execute student_ Count 'data structure' ,@StudentSum1  OUTPUT;
Select @ studentsum1 as the number of people who select the data structure;

--Create a stored procedure, output the basic situation of students
alter procedure student_query
@Student number int, @ name nvarchar (8) output, @ gender char (2) output
as
	Select @ name = name, @ gender = gender
	From student table
	Where student number = @ student number;

--Call the stored procedure to view the basic situation
Declare @ name 1 nvarchar (20);
Declare @ sex 1 char (2);
execute student_ Query '7', @ name 1 output, @ gender 1 output;
Select @ name 1 as student's name, @ gender 1 as student's gender;
Select * from student table;

--Deleting a stored procedure is
--Drop procedure stored procedure name;

--In the market database
use market;
--Stored procedure Shanghai to view Shanghai customer information
select * from Customers;
Insert into customers values (3, 'AMI', 'Shanghai');
create procedure shanghai 
as
	select * from Customers
	Where city ='shanghai '

execute shanghai;
--Stored procedure goods, view the specified commodity information, commodity number as the input parameter
select * from Goods;
Insert into goods values (1, 'toothpaste', 2.5, 'toothpaste factory', 400, 'on sale');
Insert into goods values (2, 'toothbrush', 5, 'toothbrush factory', 1200, 'hot sale');
create procedure cunchuGoods
@Item number int
as
	select * from Goods
	Where goodid = @ commodity number;

Execute cunchugoods @ commodity number = 2;
--The stored procedure goodsum is used to view the total order amount of all orders of the specified customer. The customer number is used as the input parameter and the total order amount is used as the output parameter
select * from Orders;
insert into Orders values(1,1,1,2,5,'2020-1-1');
insert into Orders values(2,1,2,2,10,'2020-1-1');
insert into Orders values(3,2,2,2,10,'2020-1-1');
create procedure cunchuGoodsSum
@Customer number int, @ total order amount float output
as 
	Select @ total order amount = order sum from orders
	where  Orders.CustomerID= @Customer number;

Declare @ total order amount float; -- don't forget to declare variables
Execute cunchugoodssum 2, @ total order amount output;
Select @ total order amount as' total order amount ';

--Stored procedure insert_ Goods, insert a record into the goods table
select * from Goods;
alter procedure insert_Goods
@Item number int,@商品名称 nvarchar(20),@价格 float,@供货商 nvarchar(20),@库存量 int,@商品状态 nvarchar(20)
as 
	insert into Goods(GoodID,Gname,Price,Provider,Stocks,Status) 
	Values (@ commodity number, @ commodity name, @ price, @ supplier, @ inventory, @ commodity status);

execute insert_ Goods 3, 'toothbrush cup', 10, 'toothbrush cup factory', 5, 'soon out of stock';
--Create stored procedureGoods_Orders1,查看任何指定货品的订单情况,包括订单号,订货客户姓名以及订货数量(使用输入参数)
select * from Orders;
select * from Goods;
select * from Customers;
CREATE procedure Goods_Orders1
@Designated goods nvarchar (20)
as
	select OrderID,Cname,Quantity from Orders,Goods,Customers
	where  Orders.GoodID=Goods .GoodID and  Orders.CustomerID=Customers .CustomerID AND   Goods.Gname= @Designated goods;

EXECUTE Goods_ Orders1 @ specified goods ='toothpaste ';


--Executing stored procedures,如果不给出参数则报错,如果希望不输入参数,即默认值,得到所有货品订单,则新建表Goods_Orders2
execute Goods_ Orders1 @ specified goods; -- error
create procedure Goods_Orders2
@Designated goods nvarchar (20)=null
as
	If @ the specified item is null
	begin 
		select OrderID,Cname,Quantity from Orders,Goods,Customers
		where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID
	end
	else
	begin
	select OrderID,Cname,Quantity from Orders,Goods,Customers
	where  Orders.GoodID=Goods .GoodID and  Orders.CustomerID=Customers .CustomerID AND   Goods.Gname= @Designated goods
	end
execute Goods_Orders2;
execute Goods_ Orders2 @ specified goods ='toothbrush ';

--Create stored procedureGoods_OrderSum,来获得某个货品的订单总额(使用输入输出参数)
alter PROCEDURE Goods_OrderSum
@Product name nvarchar (20), @ order total 1 float output
as
	Select @ order total 1 = sum (ordersum) from orders, goods
	where  Goods.GoodID=Orders . goodid and gname = @ product name;

Declare @ order amount 1 float;
execute Goods_ Ordersum 'toothpaste', @ total order amount 1 output;
Select @ order total 1 as' order total ';

在这里插入图片描述在这里插入图片描述在这里插入图片描述在这里插入图片描述在这里插入图片描述在这里插入图片描述在这里插入图片描述在这里插入图片描述

Experiment 10: trigger experiment

Self designed topic to complete the preparation and debugging of 5 triggers, master the use of triggers. You can also use the assignment in the figure below.

SQL statement code

Use student homework management database;
--Example 8 create a trigger to display all the records in the table when the records in the student table are updated
create trigger  student_change
	On student table after insert, update, delete
	as
		Select * from student table;

--Check out the changes
Select * from student table;
Insert into student table values (1,'may ',' female ',' planning 06 ',' 2002-1-1 ',' 13312313213 ');

--Example 9 creates a delete trigger in the student table to realize the cascade deletion of the student table and the student assignment table
Create trigger studentdelete on student table
	after delete
as
	Delete from student worksheet
	Where student number in
		(select deleted. Student number from deleted);
--Check out the changes
Select * from students' homework list;
Insert into student assignment table values ('k001 ', 1,99,99,99');
Select * from students' homework list;
Delete from student form where name ='may ';
Select * from students' homework list;

--Example 10 create an insert trigger on the student's assignment table. When adding the student's course selection record to the student's assignment table,
--Check whether the student's student number exists. If not, the record cannot be inserted
create trigger sc_ Insert on student work list
	after insert
as
	If (select count (*) from student table, inserted where student table. Student number = inserted. Student number) = 0
	begin 
		Print 'student number does not exist, cannot insert'
		rollback transaction
	end;

--View the effect
Insert into student assignment table values ('k001 ', 1,99,99,99');

--Example 11 create an update trigger to prohibit modifying the gender of students in the student table
create trigger student_ Update on student table
	after update
as 
	If update (gender)
	begin
		Print 'no modification of student number'
		rollback transaction
	end;
--View the effect
Select * from student table;
Update student table set gender ='male 'where gender ='female' and student number = 7;
Select * from student table;

--Example 12: create a trigger on the student's assignment table. When multiple records are added to the student's assignment table at one time, delete the record whose student number does not exist in the student's assignment table,
--So as to ensure the consistency of the data, note that foreign key constraints cannot be defined in the student's Homework table
create trigger sc_ Insert1 on student work list 
	after insert
as 
	If (select count (*) from student table, inserted where inserted. Student number = student table. Student number) < > @ @ rowcount
	BEGIN
		Delete from student worksheet
		Where student number not in (select student number from student table)
	END;


--Example 13 define instead of trigger on view
Select * from student table;
create view birth_ View (student number, name, gender, birthday, professional class)
as 
	Select student number, name, gender, date of birth, professional class
	From student table;

create trigger birth_view_insert on birth_view
instead of insert
as
	Declare @ student id int
	Declare @ name varchar (20);
	Declare @ gender varchar (20);
	Declare @ birthday varchar (20);
	Declare @ professional class varchar (20);
	Select @ student number = student number, @ name = name, @ gender = gender, @ professional class = professional class
	from inserted;
	Insert into student table (student number, name, gender, professional class) values (@ student number, @ name, @ gender, @ professional class));
--View the effect
insert into birth_ View (student number, name, gender, professional class) values (2,'a Hong ',' female ',' software 04 ');



use market;
--Chapter 4 question 5 (5) set up delete trigger on Customers table to realize cascading delete of customers table and orders table
select * from Customers;
select * from Orders;

create trigger customers_delete on Customers
	after delete
as
	delete from Orders
	where CustomerID in (select deleted.CustomerID FROM DELETED);

--When an order record is added to the orders table, if the status of the goods in the order is about to be out of stock (status ='About to be out of stock ')
--The record cannot be inserted
select * from Customers;
select * from Orders;
select * from Goods;
CREATE trigger orders_insert on Orders
	AFTER INSERT
AS 
	if(select Status from Goods,inserted where  Goods.GoodID=inserted . goodid) in ('out of stock ')
	begin
		Print 'out of stock, cannot order'
		rollback transaction
	end;
	
--Try the effect
insert into Orders values(4,3,3,2,20,'2020-2-2');
--In Chapter 4, question 5 (7), the insertion trigger is established on the orders table. When an order is added, the inventory of the corresponding goods in the goods table is reduced
select * from Customers;
select * from Orders;
select * from Goods;

create trigger orders_insert1 on Orders
after insert
as 
	UPDATE Goods SET Stocks=Stocks-inserted.Quantity
	FROM Goods,inserted
	WHERE Goods.GoodID=inserted.GoodID;
	


--Chapter 4 question 5 (8) set up a trigger on the orders table. It is not allowed to modify the order date
select * from Customers;
select * from Orders;
select * from Goods;
CREATE TRIGGER orders_create ON Orders
	AFTER UPDATE
as
	IF UPDATE(Date)
	BEGIN
	Raiserror ('cannot be modified manually ', 10,1)
	ROLLBACK TRANSACTION
	END;

--In Chapter 4, question 5 (9), a trigger is established to implement the referential integrity constraint, that is, if a record is added to the orders table, the goods in the order must also be in the
--There is a record in the goods table, otherwise the record cannot be added;
select * from Customers;
select * from Orders;
select * from Goods;

CREATE TRIGGER orders_create1 ON Orders
	AFTER INSERT
AS
	IF (SELECT COUNT(*) from Goods,inserted
		WHERE Goods.GoodId=inserted.GoodID)=0
	BEGIN
		The goods do not exist
		rollback transaction
	END;

在这里插入图片描述