代码如下:
CREATE OR REPLACE PROCEDURE count_resolution_applicator(
endFlag1 INT,
rec_cur OUT pkg_return_recoder.re_cursor
)
AS
cmdSql VARCHAR2(1000);
total INT;
BEGIN SELECT count(*) INTO total FROM all_tables WHERE table_name = 'COUNT';
IF total<>0 THEN
--如果系统存在表就先删除表
cmdSql:='TRUNCATE TABLE count';
EXECUTE IMMEDIATE cmdSql;
cmdSql:='DROP TABLE count';
EXECUTE IMMEDIATE cmdSql;
END IF; SELECT count(*) INTO total FROM all_tables WHERE table_name = 'COUNTMODULE'; IF total<>0 THEN
--如果系统存在表就先删除表
cmdSql:='TRUNCATE TABLE countmodule';
EXECUTE IMMEDIATE cmdSql;
cmdSql:='DROP TABLE countmodule';
EXECUTE IMMEDIATE cmdSql;
END IF; SELECT count(*) INTO total FROM all_tables WHERE table_name = 'COUNTGROUP'; IF total<>0 THEN
--如果系统存在表就先删除表
cmdSql:='TRUNCATE TABLE countgroup';
EXECUTE IMMEDIATE cmdSql;
cmdSql:='DROP TABLE countgroup';
EXECUTE IMMEDIATE cmdSql;
END IF; cmdSql:='CREATE GLOBAL TEMPORARY TABLE count('
|| 'maxper_Department VARCHAR2(375),'
|| 'resolution_Applicator VARCHAR2(75),'
|| 'total INT'
|| ')ON COMMIT PRESERVE ROWS';
--||' )ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE cmdSql; cmdSql:='INSERT INTO count '
|| 'SELECT * FROM ('
|| 'SELECT p.orgname ,b.resolution_applicator,b.total FROM'
|| ' (select resolution_applicator,department_belong,count(resolution_applicator) as total'
|| ' FROM user_improve_project'
|| ' WHERE endflag ='
|| endFlag1
|| ' GROUP BY resolution_applicator,department_belong) b,cpcorg p'
|| ' WHERE b.department_belong = p.orgid'
|| ' ORDER BY total DESC)c WHERE rownum<2';
EXECUTE IMMEDIATE cmdSql;
cmdSql:='CREATE GLOBAL TEMPORARY TABLE countmodule('
|| 'orgname VARCHAR2(375),'
|| 'total2 INT'
|| ')ON COMMIT PRESERVE ROWS';
--||' )ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE cmdSql;
cmdSql:='INSERT INTO countmodule '
|| 'SELECT * FROM ('
|| ' SELECT orgname,total2 FROM'
|| ' (SELECT b.orgname,count(a.department_belong) AS total2'
|| ' FROM user_improve_project a,cpcorg b'
|| ' WHERE b.orgid = a.department_belong'
|| ' AND endflag = '
|| endFlag1
|| ' GROUP BY orgname) b'
|| ' ORDER BY total2 DESC) c WHERE rownum<2'; EXECUTE IMMEDIATE cmdSql;
cmdSql:='CREATE GLOBAL TEMPORARY TABLE countgroup('
|| 'maxgroup_Department VARCHAR2(375),'
|| 'project_Name VARCHAR2(75),'
|| 'total3 INT'
--||' )ON COMMIT DELETE ROWS';
|| ')ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE cmdSql; cmdSql:='INSERT INTO countgroup '
|| 'SELECT * FROM ('
|| ' SELECT h.orgname,c.project_name,c.total3 FROM '
|| ' (SELECT department_belong,project_name,count(project_name) as total3'
|| ' FROM user_improve_project'
|| ' WHERE endflag = '
|| endFlag1
|| ' GROUP BY project_name,department_belong) c,cpcorg h'
|| ' WHERE c.department_belong = h.orgid'
|| ' ORDER BY total3 DESC) c WHERE rownum<2'; EXECUTE IMMEDIATE cmdSql;
COMMIT;
cmdSql:='select maxper_department,resolution_applicator,total,orgname,'
|| 'total2,maxgroup_department,project_name,total3'
|| ' from count,countmodule,countgroup'; OPEN rec_cur FOR cmdSql;
END count_resolution_applicator;问题:两个或者两个以上的用户访问时。就会抛个错误提示
ORA-14552:attempt to create,alter or drop an index on temporary table already in use
有没有什么好的方法可以解决这种问题
CREATE OR REPLACE PROCEDURE count_resolution_applicator(
endFlag1 INT,
rec_cur OUT pkg_return_recoder.re_cursor
)
AS
cmdSql VARCHAR2(1000);
total INT;
BEGIN SELECT count(*) INTO total FROM all_tables WHERE table_name = 'COUNT';
IF total<>0 THEN
--如果系统存在表就先删除表
cmdSql:='TRUNCATE TABLE count';
EXECUTE IMMEDIATE cmdSql;
cmdSql:='DROP TABLE count';
EXECUTE IMMEDIATE cmdSql;
END IF; SELECT count(*) INTO total FROM all_tables WHERE table_name = 'COUNTMODULE'; IF total<>0 THEN
--如果系统存在表就先删除表
cmdSql:='TRUNCATE TABLE countmodule';
EXECUTE IMMEDIATE cmdSql;
cmdSql:='DROP TABLE countmodule';
EXECUTE IMMEDIATE cmdSql;
END IF; SELECT count(*) INTO total FROM all_tables WHERE table_name = 'COUNTGROUP'; IF total<>0 THEN
--如果系统存在表就先删除表
cmdSql:='TRUNCATE TABLE countgroup';
EXECUTE IMMEDIATE cmdSql;
cmdSql:='DROP TABLE countgroup';
EXECUTE IMMEDIATE cmdSql;
END IF; cmdSql:='CREATE GLOBAL TEMPORARY TABLE count('
|| 'maxper_Department VARCHAR2(375),'
|| 'resolution_Applicator VARCHAR2(75),'
|| 'total INT'
|| ')ON COMMIT PRESERVE ROWS';
--||' )ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE cmdSql; cmdSql:='INSERT INTO count '
|| 'SELECT * FROM ('
|| 'SELECT p.orgname ,b.resolution_applicator,b.total FROM'
|| ' (select resolution_applicator,department_belong,count(resolution_applicator) as total'
|| ' FROM user_improve_project'
|| ' WHERE endflag ='
|| endFlag1
|| ' GROUP BY resolution_applicator,department_belong) b,cpcorg p'
|| ' WHERE b.department_belong = p.orgid'
|| ' ORDER BY total DESC)c WHERE rownum<2';
EXECUTE IMMEDIATE cmdSql;
cmdSql:='CREATE GLOBAL TEMPORARY TABLE countmodule('
|| 'orgname VARCHAR2(375),'
|| 'total2 INT'
|| ')ON COMMIT PRESERVE ROWS';
--||' )ON COMMIT DELETE ROWS';
EXECUTE IMMEDIATE cmdSql;
cmdSql:='INSERT INTO countmodule '
|| 'SELECT * FROM ('
|| ' SELECT orgname,total2 FROM'
|| ' (SELECT b.orgname,count(a.department_belong) AS total2'
|| ' FROM user_improve_project a,cpcorg b'
|| ' WHERE b.orgid = a.department_belong'
|| ' AND endflag = '
|| endFlag1
|| ' GROUP BY orgname) b'
|| ' ORDER BY total2 DESC) c WHERE rownum<2'; EXECUTE IMMEDIATE cmdSql;
cmdSql:='CREATE GLOBAL TEMPORARY TABLE countgroup('
|| 'maxgroup_Department VARCHAR2(375),'
|| 'project_Name VARCHAR2(75),'
|| 'total3 INT'
--||' )ON COMMIT DELETE ROWS';
|| ')ON COMMIT PRESERVE ROWS';
EXECUTE IMMEDIATE cmdSql; cmdSql:='INSERT INTO countgroup '
|| 'SELECT * FROM ('
|| ' SELECT h.orgname,c.project_name,c.total3 FROM '
|| ' (SELECT department_belong,project_name,count(project_name) as total3'
|| ' FROM user_improve_project'
|| ' WHERE endflag = '
|| endFlag1
|| ' GROUP BY project_name,department_belong) c,cpcorg h'
|| ' WHERE c.department_belong = h.orgid'
|| ' ORDER BY total3 DESC) c WHERE rownum<2'; EXECUTE IMMEDIATE cmdSql;
COMMIT;
cmdSql:='select maxper_department,resolution_applicator,total,orgname,'
|| 'total2,maxgroup_department,project_name,total3'
|| ' from count,countmodule,countgroup'; OPEN rec_cur FOR cmdSql;
END count_resolution_applicator;问题:两个或者两个以上的用户访问时。就会抛个错误提示
ORA-14552:attempt to create,alter or drop an index on temporary table already in use
有没有什么好的方法可以解决这种问题
我觉得没必要每次操作都drop一次表再建一次表。只要清空表就好了,两个session 插入到表的数据不会相互影响的。
既然系统存在表就先删除表,然后在create。那么就表示你这张表只是这里使用一次而已,建议你用表变量解决好了,也不用判断,更不用drop或者truncate了啊!
因为先前的数据库为SQLSERVER。存储过程,函数中使用到了大量的临时表。临时表名相同结构不同的现象肯定是存在。
有没有ORALCE 表变量的实例