mysql5.6 給存儲過程創(chuàng)建定時器

DELIMITER $$
CREATE DEFINER=`bigdata`@`%` PROCEDURE `personnel_project_authority`()
begin
 truncate table personnel_project_authority;
 INSERT into personnel_project_authority
    SELECT
    a.account_id,--  員工編號,
    b.company_id,--  公司 id,
    b.company_name,--  公司名稱,
    b.region_id,--  管控區(qū)域 id,
    b.region_name,--  管控區(qū)域名稱,
    b.area_id,--  項(xiàng)目 id,
    b.area_name, --  項(xiàng)目名稱
    b.tenant_id 
FROM
    ( SELECT account_id, tenant_id, object_id FROM lehome_business_oauth2.account_post_relation WHERE department_type = 1 GROUP BY account_id, tenant_id, department_type, object_id ) a
    LEFT JOIN (
    SELECT
        CASE WHEN d.id IS NULL THEN c.id ELSE d.id END AS company_id,
          CASE WHEN d.company_name IS NULL THEN c.company_name ELSE d.company_name  END AS company_name,
            c.id AS region_id,
            c.company_name AS region_name,
            a.id AS area_id,
            a.area_name,
            b.tenant_id 
        FROM
            ( SELECT * FROM lehome_property_db.area_info WHERE deleted_status = 'Normal' AND enabled_status = 'Enabled' -- deleted_status：狀態(tài)值,enabled_status：是否啟用
            -- AND data_tenant_id IN ( 'f3bf88fadc7b324b3b561fe39fea56e7', '53913585f1aaea19a8e0a883d93aacb5' ) 
            ) a
            LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company_area_relation ) b ON a.id = b.area_id
            LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company WHERE company_type = 2 AND STATUS = 1 ) c ON b.company_id = c.id
            LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company WHERE company_type = 1 AND STATUS = 1 ) d ON c.parent_company_id = d.id 
        WHERE
            b.tenant_id IS NOT NULL 
            AND b.tenant_id != '' 
        ) b ON a.tenant_id = b.tenant_id 
    WHERE
        b.area_id IS NOT NULL UNION ALL
    SELECT
        a.account_id,-- 員工編號,
        b.company_id,
        b.company_name,
        b.region_id,
        b.region_name,
        b.area_id,
        b.area_name,
        b.tenant_id 
    FROM
        (
        SELECT
            account_id,
            object_id 
        FROM
            lehome_business_oauth2.account_post_relation 
        WHERE
            department_type = 4 
            AND account_id NOT IN ( SELECT account_id FROM lehome_business_oauth2.account_post_relation WHERE department_type = 1 GROUP BY account_id ) 
        GROUP BY
            account_id,
            object_id 
        ) a
        LEFT JOIN (
        SELECT
            CASE WHEN d.id IS NULL THEN c.id ELSE d.id END AS company_id,
              CASE WHEN d.company_name IS NULL THEN c.company_name ELSE d.company_name END AS company_name,
                c.id AS region_id,
                c.company_name AS region_name,
                a.id AS area_id,
                a.area_name,
                b.tenant_id 
            FROM
                ( SELECT * FROM lehome_property_db.area_info WHERE deleted_status = 'Normal' AND enabled_status = 'Enabled' -- deleted_status：狀態(tài)值,enabled_status：是否啟用
                -- AND data_tenant_id IN ( 'f3bf88fadc7b324b3b561fe39fea56e7', '53913585f1aaea19a8e0a883d93aacb5' ) 
                ) a
                LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company_area_relation ) b ON a.id = b.area_id
                LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company WHERE company_type = 2 AND STATUS = 1 ) c ON b.company_id = c.id
                LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company WHERE company_type = 1 AND STATUS = 1 ) d ON c.parent_company_id = d.id 
            WHERE
                b.tenant_id IS NOT NULL 
                AND b.tenant_id != '' 
            ) b ON a.object_id = b.company_id 
        WHERE
            b.area_id IS NOT NULL UNION ALL
        SELECT
            a.account_id,-- 員工編號,
            b.company_id,
            b.company_name,
            b.region_id,
            b.region_name,
            b.area_id,
            b.area_name,
            b.tenant_id 
        FROM
            (
            SELECT
                account_id,
                object_id 
            FROM
                lehome_business_oauth2.account_post_relation 
            WHERE
                department_type = 3 
                AND account_id NOT IN ( SELECT account_id FROM lehome_business_oauth2.account_post_relation WHERE department_type IN ( 1, 4 ) GROUP BY account_id ) 
            GROUP BY
                account_id,
                object_id 
            ) a
            LEFT JOIN (
            SELECT
                CASE WHEN d.id IS NULL THEN c.id ELSE d.id END AS company_id,
                  CASE WHEN d.company_name IS NULL THEN c.company_name ELSE d.company_name END AS company_name,
                    c.id AS region_id,
                    c.company_name AS region_name,
                    a.id AS area_id,
                    a.area_name,
                    b.tenant_id 
                FROM
                    ( SELECT * FROM lehome_property_db.area_info WHERE deleted_status = 'Normal' AND enabled_status = 'Enabled' -- deleted_status：狀態(tài)值,enabled_status：是否啟用
                    -- AND data_tenant_id IN ( 'f3bf88fadc7b324b3b561fe39fea56e7', '53913585f1aaea19a8e0a883d93aacb5' ) 
                    ) a
                    LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company_area_relation ) b ON a.id = b.area_id
                    LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company WHERE company_type = 2 AND STATUS = 1 ) c ON b.company_id = c.id
                    LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company WHERE company_type = 1 AND STATUS = 1 ) d ON c.parent_company_id = d.id 
                WHERE
                    b.tenant_id IS NOT NULL 
                    AND b.tenant_id != '' 
                ) b ON a.object_id = b.region_id 
            WHERE
                b.area_id IS NOT NULL UNION ALL
            SELECT
                a.account_id,-- 員工編號,
                b.company_id,
                b.company_name,
                b.region_id,
                b.region_name,
                b.area_id,
                b.area_name,
                b.tenant_id 
            FROM
                (
                SELECT
                    account_id,
                    object_id 
                FROM
                    lehome_business_oauth2.account_post_relation 
                WHERE
                    department_type = 2 
                    AND account_id NOT IN ( SELECT account_id FROM lehome_business_oauth2.account_post_relation WHERE department_type IN ( 1, 3, 4 ) GROUP BY account_id ) 
                GROUP BY
                    account_id,
                    object_id 
                ) a
                LEFT JOIN (
                SELECT
                    CASE WHEN d.id IS NULL THEN c.id ELSE d.id END AS company_id,
                      CASE WHEN d.company_name IS NULL THEN c.company_name ELSE d.company_name END AS company_name,
                        c.id AS region_id,
                        c.company_name AS region_name,
                        a.id AS area_id,
                        a.area_name,
                        b.tenant_id 
                    FROM
                        ( SELECT * FROM lehome_property_db.area_info WHERE deleted_status = 'Normal' AND enabled_status = 'Enabled' -- deleted_status：狀態(tài)值,enabled_status：是否啟用
                        -- AND data_tenant_id IN ( 'f3bf88fadc7b324b3b561fe39fea56e7', '53913585f1aaea19a8e0a883d93aacb5' ) 
                        ) a
                        LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company_area_relation ) b ON a.id = b.area_id
                        LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company WHERE company_type = 2 AND STATUS = 1 ) c ON b.company_id = c.id
                        LEFT JOIN ( SELECT * FROM lehome_business_oauth2.company WHERE company_type = 1 AND STATUS = 1 ) d ON c.parent_company_id = d.id 
                    WHERE
                        b.tenant_id IS NOT NULL 
                        AND b.tenant_id != '' 
                    ) b ON a.object_id = b.area_id 
            WHERE
    b.area_name IS NOT NULL;
 END$$

