--隐式游标 注意变量赋值用（:=） 连接符用（||）而不是加号（+）

DECLARE

  v_pk T_PLAT_KEYWORD.ID%TYPE; --主键

  v_amount_message T_PLAT_KEYWORD_STATISTIC.AMOUNT%TYPE;

  v_amount_talk T_PLAT_KEYWORD_STATISTIC.AMOUNT%TYPE;

  CURSOR CUR IS

    SELECT DISTINCT B.NAME,B.TYPE,B.WEIXIN_PK FROM T_PLAT_KEYWORD B;

BEGIN

  FOR MY_CUR IN CUR LOOP

    v_pk:=SEQ_PLAT_KEYWORD_STATISTIC.NEXTVAL; --主键赋值

    SELECT COUNT(1) INTO v_amount_message FROM T_WEBCALL_TALK A WHERE TO_CHAR(SUBSTR(A.CONTENT,0,1000))  LIKE '%'||MY_CUR.NAME||'%';

    SELECT COUNT(1) INTO v_amount_talk FROM T_WEIXIN_MESSAGE T WHERE T.MESSAGE LIKE '%'||MY_CUR.NAME||'%';

    INSERT INTO T_PLAT_KEYWORD_STATISTIC (ID,NAME,AMOUNT,WEIXIN_PK,TYPE,CREATE_TIME)

    VALUES (v_pk,MY_CUR.NAME,(v_amount_message+v_amount_talk),MY_CUR.WEIXIN_PK ,MY_CUR.TYPE,to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss'));

  END LOOP;

END;

--DELETE FROM T_PLAT_KEYWORD_STATISTIC

--SELECT to_char(SYSDATE,'yyyy-MM-dd HH:mm:ss')  FROM dual

--TO_CHAR(SUBSTR(A.CONTENT,0,1000)) 将clob转换成字符串

-- Create sequence 创建序列

/*create sequence SEQ_PLAT_KEYWORD_STATISTIC

minvalue 1

maxvalue 999999999999

start with 11

increment by 1

nocache;*/

--将游标加入到存储过程

CREATE OR REPLACE PROCEDURE PLAT_KEYWORD_STATISTIC IS

  V_PK             T_PLAT_KEYWORD.ID%TYPE; --主键

  V_AMOUNT_MESSAGE T_PLAT_KEYWORD_STATISTIC.AMOUNT%TYPE;

  V_AMOUNT_TALK    T_PLAT_KEYWORD_STATISTIC.AMOUNT%TYPE;

  CURSOR CUR IS

    SELECT DISTINCT B.NAME, B.TYPE, B.WEIXIN_PK FROM T_PLAT_KEYWORD B;

BEGIN

  FOR MY_CUR IN CUR LOOP

    V_PK := SEQ_PLAT_KEYWORD_STATISTIC.NEXTVAL; --主键赋值

    SELECT COUNT(1)

      INTO V_AMOUNT_MESSAGE

      FROM T_WEBCALL_TALK A

     WHERE TO_CHAR(SUBSTR(A.CONTENT, 0, 1000)) LIKE

           '%' || MY_CUR.NAME || '%';

    SELECT COUNT(1)

      INTO V_AMOUNT_TALK

      FROM T_WEIXIN_MESSAGE T

     WHERE T.MESSAGE LIKE '%' || MY_CUR.NAME || '%';

    INSERT INTO T_PLAT_KEYWORD_STATISTIC (ID, NAME, AMOUNT, WEIXIN_PK, TYPE, CREATE_TIME) VALUES (V_PK, MY_CUR.NAME,  (V_AMOUNT_MESSAGE + V_AMOUNT_TALK),  MY_CUR.WEIXIN_PK,  MY_CUR.TYPE, TO_CHAR(SYSDATE, 'yyyy-MM-dd HH:mm:ss'));

  END LOOP;

EXCEPTION

  WHEN TOO_MANY_ROWS THEN

  DBMS_OUTPUT.PUT_LINE('返回值多于1行');

  WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE('关键字统计存储过程出错！');

END PLAT_KEYWORD_STATISTIC;