ÀûÓÃOracleµÄutl_smtp ·¢ËÍÓʼþ£¨¹«ÍøÓʼþºÍ·þÎñÆ÷£©
CREATE OR REPLACE PROCEDURE send_mail(
p_recipient VARCHAR2, -- Óʼþ½ÓÊÕÈË
p_subject VARCHAR2, -- Óʼþ±êÌâ
p_message VARCHAR2 -- ÓʼþÕýÎÄ
)
IS
--ÏÂÃæËĸö±äÁ¿Çë¸ù¾Ýʵ¼ÊÓʼþ·þÎñÆ÷½øÐи³Öµ
v_mailhost VARCHAR2(30) := 'smtp.163.com'; --SMTP·þÎñÆ÷µØÖ·
v_user VARCHAR2(30) := 'user'; --µÇ¼SMTP·þÎñÆ÷µÄÓû§Ãû£»Ö»ÊÇÓû§Ãû£¬²»°üÀ¨163.com²¿·Ö
v_pass VARCHAR2(20) := 'pass'; --µÇ¼SMTP·þÎñÆ÷µÄÃÜÂë
v_sender VARCHAR2(50) := 'user@xxx.com'; --·¢ËͶ¼ÓÊÏ䣬һ°ãÓë ps_user ¶ÔÓ¦
v_conn UTL_SMTP.connection; --µ½Óʼþ·þÎñÆ÷µÄÁ¬½Ó
v_msg varchar2(4000); --ÓʼþÄÚÈÝ
BEGIN
v_conn := UTL_SMTP.open_connection(v_mailhost, 25);
UTL_SMTP.ehlo(v_conn, v_mailhost); --ÊÇÓà ehlo() ¶ø²»ÊÇ helo() º¯Êý
--·ñÔò»á±¨£ºORA-29279: SMTP ÓÀ¾ÃÐÔ´íÎó: 503 5.5.2 Send hello first.
UTL_SMTP.command(v_conn, 'AUTH LOGIN'); -- smtp·þÎñÆ÷µÇ¼УÑé
UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_user))));
UTL_SMTP.command(v_conn,UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(v_pass))));
UTL_SMTP.mail(v_conn, '<' || v_sender || '>'); --ÉèÖ÷¢¼þÈË
UTL_SMTP.rcpt(v_conn, '<' || p_recipient || '>'); --ÉèÖÃÊÕ¼þÈË
&n
Ïà¹ØÎĵµ£º
Ö÷ÒªÊÇͨ¹ýreplaceºÍtranslateÀ´ÊµÏÖ
SQL> create view v_tt
2 as
3 select ename||' '||deptno as data
4 from emp;
View created.
SQL> select * from v_tt;
DATA
---------------------------------------------------
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
......
ROLLUPºÍCUBEÓï¾ä¡£
OracleµÄGROUP
BYÓï¾ä³ýÁË×î»ù±¾µÄÓï·¨Í⣬»¹Ö§³ÖROLLUPºÍCUBEÓï¾ä¡£Èç¹ûÊÇROLLUP(A, B, C)µÄ»°£¬Ê×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºó¶Ô(A¡¢B)½øÐÐGROUP BY£¬È»ºóÊÇ(A)½øÐÐGROUP BY£¬×îºó¶ÔÈ«±í½øÐÐGROUP BY²Ù×÷¡£Èç¹ûÊÇGROUP BY
CUBE(A, B, C)£¬ÔòÊ×ÏÈ»á¶Ô(A¡¢B¡¢C)½øÐÐGROUP
BY£¬È»ºóÒÀ´ÎÊÇ(A¡ ......
ÒÔǰ»¹×¨ÃÅС×ܽá¹ýÒ»ÏÂORACLEÖйØÓÚNULLµÄһЩÎÊÌ⣬ÅöÇɽñÌìÔÚ¿´ÊéµÄ¹ý³ÌÖÐÓÖ¿´µ½ÁËÁíÍâÒ»¸öÒÔǰû·¢ÏÖµÄÐèҪעÒâµÄµØ·½£¬ÄǾÍÊÇÔÚnot inÖÐʹÓÃnullµÄÎÊÌâ¡£
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- ---- ......
Ò».Êý¾Ý¿ØÖÆÓï¾ä (DML) ²¿·Ö
1.Insert (ÍùÊý¾Ý±íÀï²åÈë¼Ç¼µÄÓï¾ä)
Insert INTO ±íÃû(×Ö¶ÎÃû1, ×Ö¶ÎÃû2, ……) VALUES ( Öµ1, Öµ2, ……);
&nb ......