请教大家:如何在存储过程中创建,SELECT,INSERT,然后再DROP掉这个表呢?

Question

请教大家:我想在存储过程中创建一个临时表,同是向这个临时表插入数据,再把这个数据插入到其他表中,然后删除这个临时表,应该怎么做呢?假如临时表的名称为TEMP,和这个临时表交换数据的表名叫TEMP12!
十分感谢!!!

Answer 1

一步一步做呗
不过我建议不要每回都创建再删除的，truncate掉数据就行了
除非要建的表的列是动态的

Answer 2

首先用动态语句创建临时表，然后根据你的需要insert数据到临时表，再之后insert到你的目标表，最后drop table

Answer 3

--全动态
---on commit preserve rows 会话退出数据自动清空create or replace procedure p
as
begin
execute immediate 'create global temporary table TEMP(id number,name varchar2(10)) on commit preserve rows';
execute immediate 'insert into TEMP select * from tb' ;--向临时表插数据
commit;
execute immediate 'insert into 目标表 select * from TEMP';--向目标表插数据
commit;
execute immediate 'drop table temp';
end;

Answer 4

额  好像oracle 的存储过程不支持ddl 语句吧。。

Answer 5

这个方法可以,但是我执行存储过程的时候,他报"ORA-01031: insufficient privileges",说是没有权限吧?怎么样才能有权限在存储过程中创建表呢????

Answer 6

是要加上create or replace procedure proc_highrssi_cell(p_date date) AUTHID CURRENT_USER
as
v_date date;
如上的AUTHID CURRENT_USER吧?加上之后权限没问题了,但是在执行execute immediate 的语句中有变量v_date的就抱错说"ORA-00904: "V_DATE": invalid identifier"
含V_DATE的语句如下:
execute immediate 'insert into temp
select to_char(int_id),count(*)
from(
   select t.*,
                 trunc(t.scan_start_time)-1 +rank()over(partition by INT_ID order by t.scan_start_time desc) cnt
        from  highrssi_cell t where scan_start_time<=to_date(v_date+20/24))
 where cnt=to_date(v_date)
group by INT_ID,cnt' ;

Answer 7

真没看懂 那你需要这个临时表做什么
频繁执行ddl

Answer 8

 
wkc168
 
帮我看下那个变量报错的问题哦 
是不是在execute immediate后面的变量还要加什么才能体现出是变量啊!!!

Answer 9

execute immediate  using

Answer 10

--AUTHID CURRENT_USER嗯 就可以解决权限的问题create or replace procedure proc_highrssi_cell(p_date date) AUTHID CURRENT_USER
as
v_date date;
begin
execute immediate 'insert into temp
select to_char(int_id),count(*)
from(
  select t.*,
  trunc(t.scan_start_time)-1+rank()over(partition by INT_ID order by t.scan_start_time desc) cnt
  from highrssi_cell t where scan_start_time<=v_date+5/6)
 where cnt=v_date
group by INT_ID,cnt' ;

end;v_date date;---已经是date  不要to_date

Answer 11

不行哦!!还是提示说这个变量为无效的!
SQL> exec proc_highrssi_cell(trunc(to_date('2010-11-25 00:00:00','yyyy-mm-dd hh24:mi:ss'),'hh24'));
 
begin proc_highrssi_cell(trunc(to_date('2010-11-25 00:00:00','yyyy-mm-dd hh24:mi:ss'),'hh24')); end;
 
ORA-00904: "V_DATE": invalid identifier
ORA-06512: at "CDMAUSER.PROC_HIGHRSSI_CELL", line 262
ORA-06512: at line 2

Answer 12

ORA-06512: at "CDMAUSER.PROC_HIGHRSSI_CELL", line 262  这行有问题
估计是你的字符类型跟日期类型字段的匹配问题把你的全部贴上来

Answer 13

