ORACLE常用sql语句示例记录(持续更新)

merge_into

MERGE INTO LHJH_TBVAL_DNGHBZ A
USING (SELECT '89DAE2E3BF9D7DD5E0530100007F2F38' AS VAL_ID FROM DUAL) B
ON (A.VAL_ID = B.VAL_ID)
WHEN MATCHED THEN
  UPDATE
     SET NUM_BFDX    = '2',
         NUM_YKZBFDX = '1',
         NUM_WCL     = ROUND('1' / '2' * 100, 2)
WHEN NOT MATCHED THEN
  INSERT
    (VAL_ID, PAR_ID, REP_YM, NUM_BFDX, NUM_YKZBFDX, NUM_WCL)
  VALUES
    (SYS_GUID(), 'dk000001', '201905', '2', '1', ROUND('1' / '2' * 100, 2))

输出数字1-10

SELECT LPAD(LEVEL,2,0) FROM DUAL
CONNECT BY LEVEL<13;

SELECT LEVEL FROM DUAL CONNECT BY LEVEL < = 10

with … as … select

WITH 
T1 AS(
   SELECT ROUND(SUM(A.DECLARE_STATUS)/COUNT(1),2)*100||'%' AS DONE
FROM CXZY_NOTICE_ISSUE A
WHERE A.NOTICE_ID='0e2e237c662f489c8b29bc12830878de'
),
T2 AS(
   SELECT ROUND(SUM(CASE B.SUBMIT_STATUS WHEN '3' THEN 1 ELSE 0 END)/SUM(CASE B.SUBMIT_STATUS WHEN '0' THEN 0 ELSE 1 END),2)*100||'%' PASS
FROM CXZY_PROJECT_INFO B
WHERE B.NOTICE_ID='0e2e237c662f489c8b29bc12830878de'
),
T3 AS(
   SELECT COUNT(1) ALREADY_ISSUE
FROM CXZY_NOTICE_ISSUE C
WHERE C.NOTICE_ID='0e2e237c662f489c8b29bc12830878de'
AND C.DECLARE_STATUS = '1'
),
T4 AS(
   SELECT COUNT(D.ISSUE_ID) NOT_ISSUE
FROM CXZY_NOTICE_ISSUE D
WHERE D.NOTICE_ID='0e2e237c662f489c8b29bc12830878de'
AND D.DECLARE_STATUS ='0'
),
T5 AS(
   SELECT (CASE WHEN SYSDATE > TRUNC(TO_DATE('2019-08-05','YYYY-MM-DD'))+(INTERVAL '1' DAY) THEN 
H.TOTAL - G.NOT_LATE
 ELSE 0 END )BE_LATE
   
 FROM (
 SELECT  COUNT(F.ISSUE_ID) NOT_LATE
FROM(
SELECT E.ISSUE_ID,SUM(CASE E.IS_LATE WHEN '0' THEN 1 ELSE 0 END) ALREADY_DONE
FROM CXZY_PROJECT_INFO E
WHERE E.NOTICE_ID='0e2e237c662f489c8b29bc12830878de'
AND E.SUBMIT_STATUS <> '0'
GROUP BY E.ISSUE_ID
) F
WHERE F.ALREADY_DONE <> 0
 )G,(SELECT COUNT(1) TOTAL FROM CXZY_NOTICE_ISSUE WHERE NOTICE_ID='0e2e237c662f489c8b29bc12830878de')H
)
SELECT T1.DONE,T2.PASS,T3.ALREADY_ISSUE,T4.NOT_ISSUE,T5.BE_LATE FROM T1,T2,T3,T4,T5

查询前三名(包括重复)

SELECT D.NAME AS DEPARTMENT, E.NAME AS EMPLOYEE, E.SALARY AS SALARY
FROM TEST_EMPLOYEE  E
INNER JOIN TEST_DEPARTMENT  D
ON E.DEPARTMENTID = D.ID
WHERE (
        SELECT COUNT(DISTINCT SALARY)
        FROM TEST_EMPLOYEE
        WHERE SALARY > E.SALARY
        AND DEPARTMENTID = E.DEPARTMENTID
         ) < 3
ORDER BY E.DEPARTMENTID, SALARY DESC

查询所有表表结构信息(表名,字段名,数据类型,备注)

SELECT T.TABLE_NAME,
       T.COLUMN_NAME,
       T.DATA_TYPE || '(' || T.DATA_LENGTH || ')',
       T1.COMMENTS
