DB2,Mysql,Oracle数据库比较以及Oracle的存储过程

for your wish

已于 2022-01-21 20:31:39 修改

阅读量896

点赞数

分类专栏： SQL 技术比较文章标签：数据库 oracle mysql

于 2019-05-16 10:00:53 首次发布

原文链接：www.baidu.com

版权

SQL 同时被 2 个专栏收录

27 篇文章 3 订阅 ¥19.90 ¥99.00

订阅专栏

超级会员免费看

技术比较

10 篇文章 1 订阅

订阅专栏

本文对比了DB2在大数据分析上的适用性，Oracle在事务处理的优势，以及MySQL的逻辑复制特性。同时，详细介绍了Oracle存储过程的执行方式，包括命令窗口、PL/SQL窗口和程序调用的使用场景，并指出在存储过程中需将查询结果存入变量。

摘要由CSDN通过智能技术生成

DB2适用于数据集较大的分析，oracle适用于事务处理

Oracle的物理复制数据在大数据场景下很无力，几个G几十个G的复制，大事务很差

而mysql是逻辑复制，binlog日志

存储过程依赖于数据库，不依赖于表。这些存储过程都要先执行一遍才能调用。

执行：不单单是exec一下，还是得分情况：

1.如果是命令窗口就用exec 存储过程名，举个例子：

call  procedure();           --procedure是存储过程名

2.如果是PL/SQL窗口就用 begin 存储过程名 end; 举个例子：

begin

  procedure;       --procedure是存储过程名

end;

3.如果是程序中调用就用 call 存储过程名，举个栗子：

hibernateDao.excuteSqlUpdate("{Call proc_stuInfo()}");  //存储过程proc_stuInfo，括号内可传参

另附存储过程创建方法：

create or replace procedure pro_test        --pro_test为存储过程名，这个是不传参的存储过程
is
temp varchar2(128);--temp为存储过程临时变量
bengin
    select count(*) into temp from TEST;           --这里为什么会使用temp变量，下面会提到
    insert into TEST values(3,'sss',25,'asd');
    commit;                                        --切记commit一下（提交）
end;

注意：在存储过程中是不能直接出现"select * from test",这种简单查询，必须将查询出来的数据放入存储过程变量中，如上所示的temp变量。

一个存储过程示例

