Ò׽ؽØͼÈí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

±±´óÇàÄñoracleѧϰ±Ê¼Ç26 27 28

Êý¾Ý¿â´¥·¢Æ÷
´¥·¢Æ÷Óï¾ä
    Öƶ¨´¥·¢Æ÷¶¨Ê±¡¢Ê¼þ¡¢±íÃû¼°ÀàÐÍ
´¥·¢Æ÷Ö÷Ìå
    ÊÇpl/sql¿ì»ò¶Ô¹ý³ÌµÄµ÷ÓÃ
´¥·¢Æ÷ÏÞÖÆ
    ¿ÉÒÔͨ¹ýwhen×Ó¾äʵÏÖ
DML(insert update delete)
DDL(create alter drop)
Êý¾Ý¿â²Ù×÷(servererror logon logoff startup shutdown)

create trigger ´¥·¢Æ÷Ãû
before|after insert|delete|update of ÁÐÃû
on ±íÃû
[for each row]  --Ðм¶´¥·¢Æ÷
when Ìõ¼þ
´¥·¢Æ÷ν´Ê
inserting insert²Ù×÷
updating update²Ù×÷
deleting delete²Ù×÷
¿ÉÒÔ¸ù¾ÝÕâÈý¸öν´ÊÅжϵ½µ×ÔÚÖ´ÐÐÄĸö²Ù×÷
create or replace
Trigger tg_insert
before insert or update on student
for each row
begin
  if updating then
    dbms_output.put_line('before update');
  end if;
  if inserting then
    dbms_output.put_line('before insert');
  end if;
