DECLARE
-- 数组变量,保存查询条件
TYPE t_id IS TABLE OF dm_phs.DEV_NUM%TYPE;
v_t_id t_id;
TYPE emp_table_type IS TABLE OF DM_VT_PHS_DEV%ROWTYPE
INDEX BY BINARY_INTEGER;
v_phs_temp emp_table_type;
-- 临时表
--v_phs_temp DM_VT_PHS_DEV%ROWTYPE;
CURSOR c
IS
SELECT DEV_NUM from dm_phs where rownum< 2;--#####
-- 循环次数
CURSOR c1(V_DEV_NUM number)
IS
SELECT * FROM DM_VT_PHS_DEV WHERE DEV_NUM = V_DEV_NUM;
cnt NUMBER := 0;
BEGIN
OPEN c;
LOOP --loop 1
cnt := cnt + 1;
-- 批量更新,一次更新10000条数据
fetch c bulk collect into v_t_id LIMIT 1;
-- 这里用forall效率更高 先试试for
FOR i IN 1 .. v_t_id.COUNT LOOP --loop 2
OPEN c1(v_t_id(i));
LOOP -- loop 3
fetch c1 bulk collect into v_phs_temp;
--fetch c1 into v_phs_temp;
-----------------------------------------
问题:是在这里我希望执行以下语句,游标应该这么写?或者类似的情况,要求不要再进行到原表中select了。就对c1操作。
-----------------------------------------
-- SELECT avg(MONTHLY_AMT) into V_AVG_MONTHLY_AMT from v_phs_temp where MONTHLY_AMT>0 GROUP BY dev_num;
dbms_output.put_line('jieguo:' || V_AVG_MONTHLY_AMT);
EXIT WHEN c1%NOTFOUND;
END LOOP; --end loop3
CLOSE c1;
end loop; --end loop 2
-- 提交
commit;
-- 循环退出
exit when c%NOTFOUND;
END LOOP;--end loop 1
dbms_output.put_line('循环次数:' || cnt);
CLOSE c;
END;
-- 数组变量,保存查询条件
TYPE t_id IS TABLE OF dm_phs.DEV_NUM%TYPE;
v_t_id t_id;
TYPE emp_table_type IS TABLE OF DM_VT_PHS_DEV%ROWTYPE
INDEX BY BINARY_INTEGER;
v_phs_temp emp_table_type;
-- 临时表
--v_phs_temp DM_VT_PHS_DEV%ROWTYPE;
CURSOR c
IS
SELECT DEV_NUM from dm_phs where rownum< 2;--#####
-- 循环次数
CURSOR c1(V_DEV_NUM number)
IS
SELECT * FROM DM_VT_PHS_DEV WHERE DEV_NUM = V_DEV_NUM;
cnt NUMBER := 0;
BEGIN
OPEN c;
LOOP --loop 1
cnt := cnt + 1;
-- 批量更新,一次更新10000条数据
fetch c bulk collect into v_t_id LIMIT 1;
-- 这里用forall效率更高 先试试for
FOR i IN 1 .. v_t_id.COUNT LOOP --loop 2
OPEN c1(v_t_id(i));
LOOP -- loop 3
fetch c1 bulk collect into v_phs_temp;
--fetch c1 into v_phs_temp;
-----------------------------------------
问题:是在这里我希望执行以下语句,游标应该这么写?或者类似的情况,要求不要再进行到原表中select了。就对c1操作。
-----------------------------------------
-- SELECT avg(MONTHLY_AMT) into V_AVG_MONTHLY_AMT from v_phs_temp where MONTHLY_AMT>0 GROUP BY dev_num;
dbms_output.put_line('jieguo:' || V_AVG_MONTHLY_AMT);
EXIT WHEN c1%NOTFOUND;
END LOOP; --end loop3
CLOSE c1;
end loop; --end loop 2
-- 提交
commit;
-- 循环退出
exit when c%NOTFOUND;
END LOOP;--end loop 1
dbms_output.put_line('循环次数:' || cnt);
CLOSE c;
END;
---------------->
CURSOR c1
IS
SELECT DEV_NUM into xx1,avg(MONTHLY_AMT) into xx2
FROM DM_VT_PHS_DEV DEV_NUM = V_DEV_NUM
where MONTHLY_AMT>0
GROUP BY DEV_NUM;之后让判断的循环参数每次与c1.xx1比较取出c1.xx2。
具体的实现方法自己想一想。我再查一下有标的基本语法是否违背!
----------------------->
OPEN bbmrcvh_cur;
BEGIN
FETCH bbmrcvh_cur INTO
lt_ihc01,
lt_ihc02,
lt_ihc03,
lt_ihc19,
lt_ihc25,
lt_ihc26,
lt_ihc27,
lt_ihc28;
EXCEPTION
----------------<
修改你的这条语句:
fetch c1 into v_phs_temp
-->fetch c1 into
lt_ihc01,
lt_ihc02;
EXCEPTION
其中lt_ihc01就是需要判断是否=i 的数值,lt_ihc02就是最终要显示的内容。
数据库里面有类似MONTHLY_AMT的字段约30个,每个都要对其做类似MONTHLY_AMT的操作,
即:求均值的时候要求对应的字段大于0;写函数的话就要做30次,这样速度太慢了(约800万记录)
DECLARE
CURSOR c
IS
SELECT dev_num
FROM dm_phs
WHERE ROWNUM < 2;
-- 循环次数
CURSOR c1 (v_dev_num NUMBER)
IS
SELECT AVG (monthly_amt) AS v_avg_monthly_amt
FROM dm_vt_phs_dev
WHERE monthly_amt > 0 AND dev_num = v_dev_num; cnt NUMBER := 0;
BEGIN
OPEN c; LOOP --loop 1
cnt := cnt + 1; OPEN c1 (c.dev_num); LOOP -- loop 3
DBMS_OUTPUT.put_line ('jieguo:' || c1.v_avg_monthly_amt);
EXIT WHEN c1%NOTFOUND;
END LOOP; --end loop3 CLOSE c1; -- 提交
COMMIT;
-- 循环退出
EXIT WHEN c%NOTFOUND;
END LOOP; --end loop 1 DBMS_OUTPUT.put_line ('循环次数:' || cnt); CLOSE c;
END;
我对你程序的意见有3条:
1、使用嵌套表,似乎没有必要;
2、先用c游标循环,又使用c.count做FOR循环,似乎重复了。
3、对C1求平均值的处理有些小问题。
DECLARE
CURSOR c
IS
SELECT dev_num
FROM dm_phs
WHERE ROWNUM < 2; -- 循环次数
cd dm_phs.dev_num%TYPE; CURSOR c1 (v_dev_num NUMBER)
IS
SELECT AVG (monthly_amt) AS v_avg_monthly_amt
FROM dm_vt_phs_dev
WHERE monthly_amt > 0 AND dev_num = v_dev_num; ccc c1%ROWTYPE;
cnt NUMBER := 0;
BEGIN
OPEN c; LOOP
cnt := cnt + 1; FETCH c
INTO cd; OPEN c1 (cd); LOOP
FETCH c1
INTO ccc; DBMS_OUTPUT.put_line ('jieguo:' || ccc.v_avg_monthly_amt);
EXIT WHEN c1%NOTFOUND;
END LOOP; CLOSE c1; EXIT WHEN c%NOTFOUND;
END LOOP; DBMS_OUTPUT.put_line ('循环次数:' || cnt); CLOSE c;
END;
您好,我们是“2006中国杰出数据库工程师评选”活动组委会。
您的帖子已经被我们转载到本次评选官方网站的“专家在线答疑”区。
http://www.bestdba.cn/match_discussion.aspx在那里,进入本次评选终选的30位数据库工程师将与您展开积极的互动。他们会为您的问题提供满意的答案,此外,您还可以在“专家在线答疑”区提出新的问题并参与讨论。您的帖子位于:
http://www.bestdba.cn/match_discussion3.aspx?pointid=559&pointid2=1&pointid3=5&pcount=stc
非常感谢您对本次活动的支持!