create or replace procedure proc_highrssi_cell(p_date date) AUTHID CURRENT_USER
as
v_date date;
sql_str1 varchar2(2000);
sql_str2 varchar2(2000);begin---传入时间数据（p_date为空，则自动查找昨天忙时的数据，如果不为空，则以传入的数据为准）
select trunc(decode(p_date,null,trunc(sysdate-1),p_date)) into v_date from dual;
---向干扰小区表中插入数据insert into highrssi_cell(
INT_ID ,
scan_start_time,
CITY ,
BssID ,
BtsID ,
CellID ,
CarrierId ,
CI ,
CellName,
bts_type,
traffic,
cell_pwr,
avg_ForwardPower,
RSSI_max_m ,
RSSI_min_m ,
RSSI_avg_m ,
RSSI_max_d ,
RSSI_min_d ,
RSSI_avg_d)
select i.int_id,
i.scan_start_time,
d.city_name "CITY",
c.BssID "BssID",
c.BtsID "BtsID",
c.CellID "CellID",
c.CarrierId "CarrierId",
e.CI "CI",
c.Cell_Name "CellName",
f.device_type "站型",
case c.car_type
when '1X' then
round((a.V_AvfAssReq_AvfSvcAss_CallDur
+a.v_AvfSvcAss_AvfAssCmp_CallDur
+a.v_AvrAssCmp_AvfRlsReq_CallDur
+a.V_AvfRlsReq_KilPrc_CallDur
+a.AvrAssCmp_AvfRlsReq_CallDur
+a.AvfRlsReq_KilPrc_CallDur)/3600,4)
else
round(b.CallDuration/3600,4)
end "话务量",
case c.car_type
when '1X' then
h.CELL_PWR
else
g.ForwardTransmitPower
end "载频功率",
i.AvgForwardPower "基站平均发送功率(dBm)",
i.MaxRSSIInstant_M "主集RSSI最大值(dBm)",
i.MinRSSIInstant_M "主集RSSI最小值(dBm)",
i.AvgRSSIInstant_M "主集RSSI平均值(dBm)",
i.MaxRSSIInstant_D "分集RSSI最大值(dBm)",
i.MinRSSIInstant_D "分集RSSI最小值(dBm)",
i.AvgRSSIInstant_D  "分集RSSI平均值(dBm)"
from C_tpa_cnt_carr_unit_zx i
left join  C_tpa_cnt_carr_zx a on  a.scan_start_time=i.scan_start_time
                                  and a.int_id=i.int_id
                                  and a.sum_level=i.sum_level
                                  and a.ne_type=i.ne_type
left join C_tpa_cnt_carr_do_zx b  on  b.scan_start_time=i.scan_start_time
                                  and b.int_id=i.int_id
                                  and b.sum_level=i.sum_level
                                  and b.ne_type=i.ne_type
left join c_tzx_par_carr_do  g on  g.int_id=i.int_id
left join c_tzx_par_carr h   on  h.int_id=i.int_id
left join c_carrier c on c.int_id=i.int_id
left join c_region_city d on d.city_id=c.city_id
left join C_CELL e on c.related_cell=e.int_id
left join c_tco_pro_cell f on f.int_id=e.int_id
where i.scan_start_time=trunc(v_date+20/24,'hh24')
and i.sum_level=0
and i.ne_type=400
and (i.AvgRSSIInstant_M>-95 or i.AvgRSSIInstant_D>-95)
and c.car_type='1X'
union all
select i.int_id,
i.scan_start_time,
d.city_name "CITY",
c.BssID "BssID",
c.BtsID "BtsID",
c.CellID "CellID",
c.CarrierId "CarrierId",
e.CI "CI",
c.Cell_Name "CellName",
f.device_type "站型",
case c.car_type
when '1X' then
round((a.V_AvfAssReq_AvfSvcAss_CallDur
+a.v_AvfSvcAss_AvfAssCmp_CallDur
+a.v_AvrAssCmp_AvfRlsReq_CallDur
+a.V_AvfRlsReq_KilPrc_CallDur
+a.AvrAssCmp_AvfRlsReq_CallDur
+a.AvfRlsReq_KilPrc_CallDur)/3600,4)
else
round(b.CallDuration/3600,4)
end "话务量",
case c.car_type
when '1X' then
h.CELL_PWR
else
g.ForwardTransmitPower
end "载频功率",
i.AvgForwardPower "基站平均发送功率(dBm)",
i.MaxRSSIInstant_M "主集RSSI最大值(dBm)",
i.MinRSSIInstant_M "主集RSSI最小值(dBm)",
i.AvgRSSIInstant_M "主集RSSI平均值(dBm)",
i.MaxRSSIInstant_D "分集RSSI最大值(dBm)",
i.MinRSSIInstant_D "分集RSSI最小值(dBm)",
i.AvgRSSIInstant_D  "分集RSSI平均值(dBm)"
from C_tpa_cnt_carr_unit_zx i
left join  C_tpa_cnt_carr_zx a on  a.scan_start_time=i.scan_start_time
                                  and a.int_id=i.int_id
                                  and a.sum_level=i.sum_level
                                  and a.ne_type=i.ne_type
