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 ......
¡¡¡¡OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏ൱ÓÚoracleÊý¾Ý»¹ÔÓ뱸·Ý¡£expÃüÁî¿ÉÒÔ°ÑÊý¾Ý´ÓÔ¶³ÌÊý¾Ý¿â·þÎñÆ÷µ¼³öµ½±¾µØµÄdmpÎļþ£¬impÃüÁî¿ÉÒÔ°ÑdmpÎļþ´Ó±¾µØµ¼Èëµ½Ô¶´¦µÄÊý¾Ý¿â·þÎñÆ÷ÖС£ ÀûÓÃÕâ¸ö¹¦ÄÜ¿ÉÒÔ¹¹½¨Á½¸öÏàͬµÄÊý¾Ý¿â£¬Ò»¸öÓÃÀ´²âÊÔ£¬Ò»¸öÓÃÀ´ÕýʽʹÓÃ……
¡¡¡¡OracleÊý¾Ýµ¼Èëµ¼³öimp/exp¾ÍÏàµ±Ó ......
Êý¾Ý¿âÃû(DB_NAME)¡¢ÊµÀýÃû(Instance_name)¡¢ÒÔ¼°²Ù×÷ϵͳ»·¾³±äÁ¿(ORACLE_SID)
ÔÚORACLE7¡¢8Êý¾Ý¿âÖÐÖ»ÓÐÊý¾Ý¿âÃû(db_name)ºÍÊý¾Ý¿âʵÀýÃû(instance_name)¡£ÔÚORACLE8i¡¢9iÖгöÏÖÁËеIJÎÊý£¬¼´Êý¾Ý¿âÓòÃû(db_domain)¡¢·þÎñÃû(service_name)¡¢ÒÔ¼°²Ù×÷ϵͳ»·¾³±äÁ¿(ORACLE_SID)¡£ÕâЩ¶¼´æÔÚÓÚͬһ¸öÊý¾Ý¿âÖеıêʶ£¬ÓÃÓÚÇ ......
1¡£»ù±¾¸ÅÄ
Êý¾Ý¿âÁ¬½Ó´®Ö÷ÒªÓÃÓÚ½¨Á¢¶ÔÔ¶³ÌÊý¾Ý¿âµÄ·ÃÎÊ·½·¨£¬¿ÉÒÔÖ±½Ó¶ÁÈ¡Ô¶³ÌOracleµÄÊý¾Ý£¬»òÕßÖ±½ÓÐ޸ġ£Êý¾Ý¿âÁ¬½Ó´®¿ÉÒÔÊǹ«ÓÃÁ¬½ÓPUBLIC»òÕß˽ÓÐÁ¬½ÓPRIVATE¡£ÕâÒ»µãºÍͬÒå´ÊºÜÏàÏñ¡£
1¡£´´½¨Óï·¨£º
CREATE DATABASE LINK TEST CONNECT TO USERNAME IDENTIFIED BY PASSWORD
USING 'CONNECT_STRING ......
1.LOWER(str) Ç¿ÖÆСд
2.UPPER(str) Ç¿ÖÆ´óд
3.INITCAP(str) ÿ¸öµ¥´ÊÊ××Öĸ´óд
ʾÀý£º
SQL> select initcap('my_boy') from dual; --·µ»Ø"My_Boy"
×¢Ò⣺µ¥´ÊÖ®¼äÓÃÏ»®Ïߣ¨"_"£©·Ö¸î
4.CONCAT(str1,str2£©Á¬½Óº¯Êý,Á¬½Óstr1ºÍstr2×Ö·û´®
5.SUBSTR(string,a[,b])·µ»ØstringµÄÒ»²¿·Ö£¬aºÍbÒÔ×Ö·ûΪµ¥Î»¡£´Ó× ......