ORACLE 多表關(guān)聯(lián) UPDATE 語(yǔ)句

UPDATE T_RVW_REVIEW_WORK T1
 SET (EXAM_ID) =
     (SELECT T2.EXAM_ID
        FROM T_EMS_EXAM T2
       WHERE T2.EXAM_NAME = T1.EXAM_NAME
         )
       
       
 UPDATE T_RVW_REVIEW_WORK T1
 SET (COURSE_ID) =
     (SELECT T2.COURSE_ID
        FROM T_BAS_COURSE T2
       WHERE T2.COURSE_NAME = T1.COURSE_NAME
         )

UPDATE T_EMS_EXAM_STUDENT T1
   SET (TEST_NO) =
       (SELECT T2.STUDENT_CODE
          FROM T_BAS_STUDENT T2
         WHERE T2.STUDENT_ID = T1.STUDENT_ID)
 WHERE T1.EXAM_COURSE_ID = 'C6083DEAE6B54DB48EADE430357B93E9'

Oracle中的EXISTS與IN

SELECT * FROM T_SCN_PAPER T1 WHERE EXISTS (
  SELECT 1 FROM T_SCN_PAPER T2
  WHERE T2.EXAM_COURSE_ID = 'D87C1D2C4102472499C80B676DECF4C0'
  AND T2.IS_FETCH = 1
  AND T1.PAPER_ID = T2.PAPER_ID
 )


UPDATE T_SCN_PAPER T1
SET T1.IS_FETCH = 1 WHERE EXISTS (
  SELECT 1 FROM T_SCN_PAPER T2
  WHERE T2.EXAM_COURSE_ID = 'D87C1D2C4102472499C80B676DECF4C0'
  AND T2.IS_FETCH = 0
  AND T1.PAPER_ID = T2.PAPER_ID
 )

改字段類(lèi)型

-- # 假設(shè)字段有數(shù)據(jù)，則改為varchar2(256)執(zhí)行時(shí)會(huì)彈出：
-- “ORA-01439:要更改數(shù)據(jù)類(lèi)型,則要修改的列必須為空”，這時(shí)要用下面方法來(lái)解決這個(gè)問(wèn)題

-- 修改原字段名FULL_SCORE為FULL_SCORE_TMP;
ALTER TABLE T_TPL_OBJECTIVE_QUESTIONS RENAME COLUMN FULL_SCORE TO FULL_SCORE_TMP;

-- 增加一個(gè)和原字段名同名的字段FULL_SCORE
ALTER TABLE T_TPL_OBJECTIVE_QUESTIONS ADD FULL_SCORE VARCHAR2(256);

-- 將原字段name_tmp數(shù)據(jù)更新到增加的字段name
UPDATE T_TPL_OBJECTIVE_QUESTIONS SET FULL_SCORE =TRIM(FULL_SCORE);

-- 更新完,刪除原字段FULL_SCORE_TMP
ALTER TABLE T_TPL_OBJECTIVE_QUESTIONS DROP COLUMN FULL_SCORE_TMP;

-- 查詢
SELECT * FROM T_TPL_OBJECTIVE_QUESTIONS

列轉(zhuǎn)行函數(shù)

-- 異常分?jǐn)?shù)審核
SELECT T1.STUDENT_NAME
      ,T1.CLASS_NAME
      ,T1.COURSE_NAME
      ,T1.PAPER_ID
      ,T1.FULL_SCORE
      ,T1.COURSE_SCORE
      -- ,T1.EXAM_COURSE_ID
      -- ,T1.TEST_NO
      -- ,T1.STUDENT_NAME
      -- ,T1.CLASS_ID
      ,T2.SCAN_PATH
      ,REPLACE(T2.SCAN_PATH, T.MAPPING_PATH, T.HTTP_PATH) FULL_PATH
      
FROM V_RPT_COURSE_STUDENT T1 
 INNER JOIN (SELECT EXAM_COURSE_ID
      ,PAPER_ID
      ,LISTAGG(T_SCN_PAGE.SCAN_PATH,',') WITHIN GROUP (ORDER BY T_SCN_PAGE.PAGE_NO) SCAN_PATH
   FROM T_SCN_PAGE  
    WHERE T_SCN_PAGE.EXAM_COURSE_ID = 'C5CEE3A809234504988ECE1B134F37FD'
   GROUP BY T_SCN_PAGE.EXAM_COURSE_ID,T_SCN_PAGE.PAPER_ID) T2 
    ON T1.EXAM_COURSE_ID = T2.EXAM_COURSE_ID AND T1.PAPER_ID = T2.PAPER_ID
 INNER JOIN (SELECT * FROM T_SCN_SCAN_IMGPATH WHERE ROWNUM = 1) T ON 1 = 1   
    
WHERE T1.EXAM_COURSE_ID = 'C5CEE3A809234504988ECE1B134F37FD'
AND T1.COURSE_SCORE BETWEEN 10 AND 20
AND T1.TEST_NO = '32236'
AND T1.STUDENT_NAME = '楊思魏'
AND T1.CLASS_ID = '8DC2BCA499B94641A379172605045BDF'

