{"id":79,"date":"2021-07-09T16:25:18","date_gmt":"2021-07-09T08:25:18","guid":{"rendered":"https:\/\/szx622.top\/?p=79"},"modified":"2021-07-11T13:17:03","modified_gmt":"2021-07-11T05:17:03","slug":"oracle%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e8%bf%81%e7%a7%bb%e8%87%b3mysql","status":"publish","type":"post","link":"https:\/\/szx622.top\/?p=79","title":{"rendered":"Oracle\u5b58\u50a8\u8fc7\u7a0b\u8fc1\u79fb\u81f3mysql"},"content":{"rendered":"\n

\u4e00\u3001\u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b\u6e90\u7801<\/h1>\n\n\n\n

oracle\u6e90\u7801<\/h2>\n\n\n\n
CREATE OR REPLACE PROCEDURE P_TRANSMITDATA
(
  nor_table in VARCHAR2,  --\u6b63\u5f0f\u8868
  delsql in VARCHAR2,
  Flag in  VARCHAR2,  --1\uff0c\u589e\u91cf  2. \u5168\u91cf 3\u3001\u660e\u7ec6
  TORQ in  VARCHAR2
)
as
sqlStr  VARCHAR2(500);
delsql1  VARCHAR2(500);
colname VARCHAR2(40);
tmp_table VARCHAR2(50);
begin
    tmp_table := 'TMP_'|| nor_table;
\u200b
    if  nor_table ='B_TRANSLIST_DEF'
    then
        colname := 'transmitdate';
    elsif nor_table = 'BDM_CD_U_AP_EI_PERSON'
    then
        colname := 'CUST_ID';
    elsif nor_table = 'BDM_CD_U_AP_MI_CUST_CLS'
    then
        colname := 'CUST_COD';
    end if;
\u200b
    -- \u589e\u91cf\u60c5\u51b5
    if  Flag = '1'
    then
        ---\u5904\u7406\u9700\u8981\u66f4\u65b0\u7684\u6570\u636e
        --\u5220\u9664\u5386\u53f2\u6570\u636e
        delsql1 :='delete from ' || nor_table || ' a where exists(select 1 from '||tmp_table||' b where a.'||colname||'=b.'||colname||') '; 
        sqlStr :='insert into ' || nor_table||' select a.*,'''||TORQ||''' from '||tmp_table || ' a';
        --insert into debug1 values('delsql1='||delsql1,sysdate);
        commit;
    
        Execute Immediate delsql1;
        
    elsif  Flag ='2'
    then
\u200b
        sqlStr := 'truncate table '|| nor_table;
        --execute( sqlStr)
        Execute Immediate sqlStr;
        --sqlStr := 'insert into '|| nor_table||' select * from '|| tmp_table;
        sqlStr := 'insert \/*+ append nologging *\/ into '|| nor_table||' select \/*+ parallel(b, 4) *\/ * from '|| tmp_table;
    elsif  Flag ='3'
    then
        sqlStr:='delete from '|| nor_table|| ' where DATE_POST='|| TORQ;
        --execute ( sqlStr)
        Execute Immediate sqlStr;
    
        sqlStr :='insert inot '|| nor_table||' select *,'|| TORQ||' from '|| tmp_table;
    elsif Flag = '4'--\u5168\u91cf\u6570\u636e\u4e14\u53ea\u6709\u6708\u672b\u4e0b\u53d1
    then
        delsql1 :='delete from ' || nor_table || ' a where exists(select 1 from '||tmp_table||' b ) ';
        --insert into debug1 values('delsql1='||delsql1,sysdate);
        commit;
        Execute Immediate delsql1;
        sqlStr := 'insert \/*+ append nologging *\/ into '|| nor_table||' select \/*+ parallel(b, 4) *\/ * from '|| tmp_table;
    end if;
\u200b
    --execute ( sqlStr)
    --insert into debug1 values('sqlStr='||sqlStr,sysdate);
    commit;
    Execute Immediate sqlStr;
    
    sqlStr:='truncate table '|| tmp_table;
--   execute( sqlStr) --\u5148\u4e0d\u6e05\u4e34\u65f6\u8868
  
   -- return 0;
    commit;
end;
\/
\u200b<\/code><\/pre>\n\n\n\n
mysql\u6e90\u7801<\/h2>\n\n\n\n
DROP PROCEDURE IF EXISTS `P_TRANSMITDATA`;
\u200b
DELIMITER $$
CREATE PROCEDURE P_TRANSMITDATA
(
    IN nor_table VARCHAR(50),  -- \u6b63\u5f0f\u8868
    IN delsql VARCHAR(2000),
    IN Flag  VARCHAR(1),  -- 1\uff0c\u589e\u91cf  2. \u5168\u91cf 3\u3001\u660e\u7ec6
    IN TORQ  VARCHAR(8)
)
BEGIN
    DECLARE execSql  VARCHAR(2000);
    DECLARE sqlStr  VARCHAR(500);
    DECLARE delsql1  VARCHAR(500);
    DECLARE colname VARCHAR(40);
    DECLARE tmp_table VARCHAR(50);
    -- begin
    SET @tmp_table = CONCAT('TMP_',nor_table);
\u200b
    --  \u589e\u91cf\u60c5\u51b5
    if Flag = '1' 
    then
            -- -\u5904\u7406\u9700\u8981\u66f4\u65b0\u7684\u6570\u636e
            -- \u5220\u9664\u5386\u53f2\u6570\u636e
            SET @delsql1 = CONCAT('delete from ',nor_table,' a where exists(select 1 from ',@tmp_table,' b where a.',@colname,'=b.',@colname,')  as a');
            SET @sqlStr = CONCAT('insert into ',nor_table,' select a.*,''',TORQ,''' from ',@tmp_table,' a');
    
        PREPARE execSql from @delsql1;
            Execute execSql;
    
    elseif Flag ='2' 
    then
\u200b
            SET @sqlStr = CONCAT('truncate table ',nor_table);
            -- select @sqlStr;
        PREPARE execSql from @sqlStr;
            Execute execSql;
            -- @sqlStr := 'insert into '|| nor_table||' select * from '|| @tmp_table;
            SET @sqlStr = CONCAT('insert into ',nor_table,' select  * from ',@tmp_table);
    elseif Flag ='3'
    then
            SET @sqlStr = CONCAT('delete from ',nor_table,' where DATE_POST=',TORQ);
            PREPARE execSql from @sqlStr;
            Execute execSql;
\u200b
            SET @sqlStr = CONCAT('insert inot ',nor_table,' select *,',TORQ,' from ',@tmp_table);
    elseif Flag = '4'-- \u5168\u91cf\u6570\u636e\u4e14\u53ea\u6709\u6708\u672b\u4e0b\u53d1
    then
            SET @delsql1 = CONCAT('delete from ',nor_table,' a where exists(select 1 from ',@tmp_table,' b ) ');
    
        PREPARE execSql from @delsql1;
            Execute execSql;
\u200b
        SET @sqlStr = CONCAT('insert into ',nor_table,' select * from ',@tmp_table);
    end if;
\u200b
    -- select @sqlStr;
\u200b
    PREPARE execSql from @sqlStr;
        Execute execSql;
\u200b
    -- \u5148\u4e0d\u6e05\u4e34\u65f6\u8868
    -- SET @sqlStr = CONCAT('truncate table ',@tmp_table);
    -- PREPARE execSql from @sqlStr;
        -- Execute execSql;
    
END$$
DELIMITER ;
\u200b
\u200b<\/code><\/pre>\n\n\n\n
\u4e8c\u3001\u5dee\u5f02\u90e8\u5206<\/h1>\n\n\n\n
  • \u521b\u5efa\u65f6\u5148\u5220\u9664\u65e7\u7684\u5b58\u50a8\u8fc7\u7a0b<\/strong>\uff0cOracle \u8bed\u53e5\u7684\u610f\u601d\u662f\u521b\u5efa\u6216\u8986\u76d6\u8be5\u5b58\u50a8\u8fc7\u7a0b\uff0c\u529f\u80fd\u4e00\u6837<\/li><\/ul>\n\n\n\n
    CREATE OR REPLACE PROCEDURE P_TRANSMITDATA                      -- oracle
    DROP PROCEDURE IF EXISTS `P_TRANSMITDATA`;                      -- mysql<\/code><\/pre>\n\n\n\n
    • \u8bbe\u5b9a\u4f20\u5165\u53c2\u6570\u5c5e\u6027\u65f6<\/strong>\uff0cOracle\u548cmysql\u7684 in<\/code> \u7684\u4f4d\u7f6e\u6709\u6240\u5dee\u5f02\uff08Oracle\u5728\u53c2\u6570\u540d\u548c\u6570\u636e\u7c7b\u578b\u4e2d\u95f4\uff0cmysql\u5728\u5b57\u6bb5\u540d\u524d\u9762\uff09\uff1b<\/li>
    • Oracle\u4e2d\u6709varchar2<\/strong>\u7684\u6570\u636e\u7c7b\u578b\uff0cmysql\u6ca1\u6709<\/li>
    • \u6ce8\u91ca\uff1a<\/strong>Oracle\u7528--<\/code>\u53ef\u4ee5\u76f4\u63a5\u6ce8\u91ca\uff0cmysql\u5728--<\/code>\u540e\u9762\u8981\u6709\u7a7a\u683c\u624d\u662f\u6b63\u786e\u7684\u6ce8\u91ca\u8bed\u6cd5\uff08\u7528#\u6ce8\u91ca\uff0c\u53ef\u4ee5\u4e0d\u7528\u7a7a\u683c\uff0c\u6279\u91cf\u6ce8\u91ca\u65f6\uff0cmysql\u8fd8\u53ef\u4ee5\u7528 \/* *\/\uff09<\/li><\/ul>\n\n\n\n
      #oracle
      (
        nor_table in VARCHAR2,  --\u6b63\u5f0f\u8868
        delsql in VARCHAR2,
        Flag in  VARCHAR2,  --1\uff0c\u589e\u91cf  2. \u5168\u91cf 3\u3001\u660e\u7ec6
        TORQ in  VARCHAR2
      )
      \u200b
      #mysql
      (
          IN nor_table VARCHAR(50),  -- \u6b63\u5f0f\u8868
          IN delsql VARCHAR(2000),
          IN Flag  VARCHAR(1),  -- 1\uff0c\u589e\u91cf  2. \u5168\u91cf 3\u3001\u660e\u7ec6
          IN TORQ  VARCHAR(8)
      )<\/code><\/pre>\n\n\n\n
      • \u58f0\u660e\u53d8\u91cf\u683c\u5f0f<\/strong>\uff0cOracle\u9700\u5728\u58f0\u660e\u5b8c\u53c2\u6570\u683c\u5f0f\u540e \u7528 as \u5e76\u8ddf\u4e0a\u53d8\u91cf\u5b9a\u4e49\uff0c\u7136\u540e\u518dbegin<\/code>\u5f00\u59cb\u7f16\u5199\u5b58\u50a8\u8fc7\u7a0b\u4e3b\u4f53\uff1bmysql\u5728\u5b9a\u4e49\u5b8c\u53c2\u6570\u7c7b\u578b\u540e\uff0c\u76f4\u63a5begin<\/code>\u7136\u540e\u518d\u58f0\u660e\u53d8\u91cf\u548c\u7f16\u5199\u8fc7\u7a0b\u4e3b\u4f53\uff0c\u58f0\u660e\u53d8\u91cf\u683c\u5f0f\u65f6\uff0c\u9700\u52a0\u4e0adeclare<\/code>\uff1b<\/li><\/ul>\n\n\n\n
        #oracle
        as
        sqlStr  VARCHAR2(500);
        delsql1  VARCHAR2(500);
        colname VARCHAR2(40);
        tmp_table VARCHAR2(50);
        begin
        \u200b
        #mysql
        BEGIN
            DECLARE execSql  VARCHAR(2000);
            DECLARE sqlStr  VARCHAR(500);
            DECLARE delsql1  VARCHAR(500);
            DECLARE colname VARCHAR(40);
            DECLARE tmp_table VARCHAR(50);<\/code><\/pre>\n\n\n\n
        • \u8bbe\u5b9a\u53d8\u91cf\u503c<\/strong>\uff1aOracle\uff1avar := values<\/code>;mysql\uff1aset @var = values;<\/code>(\u5982\u679c\u4e0d\u662f\u81ea\u5b9a\u4e49\u53d8\u91cf\uff0c\u662f\u7cfb\u7edf\u53d8\u91cf\u7684\u8bdd\uff0c\u662f\u53ef\u4ee5\u4e0d\u7528\u52a0@\u7684)<\/li>
        • \u8fde\u63a5\u53c2\u6570\u6216\u8bed\u53e5\uff0c\u7ec4\u6210\u4e00\u4e2a\u65b0\u5185\u5bb9\uff1a<\/strong>Oracle\uff1a'val1' || 'val2' || 'val3';<\/code> mysql\uff1aCONCAT('val1','val2','val3');<\/code> (\u5982\u679c\u662f\u7ec4\u5408\u7684\u662f\u53d8\u91cf\u6216\u4f20\u5165\u7684\u53c2\u6570\uff0c\u4e0d\u7528\u52a0\u5f15\u53f7\uff09<\/li>
        • sql\u4e2delif\uff1a<\/strong>Oracle\uff1aelsif<\/code> MySQL\uff1aelseif<\/code><\/li>
        • \u76f4\u63a5\u6267\u884c\u7ed9\u5b9a\u8bed\u53e5\uff1a<\/strong> Oracle\uff1aExecute Immediate sql;<\/code>
          • MySQL\u7a0d\u5fae\u590d\u6742\u4e00\u4e9b<\/li>
          • # \u63d0\u4ea4\u51c6\u5907\u6267\u884c\u8bed\u53e5\u5e76\u547d\u540d\u4e3aexecsql<\/code>\uff08\u53ef\u4ee5\u81ea\u5b9a\u4e49\uff09\uff0c\u8bed\u53e5\u5982\u679c\u6765\u81ea\u53d8\u91cf\u7684\u8bdd\u9700\u8981\u52a0@\uff0cOracle\u4e0d\u7528\u3002execsql\u4e5f\u53ef\u4ee5\u63d0\u524d\u58f0\u660e\u6570\u636e\u683c\u5f0f\u3002
            PREPARE execsql FROM @SET_BANK_FLAG;\u200b
            # \u6267\u884c\u8bed\u53e5
            EXECUTE execsql;
            # \u89e3\u9664\u8bed\u53e5
            DEALLOCATE PREPARE execsql;<\/code><\/li><\/ul><\/li><\/ul>\n\n\n\n
            #oracle\n \u00a0 \u00a0if  Flag = '1'\n \u00a0 \u00a0then\n        delsql1 :='delete from ' || nor_table || ' a where exists(select 1 from '||tmp_table||' b where a.'||colname||'=b.'||colname||') '; \n        sqlStr :='insert into ' || nor_table||' select a.*,'''||TORQ||''' from '||tmp_table || ' a';<\/strong>\n        commit; \n        \n        Execute Immediate delsql1;<\/strong>\n \u00a0 \u00a0 \u00a0 \u00a0\n \u00a0  elsif  Flag ='2'<\/strong>\n \u00a0 \u00a0then\n \u00a0 \u00a0\n#mysql\n    if Flag = '1' \n    then\n \u00a0 \u00a0 \u00a0      SET @delsql1 = CONCAT('delete from ',nor_table,' a where exists(select 1 from ',@tmp_table,' b where a.',@colname,'=b.',@colname,')  as a');\n \u00a0 \u00a0 \u00a0      SET @sqlStr = CONCAT('insert into ',nor_table,' select a.*,''',TORQ,''' from ',@tmp_table,' a');<\/strong>\n    \n        PREPARE execSql from @delsql1;\n \u00a0 \u00a0 \u00a0      Execute execSql;<\/strong>\n    \n    elseif Flag ='2'<\/strong> \n    then<\/code><\/pre>\n\n\n\n
            \u4e09\u3001\u5176\u4ed6<\/h1>\n\n\n\n
            \u5e38\u89c1\u51fd\u6570\u4e0a\u7684\u4e0d\u540c\uff1a 1\u3001\u83b7\u53d6\u5f53\u524d\u65f6\u95f4\u5728mysql\u4e2d\u4f7f\u7528NOW()\u51fd\u6570\uff0cOracle\u7528sysdate 2\u3001mysql\u4e0d\u5b58\u5728DECODE\u51fd\u6570\uff0c\u4f7f\u7528IF\u51fd\u6570\u6216\u8005case when\u6765\u7b49\u4ef7\u66ff\u6362\u3002 3\u3001\u65e5\u671f\u51fd\u6570\u5904\u7406 to_date(UPDATEDATE,\u2019yyyy-mm-dd hh24:mi:ss\u2019)\u201d \u4fee\u6539\u4e3a\u5bf9\u5e94\u7684\uff1adate_format(UPDATEDATE, \u2018%Y%m%d %H%i%s\u2019) \u65f6\u95f4\u76f8\u51cf\u4f7f\u7528TIMESTAMPDIFF\u51fd\u6570\uff0c\u4f8b\u5982TIMESTAMPDIFF(SECOND,ENDDATE,NOW()) 4\u3001nvl \u4fee\u6539\u4e3a IFNULL 5\u3001chr() \u4fee\u6539 CHAR() 6\u3001\u83b7\u53d6\u65f6\u95f4\u7684\u6beb\u79d2\u6570\u4f7f\u7528UNIX_TIMESTAMP\uff0c\u65f6\u95f4\u7684\u52a0\u51cf\u8981\u4f7f\u7528date_add\u548cdate_sub<\/p>\n\n\n\n
            \u5728Oracle\u7684\u5185\u5bb9\u8f93\u51fa\uff0c\u548c\u62a5\u9519\u4fe1\u606f\u8f93\u51fa\u7684\u683c\u5f0f\u4e0a\u4e5f\u6709\u4e9b\u8bb8\u4e0d\u540c<\/p>\n\n\n\n
            \u4ee5\u4e0a\u53ea\u662f\u5728\u8fd9\u6b21\u4fee\u6539\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u53d1\u73b0\u7684\u4e0d\u540c\uff0c\u5176\u4ed6\u5730\u65b9\u5fc5\u7136\u8fd8\u6709\u5f88\u591a\uff0c\u9700\u6839\u636e\u62a5\u9519\u5185\u5bb9\u5b9a\u4f4d\uff0c\u5e76\u6839\u636e\u9700\u8981\u5b9e\u73b0\u7684\u529f\u80fd\u53c2\u8003mysql\u6587\u6863\u627e\u5230\u66ff\u4ee3\u65b9\u6cd5\u3002<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"
            \u4e00\u3001\u521b\u5efa\u5b58\u50a8\u8fc7\u7a0b\u6e90\u7801 oracle\u6e90\u7801 mysql\u6e90\u7801 \u4e8c\u3001\u5dee\u5f02\u90e8\u5206 \u521b\u5efa\u65f6\u5148\u5220\u9664\u65e7\u7684\u5b58\u50a8\u8fc7\u7a0b\uff0cOracle  […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,2],"tags":[22,21,23],"class_list":["post-79","post","type-post","status-publish","format-standard","hentry","category-linux","category-2","tag-mysql","tag-oracle","tag-23"],"_links":{"self":[{"href":"https:\/\/szx622.top\/index.php?rest_route=\/wp\/v2\/posts\/79","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/szx622.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/szx622.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/szx622.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/szx622.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=79"}],"version-history":[{"count":4,"href":"https:\/\/szx622.top\/index.php?rest_route=\/wp\/v2\/posts\/79\/revisions"}],"predecessor-version":[{"id":89,"href":"https:\/\/szx622.top\/index.php?rest_route=\/wp\/v2\/posts\/79\/revisions\/89"}],"wp:attachment":[{"href":"https:\/\/szx622.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=79"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/szx622.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=79"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/szx622.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=79"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}