存储过程学习记录
-- 创建或替换存储过程(无参) proc_attribution_wskh
create or replace procedure proc_attribution_wskh
AS
-- 定义变量,接收存储数据
V_USER_ID varchar2(14);
V_MOBILE_TEL varchar2(16);
V_PROVINCE_NAME varchar2(32);
V_PROVINCE_CODE varchar2(14);
V_CITY_NAME varchar2(64);
V_CITY_CODE varchar2(14);
total INTEGER := 0;
-- 定义游标
CURSOR CUR_ATTRIBUTION_WSKH
IS
-- 把查询的结果存储到游标中
select USER_ID,MOBILE_TEL from charles.userqueryextinfo t where t.request_status not in ('8','9');
BEGIN
OPEN CUR_ATTRIBUTION_WSKH;
LOOP
-- 抓取游标中每一次循环的值到变量中
fetch CUR_ATTRIBUTION_WSKH into V_USER_ID,V_MOBILE_TEL;
-- 游标中的数据抓取完,循环结束
EXIT
-- %NOTFOUND 有结果时是false
WHEN CUR_ATTRIBUTION_WSKH%NOTFOUND;
BEGIN
select count(*) into total from charles.mobilelocation m where substr(V_MOBILE_TEL,1,7) = m.mobile_tel;
IF total = 0 THEN
-- 输出执行中的数据
dbms_output.put_line('total!' || V_USER_ID || '为零!');
CONTINUE;
END IF;
-- 如果有数据 则执行insert into ...... select ......
insert into charles.mobilelocationext (user_id,mobile_tel,province_name,province_code,city_name,city_code)
select V_USER_ID,V_MOBILE_TEL,province_name, province_code, city_name, city_code from charles.mobilelocation where substr(V_MOBILE_TEL,1,7) = mobile_tel;
END;
END LOOP;
-- 关闭游标
CLOSE CUR_ATTRIBUTION_WSKH;
-- 提交插入记录
COMMIT;
-- 异常处理 异常则回滚
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;