view创建

create or replace view doctorview as
select
* from  doctor d, members m
where d.memberId = m.memberId and d.checkState=2;

根据已知表查询结果创建视图信息。
-- 添加toptag字段 --
alter table doctor add toptag NUMBER default 0;

alter table cash add datasource NUMBER default 0;

create or replace view doctorview as
select
  d.memberId,
  m.memberName,
  m."alias",
  m.photoUrl,
  m.cumulate,
  m."star",
  m."comment",
  m.good,
  m.fans,
  d.sex,
  d.areaCode,
  d.works,
  d.hospital,
  d.grade,
  d.department,
  d.duty,
  d.title,
  d.duties,
  d.specialty,
  d.intro,
  d.service,
  d.price,
  d.sales,
  d.createTime,
  d.hospitalId,
  d.adept,
  d.toptag
from doctor d, members m
where d.memberId = m.memberId and d.checkState=2;


create or replace view freequestionview as
select
  f.freeId,
  f.memberId,
  m.memberName,
  f.departmentId,
  f."content",
  f.sendList,
  f.createTime,
  f.state,
  f.PATIENTID,
  f.picture,
  f.consultid,
  p.patientname,
  f.money,
  f.freetype
from freeQuestion f,members m ,patient p
where f.memberId=m.memberId  and f.patientid=p.patientid;


CREATE OR REPLACE PROCEDURE KMT_REWARD_ORDER_PROC IS

  -- 定义游标,查询超过XX 小时未被医生领取的已支付的悬赏单--
  CURSOR CUR_TEMPDATA IS
    SELECT T.ORDERID, T.CONSULTID, T.BUYERID, T.MONEY
      FROM ORDERS T
     WHERE TYPEID = 17
       AND T.STATE = 1
       AND ORDERTIME < (SYSDATE - 12 / 24) --悬赏单 12 小时后过期 --
       AND EXISTS (SELECT 1
              FROM FREEQUESTION FQ
             WHERE FQ.CONSULTID = T.CONSULTID
               AND FQ.FREETYPE = 17
               AND FQ.STATE = 0);
  V_COUNT NUMBER := 0;
  V_ACCOUNT VARCHAR2(20) := '';

BEGIN

  -- 遍历 --
  FOR CUR_RESULT IN CUR_TEMPDATA LOOP
    BEGIN
      SELECT COUNT(1)
        INTO V_COUNT
        FROM ACCOUNT T
       WHERE T.MEMBERID = CUR_RESULT.BUYERID;
      
       SELECT t.accountid
        INTO V_ACCOUNT
        FROM ACCOUNT T
       WHERE T.MEMBERID = CUR_RESULT.BUYERID;

      -- update orders
      update orders T set T.state=8 where T.typeid=17 AND t.orderid=CUR_RESULT.orderid;
      -- update consult
      update consult T set T.state=8 where T.typeid=17 AND t.consultid=CUR_RESULT.CONSULTID;
      -- update freeQuestion
      UPDATE freequestion t SET t.state = 8 WHERE t.freetype = 17 AND t.consultid = CUR_RESULT.CONSULTID;
      -- insert refund
      INSERT INTO refund
        ( memberid, accountid, orderid, amount, notes, createtime, state)
      VALUES
        (CUR_RESULT.Buyerid, V_ACCOUNT, CUR_RESULT.Orderid, CUR_RESULT.Money, '订单过期,系统自动退款', SYSDATE, 1);
       
      IF V_COUNT > 0 THEN
        -- update table account
        UPDATE ACCOUNT T
           SET T.BALANCE   = T.BALANCE + nvl(CUR_RESULT.MONEY,0),
               T.AVAILABLE = T.AVAILABLE + nvl(CUR_RESULT.MONEY,0)
         WHERE T.MEMBERID = CUR_RESULT.BUYERID;
      ELSE
        -- insert table account
        INSERT INTO ACCOUNT
          (ACCOUNTNAME, MEMBERID, ACCOUNTTYPE, BALANCE, AVAILABLE)
        VALUES
          ('现金账户', CUR_RESULT.BUYERID, 1, CUR_RESULT.MONEY, CUR_RESULT.MONEY);
      END IF;

    END;
  END LOOP;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
END KMT_REWARD_ORDER_PROC;

--定义定时任务--
declare
    job number;
begin
    dbms_job.submit(job,'kmt_reward_order_proc;',sysdate,'sysdate+1/1440');
end;
COMMIT;