存储过程学习记录
学习blog
-- 创建或替换存储过程（无参） 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;