存储过程和函数

编译后的sql脚本集合，可以单独调用。外包公司负责衍生业务，调用存储过程不会对数据库造成破坏。存储过程编译后的sql，执行速度快。实现了sql变成可以降低

锁表时间和锁表的范围。可以保存查询结果，降低锁表范围。

编写案例如下：

根据部门名称，查询部门用户信息

CREATE DEFINER=`root`@`localhost` PROCEDURE `NewProc`(IN `p_dname` varchar(20))
BEGIN
    #Routine body goes here...
    e.id,e.ename,e.sex
    SELECT FROM t_emp e JOIN t_dept d on e.dept_id =d.id JOIN 
    t_job j on e.job_id = j.id 
    where d.dname = p_dname;
END

 

案例插入实习员工数据时根据员工性别，如果是男性就分配到网商部门实习；如果是女性就分配到零售部实习

CREATE DEFINER=`root`@`localhost` PROCEDURE `Proc2`(IN `p_wid` varchar(20),IN `p_ename` varchar(20),IN `p_sex` char(1),IN `p_married` boolean,IN `p_education` tinyint,IN `p_tel` varchar(20))
BEGIN
    #Routine body goes here...
DECLARE dept_id INT;
CASE 
    WHEN p_sex='女' THEN
        set dept_id = 3;
    ELSE
        set dept_id = 4;
END CASE;
INSERT INTO t_emp(wid,ename,sex,married,education,tel,dept_id,hiredate,job_id,`status`,is_deleted)
VALUES (p_wid,p_ename,p_sex,p_married,p_education,p_tel,dept_id,CURDATE(),9,1,0);

END

CALL Proc2("TE003","陈婷婷","女",false,1,"15327327002");

 

函数

编写计算个税函数

个数计算公式=（收入-3500）*税率 - 扣除数

CREATE DEFINER=`root`@`localhost` FUNCTION `func1`(`salary` decimal) RETURNS decimal(20,2)
BEGIN
    #Routine body goes here...
    DECLARE temp DECIMAL;
    DECLARE tax DECIMAL;
    SET temp = salary - 3500;
    CASE
        WHEN temp <=0 THEN
            SET tax = 0;
        WHEN temp <=1500 AND temp >0 THEN
            SET tax = temp * 0.03 -0;
        WHEN temp >1500 AND temp <=4500 THEN
            SET tax = temp * 0.10 -105;
        WHEN temp >4500 AND temp <=9000 THEN
            SET tax = temp * 0.20 -555;
        WHEN temp >9000 AND temp <=35000 THEN
            SET tax = temp * 0.25 -1005;
        WHEN temp >35000 AND temp <=55000 THEN
            SET tax = temp * 0.30 -2755;
        WHEN temp >55000 AND temp <80000 THEN
            SET tax = temp * 0.35 -5505;
        ELSE
            SET tax = temp * 0.45 - 5505;
    END CASE;
    RETURN tax;
END

 编写触发器

修改触发器，同步更新该员工的dept_id字段

UPDATE t_emp SET dept_id = NEW.id WHERE t_emp.dept_id = OLD.id

触发器，函数，存储过程与数据库集群不兼容，所以在数据库集群环境基本放弃。