--  166版本  
CREATE OR REPLACE PROCEDURE PRC_RPT_MKT_1_FST_OPEN_RPLY  as
V_START_DT DATE;
V_END_DT DATE;
BEGIN
   --  DECLARE V_STATS_DT DATE;
   --  DECLARE V_END_DT DATE;
    
    V_START_DT := TO_DATE('1980-01-01', 'YYYY-MM-DD');
    Select  trunc(Sysdate) Into V_END_DT from dual;

        EXECUTE IMMEDIATE 'TRUNCATE TABLE ODS.RPT_MKT_1_FST_OPEN_RPLY';
        INSERT INTO ODS.RPT_MKT_1_FST_OPEN_RPLY                   --  ODS.RPT_MKT_1_FST_OPEN_RPLY
        WITH TMP_DATA AS (
                SELECT
                       T2.DEPT_ID
                      ,T2.DEPT_NM
                      ,T2.PROJ_ID             --金碟
                      ,T2.IDM_PROJ_ID
                      ,T2.PROJ_CD
                      ,T2.PROJ_NM
                      ,T2.BRANCH_NM -- 城市公司名称
                      ,T5.CITY_NM
                      ,t4.land_time -- 拿地时间          -- TO_DATE(t4.land_time, 'YYYY-MM-DD')
                      ,t7.cam_trade_master_desc -- 是否营销操盘
                      ,t7.proj_trader -- 项目操盘手
                      ,t6.PLAN_OPEN_TIME_INV -- 投资版开盘日期       --TO_DATE(t6.PLAN_OPEN_TIME_INV, 'YYYY-MM-DD')

                      ,trunc(PLAN_OPEN_TIME_INV-LAND_TIME)                       invest_open_period   -- 投资开盘周期  天数
                      ,trunc(CAST(ACTL_OPEN_DT AS DATE)-CAST(LAND_TIME AS DATE)) actual_open_period   -- 实际开盘周期

                      ,T8.ACTL_OPEN_DT AS ACTL_OPEN_TIME-- 实际开盘日期   --TO_DATE(T8.ACTL_OPEN_DT, 'YYYY-MM-DD')
                      ,T3.PLAN_NODE_ID AS PLAN_NODE_ID_TOP
                 FROM ODS.T02_PROJ_TREE @PRD_BB  T2
                 LEFT JOIN (
                      SELECT T1.PROJECT_ID
                             ,T2.ID AS PLAN_NODE_ID
                             ,TRUNC(T3.OPEN_TIME) AS PLAN_OPEN_TIME_TOP
                             ,ROW_NUMBER()OVER(PARTITION BY T1.PROJECT_ID ORDER BY T2.CREATE_TIME DESC) AS SEQ_NUM
                        FROM ODS.SMS_FP_PLAN @PRD_BB T1
                       INNER JOIN ODS.SMS_FP_PLAN_NODE @PRD_BB T2
                             ON T1.ID = T2.PLAN_ID
                             AND T2.NODE_LEVEL = 3
                             AND T2.IS_DEL = 0
                             AND T2.IS_EFFECTIVE = 0
                        LEFT JOIN ODS.SMS_FP_DATE_NODE @PRD_BB T3
                             ON T2.ID = T3.PLAN_NODE_ID
                       WHERE T3.OPEN_TIME IS NOT NULL
                      ) T3
                      ON T2.IDM_PROJ_ID = T3.PROJECT_ID
                      AND T3.SEQ_NUM = 1
            left join (
            SELECT LAND_PID as proj_cd
                           ,min(LAND_PARTICIPATION_TIME) as land_time -- 拿地时间
                    FROM PT_TAKE_LIST@ild
                    WHERE IS_DELETE = 0
                    group by LAND_PID
            )t4 on t4.proj_cd=t2.proj_cd
            LEFT JOIN ODS.T02_PROJ_INFO @PRD_BB  T5
                   ON T5.PROJ_ID=T2.IDM_PROJ_ID
            left join (
                SELECT PTL.LAND_PID as proj_cd
                      ,max(PTL.OPENING_DATE) as PLAN_OPEN_TIME_INV
                FROM PT_TAKE_LIST@ild PTL
                LEFT JOIN FT_WORK_FLOW_INSTANCE@ild FFI ON FFI.BUSINESS_KEY = PTL.VERSION_ID
                WHERE 
                -- PTL.IS_DELETE = 0 AND 
                FFI.FLOW_KEY = 'AUTH_IEMS_INVEST_VERSION'
                AND FFI.STATUS_CODE = 4
                group by PTL.LAND_PID
            )t6 on t6.proj_cd=t2.proj_cd
            left join v_t02_proj_info @PRD_BB t7
            on t7.IDM_PROJ_ID=t2.IDM_PROJ_ID
            left join T02_PROJ_PLAN @PRD_BB t8 on t2.proj_cd=t8.proj_cd
             WHERE
               CAST(T8.ACTL_OPEN_DT AS DATE) >= V_START_DT
                and
              CAST(T8.ACTL_OPEN_DT AS DATE)<  V_END_DT

        )
        ,
        decide_table as(
            select
                 T2.PROJ_ID PROJ_ID
                ,T1.PROD_NM
                ,PLAN_OPEN_TIME_INV
                ,ACTL_OPEN_TIME
                ,CNTRT_DTTM
                ,CASE WHEN T2.PLAN_OPEN_TIME_INV <= T2.ACTL_OPEN_TIME --  正常开盘，延后开盘
                        AND TO_CHAR( T1.CNTRT_DTTM, 'YYYYMM' ) = TO_CHAR( T2.ACTL_OPEN_TIME, 'YYYYMM' ) THEN T1.CNTRT_AMT
                    WHEN T2.PLAN_OPEN_TIME_INV > T2.ACTL_OPEN_TIME --  抢开盘
                        AND TO_CHAR( T1.CNTRT_DTTM, 'YYYYMM' ) BETWEEN TO_CHAR( T2.ACTL_OPEN_TIME, 'YYYYMM' )
                        AND TO_CHAR( T2.PLAN_OPEN_TIME_INV, 'YYYYMM' ) THEN T1.CNTRT_AMT ELSE 0
                    END FIRST_AMT
                ,CASE WHEN T2.PLAN_OPEN_TIME_INV <= T2.ACTL_OPEN_TIME --  正常开盘，延后开盘
                        AND TO_CHAR( T1.CNTRT_DTTM, 'YYYYMM' ) = TO_CHAR( T2.ACTL_OPEN_TIME, 'YYYYMM' ) THEN T1.CNTRT_AREA
                    WHEN T2.PLAN_OPEN_TIME_INV > T2.ACTL_OPEN_TIME --  抢开盘
                        AND TO_CHAR( T1.CNTRT_DTTM, 'YYYYMM' ) BETWEEN TO_CHAR( T2.ACTL_OPEN_TIME, 'YYYYMM' )
                        AND TO_CHAR( T2.PLAN_OPEN_TIME_INV, 'YYYYMM' ) THEN
                        T1.CNTRT_AREA  ELSE 0
                    END FIRST_AREA

                -- ,CASE WHEN t2.PLAN_OPEN_TIME_INV <= T2.ACTL_OPEN_TIME --  正常开盘，延后开盘
                --          AND TO_CHAR( T1.CNTRT_DTTM, 'YYYYMM' ) = TO_CHAR( T2.ACTL_OPEN_TIME, 'YYYYMM' ) THEN T1.CNTRT_AMT
                --       WHEN t2.PLAN_OPEN_TIME_INV > T2.ACTL_OPEN_TIME --  抢开盘
                --          AND TO_CHAR( T1.CNTRT_DTTM, 'YYYYMM' ) BETWEEN TO_CHAR( T2.ACTL_OPEN_TIME, 'YYYYMM' )
                --          AND TO_CHAR( T2.PLAN_OPEN_TIME_INV, 'YYYYMM' ) THEN T1.CNTRT_AMT ELSE 0
                --          END FIRST_AMT_NOCAR
            from  ODS.T05_CNTRT_DTL @PRD_BB T1
            INNER JOIN TMP_DATA T2
            ON T1.PROJ_ID = T2.PROJ_ID
        )
        ,
        inv_table as(
                    SELECT
                        LL.LAND_PID AS PROJ_CD
                        ,gdd.NAME||'-'|| efsp.delivery_standard||'-'||gdd2.NAME||'-'||gdd3.NAME  PROD_NM
                        ,EFSP.YEAR || ( CASE WHEN LENGTH( EFSP.MONTH ) <= 1 THEN 0 || EFSP.MONTH ELSE EFSP.MONTH END ) AS YEARMONTH
                        ,SUM( CASE WHEN EFSP.THE_SUPPLY_PLAN = 3 THEN TO_NUMBER( EFSP.DATAVALUE ) ELSE 0 END ) AMTVALUE
                        ,SUM( CASE WHEN EFSP.THE_SUPPLY_PLAN = 1 THEN TO_NUMBER( EFSP.DATAVALUE ) ELSE 0 END ) AREAVALUE
                    FROM
                        ET_FORM_SUPPLY_PLAN @ILD EFSP
                    INNER JOIN ET_VERSION @ILD EV
                    ON EFSP.VERSION_ID = EV.PK_ID
                    INNER JOIN PT_TAKE_LIST @ILD LL
                    ON LL.LAND_ID = EV.LAND_ID

                    LEFT JOIN GT_DATA_DICT @ILD gdd ON to_number(efsp.PRODUCT_TYPE) = gdd.CODE AND gdd."TYPE" = '280100'
                    LEFT JOIN GT_DATA_DICT @ILD gdd2 on to_number(efsp.deal_TYPE)= gdd2.CODE AND gdd2."TYPE" = '280500'
                    LEFT JOIN GT_DATA_DICT @ILD gdd3 on to_number(efsp.ATTRIBUTE)= gdd3.CODE AND gdd3."TYPE" = '280600'
                    WHERE
                        EFSP.THE_SUPPLY_PLAN IN ( 3, 1 )
                        AND LL.IS_DELETE = 0
                        AND EV.IS_DELETE = 0
                        AND EV.TYPE_CODE = 3
                        AND EV.STATUS_CODE = 5
                        --  AND gdd.NAME NOT LIKE '%车%'      --  不含车
                         AND gdd.NAME NOT LIKE '%999%'      --  不含999
                    GROUP BY
                        LL.LAND_PID
                        ,gdd.NAME||'-'|| efsp.delivery_standard||'-'||gdd2.NAME||'-'||gdd3.NAME
                        ,EFSP.YEAR || ( CASE WHEN LENGTH( EFSP.MONTH ) <= 1 THEN 0 || EFSP.MONTH ELSE EFSP.MONTH END )

        )


    select XXX from 。。。。。

     COMMIT;

    EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END
;
/