有个游标 cursor c1(v_mcode varchar2, v_ifchk varchar2) is
select *
from a
where (a.mrlmcode = v_mcode or v_mcode is null) and
a.mrlrdate>=to_date(P_SDATE, 'yyyymmdd')
and a.mrlifchk = v_ifchk
order by a.mrlrdate; view_mrl a%rowtype; open c1(P_MICODE, P_ISCHK);
loop
fetch c1 into view_mrl;
执行c1用时0.047秒
测试到 fetch c1 into view_mrl; 停了几秒
我觉得是这影响了速度,不知道是什么原因
最后也能执行成功,就是速度慢
select *
from a
where (a.mrlmcode = v_mcode or v_mcode is null) and
a.mrlrdate>=to_date(P_SDATE, 'yyyymmdd')
and a.mrlifchk = v_ifchk
order by a.mrlrdate; view_mrl a%rowtype; open c1(P_MICODE, P_ISCHK);
loop
fetch c1 into view_mrl;
执行c1用时0.047秒
测试到 fetch c1 into view_mrl; 停了几秒
我觉得是这影响了速度,不知道是什么原因
最后也能执行成功,就是速度慢
解决方案 »
- SQL语句报表
- 求救....序列写入时变成16进制
- ORACLE表空间的问题
- commit问题?
- 一医院收费系统,orcale7.3+pb,无源代码,现在想和医保系统实时联网,请教各位方案?
- 祝贺zhangshunshi成为斑竹,祝贺CSDN越来越繁荣!
- exp 出现的LRM-00112: multiple values not allowed for parameter 'log' 的问题
- 求PL/SQL帮助,最好是中文版,能检索的那种,谢谢!
- 如何保證多個SESSION同時SELECT的時候得到數據的正確性?
- 我的系统要重装,我该怎样保留我的数据库?
- ORCALE 导出为TXT文件 速度太慢了
- oracel遇到00922错误 急急!!!
FETCH ... BULK COLLECT INTO ...
报这个错
编译错误错误:PLS-00497: 无法混合 INTO 列表中的单行和多行 (BULK)
行:40
文本:fetch c1 BULK COLLECT into view_mrl;
DECLARE
TYPE NameList IS TABLE OF employees.last_name%TYPE;
TYPE SalList IS TABLE OF employees.salary%TYPE;
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE salary > 10000;
names NameList;
sals SalList;
TYPE RecList IS TABLE OF c1%ROWTYPE;
recs RecList;
v_limit PLS_INTEGER := 10;
PROCEDURE print_results IS
BEGIN
IF names IS NULL OR names.COUNT = 0 THEN -- check if collections are empty
DBMS_OUTPUT.PUT_LINE('No results!');
ELSE
DBMS_OUTPUT.PUT_LINE('Results: ');
FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(' Employee ' || names(i) || ': $' || sals(i));
END LOOP;
END IF;
END;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- Processing all results at once ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO names, sals;
CLOSE c1;
print_results();
DBMS_OUTPUT.PUT_LINE('--- Processing ' || v_limit || ' rows at a time ---');
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO names, sals LIMIT v_limit;
EXIT WHEN names.COUNT = 0;
print_results();
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE('--- Fetching records rather than columns ---');
OPEN c1;
FETCH c1 BULK COLLECT INTO recs;
FOR i IN recs.FIRST .. recs.LAST
LOOP
-- Now all the columns from the result set come from a single record
DBMS_OUTPUT.PUT_LINE(' Employee ' || recs(i).last_name || ': $'
|| recs(i).salary);
END LOOP;
END;
/
v_mrlrdate t_mrlrdate;在loop里
if v_mrlrdate>=to_date(p_edate,'yyyymmdd')
编译错误错误:PLS-00306: 调用 '>=' 时参数个数或类型错误
行:55
文本:if v_mrlrdate>=to_date(p_edate,'yyyymmdd') and c1%rowcount=1 then
LOOP
DBMS_OUTPUT.PUT_LINE(' Employee ' || names(i) || ': $' || sals(i));
END LOOP;