FROM USER_TAB_COLS T, USER_COL_COMMENTS T1
WHERE T.TABLE_NAME = T1.TABLE_NAME
AND T.COLUMN_NAME = T1.COLUMN_NAME;

递归

SELECT T.IN_USE_OR_REVOKE,
       T.PAR_ID,
       T.PAR_NAME,
       T.PAR_SHORT_NAME,
       T.PAR_CODE,
       T.PAR_OPTION,
       T.PAR_TYPE,
       T.PAR_AFFILIATION,
       T.PAR_ORG_REL_TYPE,
       (CASE CONNECT_BY_ISLEAF
         WHEN 1 THEN
          'open'
         WHEN 0 THEN
          'closed'
       END) AS STATE,
       T.ORDER_NO
  FROM PAR_BASEINFO T, PAR_REAL_INFO R
 WHERE LEVEL = 1
 START WITH R.PAR_ID = '1'
        AND R.ENDDA = '99991231'
        AND T.PAR_ID = R.PAR_ID
CONNECT BY PRIOR R.PAR_ID = R.P_PAR_ID
       AND R.ENDDA = '99991231'
       AND T.PAR_ID = R.PAR_ID
       AND T.IN_USE_OR_REVOKE IN ('0', '1')
       AND LEVEL < 3

分页

SELECT *
  FROM (SELECT ROWNUM RRR, TTT.*
          FROM (
		  
          --查询sql
		  
          ) TTT
         WHERE ROWNUM <= '10')
 WHERE RRR > '0'

根据字段值查询所在表名和列名

--示例1
DECLARE
  CURSOR CUR_QUERY IS
  SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS T WHERE T.DATA_TYPE = 'VARCHAR2';
  A        NUMBER;
  SQL_HARD VARCHAR2(2000);
  VV       NUMBER;
BEGIN DBMS_OUTPUT.ENABLE(buffer_size => null);
  FOR REC1 IN CUR_QUERY LOOP
    A := 0;
    IF REC1.DATA_TYPE = 'VARCHAR2' OR REC1.DATA_TYPE = 'CHAR' THEN
      A := 1;
    END IF;
    IF A > 0 THEN
      SQL_HARD := '';
      SQL_HARD := 'SELECT COUNT(*) FROM ' || REC1.TABLE_NAME || ' WHERE ' ||'"'||
                  REC1.COLUMN_NAME ||'"'|| ' LIKE ''78950ec2505147df837fc7dde0a5b74a''';--字段值
      --DBMS_OUTPUT.PUT_LINE(SQL_HARD);
      EXECUTE IMMEDIATE SQL_HARD INTO VV;
      IF VV > 0 THEN
        DBMS_OUTPUT.PUT_LINE('[字段值所在的表.字段]:[' || REC1.TABLE_NAME || '].[' ||
                             REC1.COLUMN_NAME || ']');
      END IF;
    END IF;
  END LOOP;
END;

--示例2
DECLARE
  CURSOR CUR_QUERY1 IS
    SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS T WHERE T.DATA_TYPE = 'VARCHAR2';
  CURSOR CUR_QUERY2 IS
    SELECT T.MODULE_INFO_CODE,MAX(T.INFO_ID)INFO_ID
    FROM COMM_ATTACHEMENT T
    WHERE T.INFO_ID IS NOT NULL
    GROUP BY T.MODULE_INFO_CODE
    ORDER BY T.MODULE_INFO_CODE;    

  SQL_HARD VARCHAR2(2000);
  VV       NUMBER;
BEGIN DBMS_OUTPUT.ENABLE(buffer_size => null);
  FOR REC2 IN CUR_QUERY2 LOOP
   FOR REC1 IN CUR_QUERY1 LOOP
        SQL_HARD := '';
        SQL_HARD := 'SELECT COUNT(*) FROM ' || REC1.TABLE_NAME || ' WHERE ' ||'"'||
                    REC1.COLUMN_NAME ||'"'|| ' LIKE '||''''|| REC2.INFO_ID||'''';
       -- DBMS_OUTPUT.PUT_LINE(SQL_HARD);
        EXECUTE IMMEDIATE SQL_HARD INTO VV;
        IF VV > 0 THEN
        DBMS_OUTPUT.PUT_LINE('[字段值]:[' || REC2.INFO_ID || '],[CODE值]:[' ||
                               REC2.MODULE_INFO_CODE || '],[对应表名称]:['|| REC1.TABLE_NAME 
                               || '],[对应列名]:['|| REC1.COLUMN_NAME || ']');
        END IF;
    END LOOP;
  END LOOP;
