是这样的 我用delphi做了一个程序 这个程序实现是这样的 先SELECT 一些数据 然后用三方控件的oraloader 将选出来的数据在导入到oracle的数据库表里面去 结果偶尔就会报 Cursor must be opened 请大家帮我分析了 谢谢!
解决方案 »
- oracle游标的最大用途
- sql*load导入大批数据的问题
- oracle分析函数
- 安装Oracle后出现的怪现象,高手请进!!!!!!!!!!!!!!
- 再线急等 !!!oracle 用netca配置本地命名并测试 报错 ORA-000604 ORA-01756
- 怎样关掉Oracle自带的apache( 即让Apache不在开机时启动)????
- 初级问题,请不吝赐教!!
- 怎样才能用LIKE查找字段本身就包含‘%’的结果?
- 我想知道,在Oracle中写存储过程的步骤
- 怎么根据组织ID和上级组织ID来获取到全路径ID,有很多列的,求助
- 请教各位PL/SQL中单步调试ORACLE存储过程的问题,多谢
- 数据库对象的约束问题
-- 每次在使用完游标之后,是否有关闭语句,
-- 如果在打开游标后,发生异常,那么异常块中是否有判断并关闭已经打开的游标的语句!
-- 例如:CREATE OR REPLACE PROCEDURE pro_mon_website_channel_num
/***************************************************
** 功能:CV、UV按频道监控
** 说明:核对DW库中表 f_website_channel_num
** 中各频道的CV、UV数据是否存在异常
** 创建者:luoyoumou
** 创建时间:2012.05.21
****************************************************/
(
i_cdate DATE DEFAULT TRUNC(SYSDATE-1), -- 要监控的数据的日期
i_ndays NUMBER := 5, -- 近N天的平均值(包括i_cdate当天:如果当天是工作日,则表示近N个工作日的平均值;如果当天是非工作日,则表示近N个非工作日的平均值)
i_cv_rate NUMBER := 0.30, -- CV增长幅度(默认参数值已测试调整)
i_uv_rate NUMBER := 0.26 -- UV增长幅度(默认参数值已测试调整)
)
AS
v_table_name VARCHAR2(30);
v_link_name VARCHAR2(30);
v_cdate NUMBER;
v_data NUMBER; v_mail_title VARCHAR2(200);
v_mail_info VARCHAR2(32767);
v_mail_from VARCHAR2(400);
v_mail_to VARCHAR2(400); v_pro_name VARCHAR2(30);
v_if_run NUMBER(2,0);
v_run_status NUMBER(2,0);
v_run_alltimes NUMBER(18,0); v_err_code NUMBER;
v_err_msg VARCHAR2(200); CURSOR f_website_channel_num_cur(ic_cdate NUMBER,ic_ndays NUMBER,ic_cv_rate NUMBER,ic_uv_rate NUMBER)
IS
select ......; f_website_channel_num_rec f_website_channel_num_cur%ROWTYPE;
BEGIN
v_pro_name := UPPER('pro_mon_website_channel_num');
v_cdate := TO_NUMBER(TO_CHAR(i_cdate,'YYYYMMDD')); -- 查看存储过程今天是否已执行成功
SELECT NVL(COUNT(1),0) AS if_run,
NVL(SUM(run_status),0) AS run_status,
NVL(SUM(run_alltimes),0) AS run_alltimes
INTO v_if_run, v_run_status, v_run_alltimes
FROM t_pro_runlog
WHERE pro_name=pro_mon_website_channel_num.v_pro_name
AND date_id = pro_mon_website_channel_num.v_cdate
AND run_date = TRUNC(SYSDATE); IF v_if_run=0 OR (v_if_run=1 AND v_run_status =0) THEN -- 如果今天“未执行”或“未执行成功”
BEGIN v_table_name := upper('f_website_channel_num');
v_link_name := upper('tdw12_tddwweb'); v_mail_info := '';
v_mail_title := 'CV、UV按频道监控('||TO_CHAR(i_cdate,'YYYYMMDD')||')';
v_mail_from := fun_getemails('00826'); -- 信春雷 -- 先执行pro_get_singletable_inc存储过程,将i_cdate那天的数据merge into表z_global_table_check。
pro_get_singletable_inc(v_table_name,v_link_name,i_cdate); -- 将f_website_brocast_num表的i_cdate那天的数据行数打入变量v_data
SELECT incre_num INTO v_data
FROM z_global_table_check
WHERE table_name=pro_mon_website_channel_num.v_table_name
AND link_name=pro_mon_website_channel_num.v_link_name
AND check_date=pro_mon_website_channel_num.i_cdate;
IF v_data>0 THEN -- 如果表中要比较的那天有数据
BEGIN
OPEN f_website_channel_num_cur(v_cdate,i_ndays,i_cv_rate,i_uv_rate); -- 打开游标 LOOP
FETCH f_website_channel_num_cur INTO f_website_channel_num_rec;
EXIT WHEN f_website_channel_num_cur%NOTFOUND;
v_mail_info := v_mail_info||
LPAD(TO_CHAR(f_website_channel_num_rec.channel_id,'999999'),8,' ')||CHR(9)||
LPAD(f_website_channel_num_rec.channel_name,20,' ')||CHR(9)||
LPAD(TO_CHAR(f_website_channel_num_rec.cv_num,'999G999G999G999'),20,' ')||CHR(9)||
LPAD(TO_CHAR(f_website_channel_num_rec.res_ndays_avg_cv_num,'999G999G999G999'),20,' ')||CHR(9)||
LPAD(TO_CHAR(f_website_channel_num_rec.abs_cv_num_rate,'999G990D999')||'%',14,' ')||CHR(9)||
LPAD(f_website_channel_num_rec.cv_asc_status,6,' ')||CHR(9)||
LPAD(TO_CHAR(f_website_channel_num_rec.uv_num,'999G999G999G999'),20,' ')||CHR(9)||
LPAD(TO_CHAR(f_website_channel_num_rec.res_ndays_avg_uv_num,'999G999G999G999'),20,' ')||CHR(9)||
LPAD(TO_CHAR(f_website_channel_num_rec.abs_uv_num_rate,'999G990D999')||'%',14,' ')||CHR(9)||
LPAD(f_website_channel_num_rec.uv_asc_status,6,' ')||CHR(10);
END LOOP;
CLOSE f_website_channel_num_cur; -- 关闭游标 IF v_mail_info IS NOT NULL THEN
v_mail_info := LPAD('-',180,'-')||CHR(10)||
LPAD('频道ID',8,' ')||CHR(9)||
LPAD('频道名称',20,' ')||CHR(9)||
LPAD('CV数('||TO_CHAR(i_cdate,'YYYYMMDD')||')',20,' ')||CHR(9)||
LPAD('近'||i_ndays||'天平均CV',20,' ')||CHR(9)||
LPAD('CV增幅',14,' ')||CHR(9)||
LPAD('CV增势',6,' ')||CHR(9)||
LPAD('UV数('||TO_CHAR(i_cdate,'YYYYMMDD')||')',20,' ')||CHR(9)||
LPAD('近'||i_ndays||'天平均UV',20,' ')||CHR(9)||
LPAD('UV增幅',14,' ')||CHR(9)||
LPAD('UV增势',6,' ')||CHR(10)||
LPAD('-',180,'-')||CHR(10)||
v_mail_info;
v_mail_info := v_mail_info||CHR(10)||CHR(10)||' 备注: CV增幅 >'||TO_CHAR(i_cv_rate,'90D999')||
' OR UV增幅 >'||TO_CHAR(i_uv_rate,'90D999')||
' 即为符合要求的数据。'||CHR(10); -- 邮件通知相关负责人
v_mail_to := fun_getemails('00826,00161,01142,99999'); -- 信春雷、张晓、罗友谋、Data_Analysis
PRO_SENDEMAIL(v_mail_info,v_mail_title,v_mail_from,v_mail_to);
END IF; -- 更新日志表
MERGE INTO t_pro_runlog t1
USING (select pro_mon_website_channel_num.v_pro_name AS pro_name, v_cdate AS date_id, TRUNC(SYSDATE) AS run_date, SYSDATE AS lastrun_time, 1 AS run_alltimes, 1 AS run_status FROM dual) t2
ON (t1.pro_name=t2.pro_name AND t1.date_id=t2.date_id)
WHEN MATCHED THEN
UPDATE SET t1.run_date=t2.run_date, t1.lastrun_time=t2.lastrun_time, t1.run_alltimes=t1.run_alltimes+1, t1.run_status=1
WHEN NOT MATCHED THEN
INSERT (pro_name,date_id,run_date,lastrun_time,run_alltimes,run_status)
VALUES (t2.pro_name,t2.date_id,t2.run_date,t2.lastrun_time,t2.run_alltimes,t2.run_status);
COMMIT; END;
ELSE
BEGIN
v_mail_info := '表 '||v_table_name||'@'||v_link_name||' '||TO_CHAR(i_cdate,'YYYYMMDD')||' 的数据缺失。'||CHR(10)||' 导致报警任务无法执行,请核对!'||CHR(10);
-- 如果是首次异常,则邮件通知相关负责人
IF v_run_alltimes =0 THEN
v_mail_to := fun_getemails('00826,00161,01142'); -- 信春雷、张晓、罗友谋
PRO_SENDEMAIL(v_mail_info,v_mail_title,v_mail_from,v_mail_to);
END IF; -- 更新日志表
MERGE INTO t_pro_runlog t1
USING (select pro_mon_website_channel_num.v_pro_name AS pro_name, v_cdate AS date_id, TRUNC(SYSDATE) AS run_date, SYSDATE AS lastrun_time, 1 AS run_alltimes, 0 AS run_status, v_mail_info AS err_info FROM dual) t2
ON (t1.pro_name=t2.pro_name AND t1.date_id=t2.date_id)
WHEN MATCHED THEN
UPDATE SET t1.run_date=t2.run_date, t1.lastrun_time=t2.lastrun_time, t1.run_alltimes=t1.run_alltimes+1, t1.run_status=0, t1.err_info=t2.err_info
WHEN NOT MATCHED THEN
INSERT (pro_name,date_id,run_date,lastrun_time,run_alltimes,run_status,err_info)
VALUES (t2.pro_name,t2.date_id,t2.run_date,t2.lastrun_time,t2.run_alltimes,t2.run_status,t2.err_info);
COMMIT;
END;
END IF;
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
BEGIN
-- 若发生异常:关闭尚未关闭的游标 IF f_website_channel_num_cur%ISOPEN THEN
close f_website_channel_num_cur;
END IF; v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 200);
v_mail_info := 'Error code: '||v_err_code||CHR(10)||
'Error message: '||v_err_msg||CHR(10);
-- 如果是首次异常,则邮件通知相关负责人
IF v_run_alltimes =0 THEN
v_mail_to := pkg_email_fromto.fun_err_to_mail();
PRO_SENDEMAIL(v_mail_info,v_mail_title,v_mail_from,v_mail_to);
END IF; -- 更新日志表
MERGE INTO t_pro_runlog t1
USING (select pro_mon_website_channel_num.v_pro_name AS pro_name, v_cdate AS date_id, TRUNC(SYSDATE) AS run_date, SYSDATE AS lastrun_time, 1 AS run_alltimes, 0 AS run_status, v_mail_info AS err_info FROM dual) t2
ON (t1.pro_name=t2.pro_name AND t1.date_id=t2.date_id)
WHEN MATCHED THEN
UPDATE SET t1.run_date=t2.run_date, t1.lastrun_time=t2.lastrun_time, t1.run_alltimes=t1.run_alltimes+1, t1.run_status=0, t1.err_info=t2.err_info
WHEN NOT MATCHED THEN
INSERT (pro_name,date_id,run_date,lastrun_time,run_alltimes,run_status,err_info)
VALUES (t2.pro_name,t2.date_id,t2.run_date,t2.lastrun_time,t2.run_alltimes,t2.run_status,t2.err_info);
COMMIT;
END;
END;
/