mysql stored procedures, temporary tables, cursor

2011-08-30  来源:本站原创  分类:Database  人气:92 

CREATE DEFINER = `lifunet `@`%` PROCEDURE` proc_rank_article `(
in inter int
)
begin
declare temp_reply_article_id int;
declare temp_reply_total int default 0;
declare temp_article_good int default 0;
declare temp_article_sort int default 0;
declare done boolean;
-
declare tmp_article_cur cursor for select reply_article_id from tmp_article_table;
-
declare continue handler for not found set done = true;

drop temporary table if exists tmp_article_table;
drop temporary table if exists tmp_article_static_table;
create temporary table tmp_article_table (reply_article_id int);
create temporary table tmp_article_static_table (article_id int, good_click int, replyTotal int, article_sort int);
if (inter is not null) then
if (inter = 0) then
set inter = 7;
end if;
if (inter = 1) then
set inter = 360;
end if;
if (inter = 2) then
set inter = 30;
end if;
insert into tmp_article_table (reply_article_id) select reply_article_id from t_article where reply_article_id is not null and create_time> DATE_ADD (now (), INTERVAL-inter DAY) order by create_time desc limit 10000;
-
start transaction;
open tmp_article_cur;
tmp_article_cur_loop: loop
fetch tmp_article_cur into temp_reply_article_id;
if done then
leave tmp_article_cur_loop;
else
if (NOT EXISTS (select article_id from tmp_article_static_table where article_id = temp_reply_article_id)) then
-
select count (good_click) into temp_article_good from t_record where article_id = temp_reply_article_id and good_click is not null and create_time> DATE_ADD (now (), INTERVAL-inter DAY);
-
select count (reply_article_id) into temp_reply_total from tmp_article_table where reply_article_id = temp_reply_article_id;
- X =
set temp_article_sort = temp_article_good + temp_reply_total;
insert into tmp_article_static_table (article_id, good_click, replyTotal, article_sort) values ​​(temp_reply_article_id, temp_article_good, temp_reply_total, temp_article_sort);
end if;
end if;
end loop tmp_article_cur_loop;
close tmp_article_cur;
-
select * from tmp_article_static_table order by article_sort desc;
drop table tmp_article_table;
drop table tmp_article_static_table;
commit;
end if;
end;

