如果select * from tabname不是按照插入顺序返回数据的话,估计就没有别的办法了。
解决方案 »
- 关于oracle内行数据 显示成 列数据
- oracle修改表空间大小--急
- 本人刚学oracle 不明白为什么同时需要存储过程和函数啊?感觉存储过程完全可以替代函数啊? 勿喷啊,高手给点意见看看
- 左右连接
- plsql到底怎么导入dmp表呢,折磨一天了 ,求高手解答
- 请问:在ORACLE9i中的Enterprise Manager Console启动不起来,怎么解决?
- SQL SERVER 2000 中的库一次性转换到 ORACLE 当中!(急)
- 读取数据库时间的sql是那个??谢谢
- 一道面试题,怎么查询第5条到第15条记录?
- TRUNC()函数的确切含义?最好用例子说明!
- oracle中备份与恢复的问题
- 为什么索引失效?急,在线等待
select * from tabname order by rowid 应该也行。
这个计算过程是:
CREATE OR REPLACE PROCEDURE "P_COUNTRYSIDE_SAND_GROWTH" /*
日期 : 2004-11-04
作者 : 肖云
概要 : m_countryside_sand_growth表的计算,数据依据为shahua表
参数说明 : VillageCode 乡代码
返回值说明 :
主要修改 :优化处理,减少游标使用量
版本1。利用调用函数
日期 : 2004-11-16
作者 : 肖云
概要 :
*/
(VillageCode in number) is
CURSOR cur_get_data (s10_data number,s19_data number) IS SELECT NVL(SUM(s7),0) FROM m_shahua
WHERE s5= VillageCode AND s10 = s10_data AND s19 = s19_data;
TYPE cursor_update_type IS REF CURSOR ;
--用于判断m_countryside_sand_growth表中有无数据
cur_update_flag cursor_update_type;
--用于对m_countryside_sand_growth进行数据更新
cur_update_data cursor_update_type;
--存放求合后的数据
rec_sun_data m_countryside_sand_growth%ROWTYPE;
rec_sun_temp m_countryside_sand_growth%ROWTYPE;
rec_flag shahua%ROWTYPE;
Index_j INTEGER;
--治理措施
FatherMethod VARCHAR2(30);
begin
--填入数据
--如果数据表里没有该乡数据则设置函数返回值
OPEN cur_update_flag FOR SELECT s7 FROM m_shahua WHERE s5= VillageCode ;
FETCH cur_update_flag INTO rec_flag.s7;
IF cur_update_flag%NOTFOUND OR cur_update_flag%NOTFOUND IS NULL then
--设置函数返回值
--if return_values = '0' then
-- return_values :=VillageCode || ';' ;
--else
-- return_values :=return_values || VillageCode || ';';
--end if;
--填入数据日志
INSERT INTO sys_log (sid,sname,sexplain,sdate,sdept,sbt,stype)VALUES(VillageCode,(SELECT sname FROM c_countryside WHERE sid = VillageCode),'无该乡数据!',sysdate,4,VillageCode,6) ;
ELSE
--删除乡统计表中原有记录
DELETE FROM m_countryside_sand_growth WHERE ccode= VillageCode;
--开始计算
FOR Index_j IN 1 .. 18
LOOP
IF Index_j = 1 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,804,804,VillageCode);
ELSIF Index_j = 2 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,803,803,VillageCode);
ELSIF Index_j = 3 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,829,829,VillageCode);
ELSIF Index_j = 4 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,1200,1200,VillageCode);
ELSIF Index_j = 5 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,'805,806',3001,VillageCode);
ELSIF Index_j = 6 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,801,801,VillageCode);
ELSIF Index_j = 7 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,516,516,VillageCode);
ELSIF Index_j = 8 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,311,311,VillageCode);
ELSIF Index_j = 9 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,310,310,VillageCode);
ELSIF Index_j = 10 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,518,518,VillageCode);
ELSIF Index_j = 11 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,809,809,VillageCode);
ELSIF Index_j = 12 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,524,524,VillageCode);
ELSIF Index_j = 13 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,80,80,VillageCode);
ELSIF Index_j = 14 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,802,802,VillageCode);
ELSIF Index_j = 15 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,807,807,VillageCode);
ELSIF Index_j = 16 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,'852,1157,1162,1029',3002,VillageCode);
ELSIF Index_j = 17 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,21,29',3003,VillageCode);
ELSIF Index_j = 18 THEN
P_SUM_GROWTH_CURSOR(rec_sun_data,'1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,21,29,
852,1157,1162,1029,807,802,80,524,809,518,310,311,516,801,805,806,1200,829,803,804',3004,VillageCode,1);
END IF ;
INSERT INTO M_countryside_sand_growth VALUES (rec_sun_data.sid,rec_sun_data.acode,rec_sun_data.bcode
,rec_sun_data.ccode,rec_sun_data.s01,rec_sun_data.s02,rec_sun_data.s03,rec_sun_data.s04,rec_sun_data.s05
,rec_sun_data.s06,rec_sun_data.s07,rec_sun_data.s08,rec_sun_data.s09,rec_sun_data.s10,rec_sun_data.s11
,rec_sun_data.s12,rec_sun_data.s13,rec_sun_data.s14,rec_sun_data.s15,rec_sun_data.s16,rec_sun_data.s17
,rec_sun_data.s18,rec_sun_data.objectid);
END LOOP;
END IF ;
CLOSE cur_update_flag ;
COMMIT;
EXCEPTION
WHEN VALUE_ERROR
THEN
INSERT INTO sys_log (sid,sname,sexplain,sdate,sdept,sbt,stype)VALUES(VillageCode,(SELECT sname FROM c_countryside WHERE sid = VillageCode),'数据统计错误,可能统计值超出精度!',sysdate,4,VillageCode,6) ;
COMMIT;
END p_countryside_sand_growth;
CREATE OR REPLACE PROCEDURE "P_SUM_GROWTH_CURSOR" (Rec_Sum IN OUT m_countryside_sand_growth%ROWTYPE,column_name VARCHAR2,
column_Code IN NUMBER,VillageCode IN NUMBER,other in integer := 0
) is
/*
CURSOR Cur_SumForArer IS
SELECT NVL(SUM(s7),0) TOTAL,s19,s10
FROM shahua
WHERE s10 = s10_data AND s19 IN () AND s5 = VillageCode ;
*/
TYPE cur_type IS REF CURSOR;
Cur_SumForArer cur_type;
link_sign varchar2(10) := 'IN' ;
BEGIN
IF other = 1 THEN
link_sign := 'NOT IN';
END IF ;
--填入代码编号及乡级编码
Rec_Sum.sid:= VillageCode;
Rec_Sum.acode:= SUBSTR(VillageCode,1,2);
Rec_Sum.bcode:= SUBSTR(VillageCode,1,6);
Rec_Sum.ccode:= VillageCode;
--填入治理措施
Rec_Sum.s02 := column_Code ;
--填入OBJECTID
Rec_Sum.objectid := f_get_objectid (VillageCode,4);
--填入各项土地面积
--流动沙地(丘)
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 168 AND s19 '|| link_sign ||' ('|| column_name ||') AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s04;
CLOSE Cur_SumForArer;
--人工半固定沙地
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 1701 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s06;
CLOSE Cur_SumForArer;
--天然半固定沙地
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 1702 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s07;
CLOSE Cur_SumForArer;
--半固定沙地(丘)计
Rec_Sum.s05 := Rec_Sum.s07 +Rec_Sum.s06;
--人工固定沙地
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 1721 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s09;
CLOSE Cur_SumForArer;
--天然固定沙地
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 1722 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s10;
CLOSE Cur_SumForArer;
--固定沙地(丘)计
Rec_Sum.s08:=Rec_Sum.s09+Rec_Sum.s10;
--露沙地
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 175 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s11;
CLOSE Cur_SumForArer;
--沙化耕地
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 176 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s12;
CLOSE Cur_SumForArer;
--非生物治沙工程地
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 169 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s13;
CLOSE Cur_SumForArer;
--风蚀残丘 OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 174 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s14;
CLOSE Cur_SumForArer;
--风蚀劣地
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 180 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s15;
CLOSE Cur_SumForArer;
--戈壁
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 182 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s16;
CLOSE Cur_SumForArer;
--有明显沙化趋势的土地
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 190 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode;
FETCH Cur_SumForArer INTO Rec_Sum.s17;
CLOSE Cur_SumForArer;
--其它土地类型面积
OPEN Cur_SumForArer FOR 'SELECT NVL(SUM(s7),0)
FROM m_shahua
WHERE s10 = 9 AND s19 '|| link_sign ||' ( '|| column_name ||' ) AND s5 = ' || VillageCode ;
FETCH Cur_SumForArer INTO Rec_Sum.s18;
CLOSE Cur_SumForArer;
--沙化土地面积计
Rec_Sum.s03:=Rec_Sum.s08+Rec_Sum.s05+ rec_sum.s04
+Rec_Sum.s11+Rec_Sum.s12+Rec_Sum.s13
+Rec_Sum.s14+Rec_Sum.s15+Rec_Sum.s16;
--总面积
Rec_Sum.s01:= Rec_Sum.s03 + Rec_Sum.s17 + Rec_Sum.s18 ;
END ;主要过程又被另外一个过程重复调用,现在问题是读出顺序的确没有按照写入顺序。按ROWID排序也不行,单个调用该过程时,有时可能达到效果,但是很多过程线性触发,就很容易出现不按顺序的问题!