END;

自定义排序

MERGE INTO SYN_ADDRESSBOOK T1
USING (SELECT ADDRESSBOOK_ID, NEW_ORDER_NO
         FROM (SELECT R1.ADDRESSBOOK_ID,
                      R1.ORDER_NO AS ORDER_NO_R1,
                      R1.ORDER_NO_NULLS,
                      CASE
                        WHEN R1.ADDRESSBOOK_ID =
                             '3725c682eec0427299ed9d8f2eeeb1c1' THEN
                         6
                        WHEN R1.ORDER_NO < R2.ORDER_NO AND R1.ORDER_NO >= 6 THEN
                         R1.ORDER_NO + 1
                        WHEN R1.ORDER_NO > R2.ORDER_NO AND R1.ORDER_NO <= 6 THEN
                         R1.ORDER_NO - 1
                        ELSE
                         R1.ORDER_NO
                      END AS NEW_ORDER_NO,
                      R1.ORDER_NO_OLD
                 FROM (SELECT ADDRESSBOOK_ID,
                              ORDER_NO_NULLS,
                              ROWNUM         AS ORDER_NO,
                              ORDER_NO       AS ORDER_NO_OLD
                         FROM (SELECT TZW.ADDRESSBOOK_ID,
                                      TZW.ORDER_NO,
                                      TZW.ORDER_NO AS ORDER_NO_NULLS
                                 FROM SYN_ADDRESSBOOK TZW
                                WHERE TZW.PAR_ID = '00000001'
                                  AND TZW.DATASTATE = 1
                                ORDER BY TZW.ORDER_NO) TMP
                        ORDER BY TMP.ORDER_NO) R1
                 LEFT JOIN (SELECT ADDRESSBOOK_ID,
                                  ORDER_NO_NULLS,
                                  ROWNUM AS ORDER_NO
                             FROM (SELECT TZW.ADDRESSBOOK_ID,
                                          TZW.ORDER_NO,
                                          TZW.ORDER_NO AS ORDER_NO_NULLS
                                     FROM SYN_ADDRESSBOOK TZW
                                    WHERE TZW.PAR_ID = '00000001'
                                      AND TZW.DATASTATE = 1
                                    ORDER BY TZW.ORDER_NO) TMP
                            ORDER BY TMP.ORDER_NO) R2
                   ON R2.ADDRESSBOOK_ID = '3725c682eec0427299ed9d8f2eeeb1c1'
                ORDER BY NEW_ORDER_NO)
        WHERE ORDER_NO_R1 != NEW_ORDER_NO
           OR ORDER_NO_OLD != NEW_ORDER_NO
           OR ORDER_NO_NULLS IS NULL) T2
ON (T1.ADDRESSBOOK_ID = T2.ADDRESSBOOK_ID)
WHEN MATCHED THEN
  UPDATE SET T1.ORDER_NO = T2.NEW_ORDER_NO

锁表信息查询

/*查看Oracle被锁的表以及如何解锁
注意权限问题*/

--1.查看是否有被锁的表：

select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b

where b.object_id = a.object_id

--2.查看是哪个进程锁的

select b.username,b.sid,b.serial#,logon_time
from v$locked_object a,v$session b

where a.session_id = b.sid order by b.logon_time

--3.杀掉进程

alter system kill session 'sid,serial#';

删除多个表的数据

DECLARE CURSOR CUR_TRUNC
IS
SELECT T.TABLE_NAME,T.COMMENTS FROM USER_TAB_COMMENTS T 
WHERE T.TABLE_NAME LIKE '%LHKH_%' 
AND T.COMMENTS NOT LIKE '%指标对应%'
AND T.COMMENTS NOT LIKE '%指标编码%'
AND T.COMMENTS NOT LIKE '%指标配置表%'
AND T.COMMENTS NOT LIKE '%字典%';
BEGIN
FOR CUR_DEL IN CUR_TRUNC LOOP
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||CUR_DEL.TABLE_NAME;
END LOOP;
END;

统计各级党委完成率

