目录
/*触发器*/
CREATE DATABASE EDUC3;
USE EDUC3;
--1. 在数据库EDUC中，创建如教材的如下几个表：
-- student； course； sc
--STUDENT的结构
CREATE TABLE student(
sno CHAR(8) PRIMARY KEY,
sname CHAR(8) NOT NULL,
ssex CHAR(2),
sage INT,
sdept CHAR(10)
);
--COURSE的结构
CREATE TABLE course(
cno CHAR(2) PRIMARY KEY,
cname CHAR(30),
credit INT,
cpno CHAR(3)
);
--SC的结构
CREATE TABLE sc(
sno CHAR(8),
cno CHAR(2),
grade INT,
PRIMARY KEY (sno,cno),
FOREIGN KEY (sno) REFERENCES STUDENT(sno),
FOREIGN KEY (cno) REFERENCES COURSE(cno)
);
--2.向表中插入元组
--STUDENT的内容
INSERT INTO student VALUES ('95001','李勇','男',20,'CS'),
('95002','刘晨','女',19,'IS '),
('95003','王敏','女',18,'MA'),
('95004','张立','男',19,'IS '),
('95005','刘云云','女',18,'CS');
--COURSE的内容
INSERT INTO course VALUES ('1','数据库',4,'5'),
('2','数学',6,NULL),
('3','信息系统',3,'1'),
('4','操作系统',4,'6'),
('5','数据结构',4,'7'),
('6','数据处理',3,NULL),
('7','PASCAL语言',4,'6');
--SC的内容
INSERT INTO sc VALUES ('95001','1',92),
('95001','2',85),
('95001','3',88),
('95002','2',90),
('95002','3',80),
('95003','2',85),
('95004','1',58),
('95004','2',85);
-- 2.为student表创建一个名为t1的insert触发器，当向表进行插入操作时激发该触发器，并给出提示信息“有新成绩信息插入到student表中!”。
CREATE TRIGGER t1
ON student
FOR INSERT
AS PRINT '有新成绩信息插入到student表中!';
INSERT INTO student VALUES ('95006','XI','女',18,'CS');
--2. 要在educ库的Student表中插入下面所示的信息，如果在该表上未创建触发器，则可成功插入到表中。但我们希望插入的信息必须是“is” 系，否则就撤消刚才的插入，并返回错误信息。用触发器实现该约束.
CREATE TRIGGER t2
ON student
FOR INSERT
AS DECLARE @dept CHAR(20)
SELECT @dept = sdept FROM INSERTED
IF @dept != 'IS'
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('不能插入非IS的信息',16,10)
END;
--3. 为Student表创建一个名为t5的update触发器，当对该表的“姓名”列修改时激发该触发器，使用户不能修改“姓名”列。
CREATE TRIGGER t5 ON student
FOR UPDATE
AS IF UPDATE(sname)
BEGIN
ROLLBACK TRANSACTION
PRINT('不能修改姓名列')
END;
/*存储过程*/
CREATE DATABASE xmgl;
USE xmgl;
CREATE TABLE 部门表(
部门号 CHAR(11) PRIMARY KEY,
部门名 CHAR(10),
部门电话 CHAR(11),
部门地址 CHAR(40),
部门领导 CHAR(10),
部门人数 INT
);
CREATE TABLE 员工表(
员工号 CHAR(11) PRIMARY KEY,
姓名 CHAR(10),
性别 CHAR(4),
出生年月 DATE,
所在部门号 CHAR(11),
技术职称 CHAR(16),
工资 DECIMAL
);
CREATE TABLE 项目表(
项目编号 CHAR(11) PRIMARY KEY,
项目名称 CHAR(50),
所在地方 CHAR(40),
项目主管 CHAR(10),
开工日期 DATETIME,
完工日期 DATE
);
CREATE TABLE 员工参与项目表(
员工号 CHAR(11),
项目编号 CHAR(11),
职责 CHAR(10),
PRIMARY KEY(员工号,项目编号)
)
INSERT INTO 部门表(部门号,部门名,部门电话,部门地址,部门领导)
VALUES('1001','采购部','88921111','1苑1栋201室','2001'),
('1002','广告部','88922222','1苑1栋202室','2004'),
('1003','财务部','12345677','1苑1栋203室','2008'),
('1004','销售部','77623481','1苑1栋204室','2005'),
('1005','策划部','12234456','1苑1栋205室','2009'),
('1006','办公室','23345678','1苑1栋206室','2012'),
('1008','办公室','34578921','1苑1栋208室','2014');
INSERT INTO 员工表 VALUES
('2001','王毅','男','1905-5-20 00:00:00','1001','采购部长',4232),
('2002','张明','男','1905-5-24 00:00:00','1002','广告设计员',3422),
('2003','刘芳','女','1905-5-22 00:00:00','1003','出纳',2600),
('2004','李萍','男','1905-4-16 00:00:00','1002','广告部长',4600),
('2005','胡丽丽','女','1905-5-21 00:00:00','1004','销售部长',4900),
('2006','叶枫','男','1905-5-15 00:00:00','1005','策划员',3300),
('2007','林奇','男','1905-6-17 00:00:00','1001','采购员',2453),
('2008','楚楚','女','1950-5-8 00:00:00','1003','财务部长',4900),
('2009','高锐','男','1905-6-3 00:00:00','1005','策划部长',4900),
('2010','陈超','男','1905-5-13 00:00:00','1004','销售员',4427);
INSERT INTO 项目表 VALUES
('J001','采购建材','浙江省杭州市','2001','1945-8-1 00:00:00','1945-10-9 00:00:00'),
('J002','婴儿用品广告','浙江省温州市','2004','1945-4-1 00:00:00','1945-5-9 00:00:00'),
('J003','保健品广告','浙江省萧山市','2004','1954-3-28 00:00:00','1954-10-7 00:00:00'),
('J004','家电促销','浙江省宁波市','2005','1945-10-1 00:00:00','1945-10-7 00:00:00'),
('J005','公司装潢','上海市','2008','1945-6-1 00:00:00','1945-10-9 00:00:00'),
('J006','食品销售','浙江省台州市','2005','1945-2-14 00:00:00','1945-3-20 00:00:00'),
('J007','公司重组','浙江省杭州市','2009','1945-8-12 00:00:00','1945-8-15 00:00:00'),
('J008','采购瓷器','浙江省金华市','2001','1945-7-8 00:00:00','1945-8-1 00:00:00'),
('J009','公司采购','浙江省丽水市','2009','1945-10-11 00:00:00','1945-11-30 00:00:00'),
('J111','员工加薪','上海市','2008','1945-12-30 00:00:00','1945-12-30 00:00:00');
INSERT INTO 员工参与项目表 VALUES
('2001','J001','项目总监'),
('2001','J008','项目总监'),
('2002','J001','项目员工'),
('2002','J111','项目员工'),
('2002','J002','项目员工'),
('2002','J003','项目员工'),
('2002','J004','项目员工'),
('2002','J005','项目员工'),
('2002','J006','项目员工'),
('2002','J007','项目员工'),
('2002','J008','项目员工'),
('2002','J009','项目员工'),
('2003','J111','项目员工'),
('2003','J005','项目员工'),
('2004','J002','项目总监'),
('2004','J003','项目总监'),
('2005','J004','项目总监'),
('2006','J007','项目员工'),
('2006','J009','项目员工'),
('2008','J111','项目总监'),
('2008','J005','项目总监'),
('2009','J007','项目总监'),
('2009','J009','项目总监'),
('2010','J004','项目员工');
---1.创建并执行不带参数的存储过程针对项目表创建名为“p1_存储过程”的存储过程；
--执行“p1_存储过程”的存储过程进行数据浏览。
CREATE PROCEDURE p1_存储过程
AS
SELECT * FROM 项目表
GO
EXEC p1_存储过程;
--2.创建带OUTPUT输出参数的存储过程
--设计存储过程“p2_存储过程”，从员工表中计算某部门人员平均工资。
--要求输入参数为部门号，输出参数是该部门的平均工资。执行存储过程查看结果。
CREATE PROCEDURE p2_存储过程
@所在部门号 CHAR(11),@平均工资 DECIMAL output
AS
SELECT @平均工资 = AVG(CONVERT(DECIMAL,工资))
FROM 员工表
WHERE 所在部门号 = @所在部门号
GO
DECLARE @所在部门号 CHAR(11),@平均工资 DECIMAL
SET @所在部门号 = '1004'
EXEC p2_存储过程 @所在部门号,@平均工资 OUTPUT
PRINT '所在部门号'+@所在部门号 +'的平均工资是：' + CONVERT(VARCHAR,@平均工资);
--3.创建并执行带输入参数和返回状态的存储过程
--设计存储过程“p3_存储过程”，完成对员工表的元组的插入工作。要求使用输入参数。插入操作成功返回状态值，失败返回状态值-1。
--执行存储过程，如果返回状态值为，输出“插入成功”，否则输出“插入失败”。
CREATE PROCEDURE p3_存储过程
@员工号 CHAR(11),@姓名 CHAR(10),@性别 CHAR(4),@出生年月 DATE,
@所在部门号 CHAR(11),@技术职称 CHAR(16),@工资 DECIMAL OUTPUT
AS
BEGIN TRANSACTION
INSERT INTO 员工表 VALUES(@员工号,@姓名,@性别,@出生年月,@所在部门号,@技术职称,@工资)
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION RETURN -1
END
ELSE
BEGIN
COMMIT TRANSACTION RETURN 0
END
GO
DECLARE @工资 DECIMAL
EXEC @工资= p3_存储过程 '7960','席辰','男','2001-10-15','2333','250',@工资 output
IF @工资 = 0
PRINT '插入成功'
ELSE
PRINT '插入失败';
--4.修改和删除存储过程
--①修改"p1_存储过程"存储过程，要求指定项目编号作为输入参数，并增加WITH ENCRYPTION选项。
ALTER PROCEDURE p1_存储过程
@项目编号 CHAR(11)
WITH ENCRYPTION
AS BEGIN
SELECT * FROM 项目表 WHERE 项目编号 = @项目编号;
END
GO
EXEC p1_存储过程 J001;
--②查看修改后的"p1_存储过程"存储过程文本。
EXEC SP_HELPTEXT 'p1_存储过程';
--③执行"p1_存储过程"存储过程。
EXEC p1_存储过程 j001;
--④删除"p1_存储过程"存储过程。
DROP PROCEDURE p1_存储过程;
/*游标*/
--1.定义及使用游标
--针对员工表定义一个只读游标“CUR1_游标”，逐行显示员工的所有信息。
DECLARE CUR1_游标 CURSOR FORWARD_ONLY
FOR SELECT * FROM 员工表
OPEN CUR1_游标
FETCH FROM CUR1_游标
WHILE @@FETCH_STATUS=0
BEGIN
FETCH FROM CUR1_游标
END
CLOSE CUR1_游标
DEALLOCATE CUR1_游标;
--2.使用游标修改数据
--针对员工表定义一个“CUR2_游标”，将游标中绝对位置为3的员工姓名改为“杜兰特”，性别改为“男”。
DECLARE @姓名 CHAR(6)
DECLARE CUR2_游标 cursor SCROLL
FOR SELECT 姓名 FROM 员工表
OPEN CUR2_游标
FETCH ABSOLUTE 3 from cur2_游标 INTO @姓名
BEGIN
UPDATE 员工表 SET 姓名 = '杜兰特', 性别 = '男' WHERE 姓名 = @姓名
END
CLOSE CUR2_游标
DEALLOCATE CUR2_游标;
--3.使用游标删除数据
--定义一个游标“CUR3_游标”,将员工表中名为“杜兰特”的员工删除。
DECLARE @名字 CHAR(6)
DECLARE CUR3_游标 cursor SCROLL
FOR SELECT 姓名 FROM 员工表
OPEN CUR3_游标
FETCH FROM cur3_游标 INTO @名字
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@名字 = '杜兰特')
DELETE FROM 员工表 where 姓名 = @名字
FETCH FROM cur3_游标 INTO @名字
END
CLOSE CUR3_游标
DEALLOCATE CUR3_游标;
--4.设计嵌套游标
--设计嵌套游标“CUR41_游标”显示每个员工的员工号、员工姓名、技术职称、所在部门；内层游标“CUR42_游标”逐个显示当前员工所参加的项目的情况，包括项目号、项目名称、承担职责。
DECLARE @员工号 CHAR(11),@员工姓名 CHAR(10),@技术职称 CHAR(16),@所在部门号 CHAR(11)
DECLARE @项目编号 CHAR(11), @项目名称 CHAR(50), @职责 CHAR(10)
DECLARE cur41_游标 CURSOR
FOR SELECT 员工号,姓名,技术职称,所在部门号 FROM 员工表
OPEN cur41_游标
FETCH FROM cur41_游标 INTO @员工号,@员工姓名,@技术职称,@所在部门号
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @员工号+@员工姓名+@技术职称+@所在部门号
DECLARE cur42_游标 CURSOR
FOR SELECT @项目编号,@项目名称,@职责 FROM 项目表,员工参与项目表
WHERE 项目表.项目编号 = 员工参与项目表.项目编号
OPEN cur42_游标
FETCH FROM cur42_游标 INTO @项目编号,@项目名称,@职责
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @项目编号+@项目名称+@职责
FETCH FROM cur42_游标 INTO @项目编号,@项目名称,@职责
END
CLOSE cur42_游标
DEALLOCATE cur42_游标
FETCH FROM cur41_游标 INTO @员工号,@员工姓名,@技术职称,@所在部门号
END
CLOSE cur41_游标
DEALLOCATE cur41_游标
--5. 在员工表中增加一列“参加的项目总数”。创建游标“CUR5_游标”，利用游标在员工参与项目表中统计员工参加的项目数，然后将参加的数目填入员工表中的参加的项目总数列中。
ALTER TABLE 员工表 ADD 参加的项目总数 INT
GO
DECLARE @员工号 CHAR(11),@项目数 INT
DECLARE cur5_游标 CURSOR
FOR SELECT 员工号,COUNT(项目编号) AS 项目数 FROM 员工参与项目表
GROUP BY 员工号
OPEN CUR5_游标
FETCH NEXT FROM CUR5_游标 INTO @员工号,@项目数
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE 员工表 SET 参加的项目总数 = @项目数 WHERE 员工号 = @员工号
FETCH NEXT FROM CUR5_游标 INTO @员工号,@项目数
END
CLOSE CUR5_游标
DEALLOCATE CUR5_游标;
314
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