end;
´¥·¢Æ÷ÀàÐÍ£º   
1¡¢ ±í¼¶´¥·¢Æ÷£¨±í¼¶´¥·¢Æ÷¶Ôÿ¸öDMLÓï¾äÖ´ÐÐÒ»´Î)
create or replace Trigger tg_insert
before update on student
begin
  dbms_output.put_line('before update);
end;
4 rows updated
before update
2¡¢ Ðд¥·¢Æ÷ (Ðм¶´¥·¢Æ÷¶ÔDMLÓï¾äÓ°ÏìµÄÿ¸öÐÐÖ´ÐÐÒ»´Î)
create or replace
Trigger tg_insert
before update on student
for each row  --ÿÐд¥·¢Ò»´Î
begin
  dbms_output.put_line('before update');
end;
4 rows updated
before update
before update
before update
before update
ʹÓô¥·¢Æ÷ʱ£¬Á½¸öÌØÊâÖµ:new вåÈëµÄÖµ :old Ô­À´µÄÖµ  ²»ÄÜÓÃÓÚ±í¼¶´¥·¢Æ÷

 create or replace trigger trig_job
before insert or update of job
on emp
for each row
begin
if inserting then
:new.job:=upper(:new.job);
else
:new.job:=upper(:new.job);
end if;
end;
3¡¢INSTEAD OF´¥·¢(´Ë´¥·¢Æ÷ÊÇÔÚÊÓͼÉ϶ø²»ÊÇÔÚ±íÉ϶¨ÒåµÄ´¥·¢Æ÷,ËüÊÇÓÃÀ´Ìæ


Ïà¹ØÎĵµ£º

oracle¼àÌý¼ÓÃÜ ¸ü°²È«

ºÜ¶àÈ˶¼ÖªµÀ£¬OracleµÄ¼àÌýÆ÷Ò»Ö±´æÔÚ×ÅÒ»¸ö°²È«Òþ»¼£¬¼ÙÈç²»ÉèÖð²È«´ëÊ©£¬ÄÇôÄܹ»·ÃÎʵÄÓû§¾Í¿ÉÒÔÔ¶³Ì¹Ø±Õ¼àÌýÆ÷¡£
Ïà¹ØʾÀý£º
D:\>lsnrctl stop eygle
LSNRCTL for 32-bit Windows: Version 10.2.0.3.0 - Production on 28-11ÔÂ-2007 10:02:40
Copyright (c) 1991, 2006, Oracle. All rights reserved.
......

ORACLEÐÔÄÜÕï¶ÏʵÀý ÐÐǨÒƺÍÐÐÁ´½Ó

ÃÀ¹úPVH DB´æÔÚÐÐÁ´½Ó.ͨ¹ýÎҵļì²é,·¢ÏÖ,ÎÒÃǵÄDBͬÑù´æÔÚÐÐǨÒƺÍÐÐÁ´½Ó.ORACLEµÄdbms_stats.gather_schema_statsÖ»»áÊÕ¼¯ÓÅ»¯Æ÷ͳ¼ÆÐÅÏ¢,²»»á¼ì²â±íµÄ¼Ç¼ÊÇ·ñ´æÔÚÐÐǨÒƺÍÐÐÁ´½Ó.(ÎÒÔ­Ïȱ»Õâ¸ö°ü¸øÆ­ÁË,ËùÒÔÖ´ÐÐÍêdbms_stats.gather_schema_statsºóÒ»²éÊý¾Ý×Öµä:USER_TABLES,ÆäÖеÄ×Ö¶Îchain_cntֵȫΪ0).Òª¼ì²â±íµÄ¼Ç ......

oracle link

Á©Ì¨²»Í¬µÄÊý¾Ý¿â·þÎñÆ÷£¬´Óһ̨Êý¾Ý¿â·þÎñÆ÷µÄÒ»¸öÓû§¶ÁÈ¡Áíһ̨Êý¾Ý¿â·þÎñÆ÷ϵÄij¸öÓû§µÄÊý¾Ý£¬Õâ¸öʱºò¿ÉÒÔʹÓÃdblink¡£
     ÆäʵdblinkºÍÊý¾Ý¿âÖеÄview²î²»¶à£¬½¨dblinkµÄʱºòÐèÒªÖªµÀ´ý¶ÁÈ¡Êý¾Ý¿âµÄipµØÖ·£¬ssidÒÔ¼°Êý¾Ý¿âÓû§ÃûºÍÃÜÂë¡£
       ´´½¨¿ÉÒÔ ......

±±´óÇàÄñoracleѧϰ±Ê¼Ç25

¹ý³ÌÖеÄÊÂÎñ
¶¨Òå¹ý³Ìp1
create or replace procedure p1
as
begin
insert into student values(5,'xdh','m',sysdate);
rollback;
end;
¶¨Òå¹ý³Ìp2
create or replace procedure p2
as
begin
update student set stu_sex = 'a' where stu_id = 3;
p1;
end;
Ö´Ðйý³Ìp2

exec p2;
Ö´ÐÐÍê±Ï·¢ÏÖ ......

¹ØÓÚplsqlÖеÄdefine±äÁ¿ÒÔ¼°Oracle±äÁ¿·ÖÀàС½á

¹ØÓÚplsqlÖеÄdefine±äÁ¿ÒÔ¼°Oracle±äÁ¿·ÖÀàС½á
2009-07-29 15:18
ÏȼÇÔظÕÀ§ÈÅÎÒµÄÒ»¸öÎÊÌ⣬×î½üѧϰplsql£¬ÓÉÓÚËùÓÃѧϰÊé¼®ºóÃæÌṩÌâÄ¿³£Óõ½define±äÁ¿£¬µ«ÓÉÓÚÕâÒ»±äÁ¿µÄʹÓÃÌØÊâÐÔ£¬×Ô¼º±ãѰ˼ÕâÒ»±äÁ¿ËùÊéÀà±ð£¬OracleÌṩµÄ±äÁ¿·ÖÀ๲ÓÐËÄÀࣺ
1£©±êÁ¿£¨scalar£©ÀàÐÍ
2£©¸´ºÏ£¨composite£©ÀàÐÍ
3£©²ÎÕÕ£¨re ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØͼ | ¸ÓICP±¸09004571ºÅ