left join C_tpa_cnt_carr_do_zx b  on  b.scan_start_time=i.scan_start_time
                                  and b.int_id=i.int_id
                                  and b.sum_level=i.sum_level
                                  and b.ne_type=i.ne_type
left join c_tzx_par_carr_do  g on  g.int_id=i.int_id
left join c_tzx_par_carr h   on  h.int_id=i.int_id
left join c_carrier c on c.int_id=i.int_id
left join c_region_city d on d.city_id=c.city_id
left join C_CELL e on c.related_cell=e.int_id
left join c_tco_pro_cell f on f.int_id=e.int_id
where i.scan_start_time=trunc(v_date+20/24,'hh24')
and i.sum_level=0
and i.ne_type=400
and (i.AvgRSSIInstant_M>-90 or i.AvgRSSIInstant_D>-90)
and c.car_type='DO'
union all
select l.int_id,
l.scan_start_time,
d.city_name "CITY",
c.BssID "BssID",
c.BtsID "BtsID",
c.CellID "CellID",
c.CarrierId "CarrierId",
e.CI "CI",
c.Cell_Name "CellName",
f.device_type "站型",
case c.car_type
when '1X' then
round((a.V_AvfAssReq_AvfSvcAss_CallDur
+a.v_AvfSvcAss_AvfAssCmp_CallDur
+a.v_AvrAssCmp_AvfRlsReq_CallDur
+a.V_AvfRlsReq_KilPrc_CallDur
+a.AvrAssCmp_AvfRlsReq_CallDur
+a.AvfRlsReq_KilPrc_CallDur)/3600,4)
else
round(b.CallDuration/3600,4)
end "话务量",
case c.car_type
when '1X' then
h.CELL_PWR
else
g.ForwardTransmitPower
end "载频功率",
l.AvgTP_dBm "基站平均发送功率(dBm)",
l.MaxRSSI_M_dBm "主集RSSI最大值(dBm)",
l.MinRSSI_M_dBm "主集RSSI最小值(dBm)",
l.AvgRSSI_M_dBm "主集RSSI平均值(dBm)",
l.MaxRSSI_D_dBm "分集RSSI最大值(dBm)",
l.MinRSSI_D_dBm "分集RSSI最小值(dBm)",
l.AvgRSSI_D_dBm  "分集RSSI平均值(dBm)"
from C_tpa_cnt_carr_sys_zx l
left join  C_tpa_cnt_carr_zx a on  a.scan_start_time=l.scan_start_time
                                  and a.int_id=l.int_id
                                  and a.sum_level=l.sum_level
                                  and a.ne_type=l.ne_type
left join C_tpa_cnt_carr_do_zx b  on  b.scan_start_time=l.scan_start_time
                                  and b.int_id=l.int_id
                                  and b.sum_level=l.sum_level
                                  and b.ne_type=l.ne_type
