数据库开发

(1)创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程。
USE YGGL
DELIMITER $$
CREATE PROCEDURE TEST(OUT NUMBER1 INTEGER)
BEGIN
DECLARE NUMBER2 INTEGER;
SET NUMBER2=(SELECT COUNT(*) FROM Employees);
SET NUMBER1=NUMBER2;
END$$
DELIMITER;
调用该存储过程:
CALL TEST(@NUMBER);
查看结果:
selct @NUMBER;
(2)创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，
否则输出1。
DELIMITER $$
CREATE PROCEDURE
COMPA(in ID1 CHAR(6), IN ID2 CHAR(6), oUT BJ INTEGER)
BEGIN
DECLARE SR1,SR2 FLOAT(8);
SELECT InCome-OutCome INTO SR1 FROM Salary WHERE EmployeelD=ID1;
SELECT InCome-OutCome INTO SR2 FROM Salary WHERE EmployeeID=ID2;
IF ID1>ID2 THEN
SET BJ=0;
ELSE
SET BJ=1;
END IF;
END$$
DELIMITER;
调用该存储过程:
CALL COMPA(‘000001', '108991',@BJ);
查看结果:
select @BJ;
(1)创建触发器，在Employees表中删除员工信息的同时将Salary 表中该员工的信息删除，以确
保数据完整性。
DELIMITER $$

CREATE
    TRIGGER `aaa`.`DELETE_EM` AFTER DELETE
    ON `aaa`.`employees`
    FOR EACH ROW BEGIN
DELETE FROM `salary`
WHERE `employeeid`=old.`employeeid`;
    END$$

DELIMITER ;
(2)假设Departments2表和Departments表的结构和内容都相同，在Departments上创建一个触发
器，如果添加一个新的部门，该部门也会添加到Departments2表中。
DELIMITER $$
CREATE TRIGGER Departments Ins
AFTER INSERT ON Departments FOR EACH ROW
BEGIN
INSERT INTO Departments2 VALUES(NEW.DepartmentID, NEW.Department Name,NEW.Note);
END$$
DELIMITER;
(3)当修改Employees表时，若将Employes表中员工的工作时间增加1年，则将收入增加500
元，若工作时间增加2年则收入增加1000元，依次增加。若工作时间减少则无变化。
DELIMITER $$
CREATE TRIGGER ADD SALARY
AFTER UPDATE ON Employees FOR EACH ROW
BEGIN
DECLARE YEARS INTEGER;
SET YEARS= NEW.WorkYear-OLD.WorkYear;
IF YEARS>0 THEN
UPDATE Salary SET InCome=InCome+500*YEARS
WHERE EmployeeID =NEW.EmployeeID;
END IF;
END$$
DELIMITER;
a.创建UPDATE触发器，当Departments表中部门号发生变化时，Employees表中员工所属的部门号也将改变。
DELIMITER $$

CREATE
  
    TRIGGER `aaa`.`xm` AFTER UPDATE
    ON `aaa`.`departments`
    FOR EACH ROW BEGIN
UPDATE  `employees`   
    SET `departmentid`=new.`departmentid`
    WHERE `departmentid`=old.`departmentid`
 END$$
DELIMITER ;
a.创建UPDATE触发器，当Salary 表中的InCome值增加500时，OutCome值也增加500
DELIMITER $$

CREATE
   
    TRIGGER `aaa`.`dd` BEFORE UPDATE
    ON `aaa`.`salary`
    FOR EACH ROW BEGIN
DECLARE n INT;
DECLARE s FLOAT;
SET s=new.`income`-old.`income`;
SET n=s/500;
IF  n>=1 THEN 
SET new.`outcome`=old.`outcome`+500;
END IF;
    END$$

DELIMITER ;
创建hpxxbcp存储过程，完成hpxxb的增、删、改操作
DELIMITER $$

 CREATE ` PROCEDURE `hpxxbcp`(
`hpid` INT,
`hpmc` VARCHAR(50),
`hptm` VARCHAR(50),
`jldw` VARCHAR(50),
`hpzt`  TINYINT,
`dj` DECIMAL(18,3),
`cxj`  DECIMAL(18,3)  
    )
BEGIN
    /*增加货品*/
IF hpid=0 THEN 
INSERT INTO `tb_hpxxb`(`hpmc`,`hptm`,`jldw`,`dj`,`cxj`,`mcsx`)
VALUES(`hpmc`,`hptm`,`jldw`,`dj`,`cxj`,pysxcx(hpmc));
SET @hpid=@@identity;
INSERT INTO  `tb_hpkcb`(`hpid`,`kcsl`,`zrkc`,`yckc`)
VALUES(@hpid,0,0,0);
/*修改*/
ELSEIF hpid>0 THEN
UPDATE `tb_hpxxb` SET
`tb_hpxxb`.`hpmc`=hpmc,
`tb_hpxxb`.`hptm`=hptm,
`tb_hpxxb`.`jldw`=jldw,
`tb_hpxxb`.`hpzt`=hpzt,
`tb_hpxxb`.`dj`=dj,
`tb_hpxxb`.`cxj`=cxj,
`tb_hpxxb`.`mcsx`=pysxcx(hpmc)
WHERE `tb_hpxxb`.`hpid`=hpid;
ELSE 
DELETE FROM `tb_hpkcb` WHERE `tb_hpkcb`.`hpid`=-hpid;
DELETE FROM `tb_hpxxb` WHERE `tb_hpxxb`.`hpid`=-hpid;
END IF;
    END */$$
DELIMITER ;
创建hpxxbcx存储过程，完成hpxxb的查询操作
DELIMITER $$

CREATE
    PROCEDURE `csgw`.`hpxxcx`(hpid INT)
   
    BEGIN
/*查询全部货品*/
IF hpid=0 THEN
SELECT * FROM tb_hpxxb;
ELSE
/*根据hpid查询货品信息*/
SELECT * FROM tb_hpxxb WHERE tb_hpxxb.hpid=hpid;
END IF;
    END$$

DELIMITER ;