是嵌套表吧 试试这个 select open_his.bit_1,count(open_his.bit_1) hit_count bulk collect into Temp_Table_1 //////// from open_his WHERE open_his.open_date <= vd_OpenDate group by open_his.bit_1 order by hit_count; 如果还不可以那就初始化一下Temp_Table_1
首先感谢 2 位!to dihai2000(haifeng):权限是有的to l2g32003(leeshow):报 INTO 列表中的表达式 'TEMP_TABLE_1' 类型错误,还有上述代码是编译过程中的错误,应该和初始化没有关系的,‘temp_table’和‘temp_table_1’除了名称不同外,其他完全相同
create or replace procedure up_calc(vd_OpenDate in date) as str varchar2(50); begin str:='CREATE GLOBAL TEMPORARY TABLE temp_table ON COMMIT PRESERVE ROWS as select * from bit_count where 1=2'; execute immediate str; commit; insert into temp_table select open_his.bit_1,count(open_his.bit_1) hit_count from open_his WHERE open_his.open_date <= vd_OpenDate group by open_his.bit_1 order by hit_count; end; /
to beckhambobo(beckham):我的机器上恰好有 temp_table 表,所以没有报错
是不是没有操作表temp_table_1权限的问题
to Michaelyfj(星星还是那颗星星) :temp_table_1 是一个兼套表,是在存储过程中建立的
to beckhambobo(beckham):我本来是想用临时表的,但存储过程里面好像不可以用 create 啊,你刚才的例子我运行了,可以,但在引用的时候,临时表还不存在,所以编译报错啊
是兼套表嘛 我运行了一个sql 也许有用 SQL> create table emp(empno number(4),ename varchar2(10));表已创建。SQL> create table t as select * from emp where 1=0;表已创建。SQL> insert into emp select empno,ename from scott.emp;已创建14行。SQL> commit;提交完成。 1 create or replace procedure i_e as 2 type t_e is table of emp%rowtype; 3 t_v t_e; 4 begin 5 select * bulk collect into t_v from emp ; 6 for i in 1..t_v.count loop 7 insert into t values(t_v(i).empno,t_v(i).ename); 8 end loop; 9* end; SQL> /过程已创建。SQL> begin 2 i_e(); 3 end; 4 /PL/SQL 过程已成功完成。SQL> select * from t; EMPNO ENAME ---------- ---------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER已选择14行。
试试这个
select open_his.bit_1,count(open_his.bit_1) hit_count
bulk collect into Temp_Table_1 ////////
from open_his
WHERE open_his.open_date <= vd_OpenDate
group by open_his.bit_1
order by hit_count;
如果还不可以那就初始化一下Temp_Table_1
as
str varchar2(50);
begin
str:='CREATE GLOBAL TEMPORARY TABLE temp_table ON COMMIT PRESERVE ROWS as select * from bit_count where 1=2';
execute immediate str;
commit;
insert into temp_table
select open_his.bit_1,count(open_his.bit_1) hit_count
from open_his
WHERE open_his.open_date <= vd_OpenDate
group by open_his.bit_1
order by hit_count;
end;
/
我运行了一个sql 也许有用
SQL> create table emp(empno number(4),ename varchar2(10));表已创建。SQL> create table t as select * from emp where 1=0;表已创建。SQL> insert into emp select empno,ename from scott.emp;已创建14行。SQL> commit;提交完成。 1 create or replace procedure i_e as
2 type t_e is table of emp%rowtype;
3 t_v t_e;
4 begin
5 select * bulk collect into t_v from emp ;
6 for i in 1..t_v.count loop
7 insert into t values(t_v(i).empno,t_v(i).ename);
8 end loop;
9* end;
SQL> /过程已创建。SQL> begin
2 i_e();
3 end;
4 /PL/SQL 过程已成功完成。SQL> select * from t; EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER已选择14行。