--包含本级党委
WITH A0 AS(SELECT T.PAR_ID, T.P_PAR_ID, CONNECT_BY_ISLEAF AS LEAF
          FROM PAR_REAL_INFO T
         INNER JOIN PAR_BASEINFO R
            ON T.PAR_ID = R.PAR_ID
         START WITH T.PAR_ID = '1'
                AND T.PAR_ID = R.PAR_ID
                AND T.ENDDA = '99991231'
                AND R.IN_USE_OR_REVOKE = '1'
        CONNECT BY PRIOR T.PAR_ID = T.P_PAR_ID
               AND T.PAR_ID = R.PAR_ID
               AND R.IN_USE_OR_REVOKE = '1'
               AND T.ENDDA = '99991231'
               AND R.PAR_TYPE = '611'),
      A1 AS(SELECT T.PAR_ID
            FROM LHKH_DWHXX T
            WHERE T.ITEM_STATE='1'
            AND T.LEARNING_DATE BETWEEN TRUNC(SYSDATE-1,'year') AND LAST_DAY(ADD_MONTHS(SYSDATE, -1))
            GROUP BY T.PAR_ID),
      A2 AS (SELECT A.PAR_ID, COUNT(1) AS CNT_DBDWS--达标党委数
             FROM A0 A
             START WITH A.PAR_ID IN (SELECT PAR_ID FROM A1)
             CONNECT BY A.PAR_ID = PRIOR A.P_PAR_ID
             GROUP BY A.PAR_ID),
      A3 AS(SELECT ROOT_ID AS PAR_ID, COUNT(1) AS CNT_DWZS --党委总数
            FROM (SELECT PAR_ID, CONNECT_BY_ROOT(PAR_ID) AS ROOT_ID
                  FROM A0 A
                  START WITH A.PAR_ID IN (SELECT PAR_ID FROM A0)
                  CONNECT BY PRIOR A.PAR_ID = A.P_PAR_ID)
            GROUP BY ROOT_ID)
      SELECT A3.PAR_ID,
             DECODE(A3.CNT_DWZS,0,0,(A2.CNT_DBDWS / A3.CNT_DWZS)) T211
      FROM A3
      INNER JOIN A2
      ON A3.PAR_ID = A2.PAR_ID
      

--不包含本级党委
 WITH A0 AS(SELECT T.PAR_ID, T.P_PAR_ID, CONNECT_BY_ISLEAF AS LEAF
          FROM PAR_REAL_INFO T
         INNER JOIN PAR_BASEINFO R
            ON T.PAR_ID = R.PAR_ID
         START WITH T.PAR_ID = '1'
                AND T.PAR_ID = R.PAR_ID
                AND T.ENDDA = '99991231'
                AND R.IN_USE_OR_REVOKE = '1'
        CONNECT BY PRIOR T.PAR_ID = T.P_PAR_ID
               AND T.PAR_ID = R.PAR_ID
               AND R.IN_USE_OR_REVOKE = '1'
               AND T.ENDDA = '99991231'
               AND R.PAR_TYPE = '611'),
      A1 AS(SELECT T.PAR_ID
            FROM LHKH_DWHXX T
            WHERE T.ITEM_STATE='1'
            AND T.LEARNING_DATE BETWEEN TRUNC(SYSDATE-1,'year') AND LAST_DAY(ADD_MONTHS(SYSDATE, -1))
            GROUP BY T.PAR_ID),
      A2 AS (SELECT A.P_PAR_ID AS PAR_ID, COUNT(1) AS CNT_DBDWS --达标党委数
             FROM A0 A
             START WITH A.PAR_ID IN (SELECT PAR_ID FROM A1)
             CONNECT BY A.PAR_ID = PRIOR A.P_PAR_ID
             GROUP BY A.P_PAR_ID),
      A3 AS(SELECT ROOT_ID AS PAR_ID, COUNT(1) AS CNT_DWZS --党委总数
            FROM (SELECT PAR_ID, CONNECT_BY_ROOT(P_PAR_ID) AS ROOT_ID
                  FROM A0 A
                  START WITH A.P_PAR_ID IN (SELECT PAR_ID FROM A0 WHERE LEAF = 0)
                  CONNECT BY PRIOR A.PAR_ID = A.P_PAR_ID)
            GROUP BY ROOT_ID)
      SELECT A3.PAR_ID,
             DECODE(A3.CNT_DWZS,0,0,(A2.CNT_DBDWS / A3.CNT_DWZS)) T211
      FROM A3
      INNER JOIN A2
      ON A3.PAR_ID = A2.PAR_ID