ORACLE 常用的解决办法

上一篇 / 下一篇 2014-05-28 10:26:20 / 个人分类：甲骨文

第一部分、SQL&PL/SQL

[Q]怎么样查询特殊字符，如通配符%与_51Testing软件测试网,|nC-G md
[Q]如何插入单引号到 数据库表中
FgQd+HFv.o O2x0[Q]怎样设置事务一致性
:K6e2U6L.\g(}G0[Q]怎么样利用光标更新数据
a2?tM!NV0[Q]怎样自定义异常
&?)E/j8`3dso0[Q]十进制与十六进制的转换
Zd |$w)[ok'l0[Q]能不能介绍SYS_CONTEXT的详细用法
]L[+B&vt3C0[Q]怎么获得今天是星期几，还关于其它日期函数用法
(t*?8WL/y/j"? b0[Q]随机抽取前N条记录的问题
%J+EB5pU,dU1V'`0[Q]抽取从N行到M行的记录，如从20行到30行的记录51Testing软件测试网|+vrR{{a
[Q]怎么样抽取重复记录
9f!n"S$W \o2R0[Q]怎么样设置自治事务
 [Q]怎么样在过程中暂停指定时间
i)s PCU0[Q]怎么样快速计算事务的时间与日志量
w WZW}n{8?0[Q]怎样创建临时表51Testing软件测试网H([ KC'YJ&z
[Q]怎么样在PL/SQL中执行DDL语句
s4Oy DI;H0[Q]怎么样获取IP地址
AD*KJNpZ0[Q]怎么样加密存储过程
 [Q]怎么样在 ORACLE中定时运行存储过程
9Xrq'b*mx"F(yt?L5W0[Q]怎么样从数据库中获得毫秒
1|0|5J(s)L:x]0[Q]如果存在就更新，不存在就插入可以用一个语句实现吗
 [Q]怎么实现左联，右联与外联51Testing软件测试网c#~/b/M~j
[Q]怎么实现一条记录根据条件多表插入
!G*q,},\?x/i8~0[Q]如何实现行列转换
9YLQ2TYEV1Rp0[Q]怎么样实现分组取前N条记录
Q.ubOPl1Rf9S&_0[Q]怎么样把相邻记录合并到一条记录
_Ys Kq0[Q]如何取得一列中第N大的值？
UB(\HGp3Y0[Q]怎么样把查询内容输出到文本
0N:jXT-`ZD0[Q]怎么设置存储过程的调用者权限
 [Q] 如何在SQL*PLUS环境中执行OS命令？51Testing软件测试网 C-AbK^v
[Q]怎么快速获得用户下每个表或表分区的记录数51Testing软件测试网r d:Yo%|\y
[Q]怎么在Oracle中发邮件51Testing软件测试网{,J vu7qY
[Q]怎么样在Oracle中写 操作系统文件，如写日志
U8DIC"sF|n0
,AY/?$I+cI"n'y[0　第一部分、SQL&PL/SQL

51Testing软件测试网@^bI4q/{.S

P;A]]&@aE5J0[Q]怎么样查询特殊字符，如通配符%与_
[A]select * from table where name like 'A\_%' escape '\'51Testing软件测试网QfV"F3c`
51Testing软件测试网'Ul@3m`
[Q]如何插入单引号到数据库表中51Testing软件测试网h_c9MJ
[A]可以用ASCII码处理，其它特殊字符如&也一样，如
yn-Hi9V;Rw0insert into t values('i'||chr(39)||'m'); -- chr(39)代表字符'
或者用两个单引号表示一个
G fZ,B P B.s0or insert into t values('I''m'); -- 两个''可以表示一个'
*P;e"v mC^(Q051Testing软件测试网,UODJ'OhYQe
[Q]怎样设置事务一致性
[A]set transaction [isolation level] read committed; 默认语句级一致性
set transaction [isolation level] serializable;
Y.?!Ii ~V7z0read only; 事务级一致性
AR1\8\ Ytq051Testing软件测试网f&{$A&W\1HA
[Q]怎么样利用光标更新数据
RQ(R\ u A5KkJ0[A]cursor c1 is
select * from tablename51Testing软件测试网-]@#|HB&c`
where name is null for update [of column]51Testing软件测试网\n'TX5u)c/I
……
QM2wY0GMkA H0update tablename set column = ……
6z~U'|ml0where current of c1;
;C o~\F t%q8H6g|b051Testing软件测试网;\|[C'B&g
[Q]怎样自定义异常
[*?J:lKGO+xJ!d0[A] pragma_exception_init(exception_name,error_number);
\G \Yx0如果立即抛出异常
raise_application_error(error_number,error_msg,true|false);
-?.@h X ? |;XG0其中number从-20000到-20999，错误信息最大2048B
异常变量
N#E q:I&R V0SQLCODE 错误代码51Testing软件测试网mO |5{+O
SQLERRM 错误信息
*sTh T2H!AOp nQ)}0
g!S G"Xr)\"` b)P7_0[Q]十进制与十六进制的转换
[A]8i以上版本：
WY YT?4v}8I(j0to_char(100,'XX')51Testing软件测试网l-}2x5v3q*o
to_number('4D','XX')
8i以下的进制之间的转换参考如下脚本51Testing软件测试网.MFz%|z)tj
create or replace function to_base( p_dec in number, p_base in number )51Testing软件测试网)Vq8Aer]5g
return varchar251Testing软件测试网_ f9P+Mv~O
is
IUr@om"K0l_str varchar2(255) default NULL;
Qj4_$s!X$|cll}0l_num number default p_dec;
8sZLyg U0l_hex varchar2(16) default '0123456789ABCDEF';
4P,^l6m5] y)v^z0begin51Testing软件测试网5o"w*Kw-|[3V
if ( p_dec is null or p_base is null ) then
return null;
v)Qw_M x3{0end if;
mL%T_l'p~ R@0if ( trunc(p_dec) <> p_dec OR p_dec < 0 ) then51Testing软件测试网6xcofwQ\
raise PROGRAM_ERROR;
end if;51Testing软件测试网 Bz)qs9|Se
loop
1?M_S?5|(O0l_str := substr( l_hex, mod(l_num,p_base)+1, 1 ) || l_str;
l_num := trunc( l_num/p_base );
d"Ru8{wf@t]8g0exit when ( l_num = 0 );51Testing软件测试网[1A3B-Y5b6C O
end loop;51Testing软件测试网7Cw#Z b$r2Y
return l_str;51Testing软件测试网qZ?+cP)R
end to_base;51Testing软件测试网 Q"k8p{$NO0U
/51Testing软件测试网_,K0ru f}
create or replace function to_dec
( p_str in varchar2,51Testing软件测试网fo:P9}8^~7S
p_from_base in number default 16 ) return number51Testing软件测试网8{8^0Y2md;C
is
P yIP&c;o z0l_num number default 0;
0b-a$b)[Nsx}0l_hex varchar2(16) default '0123456789ABCDEF';
begin
if ( p_str is null or p_from_base is null ) then
jt7M^ [R6kx0return null;51Testing软件测试网R.F-B;v h
end if;
for i in 1 .. length(p_str) loop
;`r]5d vy0l_num := l_num * p_from_base + instr(l_hex,upper(substr(p_str,i,1)))-1;51Testing软件测试网;m7]*IDE`
end loop;
Zk9f A/P$Af0f'y t0return l_num;
p8Ot5U @)TO0end to_dec;
L\4cpDy F0/
/vXe*r F P0create or replace function to_hex( p_dec in number ) return varchar2
^$g,_,AC;fC0is
(? sF\2b.r8H(b0begin
:vnVVL|,o5I0return to_base( p_dec, 16 );
\b7eh5sC+n0end to_hex;51Testing软件测试网*qgu;s;{ A~4E
/
wH$O aKYmP0create or replace function to_bin( p_dec in number ) return varchar251Testing软件测试网 `3?w"Qn~
is
begin
return to_base( p_dec, 2 );
H&kY9f Q0end to_bin;
` BJ1b*H8ux2s0/51Testing软件测试网J/s(RB4N1Mu
create or replace function to_oct( p_dec in number ) return varchar251Testing软件测试网+U~3d@j^^
is
n^#PX/C!YH0begin
return to_base( p_dec, 8 );51Testing软件测试网F*vy?aO _
end to_oct;51Testing软件测试网v#vNQA:b
/51Testing软件测试网6y|r#{/u!ny
51Testing软件测试网qV0q-q-U"Q
[Q]能不能介绍SYS_CONTEXT的详细用法
[A]利用以下的查询，你就明白了
OIKOY$t0select
9Q;R)y(HR+u7B0SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,51Testing软件测试网0mxjmHnK
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
&| eS@6wm"k0SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
*t9^!X#W2Hg;x0SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
d,t9w1hQ,}6^+y'R0SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,51Testing软件测试网wikQ;p.D#M
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
r+^!LVok0SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,51Testing软件测试网+AQP#ZK^]g
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
?l/A~H'b0SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,51Testing软件测试网Q i!J^)n;Z&c
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,51Testing软件测试网_'Xk3^BD&s X
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
#c'w c5tgP6x qmr0SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
Yb;^$[E\0SYS_CONTEXT('USERENV','OS_USER') os_user,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,
&k+].Aoj:Xm0SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
0Q:m5pY)X)]']4f#XAU0SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,51Testing软件测试网&F+`&a*{c`
SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
,X#m2j"xEjE0hN$E0SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
R Y"d@&QG)V0SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE') authentication_type,
r:r,D^pX;c-H&W0SYS_CONTEXT('USERENV','AUTHENTICATION_DATA') authentication_data51Testing软件测试网:P v\ Nob z
from dual
VS8WFqg u0
-l6j+D-~$K0[Q]怎么获得今天是星期几，还关于其它日期函数用法
[A]可以用to_char来解决，如
*t jehL.@2I0select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
5l^-eK x`0在获取之前可以设置日期语言，如51Testing软件测试网 h@B3w4i
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
lh3yI4K B'Ud0还可以在函数中指定
f:q]x9]p0select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
$Tf,[}gX.~w0其它更多用法，可以参考to_char与to_date函数51Testing软件测试网v(W!nj&vU
如获得完整的时间格式
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;51Testing软件测试网c6w9K'{7N:z5\
随便介绍几个其它函数的用法：51Testing软件测试网'Ej2s#_.t*Xu
本月的天数
@z}7dpg;o7\`0SELECT to_char(last_day(SYSDATE),'dd') days FROM dual
今年的天数
/Fa|6@G)X"Z0select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual51Testing软件测试网 x@u0Gq?
下个星期一的日期
:y b/L X2?fdF0SELECT Next_day(SYSDATE,'monday') FROM dual51Testing软件测试网V!wCz4B*X |

[Q]随机抽取前N条记录的问题
*d(J@sui"RK+T0[A]8i以上版本
R9F Pgm0select * from (select * from tablename order by sys_guid()) where rownum < N;
O}7?*](Q'o0select * from (select * from tablename order by dbms_random.value) where rownum< N;
注：dbms_random包需要手工安装，位于$ORACLE_HOME/rdbms/admin/dbmsrand.sql
dbms_random.value(100,200)可以产生100到200范围的随机数
G!cC'B"Z q051Testing软件测试网(p'OVKz;B.]
[Q]抽取从N行到M行的记录，如从20行到30行的记录
ONZ7C@*w/?oY0[A]select * from (select rownum id,t.* from table) where id between N and M;

[Q]怎么样抽取重复记录
%L af(A1b0[A]select * from table t1 where where t1.rowed !=51Testing软件测试网AZf6\2dg
(select max(rowed) from table t251Testing软件测试网w_:C$SP/n|
where t1.id=t2.id and t1.name=t2.name)
'C:RTN(w0或者
M8Wdtz8Mls0select count(*), t.col_a,t.col_b from table t51Testing软件测试网:_.FP*j.Mk
group by col_a,col_b
having count(*)>1
w!IN&tPNU!@W?0如果想删除重复记录，可以把第一个语句的select替换为delete
"dzh }4x3y0
X1^)F6T,U:^sD:J0[Q]怎么样设置自治事务
[A]8i以上版本，不影响主事务
keXNy9XwI:i2^2h2~0pragma autonomous_transaction;51Testing软件测试网1c3m/E/R/k
……
#G\ }$B"b y4z4X0commit|rollback;51Testing软件测试网0j/f#Wfb

[Q]怎么样在过程中暂停指定时间
[A]DBMS_LOCK包的sleep过程
A&r\wgS0如：dbms_lock.sleep(5);表示暂停5秒。

!`4n \Lr,c.XXJ h0[Q]怎么样快速计算事务的时间与日志量
[A]可以采用类似如下的脚本
DECLARE51Testing软件测试网g8G A0Fm]? @
start_time NUMBER;51Testing软件测试网}fZB/w_qO
end_time NUMBER;
[tlHuN:H^0start_redo_size NUMBER;
end_redo_size NUMBER;
VY!cU#l J#{n0BEGIN
start_time := dbms_utility.get_time;51Testing软件测试网1}uc:jk)mcL
SELECT VALUE INTO start_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
#l/v }o0]o i0AND s.NAME='redo size';
2u6_%b&Z;[e0--transaction start
INSERT INTO t151Testing软件测试网Q{/O.{5OZ|
SELECT * FROM All_Objects;
5`U"PQY$j0--other dml statement51Testing软件测试网*HJ#k)D&B&ae
COMMIT;
$o%Lm2`U }0end_time := dbms_utility.get_time;
)dTC8^?d0SELECT VALUE INTO end_redo_size FROM v$mystat m,v$statname s
WHERE m.STATISTIC#=s.STATISTIC#
:P f8tU$u_/z0AND s.NAME='redo size';
Ez }#ge0dbms_output.put_line('Escape Time:'||to_char(end_time-start_time)||' centiseconds');
*Fb x*o9b'vs(` F0dbms_output.put_line('Redo Size:'||to_char(end_redo_size-start_redo_size)||' bytes');
END;
@_vv%npg0
'hs|b1]d0SB$J0[Q]怎样创建临时表
[A]8i以上版本51Testing软件测试网s%b_.`5@6r
create global temporary tablename(column list)
&J+ej?^*{0on commit preserve rows; --提交保留数据会话临时表51Testing软件测试网Rq_h^
on commit delete rows; --提交删除数据事务临时表
cdA"O^X0临时表是相对于会话的，别的会话看不到该会话的数据。51Testing软件测试网0["O1B{1Z id

w|/v^bN2T-?0[Q]怎么样在PL/SQL中执行DDL语句51Testing软件测试网h7PnO G7?
[A]1、8i以下版本dbms_sql包51Testing软件测试网(V'_%d2wA+l
2、8i以上版本还可以用
n _L4uXM]| }H0execute immediate sql;
;K!z7D2Y]Y0dbms_utility.exec_ddl_statement('sql');51Testing软件测试网j0ETm,I!z:U

5f+}.iOp E7N9v$Zx0[Q]怎么样获取IP地址
[A]服务器(817以上)：utl_inaddr.get_host_address
8Ch? n-_S0客户端：sys_context('userenv','ip_address')
)An&n!j(r9g-P051Testing软件测试网~Z8L I8G-M6y
[Q]怎么样加密存储过程
o:H)S]A:T0[A]用wrap命令，如（假定你的存储过程保存为a.sql）
RO"W[e `(~0wrap iname=a.sql51Testing软件测试网-_)W2u.e.kE
PL/SQL Wrapper: Release 8.1.7.0.0 - Production on Tue Nov 27 22:26:48 2001
F"q0fn#G7q4P^0Copyright (c) Oracle Corporation 1993, 2000. All Rights Reserved.
Processing a.sql to a.plb51Testing软件测试网YvxV,]
提示a.sql转换为a.plb，这就是加密了的脚本，执行a.plb即可生成加密了的存储过程51Testing软件测试网pm(SQJp
51Testing软件测试网 _.g}YTP
[Q]怎么样在ORACLE中定时运行存储过程
[A]可以利用dbms_job包来定时运行作业，如执行存储过程，一个简单的例子，提交一个作业：
6N&DSc2OS/Q0VARIABLE jobno number;51Testing软件测试网B0p|tN"Q
BEGIN
$wSOB)D-p,ip0DBMS_JOB.SUBMIT(:jobno, 'ur_procedure;',SYSDATE,'SYSDATE + 1');51Testing软件测试网vEXOmf0P
commit;
8ek,PA)z0END;
N+|.T W{9x2Z0之后，就可以用以下语句查询已经提交的作业51Testing软件测试网.M-vtx8H2Rd
select * from user_jobs;
l"H;@*JDW0
;X IjYq,@ y*wU0[Q]怎么样从数据库中获得毫秒
[A]9i以上版本，有一个timestamp类型获得毫秒，如
f2O-_Q\5` ~}0B0SQL>select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff') time1,51Testing软件测试网.wW$R)Y&ID3sF
to_char(current_timestamp) time2 from dual;
r!Lv2@ O0
TIME1 TIME2
----------------------------- ----------------------------------------------------------------51Testing软件测试网&r_"z[4|'F
2003-10-24 10:48:45.656000 24-OCT-03 10.48.45.656000 AM +08:00
YFI0H9sk`0可以看到，毫秒在to_char中对应的是FF。51Testing软件测试网N#{H"mU;v1?
8i以上版本可以创建一个如下的java函数51Testing软件测试网nBC x!CN'\
SQL>create or replace and compile
j&M#]mq-v$Z/aK/C0java source
named "MyTimestamp"
H2TV6icz0as
q3sK)d6b7k0import java.lang.String;
V^2Q+J8Gt+i0l]m0import java.sql.Timestamp;
vQ5T#JEk0
N\P;ey0public class MyTimestamp
F0M-{$t.sb/X(j$g"}Fe0{51Testing软件测试网 [}9NK i XLA
public static String getTimestamp()
{
return(new Timestamp(System.currentTimeMillis())).toString();51Testing软件测试网0u'{1f|%r`
}51Testing软件测试网1h.S|z'a
};
SQL>java created.51Testing软件测试网#}f?5k@ Z
注：注意java的语法，注意大小写
nzVYp8x0SQL>create or replace function my_timestamp return varchar2
E&T4YX#k`0as language java
xB2D3@A0name 'MyTimestamp.getTimestamp() return java.lang.String';51Testing软件测试网|Fs8tr#@? }
/
cOW;a;M~k0SQL>function created.51Testing软件测试网1Y4]f0a/{K C
SQL>select my_timestamp,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') ORACLE_TIME from dual;51Testing软件测试网)_Ne{V
MY_TIMESTAMP ORACLE_TIME51Testing软件测试网 [R*l8hH-W C
------------------------ -------------------
2003-03-17 19:15:59.688 2003-03-17 19:15:59
6g+tB)z)p0|1~0如果只想获得1/100秒(hsecs)，还可以利用dbms_utility.get_time51Testing软件测试网oA9d-_j_