相关文章
  • mysql stored procedures, temporary tables, cursor 2011-08-30

    CREATE DEFINER = `lifunet `@`%` PROCEDURE` proc_rank_article `( in inter int ) begin declare temp_reply_article_id int; declare temp_reply_total int default 0; declare temp_article_good int default 0; declare temp_article_sort int default 0; declare

  • The oracle stored procedures, temporary tables, cursor example 2011-09-22

    Reference material 1 ORACLE stored procedure result set to return a temporary table http://hi.baidu.com/h_sn999/blog/item/4211810f4d7542fdaa645738.html 2 ORACLE stored procedure using temporary tables http://blog.csdn.net/wekily/article/details/61209

  • (R) MySQL stored procedures, and include transactions, parameters, nested calls, cursor, loop, etc. 2011-04-20

    MySQL stored procedures, and include services, parameters, nested calls, cursors, loops, etc., read the MySQL stored procedures, and include services, parameters, nested calls, cursors, loops, etc., view plaincopy to clipboardprint? Drop procedure if

  • mysql stored procedures Study Notes - Error Handling Collection 2011-01-06

    mysql stored procedures Study Notes - Error handling collection definition: DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLSTATE sqlstate_code | MySQL error code | condition_name} handler_actions The definition includes: · Handler type (CONTINUE, EXIT) /

  • MySQL stored procedures and functions of the difference 2011-06-23

    Transfer: http://www.gooseeker.com/cn/node/Fuller/2010061201 MySQL stored procedures and functions of the difference MySQL stored procedure (stored procedure) and functions (stored function) collectively referred to as stored routines, whether stored

  • Turn: mysql stored procedures Detailed 2010-11-30

    Detailed MySQL stored procedure Transfer: http://www.owe-love.com/myspace/?action=show&id=187 1. Stored Procedures Introduction We used to manipulate the database language SQL statement executed when the need first compiled and then executed, and sto

  • mysql stored procedures first test 2011-01-10

    Since 5.0, mysql support stored procedures, nothing small test last weekend, in this preliminary record their own learning process. C:\Users\michael>mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 S

  • Question: Navicat for MySQL stored procedures on 2010-08-13

    Navicat for MySQL How to create a stored procedure with parameters? Not use the console to write stored procedures, but through the interface that creates a stored procedure to create! Solution! Thank you!

  • php + mysql stored procedures in the performance of a simple comparison 2011-05-03

    PHP + MYSQL, using MYSQL stored procedure is actually very good, but will make it fast, efficient, Here, the summary of the usage of MYSQL it under review, the use of the PDO 1 / / Do not use stored procedures $ Time = microtime (TRUE); $ Mem = memor

  • Mysql stored procedures first test, record what 2011-07-19

    Today the first trial mysql stored procedure, and eventually found not suitable for my needs, stored procedures can not pass an array as a parameter ... The following snippet of code to record what the learning process: delimiter // drop procedure if

  • Create MySql Stored Procedures 2011-05-10

    Summary: Let me talk about the advantages of database stored procedures. Then MySql, for example, instructions to create a stored procedure method. Body: <1> create a database stored procedure has at least two distinct advantages: 1 SQL statements s

  • First test mysql stored procedures, records about 2011-07-19

    Today the first trial mysql stored procedure, and eventually found not suitable for my needs, stored procedures can not pass an array as a parameter ... The following snippet of code to record what the learning process: delimiter // drop procedure if

  • mysql stored procedures commonly used commands 2011-10-25

    See the wording on the stored procedure: http://trinea.iteye.com/blog/977141 mysql Chinese reference documentation see: http://dl.iteye.com/topics/download/82db8bb1-7c55-3e74-9f56-de5f8e004ded 0, stored procedures need to pay attention to the best an

  • MySQL stored procedures error handling knowledge ☞ Summary 2011-03-29

    MySQL error code: http://dev.mysql.com/doc/refman/5.0/en/error-messages-server.html MySQL stored procedure of Error Handling: http://hideto.iteye.com/blog/194416

  • mysql stored procedures and use the cursor 2011-06-26

    DELIMITER $$ DROP PROCEDURE IF EXISTS `safetyw`.`pre_url`$$ CREATE DEFINER=`root`@`localhost` PROCEDURE `pre_url`() begin declare pId bigint(20); declare pUrl varchar(80); declare no_more_departments int default 0; -- Define a cursor declare ordernum

  • MYSQL stored procedures examples 2011-04-09

    Stored procedure: Create a stored procedure: CREATE PROCEDURE sp_name ([proc_parameter [,...]]) [Characteristic ...] routine_body CREATE FUNCTION sp_name ([func_parameter [,...]]) RETURNS type [Characteristic ...] routine_body proc_parameter: [IN | O

  • MySQL stored procedures: Delete the table with like conditions 2011-04-14

    delimiter // CREATE PROCEDURE drop_table_like(IN table_prefix varchar(64), IN username varchar(256)) BEGIN DECLARE tname varchar(128) default ''; DECLARE not_found INT DEFAULT 0; DECLARE cur_tnames cursor FOR SELECT table_name FROM INFORMATION_SCHEMA

  • (R) MySQL stored procedures in the use of split strings method 2011-04-20

    Existing section of the string, such as apple, banana, orange, pears, grape, take it in accordance with a comma (,) split into: apple banana orange pears grape and then use the where in () method to check. 1, the specific function: # Function: func_s

  • mysql stored procedures exception handling 2010-07-02

    When the insert fails, I hope that is recorded in a log file, Here need to create a primary key table, and a foreign key table, we are using Innodb, so check the associated foreign key is turned on, when I insert a table outside key Non-primary key t

  • mysql stored procedures, functions, triggers an example 2010-12-02

    1, split a string with "|||" interval stored procedure. create PROCEDURE sp_InsertJoKe (lastindex int) begin declare i int; set i = 1; while (i <lastindex) do begin declare a varchar (4000); declare p int; declare part1 varchar (4000); set a