请问为什么老提示临时表不存在.
我上面已经创建啦.
高手....
/*create or replace package my_pacage as
type my_cursor is ref cursor;
end my_pacage;*/create or replace procedure mypro(my_cursor out my_pacage.my_cursor)
as
thesql varchar2(4000);
productName varchar2(4000);
begin --保存按条件分组后的数据
execute immediate 'CREATE GLOBAL TEMPORARY TABLE temp
(productid number,branchname varchar2(20),businesshallname varchar2(20), brand_ch varchar2(20), productname varchar2(20),total number)
ON COMMIT PRESERVE ROWS';
--commit;
execute immediate 'insert into temp
select productid,branchname,businesshallname,brand_ch,productname,count(productid) as total from v_experiencelog where
to_char(logintime,''yyyy-mm-dd'') >=''2007-10-30'' and vexptype=''手机'' and productid is not null
group by brand_ch,productname,businesshallname,branchname,productid
order by total desc';
--commit;
--保存排名前100的品牌机型数据
execute immediate 'create global temporary table topten(brand_ch VARCHAR2(20),productname VARCHAR2(20),total number) on commit delete rows';
--commit;
execute immediate'insert into topten select brand_ch,productname,count(productid) as total from v_experiencelog where
to_char(logintime,''yyyy-mm-dd'') >=''2007-10-30'' and vexptype=''手机'' and productid is not null
and rownum<102 group by brand_ch,productname
order by total desc';
--commit; --保存按条件分组后的数据在排名前100的品牌机型中的数据
execute immediate'create global temporary table truedata(productid number,branchname varchar2(20),businesshallname varchar2(20), brand_ch varchar2(20), productname varchar2(20),total number)
ON COMMIT PRESERVE ROWS';
--commit;
execute immediate'insert into truedata select productid,branchname,businesshallname,brand_ch,productname,count(productid) as total from v_experiencelog where
to_char(logintime,''yyyy-mm-dd'') >=''2007-10-30'' and vexptype=''手机'' and productid is not null
and productname in (select productname from topten) group by brand_ch,productname,businesshallname,branchname,productid
order by total desc';
--commit; declare CURSOR my_cursor1 IS SELECT distinct productname FROM v_experiencelog ;
begin
OPEN my_cursor1;
LOOP
fetch my_cursor1 into productName;
exit when my_cursor1%notfound ;
thesql:=thesql||',sum(decode(productname,'||productName||',sqty,0)) as' ||productName;
end loop;
close my_cursor1;
end;
--dbms_output.put_line(to_char(thesql));
--execute immediate'drop table temp';
--execute immediate'drop table topten';
--execute immediate'drop table truedata';
--select * from truedata
execute immediate'select branchname,businesshallname
from truedata group by branchname,businesshallname';--open my_cursor for select branchname,businesshallname,thesql from truedata group by branchname,businesshallname;
open my_cursor for 'select * from temp';end mypro;
我上面已经创建啦.
高手....
/*create or replace package my_pacage as
type my_cursor is ref cursor;
end my_pacage;*/create or replace procedure mypro(my_cursor out my_pacage.my_cursor)
as
thesql varchar2(4000);
productName varchar2(4000);
begin --保存按条件分组后的数据
execute immediate 'CREATE GLOBAL TEMPORARY TABLE temp
(productid number,branchname varchar2(20),businesshallname varchar2(20), brand_ch varchar2(20), productname varchar2(20),total number)
ON COMMIT PRESERVE ROWS';
--commit;
execute immediate 'insert into temp
select productid,branchname,businesshallname,brand_ch,productname,count(productid) as total from v_experiencelog where
to_char(logintime,''yyyy-mm-dd'') >=''2007-10-30'' and vexptype=''手机'' and productid is not null
group by brand_ch,productname,businesshallname,branchname,productid
order by total desc';
--commit;
--保存排名前100的品牌机型数据
execute immediate 'create global temporary table topten(brand_ch VARCHAR2(20),productname VARCHAR2(20),total number) on commit delete rows';
--commit;
execute immediate'insert into topten select brand_ch,productname,count(productid) as total from v_experiencelog where
to_char(logintime,''yyyy-mm-dd'') >=''2007-10-30'' and vexptype=''手机'' and productid is not null
and rownum<102 group by brand_ch,productname
order by total desc';
--commit; --保存按条件分组后的数据在排名前100的品牌机型中的数据
execute immediate'create global temporary table truedata(productid number,branchname varchar2(20),businesshallname varchar2(20), brand_ch varchar2(20), productname varchar2(20),total number)
ON COMMIT PRESERVE ROWS';
--commit;
execute immediate'insert into truedata select productid,branchname,businesshallname,brand_ch,productname,count(productid) as total from v_experiencelog where
to_char(logintime,''yyyy-mm-dd'') >=''2007-10-30'' and vexptype=''手机'' and productid is not null
and productname in (select productname from topten) group by brand_ch,productname,businesshallname,branchname,productid
order by total desc';
--commit; declare CURSOR my_cursor1 IS SELECT distinct productname FROM v_experiencelog ;
begin
OPEN my_cursor1;
LOOP
fetch my_cursor1 into productName;
exit when my_cursor1%notfound ;
thesql:=thesql||',sum(decode(productname,'||productName||',sqty,0)) as' ||productName;
end loop;
close my_cursor1;
end;
--dbms_output.put_line(to_char(thesql));
--execute immediate'drop table temp';
--execute immediate'drop table topten';
--execute immediate'drop table truedata';
--select * from truedata
execute immediate'select branchname,businesshallname
from truedata group by branchname,businesshallname';--open my_cursor for select branchname,businesshallname,thesql from truedata group by branchname,businesshallname;
open my_cursor for 'select * from temp';end mypro;
type my_cursor is ref cursor;
end my_pacage;*/create or replace procedure mypro(my_cursor out my_pacage.my_cursor)
as
thesql varchar2(4000);
productName varchar2(4000);
begin --保存按条件分组后的数据
execute immediate 'CREATE GLOBAL TEMPORARY TABLE temp
(productid number,branchname varchar2(20),businesshallname varchar2(20), brand_ch varchar2(20), productname varchar2(20),total number)
ON COMMIT PRESERVE ROWS';
--commit;
execute immediate 'insert into temp
select productid,branchname,businesshallname,brand_ch,productname,count(productid) as total from v_experiencelog where
to_char(logintime,''yyyy-mm-dd'') >=''2007-10-30'' and vexptype=''手机'' and productid is not null
group by brand_ch,productname,businesshallname,branchname,productid
order by total desc';
--commit;
--保存排名前100的品牌机型数据
execute immediate 'create global temporary table topten(brand_ch VARCHAR2(20),productname VARCHAR2(20),total number) on commit delete rows';
--commit;
execute immediate'insert into topten select brand_ch,productname,count(productid) as total from v_experiencelog where
to_char(logintime,''yyyy-mm-dd'') >=''2007-10-30'' and vexptype=''手机'' and productid is not null
and rownum<102 group by brand_ch,productname
order by total desc';
--commit; --保存按条件分组后的数据在排名前100的品牌机型中的数据
execute immediate'create global temporary table truedata(productid number,branchname varchar2(20),businesshallname varchar2(20), brand_ch varchar2(20), productname varchar2(20),total number)
ON COMMIT PRESERVE ROWS';
--commit;
execute immediate'insert into truedata select productid,branchname,businesshallname,brand_ch,productname,count(productid) as total from v_experiencelog where
to_char(logintime,''yyyy-mm-dd'') >=''2007-10-30'' and vexptype=''手机'' and productid is not null
and productname in (select productname from topten) group by brand_ch,productname,businesshallname,branchname,productid
order by total desc';
--commit; declare CURSOR my_cursor1 IS SELECT distinct productname FROM truedata order by total;
begin
OPEN my_cursor1;
LOOP
fetch my_cursor1 into productName;
exit when my_cursor1%notfound ;
thesql:=thesql||',sum(decode(productname,'||productName||',sqty,0)) as' ||productName;
end loop;
close my_cursor1;
end;
--dbms_output.put_line(to_char(thesql));
--execute immediate'drop table temp';
--execute immediate'drop table topten';
--execute immediate'drop table truedata';
--select * from truedata
execute immediate'select branchname,businesshallname
from truedata group by branchname,businesshallname';--open my_cursor for select branchname,businesshallname,thesql from truedata group by branchname,businesshallname;
open my_cursor for 'select * from temp';
commit;
end mypro;
这个里面的truedata表找不到.
不能这么使用临时表.当在oracle的数据字典中还不存在这个对象的时候,oracle还没有能力从你的动态sql中预测到会有这个即将建立的表格.必须先建立.