sql过程、函数、触发器语句练习_sql触发器题目-优快云博客

本文链接：https://blog.youkuaiyun.com/vgfvgf/article/details/129948252

过程和函数

1. 根据编号查询名字【传入编号，获取名字】

2. 根据编号查询vip的名字(传入编号，获取名字和性别)

3. 创建过程，根据输入的成绩编号【pk】，获取该学号，科目号以及成绩

4. 创建过程，根据输入的成绩编号【pk】，获取该学生名字，科目名号以及成绩

5. 输入员工号，查找员工名字，上级领导名字以及工资

7. 使用存储函数|过程传入学号，获取学生的姓名，总分，平均分

 --1. 根据编号查询名字【传入编号，获取名字】 
 --过程：
 delimiter$$
			CREATE PROCEDURE demo1(v_id INT , OUT v_name VARCHAR(50))
			 BEGIN
			 SELECT vipname into v_name FROM vip WHERE vipid = v_id; 
 END$$
 delimiter ;
 CALL demo1(1,@vname);
 SELECT @vname;
 
 --函数
DELIMITER $$
CREATE FUNCTION fun1(v_id INT) RETURNS VARCHAR(50)
BEGIN
DECLARE vname VARCHAR(50);
SELECT vipname INTO vname FROM vip WHERE vipid = v_id;
RETURN vname;
END$$
DELIMITER ;
SELECT fun1(1) vipname;
 --2. 根据编号查询vip的名字(传入编号，获取名字和性别) 
 --过程
  delimiter$$
			CREATE PROCEDURE demo2(v_id INT , OUT v_name VARCHAR(50),OUT v_sex VARCHAR(50))
			 BEGIN
			 SELECT vipname,vipsex into v_name,v_sex FROM vip WHERE vipid = v_id; 
 END$$
 delimiter ;
 CALL demo2(1,@vname,@vsex);
 SELECT @vname,@vsex; 
 --函数
 USE pppp;
 DELIMITER $$
CREATE FUNCTION fun2(v_id INT,vname VARCHAR(50),vsex VARCHAR(50)) RETURNS VARCHAR(50)
BEGIN
SELECT vipname,vipsex INTO vname,vsex FROM vip WHERE vipid = v_id;
RETURN CONCAT(vname,'--',vsex);
END$$
DELIMITER ;
DROP FUNCTION fun2;
SELECT fun2(1,@vname,@vsex) '名字--性别';
 --3. 创建过程，根据输入的成绩编号【pk】，获取该学号，科目号以及成绩 
 USE myschool;
 --过程
 delimiter$$
			CREATE PROCEDURE demo3(mid INT , OUT sno VARCHAR(50),OUT sid INT,OUT sresult INT)
			 BEGIN
			  SELECT StudentNo,SubjectId,StudentResult INTO sno,sid,sresult FROM result WHERE Id = mid;
 END$$
 delimiter ;
 DROP PROCEDURE demo3;
 CALL demo3(1,@sno,@sid,@sresult);
 SELECT  @sno,@sid,@sresult;
 --函数
  DELIMITER $$
CREATE FUNCTION fun3(mid INT,sno VARCHAR(50),sid INT,sresult INT) RETURNS VARCHAR(50)
BEGIN
SELECT StudentNo,SubjectId,StudentResult INTO sno,sid,sresult FROM result WHERE Id = mid;
RETURN CONCAT(sno,'--',sid,'--',sresult);
END$$
DELIMITER ;
DROP FUNCTION fun3;
SELECT fun3(1,@sno,@sid,@sresult) '学号--id--成绩';
 --4. 创建过程，根据输入的成绩编号【pk】，获取该学生名字，科目名号以及成绩 
 --过程
  delimiter$$
			CREATE PROCEDURE demo4(mid INT , OUT sname VARCHAR(50),OUT subname VARCHAR(50),OUT sresult INT)
			 BEGIN
			 DECLARE sid int;
			 DECLARE sno VARCHAR(50);
			  SELECT SubjectId,StudentNo,StudentResult INTO sid,sno,sresult FROM result WHERE Id = mid;
				SELECT SubjectName INTO subname FROM subjects WHERE SubjectId = sid;
				SELECT StudentName INTO sname FROM student WHERE StudentNo = sno;
 END$$
 delimiter ;
 DROP PROCEDURE demo4;
 CALL demo4(1,@sname,@subname,@sresult);
 SELECT  @sname,@subname,@sresult;
 --函数
   DELIMITER $$
CREATE FUNCTION fun4(mid INT,sname VARCHAR(50), subname VARCHAR(50), sresult INT)RETURNS VARCHAR(50)
BEGIN
DECLARE sid int;
			 DECLARE sno VARCHAR(50);
			  SELECT SubjectId,StudentNo,StudentResult INTO sid,sno,sresult FROM result WHERE Id = mid;
				SELECT SubjectName INTO subname FROM subjects WHERE SubjectId = sid;
				SELECT StudentName INTO sname FROM student WHERE StudentNo = sno;
