ORACLE 常用PL/SQL备忘

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;

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;
           --错误后循环继续执行。
           EXCEPTION
           WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLERRM);
     END;             
     END LOOP;
END;


DECLARE
    --表空间名称
  TABLESPACE_NAME VARCHAR2(100);
BEGIN
  TABLESPACE_NAME:='REFORMEREMS_DATA';
  P_REBUILD_ALL_INDEX(TABLESPACE_NAME);
END;
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 204,293评论 6赞 478
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,604评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,958评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,729评论 1赞 277
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,719评论 5赞 366
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,630评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,000评论 3赞 397
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,665评论 0赞 258
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,909评论 1赞 299
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,646评论 2赞 321
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,726评论 1赞 330
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,400评论 4赞 321
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,986评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,959评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,197评论 1赞 260
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 44,996评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,481评论 2赞 342