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

Oracle ¼¸¸ö¹Ø¼ü×ÖÀí½â

/*
--DML´¥·¢Æ÷ÀàÐÍ:
·ÖΪÓï¾ä¼¶´¥·¢Æ÷ºÍÐм¶´¥·¢Æ÷.
Óï¾ä¼¶´¥·¢Æ÷²»ÐèÒª¼Ófor each row¹Ø¼ü×Ö,²¢ÇÒ²»ÄÜʹÓÃ:new ºÍ :old¹Ø¼ü×Ö.¶ÔÕû¸ösqlÓï¾äÖ»Ö´ÐÐÒ»´Î´¥·¢Æ÷.
Ðм¶´¥·¢Æ÷ÐèÒª¼Ófor each row¹Ø¼ü×Ö,¿ÉÒÔʹÓÃ:newºÍ:old¹Ø¼ü×Ö,¶ÔÕû¸ösqlÓï¾äÓ°Ïìµ½µÄÿһÐж¼Ö´ÐÐÒ»´Î´¥·¢Æ÷.
--:newºÍ:old¹Ø¼ü×Ö×ܽá
:newÖ¸µÄÊǵ±Ç°DMLÓï¾ä²Ù×÷Ö®ºóÓ°ÏìµÄijһÐмǼµÄÒýÓÃ.
:oldÖ¸µÄÊǵ±Ç°DMLÓï¾ä²Ù×÷֮ǰӰÏìµÄijһÐмǼµÄÒýÓÃ.(×¢Òâ,:old²»ÄÜËæ±ã¸³Öµ).
DML´¥·¢Æ÷ÖÐ,²»¹ÜÊÇbefore»¹ÊÇafter´¥·¢,:new¡¢:old¶¼»á¸ù¾Ý²Ù×÷µÄ²»Í¬¶ø´æÔÚÖµ.
insert: :newÖдæÔÚÒªinsertµÄÖµ.
update: :newÖдæÔÚÒªupdateµÄÖµ,:old´æÔÚupdate֮ǰµÄÖµ.
delete: :oldÖдæÔÚÒªdeleteµÄÖµ.
--:newºÍ:oldС¼¼ÇÉ
ÔÚbeforeµÄinsertºÍupdate²Ù×÷ÖÐÊÇ¿ÉÒÔͨ¹ý:newÌáǰ¸üÐÂÒª²Ù×÷ÐеÄÊý¾Ý,ÈÃinsert ºÍupdate¸üеÄÊý¾ÝΪ´¥·¢Æ÷Êý,ÆäËûµÄÔò²»ÐÐ.
:oldÔÚ´¥·¢Æ÷ÖÐÊDz»Äܸ³ÖµµÄ. :old.test01 := '123'; --ÕâÑù»á±¨´í.
*/
--Ìí¼Ó»òɾ³ý»òÐÞ¸ÄÒÔǰ´¥·¢
create or replace trigger test_biud
before insert or update or delete on test
for each row
begin
    if inserting then
        :new.test01 := '01';
        :new.test02 := '02';
        :new.test03 := '03';
    elsif updating then
        :new.test01 := '001';
        :new.test02 := '002';
        :new.test03 := '003';
    elsif deleting then
       dbms_output.put_line('asd');
    end if;
end;
drop trigger test_biud;
--Ìí¼Ó»òɾ³ý»òÐÞ¸ÄÒÔºó´¥·¢
create or replace trigger test_aiud
after insert or update or delete on test
for each row
begin
    if inserting then
        dbms_output.put_line('new:' ||  :new.test01 || '   old:' || :old.test01);
    elsif updating then
        dbms_output.put_line('new:'


Ïà¹ØÎĵµ£º

²é¿´oracleÊý¾Ý¿â±íÐÅÏ¢

1. ²é¿´±í¿Õ¼äµÄÃû³Æ¼°´óС
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
2¡¢²é¿´±í¿Õ¼äÎïÀíÎļþµÄÃû³Æ¼°´óС
select tablespace_name, file_id, file_name,
round(byte ......

oracle——·ÖÎöº¯ÊýOVER ()

·ÖÎöº¯ÊýÓÃÓÚ¼ÆËã»ùÓÚ×éµÄijÖÖ¾ÛºÏÖµ£¬ËüºÍ¾ÛºÏº¯ÊýµÄ²»Í¬Ö®´¦ÊÇ
¶ÔÓÚÿ¸ö×é·µ»Ø¶àÐУ¬¶ø¾ÛºÏº¯Êý¶ÔÓÚÿ¸ö×éÖ»·µ»ØÒ»ÐС£
SQL> select t.empno,t.ename,sum(t.sal)
2 from emp t;
select t.empno,t.ename,sum(t.sal)
*
ERROR at line 1:
ORA-00937: not a single-group group function
SQL> selec ......

oracle ²ð·Ö×Ö·û´®

×î½ü´ÓExcelÖе½ÁËһЩÊý¾Ý£¬½á¹ûÒ»¸öÁеÄÊý¾ÝÊÇÒÔ¶ººÅ·Ö¸ôµÄ×Ö·û´®£¬Ïë°ÑËü²ð·Ö¿ªÕÒµ½ÁËϱߵķ½·¨£¬Ìù³öÀ´·ÖÏíһϣº
CREATE   OR   REPLACE   PROCEDURE   sptstr   (srcstr   varchar2)   as  
      stmp   varchar2(2000);  
  begin &n ......

Oracle¶¯Ì¬ÐÔÄÜÊÓͼѧϰ֮ V$ROLLSTAT

À´Ô´ÓÚhttp://hi.baidu.com/edeed/blog/item/33576327d1b73d00918f9dd4.html
±¾ÊÓͼ×ÔÆô¶¯¼´±£³Ö²¢¼Ç¼¸÷»Ø¹ö¶Îͳ¼ÆÏî¡£ÔÚѧϰ±¾ÊÓͼ֮ǰ£¬ÎÒÃÇÏÈÀ´Á˽âһϻعö¶Î(rollback segment)µÄÏà¹Ø¸ÅÄ
»Ø¹ö¶Î¸ÅÊö
»Ø¹ö¶ÎÓÃÓÚ´æ·ÅÊý¾ÝÐÞ¸Ä֮ǰµÄÖµ£¨°üÀ¨Êý¾ÝÐÞ¸Ä֮ǰµÄλÖúÍÖµ£©¡£»Ø¹ö¶ÎµÄÍ·²¿°üº¬ÕýÔÚʹÓõĸûعö¶ÎÊÂÎñµÄÐ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