oracle 数据库常用语句(逐步增加)

--创建用户并赋权
sql> grant select,update,insert,delete,drop,create on *.* to icdpub@localhost
identified by 'icdpub';
--SQL分类：
--DDL—数据定义语言（CREATE，ALTER，DROP，DECLARE）
--DML—数据操纵语言（SELECT，DELETE，UPDATE，INSERT）
--DCL—数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）

--定义字段
tErrCode        INT;
   tErrMsg         VARCHAR2(200);
--异常中加入获取异常信息
tErrCode := SQLCODE;
tErrMsg := SUBSTRB(SQLERRM, 1, 200);


 EXCEPTION
      WHEN OTHERS THEN
        tErrCode := SQLCODE;
        tErrMsg := SUBSTRB(SQLERRM, 1, 200);
        ROLLBACK;
    END;


引用
某位高人说:job 比存储过程跑数据快.   (有机会验证一下)



--查询重复数据
SELECT * FROM T_SQM_TRACK4MONITOR T
 WHERE ROWID <> (SELECT MAX(ROWID)
                     FROM T_SQM_TRACK4MONITOR K
                    WHERE NVL(K.PARTID,0) = NVL(T.PARTID,0)
                      AND NVL(K.SERIALNO,0) = NVL(T.SERIALNO,0)
                      AND NVL(K.CALLERNO,0) = NVL(T.CALLERNO,0)
                      AND NVL(K.CALLEDNO,0) = NVL(T.CALLEDNO,0)
                      AND NVL(K.SUBSNUMBER,0) = NVL(T.SUBSNUMBER,0)
                      AND NVL(K.CUSTCITYID,0) = NVL(T.CUSTCITYID,0)
                      AND NVL(K.SERVICECITYID,0) = NVL(T.SERVICECITYID,0)
                      AND NVL(K.STAFFCITYID,0) = NVL(T.STAFFCITYID,0)
                      AND NVL(K.SUBSCITYID,0) = NVL(T.SUBSCITYID,0)
                      AND K.CONTACTSTARTTIME = T.CONTACTSTARTTIME
                      AND K.CONTACTENDTIME = T.CONTACTENDTIME
                      AND NVL(K.CONTACTDURATION,0) = NVL(T.CONTACTDURATION,0)
                      AND NVL(K.DEALSTATE,0) = NVL(T.DEALSTATE,0)
                      AND NVL(K.LOCKSTAFFID,0) = NVL(T.LOCKSTAFFID,0)
                  )

--查询重复数据
 select contactid
   from t_cct_contactdetail
  group by contactid
 having count(*) > 1

--剔除空值
NVL( string1, replace_with)
　　功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值。 
--t_ucp_roleauth表中所有moduleid, authid字段插入到t_ucp_putauth表中
 insert into t_ucp_putauth
  (staffid, moduleid, authid)
  select 'KF080401647', moduleid, authid from t_ucp_roleauth

--随机10条记录
 select *
from  
(
select * from t_agt_testitem order by dbms_random.value
)  
where rownum <= 10

--分秒取值

FLOOR(ROUND(TO_NUMBER(T5.ENDTIME - T5.STARTTIME) * 24 * 60 * 60)/60)||'分'||
                       MOD(ROUND(TO_NUMBER(T5.ENDTIME - T5.STARTTIME) * 24 * 60 * 60),60) ||'秒' as TIMELONG,



--drop table if exists t_c_pan_log;

--查询两表多余的数据

 select *
      from icdpub.t_sqm_tmptrack4monitor t
     where t.partid = '1103'
       and not exists (select 1
              from icdpub.t_sqm_track4monitor k
             where t.serialno = k.serialno
               and k.partid = '1103')

--查询job执行时间
select t.NEXT_DATE
  from user_jobs t
 where upper(t.WHAT) like 'P_SQM_INITSTAFF%'

--序列创建 从1开始,每次递增1.
create sequence studentPKSequence start with 1 increment by 1;
--查询(查询完后,序列递增)
select studentPKSequence.nextval from dual;

--查询所有用户的表信息,LOWER()和UPPER()完成大小写转换
SELECT * FROM ALL_ALL_TABLES T WHERE T.table_name  LIKE UPPER('%qa%')

--通过工号获取组织机构树
SELECT O.ORGACODE,O.ORGAID,O.ORGANAME
           FROM T_UCP_STAFFBASICINFO S, T_UCP_ORGAINFO O
          WHERE S.STAFFID = 'meiyoudao'
           -- AND S.ORGAID LIKE O.ORGAID || '%'   --向上
            AND O.ORGAID LIKE S.ORGAID || '%'     --向下

--获取列最长的记录
select max(length(t.name)) lens, t.name
  from icdpub.T_SR_SERVICEREQUESTTYPE t
 group by t.name
 order by lens desc


select TRUNC(SYSDATE+1)+8.5/24
  from dual;

--拼接字符串ORACLE 10G以上
select t.testid,
       wmsys.wm_concat(t.optionid), 
       wmsys.wm_concat(t.optioncontent)
  from t_agt_testoption t
 group by t.testid;

--查询当前登录的用户
select   user   from   dual;  
--查询登录数据库的所有用户(需要有特权用户才能查询)
select   *   from   v$session;