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;