ORACLE 多表关联 UPDATE 语句

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
 )

改字段类型

-- # 假设字段有数据，则改为varchar2(256)执行时会弹出：
-- “ORA-01439:要更改数据类型,则要修改的列必须为空”，这时要用下面方法来解决这个问题

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

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

-- 将原字段name_tmp数据更新到增加的字段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

列转行函数

-- 异常分数审核
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中分页查询

-- 异常分数审核

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  -- 用于分页控制
      
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'*/

/****************************************************************************
名称：
通过一次查询解决取总数和分页的问题

修订记录：
编辑时间      编辑人  修改描述
2017-08-31.   CHENQP   初始创建

Tips:
1.ROWNUM <= pageIndex * pageSize;
2.RN > (pageIndex - 1) * pageSize;
3.最内层输入分页参数之前靠count(*) over ()获得记录总数，然后把这个值作为记录的一列传出来。

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

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结构数据表）

-- 查找一个节点的所有直属父节点（祖宗）。
SELECT * FROM T_BAS_ORGANIZATION M START WITH M.ORG_ID = '586BAB992AA64E948ACEC7367FA257C4' 
CONNECT BY PRIOR M.PARENT_ORG_ID = M.ORG_ID;

-- 查找一个节点的所有直属子节点（所有后代）。
SELECT * FROM T_BAS_ORGANIZATION M 
START WITH M.ORG_ID = '586BAB992AA64E948ACEC7367FA257C4' 
CONNECT BY M.PARENT_ORG_ID=PRIOR M.ORG_ID;

-- 根据用户ID获取考试列表,按组织机构方式(递归查询)

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位小数、四舍五入、数字格式化

-- 取上取整、向下取整、保留N位小数、四舍五入、数字格式化  
-- 取整（向下取整）
SELECT FLOOR(5.534) FROM DUAL;
SELECT TRUNC(5.534) FROM DUAL;
-- 上面两种用法都可以对数字5.534向下取整，结果为5.

-- 如果要向上取整 ，得到结果为6，则应该用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;
-- 结果分别为 6,  6,  5.5,  5.53
 

-- 保留N位小数（不四舍五入）： 
SELECT TRUNC(5.534,0) FROM DUAL;
SELECT TRUNC(5.534,1) FROM DUAL;
SELECT TRUNC(5.534,2) FROM DUAL;
-- 结果分别是 5,5.5,5.53，其中保留0位小数就相当于直接取整了。
 

-- 数字格式化： 
SELECT TO_CHAR(12345.123,'99999999.9999') FROM DUAL;
-- 结果为12345.123

SELECT TO_CHAR(12345.123,'99999999.9900') FROM DUAL;
-- 小数后第三第四为不足补0，结果为12345.1230

SELECT TO_CHAR(0.123,'99999999.9900') FROM DUAL;
SELECT TO_CHAR(0.123,'99999990.9900') FROM DUAL;
-- 结果分别为 .123, 0.123

Oracle 里面定时执行任务，比如存储过程内容

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

恢复数据

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

Oracle补零

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