关于Oracle存储过程使用小结 , ACE网络编程开发|开源框架

freeeyes 发表于 2012-7-9 10:35:28

关于Oracle存储过程使用小结

这一段时间比较忙，一直没来这里更新一些东西。
呵呵，最近三天在搞oracle的一些东西，以前没用写过。于是恶补了一下，在这里记录之。
因为我要写一个计算GPS车辆移动轨迹和距离的东东，程序记录的数据不准确（由于一些历史原因），我只能根据记载在数据库里面的车辆经纬度，换算出行驶距离。并统计出来。
因为数据量比较大，程序采用的方式是每天一张表的方式存放（每天数据量差不多在2000W-5000W之间）。
设计这个Job，有以下难点。
1.需要从数据表获取一系列的数据，并与相邻的经纬度比较获得距离参数。
2.需要动态组织游标，因为每次执行的表名是不同的。
3.需要拼接执行的SQL。

以下记录只是动态游标的使用方法。

CREATE OR REPLACE
PROCEDURE Proc_GetAllCar
AS
TYPE ref_cursor_type IS REF CURSOR;
proc_MSISDN VARCHAR(20);
proc_SQL VARCHAR(500);
proc_Today_Day VARCHAR(2);
proc_Today_Mon VARCHAR(2);
proc_date_len NUMBER;

proc_NowDATE;

c_job ref_cursor_type;
BEGIN

--获得要执行的存储过程

--获得前一天的日期，并组成表名
proc_Now := sysdate -1;

proc_Today_Day := to_char(proc_Now, 'dd' );
proc_Today_Mon := to_char(proc_Now, 'MM');

proc_date_len := length(proc_Today_Day);
IF proc_date_len < 2 THEN
proc_Today_Day := '0' || proc_Today_Day;
END IF;

proc_date_len := length(proc_Today_Mon);
IF proc_date_len < 2 THEN
proc_Today_Mon := '0' || proc_Today_Mon;
END IF;

proc_SQL := 'select MSISDN from TERMINAL_POS_LOG'|| to_char(proc_Today_Mon) || to_char(proc_Today_Day)|| ' group byMSISDN';
DBMS_OUTPUT.PUT_LINE('proc_SQL=' || proc_SQL);
OPEN c_job FOR proc_SQL;

