oracle 数据库存储过程编写

  存储过程学习记录

学习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;