【oracle】多表联查和更新

来源：互联网发布：手机电池修复软件下载编辑：程序博客网时间：2024/06/06 18:21

/** *多表联查 */SELECT T3.BIZ_NAME,       T1.MEMBER_ID,       T1.TEAM_ID,       T2.NAME,       T1.NAME,       T1.STATUS,       T1.BIZ_TYPE,       T2.BIZ_TYPE  FROM T_ZZ_TEAM_MEMBERS T1  LEFT JOIN T_ZZ_PRVETION_TEAM T2    ON T1.TEAM_ID = T2.TEAM_ID  LEFT JOIN T_ZZ_DETAIL_FUNC T3    ON T3.TEAM_ID = T2.TEAM_ID  LEFT JOIN T_DC_PARTY_USER_CERTIFY T4    ON T4.USER_ID = T1.USER_ID WHERE T1.STATUS = '001'   AND T2.STATUS = '1'   AND T2.BIZ_TYPE = '2'

/** * 多表联查更新*/DECLARE  AN_FLAG NUMBER;BEGIN  EXECUTE IMMEDIATE 'UPDATE T_ZZ_TEAM_MEMBERS T1 SET T1.BIZ_TYPE = ''CARE_ROAD_MEMBER''        WHERE EXISTS (SELECT 1          FROM T_ZZ_PRVETION_TEAM T2          LEFT JOIN T_ZZ_DETAIL_FUNC T3          ON T3.TEAM_ID = T2.TEAM_ID          WHERE T1.TEAM_ID = T2.TEAM_ID          AND T2.STATUS = ''1''          AND T2.BIZ_TYPE = ''2''        )        AND EXISTS (SELECT 1          FROM T_DC_PARTY_USER_CERTIFY T4          WHERE T4.USER_ID = T1.USER_ID        )        AND T1.STATUS = ''001''';END;/ COMMIT;

0 0