left join c_tzx_par_carr_do  g on  g.int_id=l.int_id
left join c_tzx_par_carr h   on  h.int_id=l.int_id
left join c_carrier c on c.int_id=l.int_id
left join c_region_city d on d.city_id=c.city_id
left join C_CELL e on c.related_cell=e.int_id
left join c_tco_pro_cell f on f.int_id=e.int_id
where l.scan_start_time=trunc(v_date+20/24,'hh24')
and l.sum_level=0
and l.ne_type=400
and (l.AvgRSSI_M_dBm>-95 or l.AvgRSSI_D_dBm>-95)
AND C.CAR_TYPE='1X'
UNION ALL
select l.int_id,
l.scan_start_time,
d.city_name "CITY",
c.BssID "BssID",
c.BtsID "BtsID",
c.CellID "CellID",
c.CarrierId "CarrierId",
e.CI "CI",
c.Cell_Name "CellName",
f.device_type "站型",
case c.car_type
when '1X' then
round((a.V_AvfAssReq_AvfSvcAss_CallDur
+a.v_AvfSvcAss_AvfAssCmp_CallDur
+a.v_AvrAssCmp_AvfRlsReq_CallDur
+a.V_AvfRlsReq_KilPrc_CallDur
+a.AvrAssCmp_AvfRlsReq_CallDur
+a.AvfRlsReq_KilPrc_CallDur)/3600,4)
else
round(b.CallDuration/3600,4)
end "话务量",
case c.car_type
when '1X' then
h.CELL_PWR
else
g.ForwardTransmitPower
end "载频功率",
l.AvgTP_dBm "基站平均发送功率(dBm)",
l.MaxRSSI_M_dBm "主集RSSI最大值(dBm)",
l.MinRSSI_M_dBm "主集RSSI最小值(dBm)",
l.AvgRSSI_M_dBm "主集RSSI平均值(dBm)",
l.MaxRSSI_D_dBm "分集RSSI最大值(dBm)",
l.MinRSSI_D_dBm "分集RSSI最小值(dBm)",
l.AvgRSSI_D_dBm  "分集RSSI平均值(dBm)"
from C_tpa_cnt_carr_sys_zx l
left join  C_tpa_cnt_carr_zx a on  a.scan_start_time=l.scan_start_time
                                  and a.int_id=l.int_id
                                  and a.sum_level=l.sum_level
                                  and a.ne_type=l.ne_type
left join C_tpa_cnt_carr_do_zx b  on  b.scan_start_time=l.scan_start_time
                                  and b.int_id=l.int_id
                                  and b.sum_level=l.sum_level
                                  and b.ne_type=l.ne_type
left join c_tzx_par_carr_do  g on  g.int_id=l.int_id
left join c_tzx_par_carr h   on  h.int_id=l.int_id
left join c_carrier c on c.int_id=l.int_id
left join c_region_city d on d.city_id=c.city_id
left join C_CELL e on c.related_cell=e.int_id
left join c_tco_pro_cell f on f.int_id=e.int_id
where l.scan_start_time=trunc(v_date+20/24,'hh24')
and l.sum_level=0
and l.ne_type=400
and (l.AvgRSSI_M_dBm>-90 or l.AvgRSSI_D_dBm>-90)
AND C.CAR_TYPE='DO';
commit;---更新字段days的值
---将统计的昨天的20点出现的所有高干扰小区的连续干扰情况的查询结果入到临时表：---第一步：建临时表execute immediate 'create global temporary table temp(int_id integer,num integer) on commit preserve rows';---第二步，向临时表中插入数据sql_str1 :='insert into temp
select to_char(int_id),count(*)
from(select t.*,trunc(t.scan_start_time)-1 +rank()over(partition by INT_ID order by t.scan_start_time desc) cnt
from  highrssi_cell t where scan_start_time<='||v_date||'+20/24) where cnt='||v_date||'group by INT_ID,cnt';dbms_output.put_line(sql_str1);execute immediate sql_str1;
commit;---第三步：向目标表插数据（更新干扰表中的字段days的数据（截止到昨天该小区连续干扰的天数））
sql_str2 :='update highrssi_cell a
set days=(select num from temp where int_id=a.int_id)
where scan_start_time='||v_date||'+20/24';execute immediate sql_str2;
commit;----第4步，DROP掉临时表
execute immediate 'drop table temp';commit;end;