RETURN CONCAT(sname,'--',subname,'--',sresult);
END$$
DELIMITER ;
DROP FUNCTION fun4;
SELECT fun4(1,@sname,@subname,@sresult) '姓名--科目--成绩';
 --5. 输入员工号，查找员工名字，上级领导名字 以及 工资 
 --过程
 USE mytest;
   delimiter$$
			CREATE PROCEDURE demo5(eid INT , OUT ename VARCHAR(50),OUT mname VARCHAR(50),OUT esalary INT)
			 BEGIN
			 DECLARE mid int;
			  SELECT CONCAT(first_name,'-',last_name),manager_id,salary INTO ename,mid,esalary FROM employees WHERE employee_id  = eid;
				SELECT CONCAT(first_name,'-',last_name)  INTO mname FROM employees WHERE employee_id  = mid;
 END$$
 delimiter ;
 DROP PROCEDURE demo5;
 CALL demo5(105,@ename,@mname,@esalary);
 SELECT  @ename,@mname,@esalary;
 --函数
    delimiter$$
			CREATE FUNCTION fun5(eid INT ,  ename VARCHAR(50), mname VARCHAR(50), esalary INT)RETURNS VARCHAR(50)
			 BEGIN
			 DECLARE mid int;
			  SELECT CONCAT(first_name,'-',last_name),manager_id,salary INTO ename,mid,esalary FROM employees WHERE employee_id  = eid;
				SELECT CONCAT(first_name,'-',last_name)  INTO mname FROM employees WHERE employee_id  = mid;
				RETURN CONCAT(ename,'-',mname,'-',esalary);
 END$$
 delimiter ;
 DROP FUNCTION fun5;
 SELECT fun5(105,@ename,@mname,@esalary) '员工名-领导名-工资';
 --7. 使用存储函数|过程 传入学号，获取学生的姓名，总分，平均分
USE myschool;
--过程	
 delimiter$$
 CREATE PROCEDURE demo7(sno VARCHAR(50),OUT sname VARCHAR(50),OUT sumscore INT,OUT  avgscore INT)
 BEGIN
		SELECT StudentName INTO sname FROM student WHERE StudentNo = sno;
		SELECT SUM(StudentResult),AVG(StudentResult) INTO sumscore, avgscore FROM result WHERE StudentNo = sno GROUP BY StudentNo;
 END$$
 delimiter ;
 drop PROCEDURE demo7;
 CALL demo7('s1001',@sname,@sumscore,@avgscore);
 SELECT @sname,@sumscore,@avgscore;
 --函数
  delimiter$$
 CREATE FUNCTION fun7(sno VARCHAR(50), sname VARCHAR(50), sumscore INT,  avgscore INT)RETURNS VARCHAR(50)
 BEGIN
		SELECT StudentName INTO sname FROM student WHERE StudentNo = sno;
		SELECT SUM(StudentResult),AVG(StudentResult) INTO sumscore, avgscore FROM result WHERE StudentNo = sno GROUP BY StudentNo;
		RETURN CONCAT(sname,'-',sumscore,'-',avgscore);
 END$$
 delimiter ;
 SELECT fun7('s1001',@sname,@sumscore,@avgscore) '名字-总分-平均分';

过程

8. 输入，科目名字，学时，以及gradeid ，将该数据插入到数据库

9. 根据传入的科目编号删除科目信息，如果该科目以及被考过试，则不能删除

--8. 输入，科目名字，学时，以及gradeid ，将该数据插入到数据库 
  delimiter$$
 CREATE PROCEDURE demo8(sid INT,sname VARCHAR(50), shour INT, gid INT)
 BEGIN
		INSERT INTO subjects VALUES(sid,sname,shour,gid);
		 SELECT * FROM subjects;
 END$$
 delimiter ;
 drop PROCEDURE demo8;
 CALL demo8(6,'近代史',48,2);
 --9. 根据传入的科目编号删除科目信息，如果该科目已经被考过试，则不能删除 
 delimiter$$
 CREATE PROCEDURE demo9(sid INT)
 BEGIN
 DECLARE scount INT;
	   SELECT COUNT(1) into scount FROM result WHERE SubjectId = sid;
		 IF scount = 0 THEN
		    DELETE FROM subjects WHERE SubjectId = sid;
				END if;
				SELECT * FROM subjects;
 END$$
 delimiter ;
 drop PROCEDURE demo9;
 CALL demo9(5);

触发器

10. 自动更新会员等级， -- 总金额达到2000的时候，等级编号变为2 -- 总金额达到5000的时候，等级编号变为3 -- 总金额达到10000的时候，等级编号变为4 -- 总金额达到20000的时候，等级编号变为5

USE pppp;
 delimiter$$
 CREATE TRIGGER setlevel AFTER INSERT ON sales FOR EACH ROW
 BEGIN 
 DECLARE summoney INT;
 SELECT SUM(smoney) into summoney FROM sales WHERE vipid = new.vipid;
 IF summoney >= 20000 THEN
  update vip SET viplevel = 5 WHERE vipid = new.vipid;
	ELSEif summoney >= 10000 THEN
  update vip SET viplevel = 4 WHERE vipid = new.vipid;
	ELSEif summoney >= 5000 THEN
  update vip SET viplevel = 3 WHERE vipid = new.vipid;
	ELSEif summoney >= 2000 THEN
	update vip SET viplevel = 2 WHERE vipid = new.vipid;
	END if;
 END$$
 delimiter ;
 INSERT INTO sales VALUES(1,2000,null,1);
 SELECT * FROM vip WHERE vipid =1;
 INSERT INTO sales VALUES(2,3000,null,1);
  SELECT * FROM vip WHERE vipid =1;