经典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):='红色球';
begin
for v in 1..5 loop
for 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;
希望不要恶意回帖 MARK 顶的大哥大姐 放心理就行 小弟心领了
[解决办法]
查询新建用户
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,s.program,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
[解决办法]
比如：获取系统信息：
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')
authentication_type,
SYS_CONTEXT('USERENV','AUTHENTICATION_DATA')
authentication_data
from dual
[解决办法]
Oracle计算时间差表达式
--获取两时间的相差豪秒数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒数 FROM DUAL;
/*
相差豪秒数
----------
86401000
1 row selected
*/
--获取两时间的相差秒数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 FROM DUAL;
/*
相差秒数
----------
86401
1 row selected
*/
--获取两时间的相差分钟数
select ceil(((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss'))) * 24 * 60) 相差分钟数 FROM DUAL;
/*
相差分钟数
----------
1441
1 row selected
*/
--获取两时间的相差小时数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24) 相差小时数 FROM DUAL;
/*
相差小时数
----------
25
1 row selected
*/
--获取两时间的相差天数
select ceil((To_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - To_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss'))) 相差天数 FROM DUAL;
/*
相差天数
----------
2
1 row selected
*/
--获取两时间月份差
select (EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd'))) * 12 +
EXTRACT(month FROM to_date('2008-05-01','yyyy-mm-dd')) - EXTRACT(month FROM to_date('2008-04-30','yyyy-mm-dd')) months
from dual;
/*
MONTHS
----------
13
1 row selected
*/
--获取两时间年份差
select EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd')) years from dual;
/*
YEARS
----------
1
1 row selected
*/
--Insert multiple rows into different tables with a single statement:
INSERT ALL
WHEN type=1 THEN INTO tab1 VALUES (myseq.NEXTVAL, val)
WHEN type=2 THEN INTO tab2 VALUES (myseq.NEXTVAL, val)
WHEN type IN (3,4,5) THEN INTO tab3 VALUES (myseq.NEXTVAL, val)
ELSE INTO tab4 VALUES (myseq.NEXTVAL, val)
SELECT type, val FROM source_tab;
execute immediate 'drop table '
[解决办法]
v_tname;
end if;
begin
execute immediate 'create table '
[解决办法]
v_tname
[解决办法]
' (id1 number)';
exception
when others then
raise e_createerror;
end;
execute immediate 'insert into '
[解决办法]
v_tname
[解决办法]
' values (3)';
execute immediate 'insert into '
[解决办法]
v_tname
[解决办法]
' values (3)';
execute immediate 'insert into '
[解决办法]
v_tname
[解决办法]
' values (3)';
execute immediate 'insert into '
[解决办法]
v_tname
[解决办法]
' values (3)';
commit;
execute immediate ' select count(*) from all_tables where table_name ='''
[解决办法]
upper(v_tname)
[解决办法]
''''
into m;
if m > 0 then
--execute immediate ' select count(*) from '''
[解决办法]
upper(v_tname)
[解决办法]
'''' into m;
dbms_output.put_line('good1!');dbms_output.put_line('count of v_tname:'
[解决办法]
m);
end if;
exception
when e_createerror then
dbms_output.put_line('表创建语句出错请检查');
end;
/
[解决办法]
-- 定义游标
declare
cursor aa is
select names,num from test;
begin
for bb in aa
loop
if bb.names = "ORACLE" then
end if
end loop;
end
论坛里的例子:
create or replace procedure test is
cursor v_cur_6(ids varchar2) is
select * from T where instr(','
[解决办法]
ids
[解决办法]
',',','
[解决办法]
to_char(A)
[解决办法]
',')=0;
v_cur_line_6 v_cur_6%ROWTYPE;
begin
open v_cur_6('1,2');
loop
fetch v_cur_6 into v_cur_line_6;
exit when v_cur_6%notfound;
dbms_output.put_line('---------');
end loop;
close v_cur_6;
end;
[解决办法]
我也看到一篇文章，分享
http://www.cnblogs.com/liuweitoo/archive/2007/04/02/697293.html
[解决办法]
以工作分组，按名称排序，取出汇总每个工作下的人员名称，并以“,”符号作为分隔符
select job, ltrim(sys_connect_by_path(ename, ','), ',') scbp
from (select job,
ename,
row_number() over(partition by job order by ename) rn,
count(*) over(partition by job) cnt
from scott.emp) t
where rn = cnt
start with rn = 1
connect by prior job = job
and prior rn = rn - 1
order by job
-- 定义游标
declare
cursor aa is
select names,num from test;
begin
for bb in aa
loop
if bb.names = "ORACLE" then
end if
end loop;
end
论坛里的例子:
create or replace procedure test is
cursor v_cur_6(ids varchar2) is
select * from T where instr(','
[解决办法]
ids
------解决方案--------------------
',',','
[解决办法]
to_char(A)
[解决办法]
',')=0;
v_cur_line_6 v_cur_6%ROWTYPE;
begin
open v_cur_6('1,2');
loop
fetch v_cur_6 into v_cur_line_6;
exit when v_cur_6%notfound;
dbms_output.put_line('---------');
end loop;
close v_cur_6;
end;
