我的存储过程如下
create or replace procedure xxx is
type rc is ref cursor;
l_cursor rc;
v_sql varchar2(1000);
v_tab_name varchar2(30);
times varchar(20);
log_time varchar(40);
begin
select to_char(sysdate, 'yyyymmdd') into times from dual;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into log_time from dual;
select 'ex_log' || to_char(sysdate,'yyyymmdd') into v_tab_name from dual;
; v_sql:= 'select res_name, count(req_type) as resSum from ' || v_tab_name || ' group by res_name';
open l_cursor for v_sql;
loop
insert into TMON_monitor_count_exlog values(test.nextval, times, l_cursor.res_name, log_time,l_cursor.resSum,l_cursor.resSum);
end loop;
close l_cursor ;
end;
怎么把游标里面的值插入到 表 TMON_monitor_count_exlog 中
能上面那样用吗? open l_cursor for v_sql;
loop
insert into TMON_monitor_count_exlog values(test.nextval, times, l_cursor.res_name, log_time,l_cursor.resSum,l_cursor.resSum);
end loop;
close l_cursor ;
create or replace procedure xxx is
type rc is ref cursor;
l_cursor rc;
v_sql varchar2(1000);
v_tab_name varchar2(30);
times varchar(20);
log_time varchar(40);
begin
select to_char(sysdate, 'yyyymmdd') into times from dual;
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') into log_time from dual;
select 'ex_log' || to_char(sysdate,'yyyymmdd') into v_tab_name from dual;
; v_sql:= 'select res_name, count(req_type) as resSum from ' || v_tab_name || ' group by res_name';
open l_cursor for v_sql;
loop
insert into TMON_monitor_count_exlog values(test.nextval, times, l_cursor.res_name, log_time,l_cursor.resSum,l_cursor.resSum);
end loop;
close l_cursor ;
end;
怎么把游标里面的值插入到 表 TMON_monitor_count_exlog 中
能上面那样用吗? open l_cursor for v_sql;
loop
insert into TMON_monitor_count_exlog values(test.nextval, times, l_cursor.res_name, log_time,l_cursor.resSum,l_cursor.resSum);
end loop;
close l_cursor ;
解决方案 »
- 可否向dual表中插入值
- 急急急高手指点
- 如何在 ORACLE PORTAL 中使HTML页 后退按钮 点击后转向自己需要的页面地址---急!!!
- ASP.net在打开oracle数据库时出错 很急!!!!!!
- 行转列
- Oracle like 是不是只支持%和_两个通配符?
- 帮兄弟一把
- 存储过程中如何使用SQL返回结果集,我用最简单的SQL语句都不行???超级郁闷
- 在Oracle 8i 如何设置监听Listener! version:Release 8.0.5 for Windows NT
- oracle 用户查询权限问题
- 这个建表的语句怎么总是报错,提示缺少右括号?应该怎么改?
- 求教,为什么我的bdump,和udump,不存在?
(
ocursor out sys_refcursor
)
as
begin
open ocursor for select * from TMON_monitor_count_exlog ;
end;
loop
fetch 1_cursor into v_1,v_2;
exit when 1_cursor%notfound;
insert into TMON_monitor_count_exlog values(test.nextval, times, v_1, log_time,v_2,v_2);
end loop;
close l_cursor ; *********
v_1,v_2需要定义;
loop
fetch l_cursor into l_cursor_record -- 变量l_cursor_record要在前面声明
exit when l_cursor%notfound;
insert into TMON_monitor_count_exlog values(test.nextval, times, l_cursor_record.res_name, log_time,l_cursor_record.resSum,l_cursor_record.resSum);
end loop;
close l_cursor ; 引用游标和静态游标的使用步骤是相同的:
1、声明游标;
2、打开游标;
3、取出游标;
4、关闭游标。
loop
fetch l_cursor into aa,bb;
exit when l_cursor%NOTFOUND
insert into TMON_monitor_count_exlog values(test.nextval, times, aa, log_time,bb,bb);
end loop;
close l_cursor ;aa和bb要在begin前定义