17,086
社区成员
发帖
与我相关
我的任务
分享
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;
SELECT DATA_TYPE into colType,NAME into colName FROM 改为
SELECT DATA_TYPE,NAME into colType,colName FROM
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;