Oracle中分頁(yè)查詢

-- 異常分?jǐn)?shù)審核

SELECT * FROM (
SELECT T1.STUDENT_NAME
      ,T1.CLASS_NAME
      ,T1.COURSE_NAME
      ,T1.PAPER_ID
      ,T1.FULL_SCORE
      ,T1.COURSE_SCORE
      -- ,T1.EXAM_COURSE_ID
      -- ,T1.TEST_NO
      -- ,T1.STUDENT_NAME
      -- ,T1.CLASS_ID
      -- ,T2.SCAN_PATH
      ,REPLACE(T2.SCAN_PATH, T.MAPPING_PATH, T.HTTP_PATH) FULL_PATH
      ,ROWNUM RN  -- 用于分頁(yè)控制
      
FROM V_RPT_COURSE_STUDENT T1 
 INNER JOIN (SELECT EXAM_COURSE_ID
      ,PAPER_ID
      ,LISTAGG(T_SCN_PAGE.SCAN_PATH,',') WITHIN GROUP (ORDER BY T_SCN_PAGE.PAGE_NO) SCAN_PATH
   FROM T_SCN_PAGE  
    WHERE T_SCN_PAGE.EXAM_COURSE_ID = 'C5CEE3A809234504988ECE1B134F37FD'
   GROUP BY T_SCN_PAGE.EXAM_COURSE_ID,T_SCN_PAGE.PAPER_ID) T2 
    ON T1.EXAM_COURSE_ID = T2.EXAM_COURSE_ID AND T1.PAPER_ID = T2.PAPER_ID
 INNER JOIN (SELECT * FROM T_SCN_SCAN_IMGPATH WHERE ROWNUM = 1) T ON 1 = 1   
    
WHERE T1.EXAM_COURSE_ID = 'C5CEE3A809234504988ECE1B134F37FD'

AND ROWNUM <= 5 * 10 ) L                                      -- <= pageIndex * pageSize
 WHERE L.RN > ( 5 - 1) * 10 ORDER BY CLASS_NAME,STUDENT_NAME  -- RN > (pageIndex - 1) * pageSize

/*AND T1.COURSE_SCORE BETWEEN 10 AND 20
AND T1.TEST_NO = '32236'
AND T1.STUDENT_NAME = '楊思魏'
AND T1.CLASS_ID = '8DC2BCA499B94641A379172605045BDF'*/

/****************************************************************************
名稱(chēng)：
通過(guò)一次查詢解決取總數(shù)和分頁(yè)的問(wèn)題

修訂記錄：
編輯時(shí)間      編輯人  修改描述
2017-08-31.   CHENQP   初始創(chuàng)建

Tips:
1.ROWNUM <= pageIndex * pageSize;
2.RN > (pageIndex - 1) * pageSize;
3.最內(nèi)層輸入分頁(yè)參數(shù)之前靠count(*) over ()獲得記錄總數(shù)，然后把這個(gè)值作為記錄的一列傳出來(lái)。

*****************************************************************************/

SELECT *
  FROM (SELECT T.* ,ROWNUM RN
         FROM (SELECT T_ACT_USER.USER_ID
                     ,T_ACT_USER.USER_NAME
                      ,COUNT(*) OVER() TOTAL
    FROM T_ACT_USER
    ORDER BY USER_NAME
    ) T  WHERE ROWNUM <= 2 * 5  )  WHERE RN > (2 - 1) * 5 

ORACLE遞歸查詢（適用于ID,PARENT_ID結(jié)構(gòu)數(shù)據(jù)表）

-- 查找一個(gè)節(jié)點(diǎn)的所有直屬父節(jié)點(diǎn)（祖宗）。
SELECT * FROM T_BAS_ORGANIZATION M START WITH M.ORG_ID = '586BAB992AA64E948ACEC7367FA257C4' 
CONNECT BY PRIOR M.PARENT_ORG_ID = M.ORG_ID;

-- 查找一個(gè)節(jié)點(diǎn)的所有直屬子節(jié)點(diǎn)（所有后代）。
SELECT * FROM T_BAS_ORGANIZATION M 
START WITH M.ORG_ID = '586BAB992AA64E948ACEC7367FA257C4' 
CONNECT BY M.PARENT_ORG_ID=PRIOR M.ORG_ID;

-- 根據(jù)用戶ID獲取考試列表,按組織機(jī)構(gòu)方式(遞歸查詢)

