梦幻之旅

DEBUG - 天道酬勤

   :: 首页 :: 新随笔 :: 联系 :: 聚合  :: 管理 ::
  671 随笔 :: 6 文章 :: 256 评论 :: 0 Trackbacks
ocr

 

CREATE OR REPLACE TRIGGER upd_ocr
    BEFORE 
INSERT ON ex_edm_ocr FOR EACH ROW
DECLARE
      v_province ip_city.province
%TYPE;
      v_city ip_city.city
%TYPE;
      v_task_id ex_edm_task_detail.task_id
%TYPE;
      v_project_id ex_edm_task_detail.project_id
%TYPE;
      v_user_id ex_edm_task_detail.
user_id%TYPE;
      v_email ex_edm_task_detail.email
%TYPE;

BEGIN
      
SELECT task_id,
             project_id,
             
user_id,
             email
          
INTO
             v_task_id,
             v_project_id,
             v_user_id,
             v_email
      
FROM ex_edm_task_detail
      
WHERE id=:new.detail_id;

      
SELECT province,
             city
          
INTO
             v_province,
             v_city
      
FROM ip_city
      
WHERE ip2number(:new.ip) BETWEEN ip2number(ip_begin) AND ip2number(ip_end);

      :new.task_id:
=v_task_id;
      :new.project_id:
=v_project_id;
      :new.
user_id:=v_user_id;
      :new.email:
=v_email;
      :new.province:
=v_province;
      :new.city:
=v_city;
EXCEPTION
      
WHEN no_data_found THEN
          :new.city :
= '未知';
          :new.city :
= '未知';
      
WHEN too_many_rows THEN
          :new.task_id:
=v_task_id;
          :new.project_id:
=v_project_id;
          :new.
user_id:=v_user_id;
          :new.email:
=v_email;
      
WHEN OTHERS THEN
          :new.city :
= '未知';
          :new.city :
= '未知';
END;
posted on 2011-04-19 16:11 HUIKK 阅读(98) 评论(0)  编辑  收藏 所属分类: DataBase

只有注册用户登录后才能发表评论。


网站导航: