CREATE OR REPLACE PROCEDURE proc_createTempTable(routine_id IN INT) AUTHID CURRENT_USER
IS
str VARCHAR2(1000);
colName VARCHAR2(100);
colType VARCHAR2(100);
tableColumn VARCHAR2(2000);
v_count INT;
all_num INT;
i INT;
BEGIN
v_count:=-1;
all_num:=0;
i:=0;
SELECT COUNT(*) INTO v_count FROM USER_TABLES WHERE TABLE_NAME='FX_TEMP_SAMPLE';
SELECT COUNT(*) INTO all_num FROM FX_RESULTFIELD WHERE subroutine_id =routine_id;
FOR i in 0..all_num LOOP
SELECT DATA_TYPE into colType,NAME into colName FROM FX_RESULTFIELD WHERE (subroutine_id =routine_id AND RFS_IDX =i);
IF i=all_num-1 THEN
tableColumn:= tableColumn || colName || colType;
ELSIF i<(all_num-1) THEN
tableColumn:= tableColumn || colName || colType || ',';
END IF;
END LOOP;
IF v_count <= 0 THEN
str:='CREATE GLOBAL TEMPORARY TABLE FX_TEMP_SAMPLE(' || tableColumn || ') ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE str;
END IF;
COMMIT;
END;
IS
str VARCHAR2(1000);
colName VARCHAR2(100);
colType VARCHAR2(100);
tableColumn VARCHAR2(2000);
v_count INT;
all_num INT;
i INT;
BEGIN
v_count:=-1;
all_num:=0;
i:=0;
SELECT COUNT(*) INTO v_count FROM USER_TABLES WHERE TABLE_NAME='FX_TEMP_SAMPLE';
SELECT COUNT(*) INTO all_num FROM FX_RESULTFIELD WHERE subroutine_id =routine_id;
FOR i in 0..all_num LOOP
SELECT DATA_TYPE into colType,NAME into colName FROM FX_RESULTFIELD WHERE (subroutine_id =routine_id AND RFS_IDX =i);
IF i=all_num-1 THEN
tableColumn:= tableColumn || colName || colType;
ELSIF i<(all_num-1) THEN
tableColumn:= tableColumn || colName || colType || ',';
END IF;
END LOOP;
IF v_count <= 0 THEN
str:='CREATE GLOBAL TEMPORARY TABLE FX_TEMP_SAMPLE(' || tableColumn || ') ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE str;
END IF;
COMMIT;
END;
解决方案 »
- oracle上产生的core文件
- Oracle中,存储过程能再调用存储过程吗。
- 带返回参数的ORACLE存储过程
- 请教一个事务处理的问题
- 新手关于索引的问题。为何在以下情况下,建B*-树索引占用100K空间,而位图索引只占16K。我是新手,请大家进来看看吧。
- 这难道是oracle的bug?
- oracle函数返回字符串长度超标
- 在ORACLE中建表时出现ORA-00922错误
- 在POWERDESIGN中做的数据库设计倒入ORACLE中,能否保留住NAME栏的中文注释?
- 如果procedure中执行的是一条SQL语句,如何得到返回的数据集?
- 查询结果表头如何动态显示????????
- 关于隐式游标提示数据的问题
SELECT COUNT(*) INTO v_count FROM USER_TABLES WHERE TABLE_NAME='FX_TEMP_SAMPLE';
SELECT COUNT(*) INTO all_num FROM FX_RESULTFIELD WHERE subroutine_id =routine_id;
---------------SELECT COUNT(*) a INTO v_count FROM USER_TABLES WHERE TABLE_NAME='FX_TEMP_SAMPLE';
SELECT COUNT(*) b INTO all_num FROM FX_RESULTFIELD WHERE subroutine_id =routine_id;
这里没有问题吗???
SELECT DATA_TYPE into colType,NAME into colName FROM 改为
SELECT DATA_TYPE,NAME into colType,colName FROM