BEGIN
   --打开游标
   --open c_job;

   loop
      --提取一行数据到c_row
      fetch c_job INTO proc_MSISDN;
      EXIT WHEN c_job%NOTFOUND;

   --执行存储过程
   proc_SQL:=' call CarDistance(''' || proc_MSISDN || ''') ';
      --DBMS_OUTPUT.PUT_LINE('proc_MSISDN=' || proc_MSISDN);
   execute immediate proc_SQL;

end loop;

close c_job;
END;
END;

这个存储过程是从昨天的定位数据表中获得所有需要统计的车辆SIM卡ID，获得后，再分别调用CarDistance()这个存储过程完成里程统计。
关于CarDistance()的实现如下：
CREATE OR REPLACE
PROCEDURE CarDistance(PhoneCode IN VARCHAR)
AS
TYPE ref_cursor_type IS REF CURSOR;
proc_LONGITUDE NUMBER;
proc_LATITUDE NUMBER;
proc_INCREMENT_MILEAGE NUMBER;

proc_bef_LONGITUDE NUMBER;
proc_bef_LATITUDE NUMBER;
proc_bef_INCREMENT_MILEAGE NUMBER;

proc_bef_fLONGITUDE FLOAT;
proc_bef_fLATITUDE FLOAT;

proc_fLONGITUDE FLOAT;
proc_fLATITUDE FLOAT;

proc_i NUMBER;
proc_PointMaleage NUMBER;
proc_DataMaleage NUMBER;

proc_PI FLOAT;
porc_Temp_b FLOAT;
porc_Temp_s FLOAT;
proc_Temp_WDSin1 FLOAT;
proc_Temp_WDSin2 FLOAT;
proc_Temp_WDSin3 FLOAT;
proc_Temp_WDSin4 FLOAT;
proc_Temp_WDCos1 FLOAT;
proc_Temp_WDCos2 FLOAT;

proc_LogTable_number NUMBER;
proc_SQL VARCHAR(500);
proc_Today_Year VARCHAR(10);
proc_Today_Day VARCHAR(10);
proc_Today_Mon VARCHAR(10);
proc_Today_Date varchar(20);
proc_strLogaTableName VARCHAR(50);
proc_date_len NUMBER;
proc_NowDATE;

c_job ref_cursor_type;

BEGIN
   --设置PI的常量

   proc_PI := asin(1)*2;
   --DBMS_OUTPUT.PUT_LINE('proc_PI='||to_char(proc_PI));
   proc_PointMaleage := 0;
proc_DataMaleage := 0;

--获得前一天的日期，并组成表名
proc_Now := sysdate -1;

   proc_Today_Year :=to_char(proc_Now, 'YYYY' );
proc_Today_Day := to_char(proc_Now, 'dd' );
proc_Today_Mon := to_char(proc_Now, 'MM');

proc_Today_Date := proc_Today_Year || '-' || proc_Today_Mon || '-' || proc_Today_Day;

proc_date_len := length(proc_Today_Day);
IF proc_date_len < 2 THEN
proc_Today_Day := '0' || proc_Today_Day;
END IF;

proc_date_len := length(proc_Today_Mon);
IF proc_date_len < 2 THEN
proc_Today_Mon := '0' || proc_Today_Mon;
END IF;

--DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');
--拼接动态执行的SQL语句
proc_SQL := ' SELECTLONGITUDE, LATITUDE, INCREMENT_MILEAGE FROM TERMINAL_POS_LOG' || to_char(proc_Today_Mon) || to_char(proc_Today_Day) || ' WHERE MSISDN =''' || PhoneCode || '''AND GPS_FLAG = '''|| '0' || '''ORDER BY COLLECTION_TIME';
--proc_SQL := ' SELECTLONGITUDE, LATITUDE, INCREMENT_MILEAGE FROM TERMINAL_POS_LOG' ||'0620' || ' WHERE MSISDN =''' || PhoneCode || '''AND GPS_FLAG = '''|| '0' || '''ORDER BY COLLECTION_TIME';
DBMS_OUTPUT.PUT_LINE('proc_SQL=' || proc_SQL);
OPEN c_job FOR proc_SQL;

--DECLARE REF CURSOR c_job IS SELECTLONGITUDE, LATITUDE, INCREMENT_MILEAGE FROM TERMINAL_POS_LOG0620 WHERE MSISDN = PhoneCode AND GPS_FLAG = '0'ORDER BY COLLECTION_TIME;

BEGIN
   --打开游标
   --open c_job;

   proc_i := 0;
   loop
      --提取一行数据到c_row
      fetch c_job INTO proc_LONGITUDE, proc_LATITUDE, proc_INCREMENT_MILEAGE;
      EXIT WHEN c_job%NOTFOUND;
      --DBMS_OUTPUT.PUT_LINE('proc_LONGITUDE='||to_char(proc_LONGITUDE));
      --DBMS_OUTPUT.PUT_LINE('proc_LATITUDE='||to_char(proc_LATITUDE));
      --DBMS_OUTPUT.PUT_LINE('proc_INCREMENT_MILEAGE='||to_char(proc_INCREMENT_MILEAGE));

      IF proc_i = 0 THEN
      BEGIN
            --第一次不计算
            proc_bef_LONGITUDE := proc_LONGITUDE;
            proc_bef_LATITUDE := proc_LATITUDE;
            proc_bef_INCREMENT_MILEAGE := proc_INCREMENT_MILEAGE;
      END;
      ELSE
      BEGIN
            --开始计算里程

            proc_bef_fLONGITUDE := proc_bef_LONGITUDE / 1000.0 / 60.0 * proc_PI / 180.0;
            proc_bef_fLATITUDE := proc_bef_LATITUDE /1000.0 / 60.0 * proc_PI / 180.0;

            proc_fLONGITUDE := proc_LONGITUDE / 1000.0 / 60.0 * proc_PI / 180.0;
         proc_fLATITUDE := proc_LATITUDE /1000.0 / 60.0 * proc_PI / 180.0;

            --计算里程中间变量
            porc_Temp_b := ABS(proc_bef_fLONGITUDE - proc_fLONGITUDE);
proc_Temp_WDSin1 := SIN(ABS(proc_bef_fLATITUDE - proc_fLATITUDE)/2);
proc_Temp_WDSin2 := SIN(ABS(proc_bef_fLATITUDE - proc_fLATITUDE)/2);
            proc_Temp_WDCos1 := COS(proc_bef_fLATITUDE);
proc_Temp_WDCos2 := COS(proc_fLATITUDE);
            proc_Temp_WDSin3 := SIN(porc_Temp_b/ 2);
proc_Temp_WDSin4 := SIN(porc_Temp_b/ 2);
            porc_Temp_s := 2 * SQRT(proc_Temp_WDSin1 * proc_Temp_WDSin2 + proc_Temp_WDCos1 * proc_Temp_WDCos2 *proc_Temp_WDSin3 * proc_Temp_WDSin4);
            porc_Temp_s := porc_Temp_s * 6378137;
            IF porc_Temp_s > 6000000.0 THEN
            BEGIN
               porc_Temp_s := 6000000.0;
            END;
            END IF;

            --累加距离
            proc_PointMaleage := proc_PointMaleage + ROUND(porc_Temp_s);
            proc_DataMaleage := proc_DataMaleage + proc_INCREMENT_MILEAGE;

            --DBMS_OUTPUT.PUT_LINE('proc_i='||to_char(proc_i));
--DBMS_OUTPUT.PUT_LINE('porc_Temp_s='||to_char(porc_Temp_s));
--DBMS_OUTPUT.PUT_LINE('proc_INCREMENT_MILEAGE='||to_char(proc_INCREMENT_MILEAGE));
--DBMS_OUTPUT.PUT_LINE('proc_PointMaleage='||to_char(proc_PointMaleage));
--DBMS_OUTPUT.PUT_LINE('proc_DataMaleage='||to_char(proc_DataMaleage));

            proc_bef_LONGITUDE := proc_LONGITUDE;
            proc_bef_LATITUDE := proc_LATITUDE;
            proc_bef_INCREMENT_MILEAGE := proc_INCREMENT_MILEAGE;
      END;
      END IF;

      proc_i :=proc_i + 1;
   end loop;

   --DBMS_OUTPUT.PUT_LINE('proc_PointMaleage='||to_char(proc_PointMaleage)||',proc_DataMaleage='||to_char(proc_DataMaleage));
   --DBMS_OUTPUT.PUT_LINE('proc_i='||to_char(proc_i));
   --nPointMailage := proc_PointMaleage;
   --nDataMailage := proc_DataMaleage;

   --判断日志表是否存在，如果存在则直接添加，否则建立表

--proc_Today_Day := to_char(sysdate, 'dd' );
--proc_Today_Mon := to_char(sysdate, 'MM');
--proc_strLogaTableName := 'SELECT COUNT(1) into proc_LogTable_number fromwhere table_name = Log_CarMale_''' || proc_Today_Mon|| proc_Today_Day + '''';
--DBMS_OUTPUT.PUT_LINE('proc_strLogaTableNamei='||proc_strLogaTableName);
INSERT INTOLog_MaleageAccount_User(MSISDN, MaleAge, accountTime, createTime) VALUES(PhoneCode, proc_PointMaleage, to_date(proc_Today_Date, 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American'), sysdate);

   --关闭游标
   close c_job;
   END;

END;

页: [1]

ACE Developer's Archiver

关于Oracle存储过程使用小结