DELIMITER ;

personnel_project_authorit 是創(chuàng)建的存儲過程的名字

DROP EVENT IF EXISTS `lehome_business_oauth2`.`e_personnel_project_authority`;

CREATE EVENT `lehome_business_oauth2`.`e_personnel_project_authority` 
  ON SCHEDULE EVERY 20 MINUTE 
  STARTS  '2022-12-09 15:01:00'  ON COMPLETION PRESERVE  
  ENABLE  
  DO call personnel_project_authority();

在sql客戶端上面創(chuàng)建定時器

image.png

通過sql命令創(chuàng)建定時任務(wù)
事件名稱為e_personnel_project_authority 每二十分鐘執(zhí)行一次執(zhí)行的內(nèi)容為 personnel_project_authority 這個存儲過程

CREATE EVENT e_personnel_project_authority
ON SCHEDULE EVERY 20 MINUTE
DO CALL personnel_project_authority();

最后編輯于：2023.07.28 14:08:21

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末湿镀，一起剝皮案震驚了整個濱河市洗搂，隨后出現(xiàn)的幾起案子递沪，更是在濱河造成了極大的恐慌，老刑警劉巖窜司，帶你破解...
沈念sama閱讀 211,817評論 6贊 492
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件轰驳，死亡現(xiàn)場離奇詭異，居然都是意外死亡绿聘，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 90,329評論 3贊 385
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門次舌，熙熙樓的掌柜王于貴愁眉苦臉地迎上來熄攘，“玉大人，你說我怎么就攤上這事彼念∨不” “怎么了？”我有些...
開封第一講書人閱讀 157,354評論 0贊 348
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵国拇，是天一觀的道長洛史。經(jīng)常有香客問我惯殊，道長酱吝，這世上最難降的妖魔是什么？我笑而不...
開封第一講書人閱讀 56,498評論 1贊 284
?港島之戀（遺憾婚禮）
正文為了忘掉前任土思，我火速辦了婚禮务热，結(jié)果婚禮上忆嗜，老公的妹妹穿的比我還像新娘。我一直安慰自己崎岂，他們只是感情好捆毫，可當(dāng)我...
茶點(diǎn)故事閱讀 65,600評論 6贊 386
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著冲甘，像睡著了一般绩卤。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上江醇，一...
開封第一講書人閱讀 49,829評論 1贊 290
城市分裂傳說
那天濒憋，我揣著相機(jī)與錄音，去河邊找鬼陶夜。笑死凛驮，一個胖子當(dāng)著我的面吹牛，可吹牛的內(nèi)容都是我干的条辟。我是一名探鬼主播黔夭，決...
沈念sama閱讀 38,979評論 3贊 408
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼，長吁一口氣：“原來是場噩夢啊……” “哼羽嫡！你這毒婦竟也來了本姥？” 一聲冷哼從身側(cè)響起，我...
開封第一講書人閱讀 37,722評論 0贊 266
萬榮殺人案實(shí)錄
序言：老撾萬榮一對情侶失蹤杭棵，失蹤者是張志新（化名）和其女友劉穎扣草，沒想到半個月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體颜屠，經(jīng)...
沈念sama閱讀 44,189評論 1贊 303
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡辰妙，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 36,519評論 2贊 327
?白月光啟示錄
正文我和宋清朗相戀三年，在試婚紗的時候發(fā)現(xiàn)自己被綠了甫窟。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片密浑。...
茶點(diǎn)故事閱讀 38,654評論 1贊 340
活死人
序言：一個原本活蹦亂跳的男人離奇死亡，死狀恐怖粗井，靈堂內(nèi)的尸體忽然破棺而出尔破，到底是詐尸還是另有隱情，我是刑警寧澤浇衬，帶...
沈念sama閱讀 34,329評論 4贊 330
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布懒构，位于F島的核電站，受9級特大地震影響耘擂，放射性物質(zhì)發(fā)生泄漏胆剧。R本人自食惡果不足惜，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,940評論 3贊 313
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望秩霍。院中可真熱鬧篙悯，春花似錦、人聲如沸铃绒。這莊子的主人今日做“春日...
開封第一講書人閱讀 30,762評論 0贊 21
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽颠悬。三九已至矮燎，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間赔癌，已是汗流浹背漏峰。一陣腳步聲響...
開封第一講書人閱讀 31,993評論 1贊 266
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留届榄，地道東北人浅乔。一個月前我還...
沈念sama閱讀 46,382評論 2贊 360
代替公主和親
正文我出身青樓，卻偏偏與公主長得像铝条，于是被迫代替她去往敵國和親靖苇。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 43,543評論 2贊 349

mysql5.6 給存儲過程創(chuàng)建定時器

推薦閱讀更多精彩內(nèi)容