|
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) |