创建 trigger，sequence , procdure

1.创建sequence

    create sequence SEQ_SMS_VERIFY_ID  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 ORDER NOCYCLE ;

2.创建trigger

   create or replace trigger tri_sms_verify_id before
 insert on tb_sms_verify for each row 
 declare nextid number;
 begin
  if :new.id is null or :new.id = 0 then
    select seq_sms_verify_id into nextid from sys.dual;
    :new.id := nextid;
  end if;
 end;

3.创建procedure

create or replace
PROCEDURE proc_ask(
    p_id       IN tb_ask.id%type,
    p_userId   IN tb_ask.userid%type,
    p_subject  IN tb_ask.subject%type,
    p_content  IN tb_ask.content%type,
    p_category IN tb_ask.category%type,
    p_tag      IN VARCHAR2,
    p_gardenId IN VARCHAR2,
    p_operate  IN VARCHAR2,
    p_param1   IN VARCHAR2,
    p_param2   IN VARCHAR2,
    p_result1 OUT VARCHAR2,
    p_result2 OUT VARCHAR2,
    p_rs OUT pk_type.mycursor)
IS
  t_id     NUMBER(19,0);
  t_tagId  NUMBER(19,0);
  t_askId  NUMBER(19,0);
  t_count  NUMBER(9,0);
  v_result VARCHAR2(100);
  v_msg    VARCHAR2(100);
  v_cursor pk_type.mycursor;
  myexception EXCEPTION;