Answer 14

补充:引用里的是整个存储过程,现在执行的错误如下(和之前的不一样,因为我修改了些东西):
SQL>  exec proc_highrssi_cell(trunc(to_date('2010-11-25 00:00:00','yyyy-mm-dd hh24:mi:ss'),'hh24'));
 
begin proc_highrssi_cell(trunc(to_date('2010-11-25 00:00:00','yyyy-mm-dd hh24:mi:ss'),'hh24')); end;
 
ORA-00907: missing right parenthesis
ORA-06512: at "CDMAUSER.PROC_HIGHRSSI_CELL", line 272
ORA-06512: at line 2
 

Answer 15

'insert into temp
select to_char(int_id),count(*)
from(
  select t.*,
  trunc(t.scan_start_time)-1+rank()over(partition by INT_ID order by t.scan_start_time desc) cnt
  from highrssi_cell t where scan_start_time<='||v_date||'+5/6)
 where cnt='||v_date||'
group by INT_ID,cnt' 

Answer 16

角色在存储过程中是不可见的. 对于通过赋予角色而得到的系统权限,可以在创建过程时加 AUTHID CURRENT_USER 解决,要是换成对象权限,加这个也不行,必须显式的赋予权限才可以!

Answer 17

--加了个时间变量，以及你后面的拼接方式改了下
create or replace procedure proc_highrssi_cell(p_date date) AUTHID CURRENT_USER
as
v_date date;
v_date1 date;
sql_str1 varchar2(2000);
sql_str2 varchar2(2000);begin
---更新字段days的值
.......---将统计的昨天的20点出现的所有高干扰小区的连续干扰情况的查询结果入到临时表：---第一步：建临时表execute immediate 'create global temporary table temp(int_id integer,num integer) on commit preserve rows';---第二步，向临时表中插入数据
v_date1:=v_date+20/24;
sql_str1 :='insert into temp
select to_char(int_id),count(*)
from (select t.*,trunc(t.scan_start_time)-1 +rank()over(partition by INT_ID order by t.scan_start_time desc) cnt
from highrssi_cell t where scan_start_time<='''||v_date1||''') where cnt='''||v_date||'''  group by INT_ID,cnt';dbms_output.put_line(sql_str1);execute immediate sql_str1;
commit;---第三步：向目标表插数据（更新干扰表中的字段days的数据（截止到昨天该小区连续干扰的天数））
sql_str2 :='update highrssi_cell a
set days=(select num from temp where int_id=a.int_id)
where scan_start_time='''||v_date1||'''';execute immediate sql_str2;
commit;----第4步，DROP掉临时表
execute immediate 'drop table temp';commit;end;

Answer 18

你好:
我用了你这个方法,以前报错的地方不抱错了,但是有新的问题:就是在删除临时表的时候报"ORA-14452: attempt to create, alter or drop an index on temporary table already in use
ORA-06512: at "CDMAUSER.PROC_HIGHRSSI_CELL", line 289
ORA-06512: at line 2",
如果我把----第4步，DROP掉临时表
execute immediate 'drop table temp';
注释了,那么存储过程就可以执行成功?不明白为什么已经用完了这个临时表,他还是被占用?另:你的拼接的原则是什么呢?必须要3个|||??

Answer 19

其实你可以把临时表放到外面单独建立因为临时表 是preserve类型的 所以必须先truncate table tmp
然后drop这是他的特性  最好是放到外面单独建立 就ok