存储过程游标的使用

全过程带注释

CREATE OR REPLACE PROCEDURE "基站通报11111111_LI"(v_period IN VARCHAR2) AS
		unitname1b VARCHAR2(20);
		unitname2b VARCHAR2(20);
		unitname3b VARCHAR2(20);
		unitname4b VARCHAR2(20);
		unitname5b VARCHAR2(20);
		unitname6b VARCHAR2(20);
    numberu1b NUMBER;
    numberu2b NUMBER;
    numberu3b NUMBER;
    numberu4b NUMBER;
    numberu5b NUMBER;
    numberu6b NUMBER;	
		    TYPE cur_type IS RECORD (
        c_unitname jilin.dt_site_profit_unit.c_unitname%TYPE,  -- 地市名称
        Ratio      NUMBER,                                      -- 比率
        RankHigh   NUMBER                                       -- 排名
    );
		-- 声明一个变量来存储从游标提取的数据
    cur_rec cur_type;
    -- 小于0大于-5000
    counterb NUMBER := 0; 
    CURSOR curb IS
        SELECT *
        FROM (
            SELECT 
                a.c_unitname,
                ROUND(100 * n_amount2 / n_amount5, 2) AS Ratio,
                RANK() OVER (ORDER BY n_amount2 / n_amount5 DESC) AS RankHigh 
            FROM
                jilin.dt_site_profit_unit a
                LEFT JOIN jilin.base_unit_order b ON a.c_unitno = b.c_unitno
            WHERE
                c_period = v_period AND 
                a.c_unitname != '全省'
        ) t
        WHERE
            RankHigh IN (1, 2, 3, 7, 8, 9)
        ORDER BY
            RankHigh;
  
BEGIN
  
  -- 打开游标
    OPEN curb;
    
    -- 循环提取数据
    LOOP
        -- 提取一行数据到 cur_rec 中
        FETCH curb INTO cur_rec;
        
        -- 检查是否已提取完所有数据
        EXIT WHEN curb%NOTFOUND;
				
				
        -- 增加计数器
        counterb := counterb + 1;
        
        -- 根据计数器值将 c_unitname 赋予不同的变量
        CASE counterb
            WHEN 1 THEN unitname1b := cur_rec.c_unitname;
												numberu1b := cur_rec.Ratio;
            WHEN 2 THEN unitname2b := cur_rec.c_unitname;
												numberu2b := cur_rec.Ratio;
            WHEN 3 THEN unitname3b := cur_rec.c_unitname;
												numberu3b := cur_rec.Ratio;
            WHEN 4 THEN unitname4b := cur_rec.c_unitname;
												numberu4b := cur_rec.Ratio;
            WHEN 5 THEN unitname5b := cur_rec.c_unitname;
												numberu5b := cur_rec.Ratio;
            WHEN 6 THEN unitname6b := cur_rec.c_unitname;
												numberu6b := cur_rec.Ratio;
        END CASE;
    END LOOP;
    
    -- 关闭游标
    CLOSE curb;
 
 
  -- 删除指定期间的记录
  DELETE FROM JILIN.WORD_ICT_DETAILS WHERE c_period = v_period AND C_TYPE='基站';
  COMMIT; -- 提交删除操作
  -- 插入值
  INSERT INTO JILIN.WORD_ICT_DETAILS
  VALUES (v_period,unitname1b,'基站');
  COMMIT; -- 提交插入操作
  
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生错误: ' || SQLCODE || ' - ' || SQLERRM);
    ROLLBACK; -- 回滚事务，撤销所有对数据库的更改
END;