oracle存储过程-cursor游标基础

古城微笑少年丶 2022-01-14 15:39 1094阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

`Cursor的应用场景：`

查询结果集`list` ，使用`cursor` 的情况，肯定会有下边这两种情况，才可能使用到`cursor` ：

1.  需要依据查询到的结果集，作为条件，进行下一步的查询。
2.  需要在结果集某个位置上，满足某种条件时，对数据进行不同的修改。

上边的两种情况，都会涉及到遍历数据，所以游标的使用也需要注意：

比如，你的查询结果集很大，首先占了很多的内存，其次，遍历这么庞大的结果集，效率也肯定高不了。  
另外，如果还有其他方式解决问题的话，那就不要选择使用游标，但是游标在使用中，的确有，定义游标、打开游标、关闭游标，这些操作，这样也肯定快不了多少。

Cursor的类别：

1.隐式游标。

DML操作和单行SELECT语句会使用隐式游标，如：INSERST，UPDATE，DELETE , SELECT ... INTO ..

另一种方式 ：

create or replace procedure test_class_procedure
    as
    begin
    for class_dec in (
      select * from t_class
    )
    loop
    dbms_output.put_line('姓名：'||class_dec.name||' 年龄：'||class_dec.age);
    end loop;
    end;

执行：

set serveroutput on;
    begin
     test_class_procedure;
    end;

隐式游标其实默认已经打开游标，并在执行完成后关闭游标释放资源。

2.显示游标。

显示游标分为静态游标与动态游标，显示游标的写法会在存储过程中定义`Cursor` ,并且一般都有固定的四个步骤：`声明游标` 、`打开游标` 、`提取数据` 、`关闭游标` 。显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

示例：