BEGIN
  IF p_operate = 'addAsk' THEN
    ----添加ask
    SELECT SEQ_ASK_ID.nextval*100000000 + TRUNC(dbms_random.value(0,99999999))
    INTO t_id
    FROM sys.dual;
    INSERT
    INTO tb_ask
      (
        id,
        subject,
        content,
        category,
        answercount,
        readcount,
        supportcount,
        againstcount,
        createdate,
        updatedate,
        userid,
        isclosed,
        isaudited,
        isdeleted,
        ispassed
      )
      VALUES
      (
        t_id,
        p_subject,
        p_content,
        p_category,
        0,0,0,0,
        sysdate,
        sysdate,
        p_userId,
        0,1,0,1
      );
    t_askId := t_id;
    --    dbms_output.put_line(t_askId);
    /*
    2、插入事件表
    */
    DECLARE
      p_rs_event pk_type.myCursor;
      t1 VARCHAR2(1000);
      t2 VARCHAR2(1000);
      t3 VARCHAR2(100);
      t4 VARCHAR2(100) := '';
    BEGIN
      zyq.proc_event(0, t_id, '', '', 'add', '' ,p_userid, 'tb_ask', 'add', '', '', t1, t2, p_rs_event);
      v_result := t1;
      v_msg    :=t2;
    END;
    IF v_result  = 'error' THEN
      p_result1 := 'error';
      p_result2 := v_msg;
      raise myexception;
    ELSE
      p_result1 :='success';
      p_result2 := TO_CHAR(t_id);
    END IF;
    zyq.proc_servicelog(p_userId,'添加问吧问题','','',t_id,systimestamp,v_result,v_msg,v_cursor);--记录日志
    -----添加问吧的tag
    FOR cur IN
    (SELECT column_value AS content FROM TABLE(f_split(p_tag))
    )
    LOOP
      zyq.proc_tag
      (
        '',cur.content,1,p_userid,'addTag',v_result,v_msg
      )
      ;
      IF v_result = 'success' THEN
        t_tagId  := v_msg;
      END IF;
      ----添加ask与tag的关联
      SELECT SEQ_ASK_TAG_ID.nextval*100000000 + TRUNC(dbms_random.value(0,99999999))
      INTO t_id
      FROM sys.dual;
      INSERT
      INTO tb_ask_tag
        (
          id,
          askid,
          tagid,
          userid,
          createdate,
          isdeleted
        )
        VALUES
        (
          t_id,
          t_askId,
          t_tagId,
          p_userid,
          sysdate,
          0
        );
    END LOOP;
    --添加问题与小区的关联
    FOR cur IN
    (SELECT column_value AS gardenid FROM TABLE(f_split(p_gardenid))
    )
    LOOP
      INSERT
      INTO tb_askgarden
        (
          askid,
          gardenid,
          createdate,
          userid,
          isdeleted
        )
        VALUES
        (
          t_askId,
          cur.gardenid,
          sysdate,
          p_userid,
          0
        );
    END LOOP;
    ---删除问题
  elsif p_operate = 'deleteAsk' then
    update tb_ask
    set isdeleted = 1
    where id = p_id
    and userid = p_userid
    and isdeleted = 0;
    ---删除问题与标签的关联
    update tb_ask_tag
    set isdeleted = 1
    where askid = p_id
    and userid = p_userid
    and isdeleted = 0;
    ---删除问题与小区的关联
    update tb_askgarden
    set isdeleted =1
    where askid = p_id
    and isdeleted = 0;
          /*
    2、插入事件表
    */
    DECLARE
      p_rs_event pk_type.myCursor;
      t1 VARCHAR2(1000);
      t2 VARCHAR2(1000);
      t3 VARCHAR2(100);
      t4 VARCHAR2(100) := '';
    BEGIN
      zyq.proc_event(0, p_id, '', '', 'delete', '' ,p_userid, 'tb_ask', 'add', '', '', t1, t2, p_rs_event);
      v_result := t1;
      v_msg    :=t2;
    END;
    IF v_result  = 'error' THEN
      p_result1 := 'error';
      p_result2 := v_msg;
      raise myexception;
    ELSE
      p_result1 :='success';
      p_result2 := TO_CHAR(p_id);
    END IF;
    zyq.proc_servicelog(p_userId,'删除问题','','',p_id,systimestamp,v_result,v_msg,v_cursor);--记录日志
    ---修改问题
  elsif p_operate = 'updateAsk' THEN
    UPDATE tb_ask
    SET subject = p_subject,
      content   = p_subject,
      category  = p_category,
      updatedate = sysdate
    WHERE id    = p_id;
    ---删除所有该问题的关联
    UPDATE tb_ask_tag
    SET isdeleted = 1
    WHERE askid   = p_id
    AND userid    = p_userid
    AND isdeleted = 1;
    ---重新添加关联
    -----添加问吧的tag
    FOR cur IN
    (SELECT column_value AS content FROM TABLE(f_split(p_tag))
    )
    LOOP
      zyq.proc_tag ( '',cur.content,1,p_userid,'addTag',v_result,v_msg ) ;
      IF v_result = 'success' THEN
        t_tagId  := v_msg;
      END IF;
      ----添加ask与tag的关联
      SELECT SEQ_ASK_TAG_ID.nextval*100000000 + TRUNC(dbms_random.value(0,99999999))
      INTO t_id
      FROM sys.dual;
      INSERT
      INTO tb_ask_tag
        (
          id,
          askid,
          tagid,
          userid,
          createdate,
          isdeleted
        )
        VALUES
        (
          t_id,
          t_askId,
          t_tagId,
          p_userid,
          sysdate,
          0
        );
    END LOOP;
       /*
    2、插入事件表
    */
    DECLARE
      p_rs_event pk_type.myCursor;
      t1 VARCHAR2(1000);
      t2 VARCHAR2(1000);
      t3 VARCHAR2(100);
      t4 VARCHAR2(100) := '';
    BEGIN
      zyq.proc_event(0, p_id, '', '', 'update', '' ,p_userid, 'tb_ask', 'add', '', '', t1, t2, p_rs_event);
      v_result := t1;
      v_msg    :=t2;
    END;
    IF v_result  = 'error' THEN
      p_result1 := 'error';
      p_result2 := v_msg;
      raise myexception;
    ELSE
      p_result1 :='success';
      p_result2 := TO_CHAR(p_id);
    END IF;
    zyq.proc_servicelog(p_userId,'修改问题','','',p_id,systimestamp,v_result,v_msg,v_cursor);--记录日志
    ---回答问题
  elsif p_operate = 'addAnswer' THEN
    SELECT SEQ_ASK_ANSWER_ID.nextval*100000000 + TRUNC(dbms_random.value(0,99999999))
    INTO t_id
    FROM sys.dual;
    INSERT
    INTO tb_ask_answer
      (
        id,
        askid,
        content,
        userid,
        createdate,
        supportcount,
        againstcount,
        isaudited,
        isdeleted,
        ispassed
      )
      VALUES
      (
        t_id,
        p_id,
        p_content,
        p_userid,
        sysdate,
        0,0,1,0,1
      );
    UPDATE tb_ask SET answercount = answercount +1 WHERE id = p_id;
    p_result1 := 'success';
    p_result2 := t_id;
  END IF;
  COMMIT;
EXCEPTION
WHEN myexception THEN
  p_result1 :='error';
  p_result2 := sqlerrm;
  ROLLBACK;
WHEN OTHERS THEN
  p_result1 :='error';
  p_result2 := sqlerrm;
  ROLLBACK;
END;