存储过程--创建视图, 游标的用法,sql 中 not in(...)查询条件的用法

用存储过程实现.

1. 从数据库中查询出report_id, 可能有一个或者多个.

2. 创建视图. 创建视图的原因是, 游标中不能够动态传递参数. 所以, 最后只能通过创建视图的方式,把动态查询出来的数据保存到视图中, 游标遍历视图中的report_id.

3. 使用游标循环遍历report_id.用","分隔

4. 将遍历后的report_id去掉最后一个,

5. 将遍历得到report_id作为select查询语句的where report id not in(...)的条件.

BEGIN

DECLARE update_sql VARCHAR(1000);

DECLARE report_month varchar(6);

DECLARE temp_report_id VARCHAR(20)DEFAULT '';

DECLARE all_report_id VARCHAR(3000)DEFAULT '';

# 声明变量, 定义游标, 从视图读取Report_Id. 

DECLARE cur1 CURSOR FOR SELECT Report_Id FROM site_src_rpt_temp;

# MySQL 游标 异常后 捕捉 并设置 循环使用 变量 temp_report_id 为 null 跳出循环

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET temp_report_id = NULL;

SET @report_month = CONCAT(SUBSTR(report_date from 1 for 4),SUBSTR(report_date from 6 for 2));

#创建一个临时视图-组装游标的集合数据

DROP VIEW IF EXISTS site_src_rpt_temp; 

#创建一个视图, 读取report_id. 

SET @view_for_cur = CONCAT('create view site_src_rpt_temp as SELECT Report_Id FROM

site_src_rpt_',@report_month,'

WHERE

Report_Date=\'',report_date,'\' 

GROUP BY

Site_Id,

Report_Date,

Source_Type,

Source_Campaign_Id,

Source_Media_id,

Source_Ad_Pos_Id,

Report_Country,

Report_Province,

Report_City,

Site_Source_Code,

Site_Source_Slot_Code,

Source_Creative_Id,

Source_Sem_Plan_Id,

Source_Sem_Unit_Id,

Source_Sem_Keyword_Id');  

  

  PREPARE stmt FROM @view_for_cur;

  EXECUTE stmt;

  DEALLOCATE PREPARE stmt;

#打开游标

OPEN cur1;

#游标向下走一步

FETCH cur1 INTO temp_report_id;

# 组装sql, 要查询的表

SET @update_sql = CONCAT(

'UPDATE

site_src_rpt_',@report_month,'

SET 

Source_Campaign_Impression=0,Source_Campaign_Click=0

WHERE

Report_Date=\'',report_date,'\'

AND Report_Id NOT IN(');

/*SET @update_sql = CONCAT(

'SELECT

Source_Campaign_Impression,Source_Campaign_Click

FROM

site_src_rpt_',@report_month,'

WHERE

Report_Date=\'',report_date,'\'

AND Report_Id NOT IN('

);*/

# 循环遍历,把MySQL 游标查询出的 name 都加起并用 , 号隔开

WHILE(temp_report_id IS NOT NULL)DO

SET temp_report_id = CONCAT(temp_report_id, ",");

SET all_report_id = CONCAT(all_report_id, temp_report_id);

#游标向下走一步

FETCH cur1 INTO temp_report_id;

END WHILE;

#关闭游标

CLOSE cur1;

SELECT all_report_id;

# 去掉all_report_id的最后一个,

SET all_report_id = LEFT(

all_report_id,

char_length(all_report_id)- 1

);

SELECT all_report_id;

#组装查询的sql

SET @update_sql = CONCAT(@update_sql, all_report_id, ')');

PREPARE stmt FROM @update_sql;

EXECUTE stmt;

END