;R3Vb&H'L2H%A i%k0[Q]如果存在就更新，不存在就插入可以用一个语句实现吗
c$[ @ttN;E%l6ZJ0[A]9i已经支持了，是Merge，但是只支持select子查询，
f1r!} U4N"N0如果是单条数据记录，可以写作select …… from dual的子查询。
"gje"wa&tS0语法为：
d ZRXO2|X+Dq;M0MERGE INTO table
YwWpKw c0USING data_source
z POwy1o5@0ON (condition)
WHEN MATCHED THEN update_clause
4X2m6pe [ OJT0WHEN NOT MATCHED THEN insert_clause;
g!AcssL0如
jt^ P1UoQ;{|`zkT0MERGE INTO course c51Testing软件测试网}Ff o%h"jZ-k
USING (SELECT course_name, period,
Jt2^](^vU0course_hours
].U{5X e}!o2Zx0FROM course_updates) cu
l_,uh:p"p0ON (c.course_name = cu.course_name
~y%\#d6gr?9L0AND c.period = cu.period)
WHEN MATCHED THEN
UPDATE
^.iG8k*W/E ^:H p0SET c.course_hours = cu.course_hours51Testing软件测试网7S^YF$j;m"Wab
WHEN NOT MATCHED THEN
INSERT (c.course_name, c.period,51Testing软件测试网 l2\F%o7aO o
c.course_hours)
$i*m@2pS!QvW R+i0VALUES (cu.course_name, cu.period,
LVYM;Q:t6b%N0cu.course_hours);
n uj+A+m j0
H'hoS2I-^V0[Q]怎么实现左联，右联与外联51Testing软件测试网"@a^!I(g
[A]在9i以前可以这么写:
.KJ#e+o&P C0左联：
XL2V ohe a*Rae0select a.id,a.name,b.address from a,b
l? b%[Y,`7U9F]0where a.id=b.id(+)
-xT7v4sc0W0右联:51Testing软件测试网yRwILU&@-W
select a.id,a.name,b.address from a,b
where a.id(+)=b.id51Testing软件测试网#`-lZ7co
外联
2B`?1P@^0SELECT a.id,a.name,b.address
x7c ?#xW,p,of0FROM a,b
TB3O"Z!u0WHERE a.id = b.id(+)
)ig7u {(t:y0UNION51Testing软件测试网.uRoi}*c T
SELECT b.id,'' name,b.address
i cR:DL7c0FROM b
1LM-}A`m`&zb0WHERE NOT EXISTS (
y'nBwxe$md0SELECT * FROM a
Y7Ez ^h}X [Y0WHERE a.id = b.id);
在9i以上，已经开始支持SQL99标准，所以，以上语句可以写成：
OJ.v_-zD0默认内部联结：
&{l4b s4A0select a.id,a.name,b.address,c.subject
from (a inner join b on a.id=b.id)
inner join c on b.name = c.name
7e:j1HY Vw*F0where other_clause51Testing软件测试网6a.T8\!R#vl
左联51Testing软件测试网:X9X)f4l(?i^
select a.id,a.name,b.address
iEbV(^#z8H!z0from a left outer join b on a.id=b.id51Testing软件测试网3Wg-jk3s{A
where other_clause
G_9v:f3B~Z"^0右联
select a.id,a.name,b.address51Testing软件测试网2h'G^^F s3s
from a right outer join b on a.id=b.id51Testing软件测试网},B+AHT7J w
where other_clause
8K;ZV+I)L:Xo9i7d0外联
4b,i/V4BO9W zD0select a.id,a.name,b.address
/h {/f(Paf+]0from a full outer join b on a.id=b.id51Testing软件测试网:Sen I(U/Z'c
where other_clause
or
%k1_q3cb(}+ss0select a.id,a.name,b.address51Testing软件测试网p7{J-I7O
from a full outer join b using (id)
)P*U,kS"?u,C7~0where other_clause

lh0sT$k%t"Y?0[Q]怎么实现一条记录根据条件多表插入
9lV,]W)O#B6s0[A]9i以上可以通过Insert all语句完成，仅仅是一个语句，如：
7z-b-~ g_-C;Qz0INSERT ALL
WHEN (id=1) THEN51Testing软件测试网KzG%E"`zE
INTO table_1 (id, name)51Testing软件测试网0~,L;@ E,?rrB
values(id,name)51Testing软件测试网 u4T@0K![(Q
WHEN (id=2) THEN
PT9k;X-gR0INTO table_2 (id, name)
Bj/d4tl]LYJF%u0values(id,name)51Testing软件测试网b]9~k7K#p2X
ELSE51Testing软件测试网CT,IC+LX
INTO table_other (id, name)
3_#u+^r2_-k/W?,Z0values(id, name)51Testing软件测试网2{c)l;iA*jz
SELECT id,name
"qR kTY0FROM a;
如果没有条件的话，则完成每个表的插入，如
E$]}:{/u9G0INSERT ALL
INTO table_1 (id, name)
values(id,name)51Testing软件测试网;y5rd/ek
INTO table_2 (id, name)
b|c&a];O0values(id,name)51Testing软件测试网CKp3Sw6IH
INTO table_other (id, name)
values(id, name)
8sy0[+_.c:^0SELECT id,name
'a$s/kVAb3]0FROM a;51Testing软件测试网0C1sH@T-X1k

[Q]如何实现行列转换
T(R/](?T0[A]1、固定列数的行列转换
3j+T,x9?7{0如
-KY.z&`3H GnF0student subject grade
---------------------------51Testing软件测试网-^o,E/hi"k]
student1 语文 80
~-Jwy@Gz~4Trc0student1 数学 70
I\\2T[!}0student1 英语 60
sN:l0?N`+Q0student2 语文 90
6T.U,K J-sA0student2 数学 80
student2 英语 100
……
转换为51Testing软件测试网5E6LnL/{Bev
语文数学英语51Testing软件测试网e\/p'}Pz
student1 80 70 60
:aTER @O/T0student2 90 80 10051Testing软件测试网Hl"t9Kzpet
……
&Lw7\Pg0语句如下：51Testing软件测试网 \Ae?h[k
select student,sum(decode(subject,'语文', grade,null)) "语文",
6o]l6h_*loZq0sum(decode(subject,'数学', grade,null)) "数学",51Testing软件测试网xo~F s/}"xA
sum(decode(subject,'英语', grade,null)) "英语"
*Oe3P/G g-W?'b0from table51Testing软件测试网+g|HB$@%l1c
group by student51Testing软件测试网Tg9L3M&c7XJ
51Testing软件测试网'kx?kCf
2、不定列行列转换51Testing软件测试网vnK+Bq8{
如
c1 c2
R^|-X`S^&E0--------------
1 我
#C'o`-W*S9@g01 是51Testing软件测试网.fx%yKA0v e
1 谁
2 知
2 道
3L3n.j$I e0oT03 不51Testing软件测试网o(\NFW3tyP
……
转换为51Testing软件测试网U%r/][OyG(j
1 我是谁
)hk-Y;m!}-k]E6N02 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
#i&T,\.ff#eC3v+q0RETURN VARCHAR251Testing软件测试网Q.P~:be|{6z
IS51Testing软件测试网.B,lM#Y6q
Col_c2 VARCHAR2(4000);
jyPhvpV:CZ0BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
hQ(l6tV9f$N0END LOOP;
Col_c2 := rtrim(Col_c2,1);
iyOl&`;o0Pou0RETURN Col_c2;
ad9O#yj,d_2y0END;
Y]%q~9Xc3N8_0/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可51Testing软件测试网#NO&?'H+ar
51Testing软件测试网&d M#j9w{|
[Q]怎么样实现分组取前N条记录51Testing软件测试网M]jE9JP
[A]8i以上版本，利用分析函数
gV\n2b'`(Y9U#Q0如获取每个部门薪水前三名的员工或每个班成绩前三名的学生。
$X$?%}7r;`5S|0Select * from
%b7|4fb-y5f_0(select depno,ename,sal,row_number() over (partition by depno
order by sal desc) rn
/LL;h4Cx\|Ho0from emp)
z%NhHk%$s.M0where rn<=3
Mnx9Cc~)l0
'L*z.Q pAT!$_+p0[Q]怎么样把相邻记录合并到一条记录
!EQ!`^L0[A]8i以上版本，分析函数lag与lead可以提取后一条或前一天记录到本记录。
;b/yqH/w1g0Select deptno,ename,hiredate,lag(hiredate,1,null) over
(partition by deptno over by hiredate,ename) last_hire51Testing软件测试网AQs$Ng%|
from emp
order by depno,hiredate

v?X9?]LS0[Q]如何取得一列中第N大的值？
-^V(A(AjZ wpM0[A]select * from
&na7xau;lGF0(select t.*,dense_rank() over (order by t2 desc) rank from t)51Testing软件测试网*HH'CJL+j
where rank = &N;
y3W9~ ?&h0
"D{ `v R#Hv!B0[Q]怎么样把查询内容输出到文本
Od j*Bmoi4j0[A]a.控制语句，如set heading off
b.spool 完整文件名
c.查询语句
?l X#Pm1b2r0……
[(z\(y#P|R0d.spool off
V[*m s!leiA.s%s%Eh0
p$M;c?V0[Q]怎么设置存储过程的调用者权限
/B4?J8?J"~ f4]9s0[A]普通存储过程都是所有者权限，如果想设置调用者权限，请参考如下语句
BmTLg0iw}0create or replace
jF{t$x0procedure ……()
8\5e3CU+Cd6A+D0AUTHID CURRENT_USER51Testing软件测试网5Js@+U r
As
w7AH,X(C3?X8d"y]8E0begin
Oj0n3j*` ?yQ!g h2aTZ0……51Testing软件测试网 LRT^"qln
end;

[Q] 如何在SQL*PLUS环境中执行OS命令？
h-h^ };]h^h0[A] 比如进入了SQLPLUS，启动了数据库，忽然想起监听还没有启动，此时不用退出SQLPLUS，也不用另外起一个命令行窗口，直接输入：
SQL> host lsntctl start
或者unix/linux平台下51Testing软件测试网o8|^Pam[
SQL>!<OS command>
)K)O*?.SX(BkA0windows平台下51Testing软件测试网_uhq4T#G}vD
SQL>$<OS command>
.jz;n%rlCnGh0总结：HOST <OS command>可以直接执行OS命令。
+T*f)k MRh p^K0备注：cd命令无法正确执行。
T$N2o#G.QA+o0　

*K5x*l},E;kB3R;e3H|9u K0[Q]怎么快速获得用户下每个表或表分区的记录数
~'u`.U"^*F0[A]可以分析该用户，然后查询user_tables字典，或者采用如下脚本即可
Vn5?%O5i*]'@K0SET SERVEROUTPUT ON SIZE 20000
DECLARE51Testing软件测试网6G_M2FEq.V#UF
miCount INTEGER;
Mce5AD cN0|;O0BEGIN
7`#E~M7r|0FOR c_tab IN (SELECT table_name FROM user_tables) LOOP51Testing软件测试网Mtf:~J;c%A
EXECUTE IMMEDIATE 'select count(*) from "' || c_tab.table_name || '"' into miCount;
dbms_output.put_line(rpad(c_tab.table_name,30,'.') || lpad(miCount,10,'.'));
F f4[JS%V$Qp%M6\0--if it is partition table
SELECT COUNT(*) INTO miCount FROM User_Part_Tables WHERE table_name = c_tab.table_name;51Testing软件测试网"ihz:zU1z6R
IF miCount >0 THEN
JE+JU8IC;M0FOR c_part IN (SELECT partition_name FROM user_tab_partitions WHERE table_name = c_tab.table_name) LOOP
EXECUTE IMMEDIATE 'select count(*) from ' || c_tab.table_name || ' partition (' || c_part.partition_name || ')'51Testing软件测试网 h\"sQ3v

INTO miCount;51Testing软件测试网$e4y*T(k;|:t8J
dbms_output.put_line(' '||rpad(c_part.partition_name,30,'.') || lpad(miCount, 10,'.'));
:Y:C(v J"f[XDr ia1}1X0END LOOP;
END IF;51Testing软件测试网en9cG FB
END LOOP;51Testing软件测试网 q(cIXn9Mu
END;
:ekp1yj8kf&B051Testing软件测试网n;z#bE%J4X
[Q]怎么在Oracle中发邮件
qgI;F.Pm-a-S0[A]可以利用utl_smtp包发邮件，以下是一个发送简单邮件的例子程序51Testing软件测试网&gT\ e m8bcy
/****************************************************************************
wy1hDx:K0parameter: Rcpter in varchar2 接收者邮箱
}0ZK_;O H @0Mail_Content in Varchar2 邮件内容51Testing软件测试网8i` A_9d6r:m
desc: ·发送邮件到指定邮箱
#ts _N#~0·只能指定一个邮箱，如果需要发送到多个邮箱，需要另外的辅助程序
+U*aOdOBxf9C9E_0****************************************************************************/
[3Wlb@QJ"Z0CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
*S pU~%[0mail_content IN VARCHAR2)
IS
{1os6m3lXu \X0conn utl_smtp.connection;
}(O2`5QwE.m0--write title
F.B/BHJ+x0PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) AS51Testing软件测试网K bZ,e7cL
BEGIN
,S/[$W&h_$Ab u)E0utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
L4c/_#es9Ovi0END;
V;nJ K-n0BEGIN51Testing软件测试网&GcZfB2dZ
--opne connect
conn := utl_smtp.open_connection('smtp.com');51Testing软件测试网dV QIt&o {1P
utl_smtp.helo(conn, 'oracle');
3s }sf^q0utl_smtp.mail(conn, 'oracle info');
;M+o)Tm8P"FJI;L^0utl_smtp.rcpt(conn, Rcpter);
-Z5_)_%MN [^0utl_smtp.open_data(conn);
7Ic#LU/S8m2ZZ0--write title
EE*p)S]3Ai/D| v0send_header('From', 'Oracle Database');
send_header('To', '"Recipient" <'||rcpter||'>');
send_header('Subject', 'DB Info');
1^V#o w)T1J1n H)Sp0--write mail content
u$Jw&VX |M W)Z0utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);
l7K h OG(_5AnF0--close connect
V z#k2v7R5c/J0utl_smtp.close_data(conn);51Testing软件测试网 K'B/aY7PC;G`
utl_smtp.quit(conn);
+QK`6gy}3b9Ae]^+@j0EXCEPTION51Testing软件测试网e#g,_;\_t|^
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
?-n]Ot7Sd(t0BEGIN
utl_smtp.quit(conn);51Testing软件测试网9q/n(J9Sz lDL
EXCEPTION51Testing软件测试网.?2_OtI
WHEN OTHERS THEN
U$k e/FC-s5NdIz0NULL;51Testing软件测试网%\"cU(B4_5vC
END;
"\cu1n? ^/|2l0WHEN OTHERS THEN
[8Q:|}1F`;R)o0NULL;
END sp_send_mail;
]+X7f d4I.l~9XZ0

[Q]怎么样在Oracle中写操作系统文件，如写日志
[A]可以利用utl_file包，但是，在此之前，要注意设置好Utl_file_dir初始化参数
.` FQ*nNFZ0/**************************************************************************51Testing软件测试网9f.k$K^H,o
parameter:textContext in varchar2 日志内容
d2I"{zL9]%|F0desc: ·写日志,把内容记到服务器指定目录下
q&[&_ ^1Z%@0·必须配置Utl_file_dir初始化参数，并保证日志路径与Utl_file_dir路径一致或者是其中一个
:^z:t-ee un(N+Z&m0****************************************************************************/
-Z)odc#_.L?W:vSY@0CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)51Testing软件测试网y5Q;cZl k%i
IS51Testing软件测试网KF5w hab(y
file_handle utl_file.file_type;51Testing软件测试网7H|d PxCv
Write_content VARCHAR2(1024);
3B/kH6e#o0Write_file_name VARCHAR2(50);
7oBjz*?w0BEGIN51Testing软件测试网mPe^] g~
--open file51Testing软件测试网!O7TGV|
write_file_name := 'db_alert.log';51Testing软件测试网0i:]N7Ex&a.t
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');
O/k4i7k8A:skO0write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;
7K9c5ihrN#J:h2~0--write file
IF utl_file.is_open(file_handle) THEN
3@3J-}_O(GII0utl_file.put_line(file_handle,write_content);
u3ues Y)R%e{+_+t0END IF;
--close file
!u2PSC5Th_0utl_file.fclose(file_handle);51Testing软件测试网Z$M.U n,k3N
EXCEPTION
WHEN OTHERS THEN51Testing软件测试网7XbNUxB~j
BEGIN
1L)C Uc6UQ"X&Xe7k0IF utl_file.is_open(file_handle) THEN
k$]+?iy4~7t$F0utl_file.fclose(file_handle);
END IF;
Es1@*P.f0EXCEPTION
&Y\RhXRqS0WHEN OTHERS THEN51Testing软件测试网6H$VfG,dJTc
NULL;
Yw4_'u&uH4jb5a&c:L0END;
END sp_Write_log;