· 9 years ago · Oct 14, 2016, 10:26 PM
1DROP PROCEDURE IF EXISTS procedureName;
2CREATE PROCEDURE procedureName()
3 BEGIN
4
5 DECLARE done INT DEFAULT FALSE;
6 DECLARE rowCounter int;
7 DECLARE id int;
8 DECLARE content LONGTEXT;
9 DECLARE imgTag VARCHAR(100);
10
11 DECLARE cur1 CURSOR FOR
12 SELECT ID,
13 post_content
14 FROM tss_posts
15 WHERE post_type NOT IN (
16 'nav_menu_item',
17 'attachment',
18 'nf_sub',
19 'shop_coupon',
20 'shop_order',
21 'shop_order_refund',
22 'tss-coll-basket',
23 'wishlist');
24 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
25
26 DROP TABLE IF EXISTS TMPTABLE7456;
27 CREATE TABLE TMPTABLE7456 (ID int,LOG varchar(1000));
28
29 set rowCounter = 0;
30
31 OPEN cur1;
32
33 read_loop: LOOP
34 FETCH cur1 INTO id,content;
35 set rowCounter = rowCounter + 1;
36 IF (done or rowCounter > 50) THEN
37 LEAVE read_loop;
38 END IF;
39
40INSERT INTO TMPTABLE7456 VALUES (id,content);
41
42 /*while (LOCATE('<img', content) > 0) do*/
43 /*IF (LOCATE('<img', content) > 0) THEN
44 set imgTag = SUBSTRING(
45 content,
46 LOCATE('<img', content),
47 LENGTH(content) - LOCATE('<img', content) - 1 - (LENGTH(content) - LOCATE('>', content))
48 );
49 set content = REPLACE(
50 content,
51 imgTag,
52 ''
53 );
54 INSERT INTO TMPTABLE7456 VALUES (id,content);
55 END IF;*/
56 /*end while;*/
57
58 /*IF b < c THEN
59 INSERT INTO test.t3 VALUES (a,b);
60 ELSE
61 INSERT INTO test.t3 VALUES (a,c);
62 END IF;*/
63
64
65
66
67 END LOOP;
68
69 CLOSE cur1;
70
71 SELECT * FROM TMPTABLE7456;
72
73 END;
74
75CALL procedureName();