我本人建议你最好是在其他地方建立好了,然后再过程中使用,这样的效率会提高。动态创建
create or replace procedure pro_temp(v_col1 varchar2,v_col2 varchar2) as
v_num number;
begin
select count(*) into v_num from user_tables where table_name='T_TEMP';
--create temporary table
if v_num<1 then
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (
COL1 VARCHAR2(10),
COL2 VARCHAR2(10)
) ON COMMIT delete ROWS';
end if;
--insert data
execute immediate 'insert into t_temp values(''' v_col1 ''',''' v_col2 ''')';
execute immediate 'select col1 from t_temp' into v_num;
dbms_output.put_line(v_num);
execute immediate 'delete from t_temp';
commit;
execute immediate 'drop table t_temp';
end pro_temp;
create or replace procedure pro_temp(v_col1 varchar2,v_col2 varchar2) as
v_num number;
begin
select count(*) into v_num from user_tables where table_name='T_TEMP';
--create temporary table
if v_num<1 then
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (
COL1 VARCHAR2(10),
COL2 VARCHAR2(10)
) ON COMMIT delete ROWS';
end if;
--insert data
execute immediate 'insert into t_temp values(''' v_col1 ''',''' v_col2 ''')';
execute immediate 'select col1 from t_temp' into v_num;
dbms_output.put_line(v_num);
execute immediate 'delete from t_temp';
commit;
execute immediate 'drop table t_temp';
end pro_temp;
解决方案 »
- 求救!
- oracle安装出现以下错误 如何解决?
- 在orcale 10G 增加一个服务 服务测试不通过
- 很急~~~~~~~!
- 十分紧急的问题,紧急求助。oracle9i的left join有状况
- ORACLE的学习问题
- select count(*) as temp from pre_house where recnum not in (select distinct max(t1.recnum) from pre_house t1 group by t1.righ
- 数据库的安装问题急急急!!!
- Redhat9下Oracle
- 在oracle中如何使字段自动增加?
- 求这样一个SQL语句!!!!
- oracle怎样像sql Server那样将表结构及其数据库相关的scipt倒出到文本文件?
create or replace view as select...