请求关于动态游标的使用问题（附代码）

Question

CREATE OR REPLACE PROCEDURE Cdr_Submit_Proc
(in_flow_no IN varchar2, --服务流水号
in_ser_type IN varchar2, --服务类型
in_cdr_type IN varchar2, --话单类型
in_cont_type IN varchar2, --来访方式
in_is_service_ivr IN varchar2, --服务方式
in_prov_trans IN bus_ser_cdr12.prov_trans%type, --转递前省份
in_call_addr IN bus_ser_cdr12.call_addr%type, --主叫地址
in_called_addr IN bus_ser_cdr12.called_addr%type, --被叫地址
in_acc_device IN bus_ser_cdr12.acc_device%type, --接入设备编号
in_app_device IN bus_ser_cdr12.app_device%type, --受理设备编号
in_all_start_time IN varchar2, --总起始服务时间
in_start_time IN varchar2, --服务时间
in_during IN varchar2, --时长
in_custom_id IN bus_ser_cdr12.custom_code%type, --客户ID
in_city_code IN bus_ser_cdr12.city_code%type, --客户归属地市
in_updated_by IN varchar2, --修改人
is_acc_type IN varchar2, --业务类型 --addedbyly20010213.
out_flag OUT VARCHAR2 --成功标志（0成功。）
)
AS
vs_cdr_id bus_ser_cdr12.row_id%type; --话单ID号
vs_sql varchar2(1024);
vs_month char(2);
vs_table_name varchar2(20);
cursor_handle number;
vn_ret number;
vn_count number(2);
vd_min_date date;
vs_sysdate varchar2(14);
vd_sysdate date;
vs_start_time varchar2(20); --addedbyliuying 20010716
vs_all_start_time varchar2(20); --addedbyliuying 20010716
BEGIN
Get_Seq_No_From_Seq_Proc(14,vs_cdr_id);
vs_month := substr(in_start_time,5,2);
IF length(vs_month) < 2 OR vs_month IS NULL THEN
SELECT substr(to_char(sysdate,'yyyymmdd'),5,2)
INTO vs_month
FROM dual;
END IF;
vs_table_name := 'bus_ser_cdr' || vs_month;
SELECT to_char(sysdate,'yyyymmddhh24miss'),sysdate
INTO vs_sysdate,vd_sysdate
FROM dual;
vs_start_time := in_start_time;
vs_all_start_time := in_all_start_time;
IF length(vs_start_time) <8 OR vs_start_time IS NULL THEN
vs_start_time := vs_sysdate;
END IF;
IF length(in_all_start_time) <8 OR in_all_start_time IS NULL THEN
vs_all_start_time := vs_sysdate;
END IF;
vs_sql := 'INSERT INTO '||vs_table_name||'(row_id,flow_no,ser_type,cdr_type,cont_type,'
||'is_service_ivr,prov_trans,call_addr,called_addr,acc_device,app_device,'
||'all_start_time,start_time,during,custom_code,city_code,updated,'
||'updated_by,acc_type,service_type)'
||' VALUES('||chr(39)||vs_cdr_id||chr(39)||','||chr(39)||in_flow_no||chr(39)
||','||chr(39)||in_ser_type
||chr(39)||','||chr(39)||in_cdr_type||chr(39)||','||chr(39)||in_cont_type
||chr(39)||','||chr(39)||in_is_service_ivr||chr(39)||','||chr(39)
||in_prov_trans||chr(39)||','||chr(39)||in_call_addr||chr(39)||','
||chr(39)||in_called_addr||chr(39)||','||chr(39)||in_acc_device||chr(39)
||','||chr(39)||in_app_device||chr(39)||','||'to_date('||chr(39)
||vs_all_start_time||chr(39)||','||chr(39)||'yyyymmddhh24miss'||chr(39)
||'),to_date('||chr(39)||vs_start_time||chr(39)||','||chr(39)
||'yyyymmddhh24miss'||chr(39)||'),'||chr(39)||in_during||chr(39)||','
||chr(39)||in_custom_id||chr(39)||','||chr(39)||in_city_code||chr(39)
||',to_date('||chr(39)||vs_sysdate||chr(39)||','||chr(39)||'yyyymmddhh24miss'
||chr(39)||'),'||chr(39)||in_updated_by||chr(39)
||','||chr(39)||is_acc_type||chr(39)
||','||chr(39)||substr(in_ser_type,1,2)||chr(39)||')';
-- insert into temp_table(note,re)
-- values(vs_sql,'a');
-- commit;
cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_handle,vs_sql,DBMS_SQL.V7);
vn_ret := DBMS_SQL.EXECUTE(cursor_handle);
DBMS_SQL.CLOSE_CURSOR(cursor_handle);
--ADDEDBYLY20010619. 只保留10次通最后话。
BEGIN
INSERT INTO bus_ser_cdr_current(ser_type,cdr_type,cont_type,is_service_ivr,prov_trans,call_addr,
called_addr,acc_device,app_device,all_start_time,start_time,
during,custom_code,city_code,updated,updated_by,service_type,acc_type)
VALUES(in_ser_type,in_cdr_type,in_cont_type,in_is_service_ivr,in_prov_trans,in_call_addr,
in_called_addr,in_acc_device,in_app_device,to_date(vs_all_start_time,'YYYYMMDDHH24MISS'),
to_date(vs_start_time,'yyyymmddhh24miss'),
in_during,in_custom_id,in_city_code,vd_sysdate,in_updated_by,substr(in_ser_type,1,2),is_acc_type);
EXCEPTION
 WHEN OTHERS THEN
  NULL;
    --temp:=sqlcode;
  --dbms_output.put_line('bus_ser_cdr_current:'||temp);
END;
SELECT count(*)
INTO vn_count
FROM bus_ser_cdr_current
WHERE custom_code = in_custom_id;
IF vn_count > 10 THEN
SELECT MIN(updated)
INTO vd_min_date
FROM bus_ser_cdr_current
WHERE custom_code = in_custom_id;
DELETE bus_ser_cdr_current
WHERE custom_code = in_custom_id
AND updated = vd_min_date;
END IF;
out_flag := 0;
EXCEPTION
WHEN OTHERS THEN
out_flag := sqlcode;
END;
/
应用程序在调此过程中，报“游标达到最大的打开数”；oracle的open_cursors=1024,
帮忙检查一下是否此过程有问题？　谢谢

Answer 1

没有看到你使用动态游标。
检查Get_Seq_No_From_Seq_Proc(14,vs_cdr_id);
你最好跟踪一下，看到底哪里出错了

Answer 2

一般是以为游标打开后没关闭。
检查open和close是否对应

Answer 3

cursor_handle := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_handle,vs_sql,DBMS_SQL.V7);
vn_ret := DBMS_SQL.EXECUTE(cursor_handle);
DBMS_SQL.CLOSE_CURSOR(cursor_handle);以上是对cursor的应用，不过都是以调用包的形式处理的，具体包的信息，我不是很清楚，也无法给你什么意见。

Answer 4

楼主的问题应该属于动态SQL的范畴，不知道你用的数据库版本是多少。如果是8i的话倒是可以试试：EXECUTE IMMEDIATE str_dSQL;

Answer 5

在exception 里面要注意关闭游标！

Answer 6

我们有时候没有办法预测游标打开数量，而且游标速度慢，很耗资源，我建议使用bulk，我测试了一下，效果非常好。
具体用法是：
    select id bulk collect into v_bulkId from employee 
    .
    .
    .

Answer 7

另外，动态SQL的长度有限制，不能超过20000，用dbms_sql效率好，而且可以防止输入参数里面含有单引号引起的错误。