用动态sql
declare
c_sql varchar2(300);
counter integer;
begin
for i in 1..500 loop
c_sql:='select count(*) from a'||i;
execute immediate into counter;
end loop;
end;
declare
c_sql varchar2(300);
counter integer;
begin
for i in 1..500 loop
c_sql:='select count(*) from a'||i;
execute immediate into counter;
end loop;
end;
解决方案 »
- 请高手帮我分析下下面两个SQL语句为什么执行效率有差异??
- 大数据量排序问题
- PL/SQL 里创建如下存储过程总是报错?需要帮忙看看.那们仁兄帮忙
- oracle的存储过程中判断表是否在,在的话insert否则creat(在线等)
- imp 命令的疑问?
- 安装8.05后有documentation,是有用的帮助。但是在8i中没有找到。请问在什么地方能安装。
- 如何在developer2000中求两个日期间的时间间隔
- 大家能否推荐几个学习Oracle建议?
- Oracle 游标,
- ORA-01839: 指定月份的日期无效
- 刚才回答我问题的朋友,我还有问题问你,请回答我
- 装了9i,死活启动不了Enterprise Manager Server?帮帮我吧!
c_sql varchar2(300);
counter integer;
sum integer;
begin
for i in 1..500 loop
c_sql:='select count(*) from a'||i;
execute immediate into counter;
sum:= sum+counter;
end loop;
end;
sum是关键字,不能做变量
v_cur_select := DBMS_SQL.OPEN_CURSOR;
v_total := 0;
FOR V_TABLE IN C_TABLE LOOP
sql_stmt := 'SELECT COUNT(*) FROM ' || V_TABLE.TABLE_NAME;
DBMS_SQL.PARSE(v_cur_select,sql_stmt,DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(v_cur_select,1,v_count);
v_int := DBMS_SQL.EXECUTE(v_cur_select);
IF DBMS_SQL.FETCH_ROWS(v_cur_select)=0 THEN
EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE(v_cur_select,1,v_count);
END IF; v_total := v_total + v_count; END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cur_select);
declare
c_sql varchar2(300);
counter integer;
sum integer;
begin
for i in 1..500 loop
c_sql:='select count(*) from a'||i;
execute immediate into counter;
sum:= sum+counter;
end loop;
end;
不是用在ORACLE中吧?
I have a question :
this package will be repeat call by client application ,
when it execute more than 1000 will taken ORA-01000: maximum open cursors exceededthe following code
as :create package pk_test as
t_cur is ref cursor;
function fn_GetResult return t_cur;
end pk_test;
/
create package body pk_test as
function fn_GetResult return t_cur
is
rs t_cur;
sqlview varchar2(1000);
rst t_cur ;
begin
sqlview := 'select * from table ';
if rst%isopen then
close; --- this statement never execute
end if ;
open rst for sqlview; return rst;
end fn_GetResult;end pk_test ;
/why ?
查询 Select Table_name From all_tables Where table_name like 'A%' ;
使用游标循环将数目类加。
应该没问题。
可是 我想问一下,你统计这个数的目的是??
icesummit(icesummit)的方式我不会用
select 'select count(*) from ' || table_name || ';'
from user_tables
where table_name like 'A%';
这样会生成500条查询的语句,存放导一个文件中,比如:count.sql
然后在sqlplus下运行它就可以了。
from user_tables
where table_name like 'A%';
未选定行
最后在sqlplus中@a
也可以把结果spool到一个文件中.