SELECT EXAM_ID ExamId
       ,EXAM_NAME ExamName
       ,DISP_ORDER DispOrder
 FROM (
    SELECT DISTINCT
           T1.EXAM_ID
          ,T1.EXAM_NAME
          ,T1.DISP_ORDER
          ,T1.START_DATE
          ,T3.ORG_ID
          ,T3.PARENT_ORG_ID
    FROM T_EMS_EXAM T1
    INNER JOIN T_EMS_EXAM_COURSE T2 ON T1.EXAM_ID = T2.EXAM_ID
    INNER JOIN T_BAS_ORGANIZATION T3 ON T1.ORG_ID = T3.ORG_ID
     WHERE T1.STATUS = 1 AND T2.STATUS = 1 
    ) L
  START WITH L.ORG_ID IN ( SELECT M1.ORG_ID FROM T_BAS_ORGANIZATION M1
   INNER JOIN T_ACT_USER M2 ON M1.ORG_ID = M2.ORG_ID
   WHERE M2.USER_ID = '590CF3DAAEB7404983CB39B3DB65E6D2' 
) 
CONNECT BY L.PARENT_ORG_ID = PRIOR L.ORG_ID
ORDER BY START_DATE DESC

取上取整、向下取整、保留N位小數(shù)、四舍五入、數(shù)字格式化

-- 取上取整、向下取整、保留N位小數(shù)、四舍五入、數(shù)字格式化  
-- 取整（向下取整）
SELECT FLOOR(5.534) FROM DUAL;
SELECT TRUNC(5.534) FROM DUAL;
-- 上面兩種用法都可以對(duì)數(shù)字5.534向下取整，結(jié)果為5.

-- 如果要向上取整 ，得到結(jié)果為6，則應(yīng)該用CEIL
SELECT CEIL(5.534) FROM DUAL;
 

-- 四舍五入： 
SELECT ROUND(5.534) FROM DUAL;
SELECT ROUND(5.534,0) FROM DUAL;
SELECT ROUND(5.534,1) FROM DUAL;
SELECT ROUND(5.534,2) FROM DUAL;
-- 結(jié)果分別為 6,  6,  5.5,  5.53
 

-- 保留N位小數(shù)（不四舍五入）： 
SELECT TRUNC(5.534,0) FROM DUAL;
SELECT TRUNC(5.534,1) FROM DUAL;
SELECT TRUNC(5.534,2) FROM DUAL;
-- 結(jié)果分別是 5,5.5,5.53，其中保留0位小數(shù)就相當(dāng)于直接取整了。
 

-- 數(shù)字格式化： 
SELECT TO_CHAR(12345.123,'99999999.9999') FROM DUAL;
-- 結(jié)果為12345.123

SELECT TO_CHAR(12345.123,'99999999.9900') FROM DUAL;
-- 小數(shù)后第三第四為不足補(bǔ)0，結(jié)果為12345.1230

SELECT TO_CHAR(0.123,'99999999.9900') FROM DUAL;
SELECT TO_CHAR(0.123,'99999990.9900') FROM DUAL;
-- 結(jié)果分別為 .123, 0.123

Oracle 里面定時(shí)執(zhí)行任務(wù)，比如存儲(chǔ)過(guò)程內(nèi)容

DECLARE
   JOB_RECOVER_TASK NUMBER;   
BEGIN   
  -- 每3分鐘執(zhí)行一次
   DBMS_JOB.SUBMIT(JOB_RECOVER_TASK,'USP_RVW_RECOVER_TASK;',SYSDATE,'SYSDATE+3/1440');     
   COMMIT;
END;

恢復(fù)數(shù)據(jù)

ALTER TABLE  T_ACT_USER_ROLE ENABLE ROW MOVEMENT;
FLASHBACK TABLE  T_ACT_USER_ROLE TO TIMESTAMP SYSTIMESTAMP - INTERVAL '50' MINUTE;

Oracle補(bǔ)零

-- 前端補(bǔ)0： 
SELECT LPAD('345',8,'0') FROM DUAL;   
SELECT TO_CHAR('345','00000000') FROM DUAL; 
 
-- 后端補(bǔ)0： 
SELECT RPAD('345',8,'0') FROM DUAL; 

ORACLE批量重建索引

-- ORACLE批量重建索引
CREATE OR REPLACE PROCEDURE P_REBUILD_ALL_INDEX
   (TABLESPACE_NAME IN VARCHAR2)
AS
   SQLT VARCHAR(200);
BEGIN

    FOR IDX IN (SELECT INDEX_NAME, TABLESPACE_NAME, STATUS FROM USER_INDEXES WHERE TABLESPACE_NAME=TABLESPACE_NAME AND STATUS='VALID' AND TEMPORARY = 'N') LOOP
    BEGIN
           SQLT := 'ALTER INDEX ' || IDX.INDEX_NAME || ' REBUILD ';
           DBMS_OUTPUT.PUT_LINE(IDX.INDEX_NAME);
           DBMS_OUTPUT.PUT_LINE(SQLT);
           EXECUTE IMMEDIATE SQLT;
           --錯(cuò)誤后循環(huán)繼續(xù)執(zhí)行。
           EXCEPTION
           WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
     END;             
     END LOOP;
END;


DECLARE
    --表空間名稱(chēng)
  TABLESPACE_NAME VARCHAR2(100);
BEGIN
  TABLESPACE_NAME:='REFORMEREMS_DATA';
  P_REBUILD_ALL_INDEX(TABLESPACE_NAME);
END;