经典SQL语句收集（ORACLE）..docVIP

经典SQL语句收集（ORACLE） 1、经典的查询语句2、经典的字定义函数3、经典的与业务相关的存储过程等等抛砖引玉：备注本人彩票迷（有点缺陷）create or replace function f_ssqrandom(priornum in integer,endnum in integer)?return varchar2? ?is?v_prior_rand integer :=0;?v_end_rand integer;?v_string string(32000):=红色球;beginfor v in 1..5 loopfor v_count in 1..3 loop? v_prior_rand:=floor(dbms_random.value(1,priornum));?? ? v_end_rand:=v_prior_rand;? random? v_prior_rand:=floor(dbms_random.value(1,priornum));? if v_prior_rand!=v_end_rand then? v_string:=v_string||,||v_prior_rand||,||v_end_rand;? else? goto random;? end if;? dbms_output.put_line(v_string);? end loop;? v_string:=v_string|| 蓝色球, ||floor(dbms_random.value(1,endnum))|| 红色球;? end loop;? return v_string;?end f_ssqrandom; 查询新建用户select username? from dba_users?where username not in? (TEXT, RMAN_USER, TEST, SCOTT, TSMSYS, MDDATA, DIP,? DBSNMP, SYSMAN, MDSYS, ORDSYS, EXFSYS, DMSYS, WMSYS,? CTXSYS, ANONYMOUS, XDB, ORDPLUGINS, SI_INFORMTN_SCHEMA,? OLAPSYS, MGMT_VIEW, SYS, SYSTEM, OUTLN);查询那些用户，操纵了那些表造成了锁机? SELECT s.username,? decode(l.type,TM,TABLE LOCK,? TX,ROW LOCK,? NULL) LOCK_LEVEL,? o.owner,o.object_name,o.object_type,? s.sid,s.serial#,s.terminal,s.machine,gram,s.osuser? FROM v$session s,v$lock l,all_objects o? WHERE l.sid = s.sid? AND l.id1 = o.object_id(+)? AND s.username is NOT Null? 其中 TM 为表锁定 TX 为行锁定 ? 看锁阻塞的方法是? SELECT (select username FROM v$session WHERE sid=a.sid) blocker,? ? a.sid,? ? is blocking,? ? (select username FROM v$session WHERE sid=b.sid) blockee,? ? b.sid? ? FROM v$lock a, v$lock b? WHERE a.block = 1? ? AND b.request 0? ? AND a.id1 = b.id1? ? AND a.id2 = b.id2 --各进制之间转换CREATE OR REPLACE TYPE type_str_agg AS OBJECT(? total VARCHAR2(4000),? STATIC FUNCTION odciaggregateinitialize(sctx IN OUT type_str_agg)? RETURN NUMBER,? MEMBER FUNCTION odciaggregateiterate? (? SELF IN OUT type_str_agg,? VALUE IN VARCHAR2? ) RETURN NUMBER,? MEMBER FUNCTION odciaggregateterminate? (? SELF IN