(原创)Oracle编程技巧点滴

` iKNd*QU9o9p0        前段时间发现自己的ORACLE实在懂的太少，除了写后台存储过程，触发器，动态试图等都是一些基本运算与查找，Linux宝库7c'_YC
k;Px
也就没接触到别的ORACLE开发方面的知识，在有空的时间进行了一次恶补，这也是本人最近所研究的一些点滴。希望Linux宝库*I${/R9_ Nqt(HI
在大家开发ORACLE中能带来一定的参考。Linux宝库&]Pme/N;qf3V"C)U
   (一)以前记得如果把一个一对多的关系记录集放到一个临时表或者是具体的业务表中，会怎么去做？一般情况下就Linux宝库C a/b.eN0b"D
是写一个FOR循环下将记录放到表中，不能直接把这个记录集放到这个表中，所有这样的效率很低。解决办法就是使用Linux宝库Z3x+j BWB"~ekN
INSERT ALL.
c\ [k0\?G#{0    比如现在有一个员工表（A(employee_id,employee_name,department_id,addr,.....)）,一个部门表（B(department_id,
'Zh)WFy0department_name,......)）,现在要求将部门名称为"事业部"下的所有员工都存储到一个临时表（C(employee_id,employee_name,
;[zJfK_/{j0department_name)）中，这时可以这样来执行Linux宝库)e0m!f3D&u$zT#P
[sql]Linux宝库A'W"xO f]w
insert all whenLinux宝库&FW#j_y
  (department_name = '事业部') then into C
Hv[DH"X#rx0valuesLinux宝库1uCx
WK:R
  (department_id, employee_name, department_name)
,@\[/W$_t1R9P6R!a0  select A.department_id, A.employee_name, B.department_name
,F7Q.n$us@?O?0    from A, B
tr }7C8IHZ1n0   where a.department_id = b.department_id
8`5w yaz,r0     and B. department_name = '事业部';
A |*_0H/~G$VwhF"M0[/sql]
6u#t'?v(_N0注：这里的VALUES顺序一定要和表C的字段顺序一样，否则将会出错。Linux宝库3}E7ocO|
    (二)在ORACLE中类似C/C++操作文件方法。
lX6E#di%B [ O^R0/**************************************************************************
:EK$Pnz-@Z0parameter:textContext in varchar2 日志内容
}r~N"qB0desc: ·写日志,把内容记到服务器指定目录下
Up*H%fvQ _0·必须配置Utl_file_dir初始化参数，并保证日志路径与Utl_file_dir路径一致或者是其中一个
/HYtx:]D0G!p0****************************************************************************/Linux宝库jS$zr#h6iZP
CREATE OR REPLACE PROCEDURE sp_Write_log(text_context VARCHAR2)
8uD5Hg~D _0o0ISLinux宝库6T v3Rio0p4I
file_handle utl_file.file_type;
"lB`a/qJT6[0Write_content VARCHAR2(1024);Linux宝库sR e5D]S\
Write_file_name VARCHAR2(50);Linux宝库W*b%kX]K i)aW
BEGIN

