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:'
Ïà¹ØÎĵµ£º
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 ......
±¾ÎÄÀ´×Ôhttp://www.cnblogs.com/rootq/articles/1177562.html
Oracle Êý¾Ý¿âµÄ°ó¶¨±äÁ¿ÌØÐÔ¼°Ó¦ÓÃ
¹Ø¼ü´Ê:
°ó¶¨±äÁ¿(binding variable),¹²Ïí³Ø(shared buffer pool), SGA(system global area);
ÔÚ¿ª·¢Ò»¸öÊý¾Ý¿âϵͳǰ,ÓÐ˶ÔOracle ϵͳÁ˽âºÜ¶à,ÓÈÆäÊÇËüµÄÌØÐÔ,ºÃÏóºÜÉÙ°É;¶Ô³õѧÕßÀ´½²,Õâ¸üÊDz»¿ÉÄܵÄÊÂÇé;½ö½ö¼òµ ......
·ÖÎöº¯ÊýÓÃÓÚ¼ÆËã»ùÓÚ×éµÄ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 ......
1¡£»ù±¾¸ÅÄ
Êý¾Ý¿âÁ¬½Ó´®Ö÷ÒªÓÃÓÚ½¨Á¢¶ÔÔ¶³ÌÊý¾Ý¿âµÄ·ÃÎÊ·½·¨£¬¿ÉÒÔÖ±½Ó¶ÁȡԶ³ÌOracleµÄÊý¾Ý£¬»òÕßÖ±½ÓÐ޸ġ£Êý¾Ý¿âÁ¬½Ó´®¿ÉÒÔÊǹ«ÓÃÁ¬½ÓPUBLIC»òÕß˽ÓÐÁ¬½ÓPRIVATE¡£ÕâÒ»µãºÍͬÒå´ÊºÜÏàÏñ¡£
1¡£´´½¨Óï·¨£º
CREATE DATABASE LINK TEST CONNECT TO USERNAME IDENTIFIED BY PASSWORD
USING 'CONNECT_STRING ......