MySQL 存储过程，视图，临时表，游标可能遇到的坑

时间:2019-02-18 12:53 来源:未知作者:IT

MySQL 存储过程，视图，临时表，游标可能遇到的坑
MySQL 存储过程，视图，临时表，游标可能遇到的坑
MySQL创建视图报错
MySQL视图不可更新情况
临时表不能reopen
存储过程中使用游标一定要在最开始声明
游标的使用
MySQL存储过程中if的使用
计算字符串中某个字符个数
MySQL创建视图报错
View's SELECT contains a subquery in the FROM clause

MySQL不支持子查询，解决办法就是把子查询分成多个视图，然后再把其它视图当表一样关联查询

    -- 人员视图
    CREATE OR REPLACE VIEW view_pem_emp AS SELECT se.user_no,se.emp_name,se.dept_no from sys_employee as se WHERE se.tenants_no = 5 AND emp_status = 2;

    -- 部门视图
    CREATE OR REPLACE VIEW view_pem_dept AS SELECT sd.id AS dept_no,sd.dept_name,sd.parent_id from sys_department as sd WHERE sd.tenants_no = 5 AND is_enable = 1;

    -- 订单按员工统计视图
    CREATE OR REPLACE VIEW view_pem_sales_emp AS SELECT ps.user_no,SUM(ps.buy_amount)AS money,SUM(ps.buy_share)AS share FROM pem_sales as ps WHERE ps.tenants_no = 5 GROUP BY ps.user_no;

    -- 订单按部门统计视图
    CREATE OR REPLACE VIEW view_pem_sales_dept AS SELECT ps.dept_no,SUM(ps.buy_amount)AS money,SUM(ps.buy_share)AS share FROM pem_sales as ps WHERE ps.tenants_no = 5 GROUP BY ps.user_no;

    -- 部门订单树视图
    CREATE OR REPLACE VIEW view_pem_tree_sales_dept AS SELECT vd.dept_no as id,vd.dept_name as name,vd.parent_id,vse.money,vse.share FROM view_pem_dept AS vd LEFT JOIN view_pem_sales_dept AS vse ON vd.dept_no = vse.dept_no;

MySQL视图不可更新情况
1.包含以下关键字的sql语句：聚合函数(sum、min、max、count)、distinct、group
by 、having、union或者uinon all
2.常量视图
3.select
中包含子查询
4.join
5.from一个不可更新的视图
6.where字句的子查询引用了from字句中的表

临时表不能reopen
引擎类型只能是：memory（heap）、myisam、merge、innodb
不支持mysql cluster
同一个查询语句中只能引用一次！如 SELECT * FROM TP_TABLE , TP_TABLE AS ALIAS_NAME;　　是错误的
同一个用户存储函数中只能引用一次！

存储过程中使用游标一定要在最开始声明
游标的使用
-- 声明游标
DECLARE cur CURSOR FOR (SELECT s.parent_id FROM view_pem_tree_sales_dept AS s GROUP BY s.parent_id ORDER BY s.parent_id DESC);
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

-- 打开游标
    open cur;
        emp_loop: LOOP
            FETCH cur into parentId;
            if done = 1 THEN
                LEAVE emp_loop;
            end if;
            -- 统计算法
      set vSQL = '';
            set vSQL = CONCAT(vSQL, 'UPDATE pem_tmp_report SET money = (');
            set vSQL = CONCAT(vSQL, 'select a.money FROM(SELECT SUM(money) AS money FROM pem_tmp_report WHERE parent_id = ');
      set vSQL = CONCAT(vSQL, parentId,')a),share = (');
      set vSQL = CONCAT(vSQL, 'select b.share FROM(SELECT SUM(share) as share FROM pem_tmp_report WHERE parent_id=',parentId,')b) where id = ',parentId);
            set @sqltext:=vSQL;
            prepare stmt from @sqltext;
            execute stmt;

        end LOOP;
    CLOSE cur;

MySQL存储过程中if的使用
LOCATE(substr,str)

POSITION(substr IN str)

函数返回子串substr在字符串str中第一次出现的位置。如果子串substr在str中不存在，返回值为0。

set vSQL = '';
    set vSQL=CONCAT(vSQL,'CREATE OR REPLACE VIEW view_pem_sales_dept AS SELECT ps.dept_no,SUM(ps.buy_amount)AS money,SUM(ps.buy_share)AS share FROM pem_sales as ps WHERE ps.tenants_no = ',tenantsNo,' AND IF(locate("',fundType,'","-1")=0, ps.fund_type in (',fundType,') , 1=1) and IF(locate("',salesStatus,'","-1")=0, ps.status in (',salesStatus,') , 1=1) and IF(locate("',timeBegin,'","0000-00-00")=0, ps.buy_date >= "',timeBegin,'" , 1=1) and IF(locate("',timeEnd,'","0000-00-00")=0, ps.buy_date <= "',timeEnd,'" , 1=1) GROUP BY ps.user_no');
    set @sqltext4:=vSQL;
    prepare stmt4 from @sqltext4;
    execute stmt4;

计算字符串中某个字符个数
LENGTH(treepath)-LENGTH(REPLACE(treepath,"O",""))

(责任编辑：IT)

MySQL 存储过程，视图，临时表，游标可能遇到的坑

时间:2019-02-18 12:53 来源:未知 作者:IT

时间:2019-02-18 12:53 来源:未知作者:IT