oracle 数据库常用语句(逐步增加)
--创建用户并赋权sql> grant select,update,insert,delete,drop,create on *.* to icdpub@localhostidentified 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 descselect 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;