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

00q3oku8m8

--创建用户并赋权
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;