T"jf| V'z!h#j+_0--open fileLinux宝库[:o k1[m8lw
write_file_name := 'db_alert.log';Linux宝库I]4d~(i(dd^f"d[
file_handle := utl_file.fopen('/u01/logs',write_file_name,'a');Linux宝库6D NSLZoj#v"B%@
write_content := to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss')||'||'||text_context;Linux宝库x q]WT"PDh3S
--write fileLinux宝库y:U$j%| {8eu6q9Q?
IF utl_file.is_open(file_handle) THEN
$Sb4Vm#}0utl_file.put_line(file_handle,write_content);Linux宝库0fCc]$xpa:cu
END IF;Linux宝库rD A'HB'mB:l^X2U:?
--close fileLinux宝库.C6\zG|!P4s je
utl_file.fclose(file_handle);
Qy"l"EV4s0EXCEPTION
&|3C vZtD%@0WHEN OTHERS THEN
G@T~7J&L"L0BEGINLinux宝库 Xc
n%O`$Gv
IF utl_file.is_open(file_handle) THEN
!m/b-yX@[8G,g0utl_file.fclose(file_handle);
4uA6tZq l5\0END IF;Linux宝库 E c2OT@+w*{
EXCEPTIONLinux宝库M!V8T3e2gD!v HO'DM
WHEN OTHERS THENLinux宝库%hS/B:E*r0\T8m
NULL;
4~,u-s+jz0END;
G1M F yo7j]0END sp_Write_log;Linux宝库Up!s0Pr'@X
    (三)用ORALCE来发送邮件
8Y&e"oo^\)Y0/****************************************************************************
2MG/M!VQy,NY6{0parameter: Rcpter in varchar2 接收者邮箱
NgC,@;n9N0\&] m,yp0Mail_Content in Varchar2 邮件内容
B;\V*_@Dr+W5G0desc: ·发送邮件到指定邮箱Linux宝库kd;?Rr}
·只能指定一个邮箱，如果需要发送到多个邮箱，需要另外的辅助程序
F3ROnX(o*r0****************************************************************************/Linux宝库'S5g\kyd F
CREATE OR REPLACE PROCEDURE sp_send_mail( rcpter IN VARCHAR2,
RDo9eIub(c0mail_content IN VARCHAR2)Linux宝库X7W&E9}(|\YX
ISLinux宝库-U'E8{X%^ {YdV
m
conn utl_smtp.connection;Linux宝库'I&vZ/Ji
--write titleLinux宝库2{nxR5i.wSo
PROCEDURE send_header(NAME IN VARCHAR2, HEADER IN VARCHAR2) ASLinux宝库_YB(`gFH0v+U%e7? h'R
BEGIN
D4Qj~W Lpo0utl_smtp.write_data(conn, NAME||': '|| HEADER||utl_tcp.CRLF);
[6Lf P-Pt gN0END;Linux宝库N;V?bXS6TIb
BEGIN
s2bZR4NGB0--opne connectLinux宝库 d,J-z X.\7^
conn := utl_smtp.open_connection('smtp.com');Linux宝库
d-b$qB Xzq,}I
utl_smtp.helo(conn, 'oracle');Linux宝库u4UvivA)?
utl_smtp.mail(conn, 'oracle info');
sR_g:W1z
eKp0utl_smtp.rcpt(conn, Rcpter);
\Tc8egnr&P8p0utl_smtp.open_data(conn);Linux宝库l/R-O[WQR
--write title
;^9M\'R%Gf/O9j0send_header('From', 'Oracle Database');
Y2eSU'i6S0send_header('To', '"Recipient" <'||rcpter||'>');
2\)pJ^zz5W2O0send_header('Subject', 'DB Info');
)aD}I^i S0--write mail contentLinux宝库e$Gx;vz5XhGR
utl_smtp.write_data(conn, utl_tcp.crlf || mail_content);Linux宝库%W'uA3[m@']&c
--close connectLinux宝库M],?)w4Z
utl_smtp.close_data(conn);Linux宝库}MSGtML6SWO,k
utl_smtp.quit(conn);Linux宝库}n*E,Y5Mv5p
EXCEPTION
|8`q;@o`P A,~0WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
mE7R6h*A0BEGIN
G L;Ws#o| dH0utl_smtp.quit(conn);
+u3^'pwY.EI9EFe0EXCEPTIONLinux宝库fet.G!p
WHEN OTHERS THEN
Kc#B}&I%O)|@f4h0NULL;
s{q8a3H:O:k0END;
)z/Q4R0`Z0DF0WHEN OTHERS THENLinux宝库{5]D5\V,Jx2f
NULL;Linux宝库2h2E["\6NS
END sp_send_mail;

Linux宝库#X@(@#\6sw$O
   Linux宝库`9^*w1L3V w