CREATE OR REPLACE NONEDITIONABLE PROCEDURE PRC_MDS1_TO_MDS2 (parameter IN VARCHAR2, exitcode OUT INT)
    AS
    	-- 定义一个变量用来保存此存储过程的名字
    	v_logic_proc_name VARCHAR2(30) := 'PRC_MDS1_TO_MDS2';
    	
    	-- 声明显式游标, 查询udt_mds1_input中item不为null的数据
      	CURSOR c_udt_mds1_input_item_notnull IS SELECT * FROM scpomgr.udt_mds1_input WHERE item IS NOT NULL AND TRIM(item) IS NOT NULL;
      	-- 定义游标变量，该变量的类型为基于游标c_udt_mds1_input_item_notnull的记录 
      	c_mds1_item_notnull_row c_udt_mds1_input_item_notnull%ROWTYPE;  
      	
      	-- 声明一个游标，查询udt_mds1_input中item为null的数据 -- MARKET、itemgroup、package、config不为NULL
      	CURSOR c_udt_mds1_input_item_null IS 
      			SELECT * 
      			FROM scpomgr.udt_mds1_input 
      			WHERE (item IS NULL OR TRIM(item) IS NULL)
      			AND market IS NOT NULL AND TRIM(market) IS NOT NULL
      			AND itemgroup IS NOT NULL AND TRIM(itemgroup) IS NOT NULL 
      			AND package IS NOT NULL AND TRIM(package) IS NOT NULL
      			AND config IS NOT NULL AND TRIM(config) IS NOT NULL;
      	-- 定义游标变量
      	c_mds1_item_null_row c_udt_mds1_input_item_null%ROWTYPE;
      	
      	-- 再声明一个游标，查询udt_mds1_input中item为null -- MARKET、ITEMGROUP、PACKAGE、CONFIG有为NULL的
      	CURSOR c_udt_mds1_item_null_other IS 
      			SELECT * 
      			FROM scpomgr.udt_mds1_input 
      			WHERE (item IS NULL OR TRIM(item) IS NULL)
      			AND (market IS NULL OR TRIM(market) IS NULL
      				OR itemgroup IS NULL OR TRIM(itemgroup) IS NULL
      				OR package IS NULL OR TRIM(package) IS NULL
      				OR config IS NULL OR TRIM(config) IS NULL); 
      	-- 定义游标变量
      	c_mds1_item_null_other_row c_udt_mds1_item_null_other%ROWTYPE;
    
    BEGIN
      
    	-- 设置要返回的变量值
    	exitcode := -20999;
    
    	-- 如果传入的参数是BYPASS, 则表示不运行
    	IF parameter = 'BYPASS' THEN
    		exitcode := 0;	-- 返回0表示执行成功
          	RETURN;
       	END IF;
    
       	-- 记录具体逻辑开始执行
    	logger.info (v_logic_proc_name || ':' || parameter || ' ,Start');
       
    	------------------- 具体的业务逻辑  -------------------
    	
    	-- 1. 将UDT_MDS1_INPUT所有字段输入到MDS1_BCKUP对应同名字段中
    	pkg_log.info(v_logic_proc_name || ':' || '步骤1,将UDT_MDS1_INPUT的数据备份到MDS1_BCKUP表中');
    	INSERT INTO scpomgr.MDS1_BCKUP SELECT * FROM scpomgr.UDT_MDS1_INPUT;
    	
    	-- 2. 是否要先清空中间表和UDT_MDS2_REVIEW表中的数据
    	pkg_log.info(v_logic_proc_name || ':' || '步骤2,删除mid_mds1_mds2和udt_mds2_review表中的数据');
      	DELETE FROM scpomgr.mid_mds1_mds2;  	-- 删除中间表数据
      	DELETE FROM scpomgr.udt_mds2_review;   	-- 删除UDT_MDS2_REVIEW表数据
      	
      	-- 3. 循环处理ITEM不为NULL的数据 
      	pkg_log.info(v_logic_proc_name || ':' || '步骤3,循环处理udt_mds1_input中item不为null的数据');
      	FOR c_mds1_item_notnull_row IN c_udt_mds1_input_item_notnull LOOP
      		-- 使用嵌套内部块
      		declare
    	    	-- 定义变量
      			v_country scpomgr.udt_country_ratio.country%TYPE; 	-- 定义一个国家变量
      			v_color scpomgr.udt_basic_info.color%TYPE;       	-- 定义一个颜色变量
    	    begin
    	      	-- 3.1 查询udt_country_ratio.country信息，并赋值给v_country
    	    	BEGIN
    	    	SELECT country INTO v_country  
    	    	FROM (
    	         		SELECT ucr.country
    	         		FROM scpomgr.udt_country_ratio ucr
    	         		WHERE ucr.market=c_mds1_item_notnull_row.market 
    	         		AND ucr.itemgroup=c_mds1_item_notnull_row.itemgroup
    	         	) 
    	    	WHERE ROWNUM=1;
    	    	EXCEPTION 
    	          	WHEN no_data_found THEN
    	          	pkg_log.error(v_logic_proc_name || ':' || '根据' || c_mds1_item_notnull_row.market || '和' || c_mds1_item_notnull_row.itemgroup || 
    	          				'没有在scpomgr.udt_country_ratio查询到对应的国家信息');
    	          	prc_udt_error_log(c_mds1_item_notnull_row.itemgroup, c_mds1_item_notnull_row.market, '', 
    	          			'没有在scpomgr.udt_country_ratio查询到对应的国家信息', 1, 'PRC_MDS1_TO_MDS2');
    	    	END;
           
    	    	-- 3.2 查询udt_basic_info.color信息，并赋值给v_color
    	    	BEGIN 
    	    	SELECT color INTO v_color  
    	    	FROM (
    	         		SELECT ubi.color
    	         		FROM scpomgr.udt_basic_info ubi
    	         		WHERE ubi.item = c_mds1_item_notnull_row.item
    	         	) 
    	    	WHERE ROWNUM=1;
    	    	EXCEPTION 
    	          	WHEN no_data_found THEN
    	          	pkg_log.error(v_logic_proc_name || ':' || '根据' || c_mds1_item_notnull_row.item || '没有在scpomgr.udt_basic_info查询到对应的颜色信息');
    	          	prc_udt_error_log(c_mds1_item_notnull_row.item, '', '', '没有在scpomgr.udt_basic_info查询到对应的颜色信息', 1, 'PRC_MDS1_TO_MDS2');
    	    	END;     
        
    	    	-- 3.3 将数据保存到中间表中
    	    	INSERT INTO scpomgr.mid_mds1_mds2(market,brand,itemgroup,item,descr,status,loc,package,config,dmdtype,version_no,country,color,
    	                w0,w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w20,w21,w22,w23,w24,w25,w26,w27)   
    	    	VALUES (c_mds1_item_notnull_row.market, c_mds1_item_notnull_row.brand, c_mds1_item_notnull_row.itemgroup, c_mds1_item_notnull_row.item, c_mds1_item_notnull_row.descr,
    	             c_mds1_item_notnull_row.status, c_mds1_item_notnull_row.loc, c_mds1_item_notnull_row.package, c_mds1_item_notnull_row.config, c_mds1_item_notnull_row.dmdtype,
    	             c_mds1_item_notnull_row.version_no, v_country, v_color, c_mds1_item_notnull_row.w0, c_mds1_item_notnull_row.w1, c_mds1_item_notnull_row.w2,
    	             c_mds1_item_notnull_row.w3, c_mds1_item_notnull_row.w4, c_mds1_item_notnull_row.w5, c_mds1_item_notnull_row.w6, c_mds1_item_notnull_row.w7,
    	             c_mds1_item_notnull_row.w8, c_mds1_item_notnull_row.w9, c_mds1_item_notnull_row.w10, c_mds1_item_notnull_row.w11, c_mds1_item_notnull_row.w12,
    	             c_mds1_item_notnull_row.w13, c_mds1_item_notnull_row.w14, c_mds1_item_notnull_row.w15, c_mds1_item_notnull_row.w16, c_mds1_item_notnull_row.w17,
    	             c_mds1_item_notnull_row.w18, c_mds1_item_notnull_row.w19, c_mds1_item_notnull_row.w20, c_mds1_item_notnull_row.w21, c_mds1_item_notnull_row.w22,
    	             c_mds1_item_notnull_row.w23, c_mds1_item_notnull_row.w24, c_mds1_item_notnull_row.w25, c_mds1_item_notnull_row.w26, c_mds1_item_notnull_row.w27);
    	    end;
      	END LOOP;
      	
      	-- 4. 循环处理ITEM为NULL的数据 
      	pkg_log.info(v_logic_proc_name || ':' || '步骤4,循环处理udt_mds1_input中item为null,但MARKET/itemgroup/package/config不为NULL的数据');
      	FOR c_mds1_item_null_row IN c_udt_mds1_input_item_null LOOP
      		-- 使用嵌套内部块
      		declare
    	    	-- 定义变量
    	    	v_item scpomgr.udt_basic_info.item%TYPE;   		-- 定义item
      			v_descr scpomgr.udt_basic_info.descr%TYPE;   	-- 定义descr
    			-- 定义国家和国家占比  			
      			v_country scpomgr.udt_country_ratio.country%TYPE; 				-- 国家
      			v_country_ratio scpomgr.udt_country_ratio.country_ratio%TYPE;   -- 国家占比
    		  	
      			-- 使用数组定义15种颜色及占比
      			type v_color_varray is varray(15) of scpomgr.udt_country_ratio.color1%TYPE;		-- 颜色
      			v_color v_color_varray := v_color_varray('', '', '', '', '', '', '', '', '', '', '', '', '', '', '');
      			type v_color_ratio_varray is varray(15) of scpomgr.udt_country_ratio.color1_ratio%TYPE;		-- 颜色占比
      			v_color_ratio v_color_ratio_varray := v_color_ratio_varray(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null);
    		  	
    		  	-- 使用数组定义28周的数据量变量
    		  	type v_w_varray is varray(28) of scpomgr.udt_mds1_input.w0%TYPE;
    		  	v_w v_w_varray := v_w_varray(c_mds1_item_null_row.w0,c_mds1_item_null_row.w1,c_mds1_item_null_row.w2,c_mds1_item_null_row.w3,c_mds1_item_null_row.w4,c_mds1_item_null_row.w5,
    		  						c_mds1_item_null_row.w6,c_mds1_item_null_row.w7,c_mds1_item_null_row.w8,c_mds1_item_null_row.w9,c_mds1_item_null_row.w10,c_mds1_item_null_row.w11,
    							  	c_mds1_item_null_row.w12,c_mds1_item_null_row.w13,c_mds1_item_null_row.w14,c_mds1_item_null_row.w15,c_mds1_item_null_row.w16,c_mds1_item_null_row.w17,
    							  	c_mds1_item_null_row.w18,c_mds1_item_null_row.w19,c_mds1_item_null_row.w20,c_mds1_item_null_row.w21,c_mds1_item_null_row.w22,c_mds1_item_null_row.w23,
    							  	c_mds1_item_null_row.w24,c_mds1_item_null_row.w25,c_mds1_item_null_row.w26,c_mds1_item_null_row.w27);
    		  	
    	    begin
    		    -- 4.1 查询udt_country_ratio中抓取对应的country、 country_ratio、color1..15、color1..15_ratio，并赋值给相应的变量
    	    	BEGIN
    	    	SELECT country,country_ratio,color1,color1_ratio,color2,color2_ratio,color3,color3_ratio,color4,color4_ratio,color5,color5_ratio,color6,color6_ratio,
    	    			color7,color7_ratio,color8,color8_ratio,color9,color9_ratio,color10,color10_ratio,color11,color11_ratio,color12,color12_ratio,color13,color13_ratio,
    	    			color14,color14_ratio,color15,color15_ratio
    	    	INTO v_country,v_country_ratio,v_color(1),v_color_ratio(1),v_color(2),v_color_ratio(2),v_color(3),v_color_ratio(3),v_color(4),v_color_ratio(4),
    	    			v_color(5),v_color_ratio(5),v_color(6),v_color_ratio(6),v_color(7),v_color_ratio(7),v_color(8),v_color_ratio(8),v_color(9),v_color_ratio(9), 
    	    			v_color(10),v_color_ratio(10),v_color(11),v_color_ratio(11),v_color(12),v_color_ratio(12),v_color(13),v_color_ratio(13),v_color(14),v_color_ratio(14),
    	    			v_color(15),v_color_ratio(15)
    	    	FROM (
    	         		SELECT ucr.country,ucr.country_ratio,ucr.color1,ucr.color1_ratio,ucr.color2,ucr.color2_ratio,ucr.color3,ucr.color3_ratio,ucr.color4,ucr.color4_ratio,
    	         			ucr.color5,ucr.color5_ratio,ucr.color6,ucr.color6_ratio,ucr.color7,ucr.color7_ratio,ucr.color8,ucr.color8_ratio,ucr.color9,ucr.color9_ratio,
    	         			ucr.color10,ucr.color10_ratio,ucr.color11,ucr.color11_ratio,ucr.color12,ucr.color12_ratio,ucr.color13,ucr.color13_ratio,ucr.color14,ucr.color14_ratio,
    	         			ucr.color15,ucr.color15_ratio
    	         		FROM scpomgr.udt_country_ratio ucr
    	         		WHERE ucr.market = c_mds1_item_null_row.market
    	         		AND ucr.itemgroup = c_mds1_item_null_row.itemgroup
    	         	) 
    	    	WHERE ROWNUM=1;
    	    	EXCEPTION 
    	          	WHEN no_data_found THEN
    	          	pkg_log.error(v_logic_proc_name || ':' || '根据' || c_mds1_item_null_row.market || '和' || c_mds1_item_null_row.itemgroup || 
    	          			'没有在scpomgr.udt_country_ratio查询到对应国家和颜色及占比信息');
    	          	prc_udt_error_log(c_mds1_item_null_row.itemgroup, c_mds1_item_null_row.market, '', 
    	          			'没有在scpomgr.udt_country_ratio查询到对应国家和颜色及占比信息', 1, 'PRC_MDS1_TO_MDS2');
    	    	END;
    	    	
    	    	-- 4.2对于country_ratio、color_ratio不为NULL的记录 更新W0-W27的数据
    	    	FOR i in 1 .. 15 LOOP
    	    		-- 先判断有没有颜色，如果没有则不处理
    	    		IF(v_color(i) IS NOT NULL AND TRIM(v_color(i)) IS NOT NULL) THEN
    	    			-- 4.2.1 获取item，descr
    	    			v_item := '';
    	    			v_descr := '';	-- 先设置为默认值
    			    	BEGIN 
    			    	SELECT item,descr INTO v_item,v_descr  
    			    	FROM (
    			         		SELECT ubi.item,ubi.descr
    			         		FROM scpomgr.udt_basic_info ubi
    			         		WHERE ubi.country = v_country
    			         		AND ubi.itemgroup = c_mds1_item_null_row.itemgroup
    				         	AND ubi.package = c_mds1_item_null_row.package
    				         	AND ubi.color = v_color(i)
    				         	AND ubi.config = c_mds1_item_null_row.config
    			         	) 
    			    	WHERE ROWNUM=1;
    			    	EXCEPTION 
    			          	WHEN no_data_found THEN
    			          	pkg_log.error(v_logic_proc_name || ':' || '根据' || c_mds1_item_null_row.itemgroup || ',' || c_mds1_item_null_row.package || ',' ||
    								v_color(i) || ',' || c_mds1_item_null_row.config || '没有在scpomgr.udt_basic_info查询到对应的item和descr信息');
    			          	prc_udt_error_log(c_mds1_item_null_row.itemgroup, c_mds1_item_null_row.package|| '+' || c_mds1_item_null_row.config, '', 
    	          					'没有在scpomgr.udt_basic_info查询到对应的item和descr信息', 1, 'PRC_MDS1_TO_MDS2');
    			    	END;
    	    		
    		    		-- 4.2.2 如果不为空，则要计算v_w的数据
    					IF(v_country_ratio IS NOT NULL AND v_color_ratio(i) IS NOT NULL) THEN
    						--循环处理v_w0...v_w27的数据
    				    	FOR j in 1 .. 28 LOOP
    				    		v_w(j) := v_w(j) * v_country_ratio * v_color_ratio(i);
    				    	END LOOP;
    				    	-- 将处理完的数据插入到中间表中
    				    	INSERT INTO scpomgr.mid_mds1_mds2(market,itemgroup,package,config,country,country_ratio,color,color_ratio,item, descr,
    	                		w0,w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w20,w21,w22,w23,w24,w25,w26,w27)   
    	    				VALUES (c_mds1_item_null_row.market, c_mds1_item_null_row.itemgroup, c_mds1_item_null_row.package, c_mds1_item_null_row.config, v_country, v_country_ratio, 
    	    					v_color(i),v_color_ratio(i), v_item, v_descr, 
    	    					v_w(1), v_w(2), v_w(3), v_w(4), v_w(5), v_w(6), v_w(7), v_w(8), v_w(9), v_w(10), v_w(11), v_w(12), v_w(13), v_w(14), v_w(15), v_w(16),v_w(17), 
    	    					v_w(18), v_w(19), v_w(20), v_w(21), v_w(22), v_w(23), v_w(24), v_w(25), v_w(26), v_w(27), v_w(28));
    				    END IF;
    	    		END IF;
    			END LOOP;
      		end;
        END LOOP;
        
        -- 5. 对于若UDT_MDS1_INPUT.ITEM为NULL，MARKET、ITEMGROUP、PACKAGE、CONFIG有为NULL的
        pkg_log.info(v_logic_proc_name || ':' || '步骤5,循环处理udt_mds1_input中item为null,并且MARKET/itemgroup/package/config有为NULL字段的数据');
      	FOR c_mds1_item_null_other_row IN c_udt_mds1_item_null_other LOOP
      		-- 使用嵌套内部块
      		declare
      			-- 定义一个变量保存数据
      			v_null_filed varchar2(500);
      		begin
      			if(c_mds1_item_null_other_row.market is null or trim(c_mds1_item_null_other_row.market) is null) then
    				v_null_filed := v_null_filed || 'market,';
    			elsif(c_mds1_item_null_other_row.itemgroup is null or trim(c_mds1_item_null_other_row.itemgroup) is null) then
    				v_null_filed := v_null_filed || 'itemgroup,';
    			elsif(c_mds1_item_null_other_row.package is null or trim(c_mds1_item_null_other_row.package) is null) then
    				v_null_filed := v_null_filed || 'package,';
    			elsif(c_mds1_item_null_other_row.config is null or trim(c_mds1_item_null_other_row.config) is null) then
    				v_null_filed := v_null_filed || 'config';
    			end if;
    	  		-- 找出哪个字段为空，并记录下来
    	  		pkg_log.error(v_logic_proc_name || ':' || 'scpomgr.UDT_MDS1_INPUT表中下面字段为空: ' || v_null_filed);
        		prc_udt_error_log('', '', '', 'scpomgr.UDT_MDS1_INPUT表中下面字段为空: ' || v_null_filed, 1, 'PRC_MDS1_TO_MDS2');
    	  	END;
      	END LOOP;
      	
      	-- 6. 将中间表的数据保存到UDT_MDS2_REVIEW表中
      	pkg_log.info(v_logic_proc_name || ':' || '步骤6,将中间表mid_mds1_mds2的数据保存到udt_mds2_review表中');
      	INSERT INTO scpomgr.udt_mds2_review(market,brand,itemgroup,item,descr,status,loc,package,color,config,dmdtype,version_no,
                  w0,w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w20,w21,w22,w23,w24,w25,w26,w27)
      	SELECT market,brand,itemgroup,item,descr,status,loc,package,color,config,dmdtype,version_no,
             	w0,w1,w2,w3,w4,w5,w6,w7,w8,w9,w10,w11,w12,w13,w14,w15,w16,w17,w18,w19,w20,w21,w22,w23,w24,w25,w26,w27 
      	FROM scpomgr.mid_mds1_mds2;
    	
    	-- 说明没有错误, 执行成功
    	exitcode := 0;
       	-- 记录完成
    	logger.info (v_logic_proc_name || ':' || parameter || ' ,Success');
       	-- 成功的话, 提交
    	COMMIT;
    
    EXCEPTION
    	WHEN OTHERS THEN
    		exitcode := SQLCODE;	-- 返回错误代码
          	pkg_log.error('Failure:' || v_logic_proc_name || ':' || SQLCODE || ':' || SQLERRM || ':' || substr(dbms_utility.format_error_backtrace, 1, 1024));
          	COMMIT;
    END PRC_MDS1_TO_MDS2;

转载于:https://my.oschina.net/lyleluo/blog/3058360

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061697
[hot3.png]: /images/20220114/c45f9ad15aa948f4a47ffd43efd7bbe8.png