关于Oracle存储过程使用小结

freeeyes

这一段时间比较忙，一直没来这里更新一些东西。
呵呵，最近三天在搞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_Now  DATE;

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 by  MSISDN';
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_Now  DATE;

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 := ' SELECT  LONGITUDE, 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 := ' SELECT  LONGITUDE, 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 SELECT  LONGITUDE, 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 from  where table_name = Log_CarMale_''' || proc_Today_Mon|| proc_Today_Day + '''';
        --DBMS_OUTPUT.PUT_LINE('proc_strLogaTableNamei='||proc_strLogaTableName);
        INSERT INTO  Log_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;